What VBA Interviews Actually Test
VBA interviews rarely ask for obscure trivia; they probe whether you understand the model beneath the code. Expect questions on variable declaration and scope, the difference between ThisWorkbook and ActiveWorkbook, error-handling strategy, when to use a Function versus a Sub, and how to make macros fast. Interviewers use these to distinguish someone who recorded a macro once from someone who can maintain a business-critical automation. The strongest candidates answer with the reasoning and the trade-off, not just the syntax — explaining not only what a keyword does but why and when you would reach for it.
Cricket analogy: A VBA interview is like a selection trial that watches shot selection and game awareness, not just how hard you can slog — technique under pressure matters more than one flashy six.
Variables, Scope, and Data Types
A frequent opener is the difference between Dim, Private, Public, and Static, and how scope works. Dim inside a procedure creates a local variable that dies when the procedure ends; Static preserves its value between calls; Private at module level shares it across procedures in that module; Public exposes it project-wide. Interviewers also probe why Option Explicit matters and the cost of the Variant type — a Variant works for anything but is slower and hides bugs. A crisp answer notes that ByRef (the default) passes a reference so the callee can modify the caller's variable, while ByVal passes a copy, and that choosing the narrowest scope and most specific type is the mark of careful code.
Cricket analogy: Scope is like a player's role: a local Dim is a substitute fielder for one over, Static remembers the score between innings, Public is the captain known to the whole team.
' A common interview task: does this counter persist across calls?
Sub DemoStatic()
Static callCount As Long ' retains value between calls
callCount = callCount + 1
Debug.Print "Called " & callCount & " time(s)"
End Sub
' ByRef vs ByVal — what does the caller see?
Sub Caller()
Dim n As Long
n = 10
AddFiveByRef n ' n becomes 15 (reference modified)
Debug.Print n
AddFiveByVal n ' n stays 15 (copy modified)
Debug.Print n
End Sub
Sub AddFiveByRef(ByRef x As Long)
x = x + 5
End Sub
Sub AddFiveByVal(ByVal x As Long)
x = x + 5
End SubThisWorkbook vs ActiveWorkbook, and Error Handling
Two questions come up in almost every VBA interview. First, the difference between ThisWorkbook and ActiveWorkbook: ThisWorkbook always refers to the workbook containing the running code, while ActiveWorkbook refers to whichever workbook currently has focus — which can change if the macro opens another file or the user clicks away, making ActiveWorkbook a frequent source of bugs. Second, error handling: expect to explain On Error GoTo versus On Error Resume Next, the role of the Err object, and why Resume, Resume Next, and Resume label differ. A strong answer stresses using a labelled handler with a cleanup section and never leaving Resume Next active across a routine.
Cricket analogy: ThisWorkbook is your home dressing room, always yours; ActiveWorkbook is 'whichever crease is on strike' — it changes ball to ball, so relying on it causes mix-ups.
A sharp interview point: in the Err object, Err.Number is 0 when no error is active. After handling an error you can call Err.Clear (or the handler's Resume) to reset it. Err.Raise lets you generate custom errors with vbObjectError + n as the number.
Performance and Events
Performance questions test whether you know why macros are slow and how to fix it. The canonical answers: set Application.ScreenUpdating = False, switch Calculation to manual, disable EnableEvents when writing to cells that would otherwise retrigger event handlers, and above all minimize round-trips to the worksheet by reading/writing whole ranges as arrays. Event questions probe workbook and worksheet events — Worksheet_Change, Workbook_Open, Worksheet_SelectionChange — and the classic trap that changing a cell inside Worksheet_Change fires the event again, causing infinite recursion unless you guard it by toggling Application.EnableEvents = False. Naming these guards confidently signals real production experience.
Cricket analogy: Disabling EnableEvents inside Worksheet_Change is like calling for a timeout so your own signal to the fielders does not get relayed back and trigger a chain reaction on the field.
The infinite-recursion trap: writing to a cell inside Worksheet_Change fires Worksheet_Change again. Always wrap such writes with Application.EnableEvents = False before and = True after (in an error handler too), or Excel can hang or crash.
- Interviews test the model beneath the code: scope, references, error handling, performance, events.
- Know Dim/Private/Public/Static scope and ByRef (default, reference) vs ByVal (copy).
- ThisWorkbook is always the code's own workbook; ActiveWorkbook is whatever has focus and can change.
- Explain On Error GoTo vs Resume Next, the Err object, and Resume/Resume Next/Resume label.
- Speed up macros via ScreenUpdating=False, manual Calculation, and array read/writes over cell loops.
- Worksheet_Change writing a cell re-fires the event — guard with EnableEvents=False to avoid recursion.
- Answer with reasoning and trade-offs, not just syntax, to signal production experience.
Practice what you learned
1. What does a Static variable do inside a procedure?
2. What is the difference between ThisWorkbook and ActiveWorkbook?
3. By default, how does VBA pass arguments to a procedure?
4. Why must you set Application.EnableEvents = False when writing a cell inside Worksheet_Change?
5. Which set of changes most improves macro performance?
Was this page helpful?
You May Also Like
VBA Best Practices
Practical conventions for writing maintainable, fast, and robust VBA — from Option Explicit and naming to error handling, performance, and avoiding Select/Activate.
VBA Quick Reference
A compact cheat-sheet of the VBA constructs you reach for daily — variable types, loops, range navigation, common objects, and handy snippets.
Building a Report Automation Macro
A step-by-step walkthrough of building a real VBA macro that consolidates raw data, computes summaries, formats output, and exports a monthly report to PDF.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics