Excel VBA Professional Standards & Competencies 3 — Questions and Answers
Question 1: A macro loops through 50,000 rows and runs slowly. Which professional optimization technique should be applied first?
- Break the macro into multiple smaller macros and run them manually
- Turn off ScreenUpdating and set Calculation to Manual before the loop (Correct answer)
- Increase the computer's RAM
- Use a MsgBox inside the loop to track progress
Correct answer: Turn off ScreenUpdating and set Calculation to Manual before the loop
Disabling screen updates and automatic recalculation dramatically reduces overhead in large data loops.
Question 2: Which error-handling structure is considered the professional standard for robust VBA procedures?
- Ignoring errors with 'On Error Resume Next' throughout the entire procedure
- Using 'On Error GoTo ErrorHandler' with a labeled cleanup section (Correct answer)
- Wrapping every line in a separate If-Then statement
- Using nested error handlers inside every loop
Correct answer: Using 'On Error GoTo ErrorHandler' with a labeled cleanup section
'On Error GoTo ErrorHandler' with a structured cleanup section ensures errors are caught, logged, and resources are properly released.
Question 3: In VBA, what is the professional advantage of using Named Constants (Const) instead of magic numbers?
- Constants execute faster than literal values
- Constants cannot be changed at runtime, preventing accidental modification
- Constants make the code self-documenting and centralise values that may change (Correct answer)
- Constants are required by the VBA compiler for numeric values
Correct answer: Constants make the code self-documenting and centralise values that may change
Named constants replace cryptic literals with readable names and allow a single edit point if the value ever needs updating.
Question 4: A colleague's VBA code uses 'Select' and 'Activate' extensively (e.g., Range.Select then Selection.Copy). What is the professional concern?
- Select is faster than direct object references
- Select and Activate are required for clipboard operations in VBA
- Relying on Select slows code, causes screen flicker, and is fragile if the active sheet changes (Correct answer)
- Select is only available in Excel 365 and may not work on older versions
Correct answer: Relying on Select slows code, causes screen flicker, and is fragile if the active sheet changes
Direct object references (e.g., Range.Copy Destination:=...) are faster, more stable, and do not depend on which sheet is currently active.
Question 5: What is the professional role of a modular design approach in large VBA projects?
- It prevents other users from viewing the code
- It breaks code into small, single-purpose procedures that are easier to test and reuse (Correct answer)
- It allows VBA to run on non-Windows operating systems
- It automatically documents the code without comments
Correct answer: It breaks code into small, single-purpose procedures that are easier to test and reuse
Modular design improves testability, reuse, and maintainability by ensuring each procedure has one clear responsibility.
Question 6: When should a VBA developer use a Function instead of a Sub?
- When the procedure needs to display a dialog box
- When the procedure must return a calculated value to the caller (Correct answer)
- When the procedure modifies more than one worksheet
- When the procedure takes longer than one second to run
Correct answer: When the procedure must return a calculated value to the caller
Functions return a value to the calling code, making them appropriate for calculations and reusable logic that produces a result.
Question 7: Which data type should a professional VBA developer choose for a variable that will count worksheet rows in modern Excel?
- Integer, because rows are whole numbers
- Long, because Excel has more than 32,767 rows and Integer would overflow (Correct answer)
- Double, to handle potential decimal row counts
- String, to avoid type mismatch errors
Correct answer: Long, because Excel has more than 32,767 rows and Integer would overflow
Excel has over 1 million rows, which exceeds the Integer maximum of 32,767; Long supports values up to ~2.1 billion.
Excel VBA Professional Standards & Competencies 3
A macro loops through 50,000 rows and runs slowly.
Which professional optimization technique should be applied first?