VBA Interview Questions

Analogy categoryAnalogies

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.

vba

' 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 Sub

ThisWorkbook 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.