Excel VBA Quality Control & Assurance 3

Question 1

Which error-handling structure ensures cleanup code runs regardless of whether an error occurred in a VBA QA routine?

Accepted Answer

On Error GoTo ErrHandler with a Cleanup label called before both normal exit and the error branch

Answer

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?

Accepted Answer

Application.Intersect(range1, range2)

Answer

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?

Accepted Answer

Use Application.Match(cell.Value, approvedArray, 0) and check for an error

Answer

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?

Accepted Answer

Range.HasFormula

Answer

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?

Accepted Answer

cell.NumberFormat

Answer

The NumberFormat property contains the format string (e.g., '$#,##0.00') applied to the cell and can be compared against expected currency patterns.

Excel VBA Practice Test