For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=ARTICLES

VBA error handling and debugging

VBA.E4 On Error GoTo CleanExit · F8
VBA error handling and debugging — Excel example preview

Stepping through macro code line-by-line with F8 and ensuring ScreenUpdating and Calculation are always restored in a CleanExit block.

=YOUTUBE

Watch alongside this article

Public YouTube resources matched to this article's topic, functions, and practice goal.

Macros fail — a sheet gets renamed, a cell that should hold a number holds text, a file isn’t where it’s supposed to be. The difference between a macro that’s annoying to fix and one that’s actually reliable is usually just how it handles that moment.

On Error GoTo a label

This redirects execution to a named label the moment an error occurs, anywhere below this line in the same procedure:

On Error GoTo ErrHandler

Combined with a label further down:

ErrHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description

On Error Resume Next

This tells VBA to ignore the error and simply move to the next line, instead of stopping or jumping anywhere:

On Error Resume Next

Use it sparingly, for one specific line where you already expect a failure and know exactly what to do about it — checking whether an object exists (as in SheetExists patterns) is the classic legitimate use. The danger is leaving it turned on for a long stretch of code: every single error after that line, including ones you never anticipated — a typo, a missing sheet, a wrong data type — gets silently swallowed instead of surfacing. The macro appears to “work” while quietly doing the wrong thing or nothing at all. Turn error trapping back on as soon as the risky line is past:

On Error Resume Next
Set ws = ThisWorkbook.Worksheets("Archive")
On Error GoTo 0

On Error GoTo 0 restores normal error behavior — the next error will stop execution and show the standard error dialog again, rather than being ignored.

Breakpoints and stepping through code

Click in the grey margin to the left of a line (or put your cursor on the line and press F9) to set a breakpoint — a solid red dot and highlighted line. Run the macro normally and it pauses right before that line executes, with the current values of every variable still in memory. Hover over a variable to see its value, or check the Immediate window (Ctrl+G) or View → Locals Window for a fuller picture.

From a paused state:

  • F8 (Step Into) runs one line at a time, stepping into any procedure that line calls, so you can watch it execute line by line too.
  • Shift+F8 (Step Over) runs one line at a time but treats a called procedure as a single step, without diving into it — useful once you’ve already confirmed a called procedure works.
  • F5 resumes normal execution to the end, or the next breakpoint.

Reading the error dialog

When an unhandled error stops a macro, Excel shows a dialog with the error number, a description, and buttons for End, Debug, and sometimes Help. Click Debug — it jumps straight to the VBE, with the failing line highlighted in yellow and a yellow arrow in the margin. That’s your starting point: hover over the variables on that line to see what they actually held when it broke.

A clean error-handling pattern

The pattern that holds up well combines a label for the error, a shared cleanup section, and a Resume that routes control through cleanup either way:

Sub ProcessReport()
    On Error GoTo CleanFail

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A1:A" & lastRow).Copy Destination:=Sheets("Archive").Range("A1")

CleanExit:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub

CleanFail:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume CleanExit
End Sub

If nothing goes wrong, execution simply falls through into CleanExit after the last line of real work, restores the application settings, and exits. If something does go wrong, CleanFail shows what happened and then Resume CleanExit sends control to the same cleanup — so ScreenUpdating and EnableEvents always get turned back on, whether the macro succeeded or not. Without that guarantee, a single error partway through a macro can leave screen updating off or events disabled for the rest of the Excel session, which is a confusing thing to debug later because nothing about it looks like an error — Excel just seems to stop responding to things it normally would.

Errors you will actually meet

Five numbers cover most VBA failures. Recognising them saves the search:

Error Means Usual cause
9 Subscript out of range A name that does not exist Sheet renamed, or a typo in Worksheets("Data")
13 Type mismatch Wrong kind of value Text where a number was expected — often #N/A in a cell
91 Object variable not set Using an object you never Set A Find that returned Nothing and was not checked
1004 Application-defined error The catch-all A bad range string, a protected sheet, a missing file
438 Object doesn’t support this property Wrong object type Calling a Range method on a Worksheet

Error 1004 is the vague one, and the fastest way through it is to look at the exact string you handed Excel. Range("A" & lastRow) where lastRow is 0 gives you Range("A0"), which does not exist. Print the value before the line that fails.

Handle errors you expect; let the rest surface

The temptation with error handling is to wrap everything so nothing ever stops. That is how you get a macro that reports success and did nothing.

A useful distinction:

  • Expected and recoverable — the archive sheet might not exist yet; the lookup might not find a match. Handle these specifically, right where they occur, and carry on.
  • Unexpected — anything else. You want these loud. An error you have not thought about means the macro’s assumptions are wrong, and continuing past it produces wrong output rather than no output.

So prefer a narrow On Error Resume Next around the one risky line, closed immediately with On Error GoTo 0, over a handler draped across the whole procedure. And always check Err.Number after a Resume Next section — suppressing an error without inspecting it is the same as not knowing:

On Error Resume Next
Set ws = ThisWorkbook.Worksheets("Archive")
On Error GoTo 0

If ws Is Nothing Then
    Set ws = ThisWorkbook.Worksheets.Add
    ws.Name = "Archive"
End If

Logging beats MsgBox for anything unattended

MsgBox "Error..." works when a person is watching. For a macro that runs on a schedule, or inside a loop over 400 files, it is the wrong tool — the macro stops on a dialog nobody sees, and you lose the detail on everything else.

Collect failures instead and report once at the end:

Sub ProcessAll()
    Dim f As Variant, failures As String, okCount As Long

    For Each f In fileList
        On Error Resume Next
        ProcessOne CStr(f)
        If Err.Number <> 0 Then
            failures = failures & vbCrLf & f & " - " & Err.Description
            Err.Clear
        Else
            okCount = okCount + 1
        End If
        On Error GoTo 0
    Next f

    If Len(failures) = 0 Then
        MsgBox okCount & " files processed."
    Else
        MsgBox okCount & " processed. Failed:" & failures
    End If
End Sub

One bad file no longer abandons the other 399, and you finish with a list of exactly which ones need attention. Writing the same information to a Log sheet with a timestamp — see VBA snippets you’ll reuse constantly — makes it answerable later as well.

Fail loudly, not silently

The most dangerous error-handling pattern is the one that looks tidiest:

On Error Resume Next   ' <- do not do this at the top of a procedure

Everything after it succeeds as far as anyone can tell. The copy that did not happen, the sheet that was not found, the division by zero — all swallowed, and the macro reports completion. The resulting bug surfaces weeks later as a number that is quietly wrong, which is much more expensive than a crash would have been.

A macro that stops with a clear message has done its job. A macro that continues past a condition it does not understand has not.

The same logic argues for validating up front, before doing any work:

If Not SheetExists("Data") Then
    MsgBox "Sheet 'Data' not found. Nothing was changed.", vbExclamation
    Exit Sub
End If

Checking preconditions first is better than handling the resulting error later, because nothing has been half-done at that point.

Remember there is no undo

Running a macro clears Excel’s undo stack entirely. A procedure that errors halfway through has left the workbook in a state Ctrl + Z cannot repair, and the user has no way back.

For anything destructive, build the escape in:

  • ThisWorkbook.Save before starting, so there is a known-good version
  • Work on a copy of the sheet and swap it in at the end
  • Confirm the scale first: MsgBox "Delete " & n & " rows?", vbYesNo
  • In the handler, say what state the workbook is in — “Stopped after processing 120 of 400 rows. Rows 121 onward are unchanged.” A handler that says only “An error occurred” leaves the user unable to decide what to do next.

That last point is the difference between error handling that is for your benefit and error handling that is for the user’s.

When the error is not an error

Some failures do not raise anything. The macro runs cleanly and the answer is wrong — a loop that skipped half the rows because it deleted forwards, a range that stopped at a blank, a Find that matched the wrong cell because LookAt was inherited from a previous search.

No handler catches these. The tools are the ones in the section above — breakpoints, F8, the Locals window — plus one habit: check the result against something you already know. A row count, a control total, three rows by eye. Loops and conditions covers the specific loop mistakes that cause most of them.

FREE EXCEL UPDATES

Get practical Excel tips in your inbox

New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.

=PRACTICE

Go deeper with this skill

Turn the idea into a repeatable Excel workflow you can explain, rebuild, and review later. For this article, the goal is to practice: On Error Goto vs Resume Next, using breakpoints and Step Into to watch code run, and a clean pattern for handling errors without leaving a mess behind.

Practice workbook setup

Create a small practice workbook with one raw-data sheet, one working sheet, and one final output sheet.

Practice workflow

  1. Rebuild the example once exactly as described, then repeat it with different labels, dates, or amounts.
  2. Write a short note beside the result explaining what each step is doing and why it matters.
  3. Change one input value and confirm the output updates in the way you expected.
  4. Save a clean copy of the workbook before experimenting further.

Quality checks

  • Inputs, calculations, and final outputs are separated clearly.
  • Headings describe the data without relying on memory or hidden context.
  • The final result can be understood by someone who did not build the workbook.

Common mistakes

  • Mixing raw data and manual adjustments in the same cells.
  • Skipping a quick review after the result looks correct.
  • Building a one-off fix instead of a repeatable workflow.

Next actions

  • Apply the same pattern to a real workbook with 20 to 50 rows of sample data.
  • Add one note that explains when you would not use this approach.

Formula focus: even if this workflow is not formula-heavy, add one check cell that confirms the final output still matches the source data.