Excel VBA Research & Evidence-Based Practice 3 — Questions and Answers
Question 1: A researcher wants to log each macro run with a timestamp for audit trails. Which VBA function returns the current date and time?
- Date()
- Now() (Correct answer)
- Time()
- DateValue()
Correct answer: Now()
Now() returns a Date value combining both the current date and time, suitable for timestamping audit log entries.
Question 2: To protect research data integrity, a VBA macro should write results to a new sheet rather than overwrite source data. Which code adds a new worksheet?
- Worksheets.New
- Sheets.Add (Correct answer)
- Workbook.AddSheet
- Worksheets.Insert
Correct answer: Sheets.Add
Sheets.Add inserts a new worksheet into the active workbook, preserving source data on the original sheet.
Question 3: Which VBA error handling structure should wrap data analysis code to catch runtime errors without crashing the macro?
- Try...Catch...Finally
- On Error GoTo ErrorHandler (Correct answer)
- If Err Then
- Resume On Error
Correct answer: On Error GoTo ErrorHandler
On Error GoTo ErrorHandler redirects execution to a labeled error-handling block when a runtime error occurs.
Question 4: When exporting research results to a new workbook for distribution, which VBA method saves it without displaying a dialog?
- Workbook.SaveAs filename, FileFormat:=xlOpenXMLWorkbook (Correct answer)
- Workbook.Save
- Workbook.Export filename
- Application.Save filename
Correct answer: Workbook.SaveAs filename, FileFormat:=xlOpenXMLWorkbook
Workbook.SaveAs with a filename and FileFormat argument saves programmatically without prompting the user.
Question 5: A researcher's VBA macro must skip blank rows in a dataset. Which condition correctly identifies a blank cell in column A at row i?
- Cells(i,1).Value = 0
- Cells(i,1).Value = "" (Correct answer)
- IsBlank(Cells(i,1))
- Cells(i,1) = Null
Correct answer: Cells(i,1).Value = ""
Comparing a cell's Value to an empty string "" correctly identifies cells that contain no data.
Question 6: To analyze research survey data across multiple worksheets with identical structure, which VBA technique efficiently loops through all sheets?
- For i = 1 To 10
- For Each ws In ThisWorkbook.Worksheets (Correct answer)
- Do While ActiveSheet.Next
- Loop Through Sheets.Count
Correct answer: For Each ws In ThisWorkbook.Worksheets
For Each ws In ThisWorkbook.Worksheets iterates every worksheet regardless of how many exist in the workbook.
Question 7: Which built-in VBA constant represents the last used cell in a column when finding the end of a research dataset?
- xlLastCell
- xlDown (Correct answer)
- xlEnd
- xlCellTypeLast
Correct answer: xlDown
Cells(Rows.Count,1).End(xlUp) uses the xlUp direction constant to navigate from the bottom to the last filled cell.
Excel VBA Research & Evidence-Based Practice 3
A researcher wants to log each macro run with a timestamp for audit trails.
Which VBA function returns the current date and time?