Excel VBA Quality Control & Assurance 4 — Questions and Answers
Question 1: A QA macro must report rows where the value in column C does not match the pattern 'AA-####' (two letters, hyphen, four digits). Which VBA tool handles this?
- InStr(cell.Value, '-')
- cell.Value Like '[A-Z][A-Z]-####' (Correct answer)
- cell.Value = 'AA-####'
- IsNumeric(Mid(cell.Value, 4, 4))
Correct answer: cell.Value Like '[A-Z][A-Z]-####'
The Like operator with the pattern '[A-Z][A-Z]-####' matches exactly two uppercase letters, a hyphen, and four digits.
Question 2: Which method should a QA macro use to programmatically add a data validation rule to a cell that restricts input to whole numbers between 1 and 100?
- cell.Validation.Add Type:=xlValidateWholeNumber, Minimum:='1', Maximum:='100' (Correct answer)
- cell.AddValidation xlWholeNumber, 1, 100
- Application.DataValidation.Set cell, 1, 100
- cell.Formula1 = '=AND(A1>=1,A1<=100)'
Correct answer: cell.Validation.Add Type:=xlValidateWholeNumber, Minimum:='1', Maximum:='100'
The Validation.Add method on a Range object with xlValidateWholeNumber type configures whole-number input restrictions.
Question 3: A QA log workbook must record timestamps for each error found. Which VBA expression returns the current date and time as a single value?
- Date + Time
- Now() (Correct answer)
- DateTime.Now
- Format(Date, 'yyyy-mm-dd') & ' ' & Format(Time, 'hh:mm:ss')
Correct answer: Now()
Now() is the VBA built-in function that returns the current date and time combined as a Date value.
Question 4: To ensure a QA macro handles both 32-bit and 64-bit Excel environments when declaring Windows API functions, which compiler directive is used?
- #If Win64 Then ... #Else ... #End If
- #If VBA7 Then ... #Else ... #End If (Correct answer)
- #If OperatingSystem = '64' Then ... #Else ... #End If
- #If Bits = 64 Then ... #Else ... #End If
Correct answer: #If VBA7 Then ... #Else ... #End If
The #If VBA7 compiler directive checks for VBA version 7 (Excel 2010+, 64-bit capable) and is the correct conditional for PtrSafe declarations.
Question 5: Which technique allows a VBA QA macro to test a Private Sub in a standard module without changing its access modifier?
- Call the sub directly from the Immediate Window using its full module path
- Use Application.Run 'ModuleName.SubName' from another module (Correct answer)
- Temporarily change Option Private Module at the top of the module
- Private subs can only be tested by making them Public
Correct answer: Use Application.Run 'ModuleName.SubName' from another module
Application.Run accepts a string with the module and procedure name, allowing external callers to invoke Private procedures by name.
Question 6: A QA macro compares two ranges and must collect all differing cell addresses into a single Range object. Which method combines individual cell references?
- Application.Intersect
- Application.Union (Correct answer)
- Range.Merge
- Range.Consolidate
Correct answer: Application.Union
Application.Union combines two or more Range objects into one, allowing you to collect mismatched cells for bulk formatting or reporting.
Question 7: When writing a VBA unit test for a function that calculates a discount, which practice improves test reliability?
- Test only with the exact production data file
- Use hardcoded input/expected-output pairs that cover boundary values like 0%, 100%, and edge percentages (Correct answer)
- Rely on Application.WorksheetFunction.Round to normalize all outputs before comparing
- Run the test only in the Immediate Window to avoid side effects
Correct answer: Use hardcoded input/expected-output pairs that cover boundary values like 0%, 100%, and edge percentages
Hardcoded boundary-value test cases (zero, maximum, and edge inputs) catch off-by-one errors and logic faults independently of live data.
Excel VBA Quality Control & Assurance 4
A QA macro must report rows where the value in column C does not match the pattern 'AA-####' (two letters, hyphen, four digits).
Which VBA tool handles this?