Without error handling, a VBA macro stops and displays a cryptic error message the moment something goes wrong. Error handling gives you control over what happens next. Excel VBA provides two main error-handling statements: On Error Resume Next and On Error GoTo Label. Understanding the difference — and choosing the right one for each situation — is the key to writing robust, production-ready macros.
This guide covers both statements, the Err object, and six practical examples. These include a safe file-exists check, a graceful division-by-zero handler, a multi-step macro with centralised error reporting, a retry loop, logging errors to a sheet, and cleaning up resources even when an error occurs.
On Error Resume Next only for a single known operation that might legitimately fail. Always follow it with On Error GoTo 0 immediately after. Never leave Resume Next active across many lines of code — it silently swallows all subsequent errors.The Two Error Handling Statements
| Statement | What it does | Best used for |
|---|---|---|
| On Error Resume Next | Skips the erroring line and continues execution on the next line | Single operations where failure is expected and acceptable, e.g. testing file existence |
| On Error GoTo Label | Jumps to a labelled error handler block when any error occurs | Multi-step macros where any failure should trigger cleanup and a user message |
| On Error GoTo 0 | Turns off any active error handling and re-enables default error messages | Resetting after a Resume Next block to re-enable error visibility |
| Resume Next (inside handler) | Continues execution from the line after the one that caused the error | Recovery inside a GoTo handler — use when you can fix the condition and retry |
The Err Object
When an error occurs, VBA populates the Err object with details about the error. Two properties are most useful: Err.Number gives the error code, and Err.Description gives a human-readable message. Use these inside your error handler to log or display meaningful information. After handling an error, call Err.Clear to reset the object.
Examples 1–4: Error Handling in Practice
Resume Next is ideal here because failing to open a file is an expected, recoverable condition. The macro attempts to open the file. If the open fails, Err.Number is non-zero and the code takes the "not found" branch. The key point is that On Error GoTo 0 is called immediately after to disable Resume Next.
GoTo is the right choice for macros with several steps where any failure should stop processing and show a helpful message. The ErrHandler label at the bottom catches all errors. The Exit Sub above it prevents the handler from running when no error occurs.
For automated macros that run unattended, a message box is not useful. Instead, write the error details to a log sheet. The handler appends a new row with the error number, description, and timestamp. The macro can then continue processing the next item rather than stopping entirely.
When a macro opens a file or creates an object, it must clean up even if an error occurs. The error handler closes the workbook and sets the object to Nothing. This prevents open file handles from accumulating and corrupting the Excel session.
Examples 5–6: Retry Loop and Checking Specific Error Numbers
Some errors are transient — a file may be temporarily locked by another user. A retry loop attempts the operation up to three times before displaying a final error message. Each attempt uses Resume Next, checks Err.Number, and either continues or waits briefly before retrying.
Not all errors are equal. Error 1004 means a cell reference was invalid. Error 9 means a subscript was out of range. By checking Err.Number inside the handler, you can provide a more specific message and take different corrective actions depending on which error occurred.
Common Issues and How to Fix Them
Resume Next silently swallows errors across many lines
Leaving On Error Resume Next active for more than one or two lines is a common mistake. Every line after the statement runs silently even if it fails. The result is incorrect output with no error message. Always add On Error GoTo 0 immediately after the single line you intended to guard. This re-enables normal error reporting for the rest of the sub.
The error handler label causes a "Label not defined" compile error
The label in On Error GoTo ErrHandler must exactly match the label defined later in the sub. Labels are case-sensitive in VBA and must be followed by a colon. A common typo is a missing colon on the label line. Verify that the label declaration reads ErrHandler: (with the colon) and that the GoTo statement spells it identically.
Frequently Asked Questions
-
What is the difference between On Error Resume Next and On Error GoTo?+On Error Resume Next tells VBA to skip the line that caused the error and continue with the next line. It is appropriate for testing a single operation that might legitimately fail. On Error GoTo Label redirects execution to a named error handler block when any error occurs. It is appropriate for multi-step macros where any failure should trigger cleanup, logging, or a user message.
-
When should I use On Error Resume Next?+Use Resume Next only when you are testing a single specific operation where failure is expected and acceptable. For example, checking whether a sheet exists, opening a file that may not be present, or deleting an object that may not exist. Always follow it immediately with On Error GoTo 0 to re-enable normal error handling. Never leave Resume Next active across multiple unrelated lines of code.
-
What does Err.Number contain and how do I use it?+Err.Number contains the numeric error code of the last error that occurred. If no error occurred, it is 0. Common codes include 9 (subscript out of range), 13 (type mismatch), and 1004 (application-defined error). Use Err.Number in a Select Case block inside your error handler to provide different responses for different error types. Call Err.Clear after handling the error to reset the object to zero.
-
How do I ensure cleanup code always runs, even after an error?+Place cleanup code in the error handler and duplicate or call it in the normal exit path too. A common pattern is: normal flow executes, hits Exit Sub, skips the handler. If an error occurs, it jumps to the handler, which runs the cleanup code. This way, cleanup runs regardless of whether an error occurred. Alternatively, use a CleanUp label that both the normal flow and the error handler jump to before exiting.