Debugging: breakpoints, Watch and the Immediate window

Step through code, inspect state with the Locals and Watch windows, and use Debug.Print and Stop to find the bug fast.

The debugging tools

Option Explicit

Sub ToDebug(ByVal data As Variant)
    Dim i As Long
    Dim total As Double

    ' Debug.Print goes to the Immediate window, and only there.
    ' It is far better than MsgBox: it does not block, and it leaves a trace.
    Debug.Print "start, rows=" & UBound(data, 1)

    For i = 1 To UBound(data, 1)
        If VarType(data(i, 1)) = vbDouble Then
            total = total + data(i, 1)
        Else
            ' A breakpoint on the next line stops here and shows every value
            ' in the Locals window without changing the code.
            Debug.Print "skipped row " & i & " type " & VarType(data(i, 1))
        End If
    Next i

    Debug.Print "total=" & Format$(total, "#,##0.00")
End Sub

' Assert is compiled out of production: the line only fires when a
' condition you believed impossible is false.
Sub UseAssert(ByVal ws As Worksheet)
    Debug.Assert ws Is Not Nothing            ' only runs with the VBE open
    Debug.Assert ws.Range("A1").Value <> ""  ' stops if it is empty
End Sub

' Stop is an unconditional breakpoint that lives in the code, so it works
' even if you forgot to set one. Remove it before shipping.
Sub UseStop(ByVal n As Long)
    Dim i As Long
    For i = 1 To n
        If i = 3 Then Stop
    Next i
End Sub

A conditional breakpoint is set in the Watch window: add a watch on an expression and choose Break When Value Is True. It is the correct tool for stopping on the thousandth iteration without adding counters to the code.

The Immediate window

' With the code paused at a breakpoint, the Immediate window evaluates
' anything in scope, and can change state.

' print a value
'   ?total
'   Debug.Print UBound(data, 1)

' change a value and continue
'   i = 500
'   data(1, 1) = 0

' run a statement
'   Sheet1.Range("A1").Value = "test"
'   Application.Calculate

' check a condition before you write the If
'   ?TypeName(data)
'   ?IsArray(data)

' inspect an object
'   ?Sheet1.Name
'   ?ActiveWorkbook.FullName
'   ?SomeRange.Address

' find the last used row without leaving the debugger
'   ?Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row

' a common setup line you will type a hundred times
'   Sheet1.Range("A1").Value = Err.Number & ": " & Err.Description

' In the Immediate window, a ? is shorthand for Debug.Print.
' Anything without a ? is executed as a statement.

' Clear the Immediate window with Ctrl+A then Delete, or by right-clicking.

• Code typed in the Immediate window runs in the context of the paused procedure, so it can read and write its locals. That is what makes it so much better than a message box for experimentation.
• A Debug.Print left in a hot loop is a real performance cost. Search for it before shipping, or guard it with a module-level constant.
• The Locals window shows everything in scope but not the contents of a large object graph. Use a Watch on a specific expression for those.
• If a variable shows as Empty when you expected a value, check whether the assignment line actually ran. Step with F8 rather than guessing.

Structured debugging

' A debug switch keeps the diagnostics in the code and out of production.
#Const DEBUG_MODE = True

Sub Traced(ByVal n As Long)
    Dim i As Long
    For i = 1 To n
#If DEBUG_MODE Then
        If i Mod 100 = 0 Then Debug.Print "progress: " & i
#End If
    Next i
End Sub

' A central handler that reports where the failure happened.
' ErL is the last line number that ran, and it is only useful if you
' number your lines or set it yourself.
Sub WithContext()
    Dim ws As Worksheet
    On Error GoTo Fail

    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Range("A1").Value = 1
    Exit Sub

Fail:
    Debug.Print "error " & Err.Number & ": " & Err.Description
    Debug.Print "source: " & Err.Source
    Debug.Print "sheet: " & ws.Name            ' ws is valid here: it was set
    ' log to a file or a sheet instead of a message box in a batch run
    MsgBox "Failed: " & Err.Description, vbCritical, "Error " & Err.Number
End Sub

' The most useful single habit: reproduce with the smallest possible input.
' Copy the failing row into a fresh workbook, run the same macro, and the
' problem is usually obvious once the other thousand rows are gone.

FAQ

Related

Error handling and the classic pitfalls Speed: ScreenUpdating, calculation and array transfers

Last refreshed 2026-09-18.