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.
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 SubWatches, 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.
Practice what you learned
1. Which keyboard shortcut runs the next line but steps INTO any procedure it calls?
2. Why is Debug.Print generally preferred over MsgBox for tracing values inside a loop?
3. What does a 'Break When Value Changes' watch do?
4. What is a key limitation of Debug.Assert?
5. In break mode, what does Set Next Statement (Ctrl+F9) allow?
Was this page helpful?
You May Also Like
Error Logging and Robust Macros
Build VBA macros that fail gracefully by combining structured On Error handling, a central error handler, the Err object, and persistent logging to a file or worksheet.
Error Handling with On Error
Intercept run-time errors gracefully in VBA using On Error statements, the Err object, and disciplined cleanup patterns.
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.
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