Excel VBA Quality Control & Assurance 5

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?

Accepted Answer

Set the Interior.Color property on Application.Union of all violation cells

Answer

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?

Accepted Answer

A Scripting.Dictionary with error codes as keys

Answer

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?

Accepted Answer

Application.DisplayAlerts = False

Answer

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?

Accepted Answer

Application.EnableEvents = False

Answer

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?

Accepted Answer

Write all data to the sheet first, then call ws.Protect to lock it

Answer

Writing data first and then protecting the sheet is the simplest sequence; alternatively, UserInterfaceOnly:=True allows VBA writes to a protected sheet.

Excel VBA Practice Test

Excel VBA Practice Test

Excel VBA Quality Control & Assurance 5