Excel VBA Research & Evidence-Based Practice 5 — Questions and Answers
Question 1: A VBA procedure must document its analysis steps in a log sheet. Which approach writes text to the next available row automatically?
- Cells(1,1).Value = log
- Cells(Rows.Count,1).End(xlUp).Offset(1,0).Value = log (Correct answer)
- Range("A:A").Last.Value = log
- ActiveCell.Value = log
Correct answer: Cells(Rows.Count,1).End(xlUp).Offset(1,0).Value = log
Cells(Rows.Count,1).End(xlUp).Offset(1,0) navigates to the last filled cell and offsets one row down to append a new log entry.
Question 2: When scraping web-based research data into Excel via VBA, which object is used to make HTTP requests?
- Scripting.FileSystemObject
- MSXML2.XMLHTTP (Correct answer)
- WScript.Shell
- InternetExplorer.Application
Correct answer: MSXML2.XMLHTTP
MSXML2.XMLHTTP (or MSXML2.ServerXMLHTTP) sends HTTP GET/POST requests to retrieve web data programmatically within VBA.
Question 3: A researcher wants a VBA function to return multiple computed statistics. What is the correct approach in VBA?
- Return an array from a Function procedure
- Use ByRef parameters to pass values back
- Use a Public variable
- Both A and B are valid (Correct answer)
Correct answer: Both A and B are valid
VBA functions can return arrays directly, and ByRef parameters allow values to be modified in the calling scope — both are valid multi-value return patterns.
Question 4: To prevent a research analysis macro from running on the wrong dataset, which VBA technique validates the active workbook name before execution?
- If ActiveWorkbook.Name = "Research.xlsx" Then (Correct answer)
- If Workbook.Title = "Research" Then
- If ThisWorkbook.CodeName = "Research" Then
- If Application.Workbook = "Research.xlsx" Then
Correct answer: If ActiveWorkbook.Name = "Research.xlsx" Then
Checking ActiveWorkbook.Name against an expected string ensures the macro only runs on the intended file.
Question 5: Which VBA chart property should be set when automating the creation of a research bar chart to label the horizontal axis?
- Chart.Axes(xlCategory).HasTitle = True (Correct answer)
- Chart.XAxis.Title = "Label"
- Chart.CategoryAxis.Label = True
- Chart.Axes.X.Caption = "Label"
Correct answer: Chart.Axes(xlCategory).HasTitle = True
Setting Chart.Axes(xlCategory).HasTitle = True enables the category (X) axis title, which you then set via .AxisTitle.Text.
Question 6: A VBA script must compare two versions of a research dataset and highlight changed cells. Which approach identifies differences efficiently?
- Loop cell by cell and compare .Value between two ranges (Correct answer)
- Use Workbook.Compare method
- Use Application.WorksheetFunction.Match on each row
- Use the Track Changes VBA API
Correct answer: Loop cell by cell and compare .Value between two ranges
Iterating cells and comparing .Value between two corresponding ranges is the most reliable VBA method for cell-level change detection.
Question 7: To share a VBA analysis tool with colleagues who should not modify the code, which protection method should be applied?
- Sheet protection with password
- Save as .xlsx to strip macros
- VBA Project password via Tools > VBAProject Properties (Correct answer)
- Workbook structure protection
Correct answer: VBA Project password via Tools > VBAProject Properties
Applying a VBA Project password locks the code editor so colleagues can run macros without viewing or editing the source.
Excel VBA Research & Evidence-Based Practice 5
A VBA procedure must document its analysis steps in a log sheet.
Which approach writes text to the next available row automatically?