Debugging VBA Macros

Analogy categoryAnalogies

Why Debugging Matters in VBA

VBA runs inside Office applications, so a bug rarely crashes with a clean stack trace — instead a macro silently writes to the wrong cell, loops forever, or halts on a cryptic runtime error like '1004: Application-defined error'. Debugging is the disciplined practice of pausing execution and inspecting the actual state of variables, objects, and the worksheet at a precise moment, rather than guessing from the final result. The VBA Editor (VBE) ships a full interactive debugger, and learning it turns hours of trial-and-error into minutes of targeted inspection.

🏏

Cricket analogy: Like a batsman reviewing slow-motion replay of a dismissal to see exactly where bat met pad, debugging pauses the macro so you inspect the precise frame where the value went wrong, not just the final scorecard.

Breakpoints and Stepping Through Code

A breakpoint pauses execution on a chosen line before it runs — press F9 in the margin (or click the grey bar) to toggle one, shown as a dark red dot. When the macro hits it, execution enters break mode and the current line is highlighted yellow. From there you step: F8 (Step Into) runs the next line and stops, descending into any called Sub or Function; Shift+F8 (Step Over) runs a called procedure fully without stepping through it; and Ctrl+Shift+F8 (Step Out) finishes the current procedure and returns to its caller. A Stop statement in code acts as a permanent, saved breakpoint that travels with the file.

🏏

Cricket analogy: A breakpoint is the third umpire freezing play for a review the instant the ball passes the stumps; Step Into is watching the very next delivery ball-by-ball, while Step Over lets a whole over play out uninspected.

The Immediate Window and Debug.Print

The Immediate Window (Ctrl+G) is an interactive console. In break mode you can type '? Range("A1").Value' to print a value, assign 'x = 5' to change a variable live, or call a Sub by name to test it in isolation. Debug.Print writes to this same window from running code without interrupting it — ideal for logging loop counters or intermediate values you want to trace over many iterations. Because the output is non-modal, Debug.Print is far better than sprinkling MsgBox calls, which block execution and force you to click through every iteration.

🏏

Cricket analogy: The Immediate Window is the commentary box mic where you can query the exact run rate mid-over; Debug.Print is the ticker quietly logging every ball's outcome without stopping play like a drinks break would.

vba

Sub CalculateBonuses()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sales")

    Dim lastRow As Long, i As Long
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    For i = 2 To lastRow
        Dim sales As Double, bonus As Double
        sales = ws.Cells(i, "B").Value

        ' Set a breakpoint (F9) on the next line, then inspect 'sales' in the Locals window
        bonus = sales * 0.05

        ' Non-blocking trace: watch every iteration in the Immediate Window (Ctrl+G)
        Debug.Print "Row " & i & ": sales=" & sales & " bonus=" & bonus

        ' Conditional break: pause ONLY when the data looks wrong
        Debug.Assert sales >= 0   ' halts here if a negative sales figure sneaks in

        ws.Cells(i, "C").Value = bonus
    Next i
End Sub

Watches, Locals, and Debug.Assert

The Locals Window (View menu) auto-displays every in-scope variable and object property in a tree, so you can expand a Range object and read its Value, Address, and Font without typing anything. A Watch (right-click a variable, Add Watch) tracks a specific expression, and a 'Break When Value Changes' watch stops execution the instant a variable is modified — invaluable for finding where a value gets corrupted. Debug.Assert takes a Boolean and breaks only when it is False, letting you embed self-checking assertions that stay silent in normal runs but trap invalid states the moment they occur.

🏏

Cricket analogy: The Locals Window is the full scoreboard showing every batsman's runs at a glance; a 'break when value changes' Watch is like an alert that fires the exact ball a wicket falls, so you never miss the dismissal.

Debug.Print and Debug.Assert only execute in the VBA IDE. When a macro runs as a compiled add-in or is called from another application, Debug.Assert is ignored and never breaks — so never rely on it as your production error handling. Use it strictly as a development-time trap, and keep real validation in explicit If checks and On Error routines.

Set Next Statement (Ctrl+F9) lets you drag the yellow execution arrow to any line in the current procedure while paused — you can re-run a loop iteration or skip a problematic call without restarting the whole macro. Combined with editing variables live in the Immediate Window, you can test a fix in seconds before committing it to code.

F9 toggles a breakpoint; F8 (Step Into), Shift+F8 (Step Over), and Ctrl+Shift+F8 (Step Out) control how execution advances.
The Immediate Window (Ctrl+G) lets you query and set values live; '?' or Print prints an expression.
Debug.Print traces values non-modally into the Immediate Window — far better than blocking MsgBox calls in loops.
The Locals Window auto-shows all in-scope variables; a 'break when value changes' Watch finds where a value gets corrupted.
Debug.Assert breaks only when its condition is False, but runs only in the IDE — never use it for production validation.
Set Next Statement (Ctrl+F9) moves the execution point so you can replay or skip lines while paused.
A Stop statement is a saved breakpoint that travels with the workbook file.