Excel VBA Quality Control & Assurance 3 — Questions and Answers
Question 1: Which error-handling structure ensures cleanup code runs regardless of whether an error occurred in a VBA QA routine?
- On Error GoTo 0 with a standard Exit Sub
- A single On Error Resume Next block
- On Error GoTo ErrHandler with a Cleanup label called before both normal exit and the error branch (Correct answer)
- Wrapping all code in an If Err.Number = 0 block
Correct answer: On Error GoTo ErrHandler with a Cleanup label called before both normal exit and the error branch
Routing both the normal exit and the error branch through a shared Cleanup label guarantees resources are released in all scenarios.
Question 2: A QA macro compares values from two worksheets. Which method efficiently finds the intersection of two named ranges for comparison?
- Application.Intersect(range1, range2) (Correct answer)
- Application.Union(range1, range2)
- range1.Find(range2)
- WorksheetFunction.Match(range1, range2, 0)
Correct answer: Application.Intersect(range1, range2)
Application.Intersect returns a Range object representing cells common to both ranges, or Nothing if they don't overlap.
Question 3: When a VBA QA script must check whether a cell's value matches a list of approved codes stored in an array, which approach is correct?
- Use InStr(approvedArray, cell.Value)
- Loop through the array comparing each element to cell.Value
- Use cell.Value = Join(approvedArray, ',')
- Use Application.Match(cell.Value, approvedArray, 0) and check for an error (Correct answer)
Correct answer: Use Application.Match(cell.Value, approvedArray, 0) and check for an error
Application.Match against a Variant array returns an error if not found; wrapping it in IsError provides a clean Boolean check.
Question 4: Which property of a Range object returns True if the cell contains a formula rather than a constant value?
- Range.HasFormula (Correct answer)
- Range.IsFormula
- Range.Formula <> ''
- Range.FormulaHidden
Correct answer: Range.HasFormula
The HasFormula property returns True when the cell (or all cells in a multi-cell range) contains a formula.
Question 5: A QA sub must verify that all numeric cells in a report column are formatted as currency. Which property should it inspect?
- cell.Style
- cell.NumberFormat (Correct answer)
- cell.FormatConditions(1).NumberFormat
- cell.Font.Bold
Correct answer: cell.NumberFormat
The NumberFormat property contains the format string (e.g., '$#,##0.00') applied to the cell and can be compared against expected currency patterns.
Question 6: In Excel VBA, what is the purpose of using Assert statements from a custom testing framework compared to standard error handling?
- Assert statements are built into VBA and pause the debugger
- Custom Assert subs raise descriptive test-failure errors that identify which condition failed, unlike generic Err objects (Correct answer)
- Assert replaces On Error and prevents all runtime errors
- Assert statements are only available in 64-bit VBA
Correct answer: Custom Assert subs raise descriptive test-failure errors that identify which condition failed, unlike generic Err objects
A custom Assert sub raises a meaningful error with context (expected vs. actual) when a condition fails, making QA test failures easier to diagnose.
Question 7: What technique prevents a QA macro from modifying production data while it runs validation checks on a live workbook?
- Set Application.EnableEvents = False at the start
- Open the workbook using Workbooks.Open with ReadOnly:=True (Correct answer)
- Use ActiveSheet.Protect before iterating cells
- Set ActiveWorkbook.Saved = True before running checks
Correct answer: Open the workbook using Workbooks.Open with ReadOnly:=True
Opening the workbook with ReadOnly:=True ensures VBA can read all data without the ability to save any unintended changes back.
Excel VBA Quality Control & Assurance 3
Which error-handling structure ensures cleanup code runs regardless of whether an error occurred in a VBA QA routine?