Excel VBA Research & Evidence-Based Practice 4 — Questions and Answers
Question 1: A VBA macro must calculate the standard deviation of a research sample (not population). Which WorksheetFunction should be used?
- StDev (Correct answer)
- StDevP
- Var
- Average
Correct answer: StDev
WorksheetFunction.StDev calculates sample standard deviation (n-1), appropriate for sample-based research data.
Question 2: When running a VBA analysis macro on a large research dataset, which property should be set to False to improve performance?
- Application.EnableEvents
- Application.ScreenUpdating (Correct answer)
- Application.DisplayAlerts
- Application.Calculation
Correct answer: Application.ScreenUpdating
Setting Application.ScreenUpdating = False prevents Excel from refreshing the display during the macro, significantly speeding up execution.
Question 3: To store intermediate research calculation results efficiently in memory without writing to the worksheet, a VBA developer should use a:
- Named Range
- Collection object
- Module-level variable array (Correct answer)
- Comment
Correct answer: Module-level variable array
A module-level variable array holds data in RAM for fast access during computation, avoiding repeated slow worksheet reads.
Question 4: Which VBA statement correctly reads all values from a worksheet range into an array for batch processing?
- Dim arr() As Variant: arr = Range("A1:A100").Value (Correct answer)
- Dim arr(100): arr.Load Range("A1:A100")
- arr = Cells(1,1).Resize(100).Array
- ReDim arr = Range("A1:A100")
Correct answer: Dim arr() As Variant: arr = Range("A1:A100").Value
Assigning a multi-cell Range's .Value to a Variant array loads all values at once, far faster than cell-by-cell access.
Question 5: A researcher needs to find the correlation coefficient between two data columns using VBA. Which WorksheetFunction provides this?
- WorksheetFunction.Correl (Correct answer)
- WorksheetFunction.Pearson
- WorksheetFunction.RSquared
- WorksheetFunction.Covariance
Correct answer: WorksheetFunction.Correl
WorksheetFunction.Correl(array1, array2) returns the Pearson correlation coefficient between two data arrays.
Question 6: To generate a reproducible random sample from a research dataset using VBA, what must be done before calling Rnd()?
- Set Randomize = True
- Call Randomize(seed)
- Randomize with a fixed seed value (Correct answer)
- Rnd(-1)
Correct answer: Randomize with a fixed seed value
Calling Randomize with a fixed numeric seed initializes the random number generator to produce the same sequence each run.
Question 7: Which method allows a VBA macro to run a SQL query against an Excel table as a data source for research analysis?
- ADODB.Connection with Provider=Microsoft.ACE.OLEDB (Correct answer)
- Workbook.RunSQL
- Range.ExecuteSQL
- Application.DBQuery
Correct answer: ADODB.Connection with Provider=Microsoft.ACE.OLEDB
An ADODB.Connection using the ACE.OLEDB provider enables SQL SELECT queries directly against Excel sheets or tables.
Excel VBA Research & Evidence-Based Practice 4
A VBA macro must calculate the standard deviation of a research sample (not population).
Which WorksheetFunction should be used?