Excel VBA Quality Control & Assurance 2 — Questions and Answers
Question 1: Which VBA approach best validates that a user-entered date falls within a fiscal year range before processing?
- Check IsDate() then compare to hardcoded strings
- Use IsDate() to confirm it's a date, then compare the value to fiscal start/end date variables (Correct answer)
- Convert the input to a string and use InStr to look for year digits
- Use the DateDiff function alone without any IsDate check
Correct answer: Use IsDate() to confirm it's a date, then compare the value to fiscal start/end date variables
IsDate() confirms the value is a valid date before numeric date comparisons are made against fiscal boundary variables.
Question 2: A QA macro must abort and log an error if a required worksheet named 'Data' is missing. Which code pattern is correct?
- If Worksheets('Data') = Nothing Then GoTo ErrLog
- On Error Resume Next: Set ws = Sheets('Data'): On Error GoTo 0: If ws Is Nothing Then GoTo ErrLog (Correct answer)
- If Not Sheets.Exists('Data') Then GoTo ErrLog
- Try: Set ws = Sheets('Data'): Catch: GoTo ErrLog
Correct answer: On Error Resume Next: Set ws = Sheets('Data'): On Error GoTo 0: If ws Is Nothing Then GoTo ErrLog
Using On Error Resume Next lets VBA attempt the assignment; if the sheet is missing, ws remains Nothing, triggering the error branch.
Question 3: When auditing cell formulas in a QA macro, which property returns the formula string of a cell?
- Cell.Value
- Cell.Text
- Cell.Formula (Correct answer)
- Cell.FormulaR1C1
Correct answer: Cell.Formula
Cell.Formula returns the formula as entered in A1 notation, suitable for string inspection in QA routines.
Question 4: A QA sub needs to flag all cells in column B that contain negative numbers. Which loop construct is most appropriate?
- For Each cell In Range('B:B')
- For Each cell In Range('B1').CurrentRegion
- For Each cell In Columns('B').SpecialCells(xlCellTypeConstants, xlNumbers) (Correct answer)
- Do While ActiveCell <> ''
Correct answer: For Each cell In Columns('B').SpecialCells(xlCellTypeConstants, xlNumbers)
SpecialCells(xlCellTypeConstants, xlNumbers) restricts the loop to cells with numeric constants, avoiding blank/formula cells and improving performance.
Question 5: Which VBA statement writes a QA error message to the Immediate Window without halting execution?
- MsgBox 'Error: ' & msg
- Debug.Print 'Error: ' & msg (Correct answer)
- Err.Raise 1000, , msg
- Application.StatusBar = msg
Correct answer: Debug.Print 'Error: ' & msg
Debug.Print outputs text to the Immediate Window at runtime without pausing or stopping the macro.
Question 6: A data-validation macro must ensure no duplicate order IDs exist in column A. Which WorksheetFunction is most efficient for this check?
- Application.WorksheetFunction.Match
- Application.WorksheetFunction.CountIf (Correct answer)
- Application.WorksheetFunction.VLookup
- Application.WorksheetFunction.Index
Correct answer: Application.WorksheetFunction.CountIf
CountIf can count occurrences of each ID; any result greater than 1 flags a duplicate without needing a helper column.
Question 7: In a QA macro, what does setting Application.ScreenUpdating = False accomplish?
- Prevents users from editing cells during the macro
- Hides the Excel window entirely from the user
- Stops the screen from refreshing, speeding up macro execution (Correct answer)
- Disables all worksheet events while the macro runs
Correct answer: Stops the screen from refreshing, speeding up macro execution
Disabling ScreenUpdating prevents Excel from repainting the UI on each change, which significantly speeds up macros that modify many cells.
Excel VBA Quality Control & Assurance 2
Which VBA approach best validates that a user-entered date falls within a fiscal year range before processing?