Research & Evidence-Based Practice Flashcards

A VBA macro must calculate the standard deviation of a research sample (not population). Which WorksheetFunction should be used?

Answer: StDev

WorksheetFunction.StDev calculates sample standard deviation (n-1), appropriate for sample-based research data.

When running a VBA analysis macro on a large research dataset, which property should be set to False to improve performance?

Answer: Application.ScreenUpdating

Setting Application.ScreenUpdating = False prevents Excel from refreshing the display during the macro, significantly speeding up execution.

To store intermediate research calculation results efficiently in memory without writing to the worksheet, a VBA developer should use a:

Answer: Module-level variable array

A module-level variable array holds data in RAM for fast access during computation, avoiding repeated slow worksheet reads.

Which VBA statement correctly reads all values from a worksheet range into an array for batch processing?

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.

A researcher needs to find the correlation coefficient between two data columns using VBA. Which WorksheetFunction provides this?

Answer: WorksheetFunction.Correl

WorksheetFunction.Correl(array1, array2) returns the Pearson correlation coefficient between two data arrays.

To generate a reproducible random sample from a research dataset using VBA, what must be done before calling Rnd()?

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.

Which method allows a VBA macro to run a SQL query against an Excel table as a data source for research analysis?

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.