VBA error handling and debugging

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.