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

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

📎 This article includes 1 downloadable practice file ↓

⏱ 3 min readUpdated 27 September 2026

📘 Excel VBA Course · Lesson 7 of 15 — see all lessons

In this article
  1. The three kinds of errors
  2. A proper error handler
  3. On Error Resume Next — use with care
  4. Debugging tools you should use
  5. Comments that actually help
  6. Try it yourself: step by step

Every macro eventually meets a missing file, an empty sheet or an unexpected value. Good error handling turns a scary crash into a helpful message — and good debugging skills find the cause quickly.

The three kinds of errors

  • Syntax errors — red lines as you type (a missing End If).
  • Run-time errors — the code is valid but something fails while running (file not found, error 1004).
  • Logic errors — no message at all, just wrong results. The hardest ones.

A proper error handler

Sub ImportReport()
    On Error GoTo Fail
    Application.ScreenUpdating = False

    Workbooks.Open "C:\Reports\daily.xlsx"
    ' ...work...

Tidy:
    Application.ScreenUpdating = True
    Exit Sub
Fail:
    MsgBox "Import failed: " & Err.Description & " (error " & Err.Number & ")", vbExclamation
    Resume Tidy
End Sub

The pattern is: jump to Fail on error, show a useful message, then Resume Tidy to restore settings. Without the tidy block, an error can leave screen updating off and the user thinks Excel froze.

On Error Resume Next — use with care

Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets("Archive")
On Error GoTo 0            ' switch normal errors back on immediately
If ws Is Nothing Then Set ws = Worksheets.Add: ws.Name = "Archive"
⚠️ Leaving On Error Resume Next active hides every later error. Wrap only the one line that may fail, then reset with On Error GoTo 0.

Debugging tools you should use

Tool How Why
Step Into F8 Run one line at a time
Breakpoint F9 or click the margin Pause at a specific line
Immediate window Ctrl+G Type ? lastRow to see a value; Debug.Print writes here
Locals window View → Locals See every variable at once
Watch Right-click a variable → Add Watch Break when a value changes
💡 Hover the mouse over a variable while paused to see its value. This alone solves most logic errors.

Comments that actually help

' Skip the header and the grand-total row at the bottom (added by SAP export)
For r = 2 To lastRow - 1

Comment the why, not the what. ' loop through rows adds nothing; the reason for lastRow - 1 saves the next person an hour. Select several lines and use the Comment Block button on the Edit toolbar to disable code while testing.

Practise spotting bugs in the VBA Bug Hunt, and see 7 causes of error 1004.

Try it yourself: step by step

  1. Run L07_SafeOpen and press Cancel in the file dialog — you get a friendly message instead of a crash.
  2. Comment out the On Error GoTo Fail line and run again to see the raw error.
  3. Put a breakpoint (F9) on the MsgBox line and run: execution pauses so you can inspect variables.
  4. Open the Locals window (View → Locals) while paused.
  5. Practise spotting bugs in the VBA Bug Hunt.

📎 Practice files for this article

  • 📗
    Practice workbook with all course macros (.xlsm)Open it, click Enable Content in the yellow bar, go to the Macros sheet and press any button. 60 sales rows included to test on.
    ⬇ XLSM · 29 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.

Keep reading

Excel VBARun-time Error 1004 in Excel VBA: 7 Common Causes and How to Fix ThemExcel VBALink Excel Charts to PowerPoint and Refresh Them AutomaticallyExcel VBAAutomate SAP from Excel: Run a Report and Export It with SAP GUI Scripting