Excel VBA Quality Control & Assurance 5 — Questions and Answers
Question 1: A QA macro must verify that every cell in a summary range references a formula, not a hardcoded value. After identifying violations, which Range method highlights all of them at once?
- violationRange.Select and then manually format
- Set the Interior.Color property on Application.Union of all violation cells (Correct answer)
- Use violationRange.FormatConditions.Add to add a highlight rule
- Loop and set each cell's Interior.Color individually
Correct answer: Set the Interior.Color property on Application.Union of all violation cells
Building a Union range of all violations and setting Interior.Color in a single statement is faster than looping and setting each cell individually.
Question 2: Which VBA collection type is best suited for de-duplicating a list of error codes collected during a QA pass?
- An Array with a manual dedup loop
- A Scripting.Dictionary with error codes as keys (Correct answer)
- A Collection using numeric indexes
- A Variant array sorted with a bubble sort
Correct answer: A Scripting.Dictionary with error codes as keys
Dictionary keys must be unique, so adding error codes as keys automatically ignores duplicates without extra logic.
Question 3: A QA automation script must run silently without displaying any Excel alerts or confirmation dialogs. Which setting suppresses these?
- Application.DisplayAlerts = False (Correct answer)
- Application.Interactive = False
- Application.EnableEvents = False
- Application.ScreenUpdating = False
Correct answer: Application.DisplayAlerts = False
Setting DisplayAlerts = False suppresses Excel's built-in dialog boxes; remember to restore it to True after the macro completes.
Question 4: When a QA macro modifies workbook structure (adds/deletes sheets), which event must be temporarily disabled to prevent cascading event-driven code from firing?
- Application.ScreenUpdating = False
- Application.EnableEvents = False (Correct answer)
- Application.Calculation = xlCalculationManual
- Application.DisplayAlerts = False
Correct answer: Application.EnableEvents = False
EnableEvents = False prevents Workbook_SheetChange and similar events from triggering while the macro restructures the workbook.
Question 5: A QA script must write a structured error report to a new worksheet and then protect it so users cannot edit it. What is the correct sequence?
- Protect the sheet first, write data, then unprotect
- Write all data to the sheet first, then call ws.Protect to lock it (Correct answer)
- Use ws.Protect with UserInterfaceOnly:=True before writing so VBA can still write
- Protect with a password before writing, then unprotect at the end
Correct answer: Write all data to the sheet first, then call ws.Protect to lock it
Writing data first and then protecting the sheet is the simplest sequence; alternatively, UserInterfaceOnly:=True allows VBA writes to a protected sheet.
Question 6: Which approach correctly tests that a VBA function returns a specific Err.Number when passed invalid input, without crashing the test runner?
- Call the function inside an If statement and check its return value
- Use On Error Resume Next before calling the function, then inspect Err.Number immediately after (Correct answer)
- Wrap the call in a standard On Error GoTo label and check Err.Number in the handler
- Use Debug.Assert to catch the error automatically
Correct answer: Use On Error Resume Next before calling the function, then inspect Err.Number immediately after
On Error Resume Next lets execution continue after the error; Err.Number is then immediately available for assertion before it is cleared.
Question 7: In a VBA QA pipeline, what is the primary benefit of separating data-reading, validation logic, and error-reporting into distinct Sub or Function procedures?
- It reduces the number of variables VBA needs to track at runtime
- It makes each component independently testable and replaceable without affecting other stages (Correct answer)
- It eliminates the need for error handling within each procedure
- It allows the macro to run across multiple threads simultaneously
Correct answer: It makes each component independently testable and replaceable without affecting other stages
Separation of concerns means each procedure can be unit-tested in isolation, and one component can be updated without breaking the others.
Excel VBA Quality Control & Assurance 5
A QA macro must verify that every cell in a summary range references a formula, not a hardcoded value.
After identifying violations, which Range method highlights all of them at once?