VBA Breakpoint in Excel — Freeze a Macro and Step Through It Line by Line

TL;DR — A breakpoint pauses your macro on a line before it runs and drops you into break mode. There you can read every live value — hover a variable for a data tip, or ask the Immediate Window — and then walk the code one line at a time. F9 sets or clears a breakpoint, F5 runs to it, F8 steps to the next line, Shift+F8 steps over a called procedure. This is how you find which line goes wrong, not just what the values were. Two things to know: breakpoints are not saved with the file, and a Stop statement in shipped code will freeze a user's Excel.

Sub Investigate()
    Dim total As Double, r As Long
    For r = 2 To 100
        total = total + Cells(r, 3).Value   ' F9 here sets a breakpoint (red dot)
    Next r                                   ' F8 steps; hover 'total' to watch it grow
    Debug.Assert total > 0                   ' breaks in the editor ONLY if this is False
End Sub

What you'll learn

The mental model — a breakpoint freezes time so you can walk the code
The three observers of rising power, and why this is the heavy one
Setting breakpoints with F9, and stepping with F8 vs Shift+F8
Why breakpoints vanish, and why Stop must never ship
How Debug.Assert gives you a breakpoint that is safe in production
When to step, and when to scatter Debug.Print instead

The mental model: freeze time, then walk the code

A breakpoint stops execution on a chosen line before that line runs, and hands you the macro frozen mid-flight. Every variable holds its live value, the call stack is intact, and Excel is paused exactly where your logic is. From there you step: F8 runs the current line and stops on the next, so you walk the program forward at human speed, watching each statement take effect.

That is the difference that matters. Debug.Print tells you what the values were after the run; a breakpoint lets you find which line the behaviour first diverges from what you expected. When the values are wrong but you cannot say where they went wrong, you do not need more logging — you need to freeze the macro and watch it happen.

The three observers, of rising power

A breakpoint is the top rung of a ladder. When a macro misbehaves, each tool answers a different question, and this is the one that stops time:

Observer	The question it answers	Its signature lie
`Debug.Print`	What were the values as it ran?	Prints to a window closed by default
Immediate Window	What is true right now, at this pause?	A `?` query actually runs the code
Breakpoint + F8	Which line does it go wrong on?	They vanish when you close the workbook

Debug.Print shows the past; the Immediate Window interrogates the present; a breakpoint creates the present, freezing the macro so you can inspect and step. It is the most powerful of the three and the slowest to use — stepping 10,000 loop iterations by hand is nobody's idea of an afternoon — so you reach for it precisely when the cheaper observers cannot answer "which line?"

Setting breakpoints, and F8 vs Shift+F8

Click in the grey margin to the left of a line, or put the cursor on the line and press F9 — a red dot marks it, and the next run pauses there. Once paused, four keys do the walking:

F8 (Step Into) — run this line; if it calls a Sub or Function, step into it and continue line by line inside.
Shift+F8 (Step Over) — run a called procedure in full and stop on the next line here.
Ctrl+Shift+F8 (Step Out) — finish the current procedure and stop where it was called from.
F5 (Run) — stop stepping and run to the next breakpoint or the end.

The number-one time sink is F8-ing straight into a 500-line helper you already trust — a formatting routine, a library call — and stepping through all of it. If you did not write the bug, step over it with Shift+F8. F8 is for the code you suspect; Shift+F8 is for the code you do not.

Trap 1: breakpoints do not save — and Stop is the trap that does

Close the workbook and every breakpoint is gone. They live in the editor session, not in the file, so they never travel with your macro and never fire for anyone else. That is usually what you want.

The tempting fix is the Stop statement — a permanent breakpoint written into the code:

Sub Risky()
    Stop                     ' pauses here EVERY run - including on a user's machine
    ' ... your real work ...
End Sub

Stop is genuinely useful while developing because it survives a restart. But if it reaches production, a user runs the macro, hits Stop, and Excel appears frozen at a break they cannot understand and cannot clear without the VBA editor. Treat Stop as a dev-only marker you must delete before shipping — never as error handling, and never in code that leaves your machine.

Trap 2: Debug.Assert is the breakpoint that is safe to ship

When you want a breakpoint that checks a condition and is harmless in production, use Debug.Assert:

Debug.Assert cnt = expected      ' breaks in the editor if the counts disagree
Debug.Assert Not rng Is Nothing  ' breaks if the range failed to resolve

Debug.Assert condition breaks execution only when the condition is False, and only inside the VBA editor — at runtime, outside the IDE, the line is ignored completely. That makes it the right way to encode an invariant you believe should always hold ("the count after equals the count before", "this object is not Nothing"): during development it stops you the instant the assumption breaks, and in a user's copy it costs nothing and freezes no one. It is a conditional breakpoint that documents itself.

Trap 3: editing while paused throws away your state

In break mode you can edit code — and VBA will often reset the run to apply the change, wiping every live variable back to empty and starting over. This is the usual reason someone reports that their variables "vanished" halfway through stepping: a small edit silently restarted the macro. If you are deep in a paused run, resist the urge to fix the typo you just spotted until you have read the state you paused for. Related power tool: Set Next Statement (Ctrl+F9) lets you drag the yellow arrow to re-run or skip a line — invaluable for retrying a step, but skip an initialisation and the state you then inspect is a lie.

Breakpoint vs Debug.Print

They are not rivals; they are different phases of the same hunt:

	Breakpoint + F8	`Debug.Print`
Answers	Which line goes wrong	What the values were
Big loops	Painful — you step every pass	Ideal — thousands of lines stream by
Interactive	Yes — inspect and change live state	No — it only emits
Cost	Stops the macro	Runs at full speed

Use Debug.Print to narrow a 10,000-row loop to the region where the numbers break; then set a breakpoint there — or an Add Watch with a break condition that pauses only when a value first goes negative — so you freeze on the one iteration that matters instead of F8-ing through all of them. Logging finds the neighbourhood; a breakpoint finds the line.

The opinion: the heavy tool, used deliberately

A breakpoint is the strongest debugger VBA gives you and the slowest to wield, so spend it where the cheaper tools run out: when the values are wrong and you genuinely cannot say which statement is responsible. For everything you can answer from values — is this variable what I expect, does this loop run the right number of times — a Debug.Print or an Immediate Window query is faster and does not stop the world.

Two disciplines separate people who debug quickly from people who fight the editor. Never ship a Stop; encode your always-true assumptions as Debug.Assert instead, so they guard you in development and disappear in production. And never F8 through code you did not write — step over it. The goal is not to watch every line; it is to reach the one line that lies to you.

When the whole job is finding the broken row — describe it instead

Stepping is superb for one broken run and miserable for "which of 8,000 rows breaks the total." By the time you have set a breakpoint, added a watch, and stepped until a value goes wrong, you have hand-simulated what a query answers in one pass. ExcelMaster lets you state the question in plain English — "find the first row where the running total stops matching column E, and show me the rows around it" — and it writes Python that reads the sheet, backs up your file first, checks every row, and returns the exact offender. Use a breakpoint to understand one failure; describe the rule when the job is to find which case fails.

Frequently asked questions

How do I set a breakpoint in VBA?

Click in the grey margin to the left of a line, or put the cursor on the line and press F9. A red dot marks the line, and the next time the macro runs it pauses just before that line, dropping you into break mode. Press F9 again on the line to remove the breakpoint.

Why do my breakpoints keep disappearing?

Because breakpoints are not saved with the workbook — closing the file clears all of them. If you need a pause that lives in the code, use the Stop statement, but remove it before you share the macro, since a Stop will freeze Excel for anyone who runs it.

What is the difference between F8 and Shift+F8?

F8 (Step Into) runs one line and steps into any Sub or Function it calls, so you walk through the called code too. Shift+F8 (Step Over) runs a called procedure in full and stops on the next line of the current one. Use Step Over for helper routines you already trust.

How do I see a variable's value while a macro is paused?

Hover the mouse pointer over the variable name for a data tip, type ? varName in the Immediate Window, or add the variable to the Watch Window. These work only while the macro is paused in break mode, because local variables exist only during the run.

What does Debug.Assert do in VBA?

Debug.Assert condition breaks execution in the VBA editor only when the condition is False, and is ignored entirely when the code runs outside the editor. It is a safe way to check an invariant during development — the counts match, an object is not Nothing — without leaving a Stop that could freeze a user's Excel.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-05.