Professional Standards & Competencies Flashcards
7 cards from real Excel VBA practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 7 Professional Standards & Competencies flashcards as text
A macro loops through 50,000 rows and runs slowly. Which professional optimization technique should be applied first?
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.
Which error-handling structure is considered the professional standard for robust VBA procedures?
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.
In VBA, what is the professional advantage of using Named Constants (Const) instead of magic numbers?
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.
A colleague's VBA code uses 'Select' and 'Activate' extensively (e.g., Range.Select then Selection.Copy). What is the professional concern?
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.
What is the professional role of a modular design approach in large VBA projects?
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.
When should a VBA developer use a Function instead of a Sub?
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.
Which data type should a professional VBA developer choose for a variable that will count worksheet rows in modern Excel?
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.