VBA Error Handling and Debugging: On Error, Breakpoints and the Immediate Window

📎 This article includes 1 downloadable practice file ↓

Every macro breaks sooner or later — a renamed sheet, a missing file, text where a number should be. The difference between fragile and dependable code isn’t avoiding errors; it’s finding them quickly and handling the expected ones gracefully. This lesson covers both.

Reading an error

When a macro fails you get a box with a number and a message, plus End and Debug. Click Debug — the editor highlights the failing line in yellow. The most common ones:

Debugging tools

• F8 — run one line at a time. Hover over a variable to see its value.
• F9 — toggle a breakpoint (red dot). F5 runs until it reaches one.
• View › Locals Window — every variable and its current value, updated as you step.
• View › Immediate Window (Ctrl+G) — type ?lastRow to print a value, or run one line like Range("A1").Select.
• Debug.Print — write values to the Immediate window while the macro runs, without stopping it.

For r = 2 To lastRow
    Debug.Print r, ws.Cells(r, 1).Value, ws.Cells(r, 5).Value
Next r

On Error GoTo: handle errors properly

Sub ImportStatement()
    Dim wb As Workbook
    On Error GoTo Failed

    Set wb = Workbooks.Open(ThisWorkbook.Path & "\statement.xlsx", ReadOnly:=True)
    wb.Worksheets(1).UsedRange.Copy ThisWorkbook.Worksheets("Import").Range("A1")
    wb.Close False
    MsgBox "Imported."
    Exit Sub                       ' don't fall into the handler

Failed:
    MsgBox "Couldn't import: " & Err.Description & " (error " & Err.Number & ")", vbExclamation
    If Not wb Is Nothing Then wb.Close False
End Sub

The Exit Sub before the label is essential; without it, the handler runs every time, even when nothing went wrong.

Always restore settings

Sub SafeMacro()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    On Error GoTo Cleanup
    ' ... work ...
Cleanup:
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    If Err.Number <> 0 Then MsgBox "Stopped: " & Err.Description
End Sub

Without this, a crash leaves Excel in manual calculation and users think their formulas are broken.

On Error Resume Next — use with care

It tells VBA to ignore errors and continue. Useful for one line you expect might fail, dangerous for whole procedures:

On Error Resume Next
Set ws = ThisWorkbook.Worksheets("Archive")     ' may not exist
On Error GoTo 0                                  ' switch normal errors back on immediately
If ws Is Nothing Then Set ws = ThisWorkbook.Worksheets.Add: ws.Name = "Archive"

Prevent errors before they happen

• Check inputs: If Not IsNumeric(x) Then ... Exit Sub
• Check results: If found Is Nothing Then ... after .Find
• Check files: If Dir(path) = "" Then ...
• Option Explicit at the top of every module

Practice

The workbook contains six broken macros, one for each error in the table above. Fix them using F8, the Locals window and the Immediate window — answers are in a second module.

Next: string functions — cleaning and splitting text.