Excel VBA Research & Evidence-Based Practice 2 — Questions and Answers
Question 1: Which VBA function returns the number of cells in a range that meet a specific criterion, useful for counting occurrences in research data?
- CountA
- CountIf (Correct answer)
- SumIf
- CountBlank
Correct answer: CountIf
WorksheetFunction.CountIf counts cells matching a criterion, making it ideal for frequency analysis in research datasets.
Question 2: When automating data import from multiple CSV research files, which VBA method opens a text file for reading?
- Open ... For Output
- Open ... For Input (Correct answer)
- Open ... For Append
- Open ... For Binary
Correct answer: Open ... For Input
Open filename For Input As #n opens a text file in read mode so VBA can parse CSV research data line by line.
Question 3: A researcher needs to remove duplicate survey responses automatically. Which Excel object method achieves this in VBA?
- Range.Sort
- ListObject.DataBodyRange.RemoveDuplicates
- Range.RemoveDuplicates (Correct answer)
- Worksheet.Consolidate
Correct answer: Range.RemoveDuplicates
Range.RemoveDuplicates removes duplicate rows based on specified columns directly from a VBA procedure.
Question 4: To validate that all entries in a research dataset column are numeric before analysis, which VBA function should you use?
- IsDate
- IsEmpty
- IsNumeric (Correct answer)
- IsNull
Correct answer: IsNumeric
IsNumeric returns True if the argument can be evaluated as a number, allowing pre-analysis data validation.
Question 5: Which VBA approach is best for iterating through an unknown number of data rows in a research spreadsheet?
- For i = 1 To 1000
- Do While Cells(i,1) <> "" (Correct answer)
- For Each cell In Range("A:A")
- While i < 500
Correct answer: Do While Cells(i,1) <> ""
Do While Cells(i,1) <> "" dynamically stops at the last row of data without hardcoding row counts.
Question 6: A VBA macro needs to calculate the average of a filtered research dataset. Which method correctly averages only visible cells?
- Application.WorksheetFunction.Average
- Application.WorksheetFunction.Subtotal(1, range)
- Range.SpecialCells(xlCellTypeVisible).Average (Correct answer)
- Selection.Average
Correct answer: Range.SpecialCells(xlCellTypeVisible).Average
Range.SpecialCells(xlCellTypeVisible) returns only the visible (non-filtered) cells, and their numeric average can then be computed.
Question 7: When using VBA to sort research data by multiple columns, what is the correct order of Sort key parameters?
- Key3, Key2, Key1
- Key1, Key2, Key3 (Correct answer)
- SortField1, SortField2, SortField3
- PrimaryKey, SecondaryKey, TertiaryKey
Correct answer: Key1, Key2, Key3
Range.Sort accepts Key1 as the primary sort column, Key2 as secondary, and Key3 as tertiary, applied in that order.
Excel VBA Research & Evidence-Based Practice 2
Which VBA function returns the number of cells in a range that meet a specific criterion, useful for counting occurrences in research data?