VBA error handling and debugging

Stepping through macro code line-by-line with F8 and ensuring ScreenUpdating and Calculation are always restored in a CleanExit block.
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.



Excel VBA Macros - Beginner to PRO Masterclass with Code Samples
Chandoo
Open on YouTube (opens in a new tab)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.Savebefore 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.
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
- Rebuild the example once exactly as described, then repeat it with different labels, dates, or amounts.
- Write a short note beside the result explaining what each step is doing and why it matters.
- Change one input value and confirm the output updates in the way you expected.
- 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.






