VBA Basic Error Handling: Patterns, Examples, and Tips

VBA basic error handling uses On Error to decide what a procedure should do when a runtime error occurs. For most macros, start with On Error GoTo and a clearly labeled handler; use On Error Resume Next only for a narrow operation whose possible failure you check immediately.

How VBA error handling works

A runtime error happens while VBA is executing code: a worksheet name might not exist, a file might be unavailable, or a calculation might divide by zero. Without an active handler, VBA stops the procedure and displays an error dialog. Error handling lets your code respond instead, for example by displaying a useful message, closing a resource, or passing the problem to the procedure that called it.

VBA does not use the same try/catch structure found in some other languages. Its error-handling behavior is set with an On Error statement in each procedure. A handler in one procedure does not automatically apply to every procedure in a project, so reusable routines need their own deliberate handling or a clear way to pass errors back to their callers.

Handling an error does not mean pretending it never happened. A good handler either recovers in a valid way or makes the failure understandable. Silently continuing can be worse than stopping: a macro might leave data incomplete while giving the impression that it finished successfully.

The three main On Error choices

VBA provides three common forms of On Error. They are not interchangeable:

On Error GoTo HandlerLabel enables a procedure-level handler. If a runtime error occurs, execution jumps to the named label.
On Error Resume Next tells VBA to continue at the statement immediately after the one that failed. Your code must inspect the result or the Err object to decide what to do.
On Error GoTo 0 disables the current procedure’s enabled handler. It does not mean “ignore errors”; subsequent unhandled errors will stop normal execution.

A label is a line ending in a colon, such as HandleError:. When using a label-based handler, put an Exit Sub or Exit Function before the handler label when normal execution must not fall through into the handler. Otherwise, the handler code could run even when there was no error.

A basic handler example

Before running any macro that changes a workbook, save a separate copy first: VBA macros cannot be undone with Ctrl+Z. This small example looks up a worksheet and reports a useful message if it is missing. The worksheet name is intentionally something you can change to test the error path.

Public Sub CheckSummarySheet()
    Dim target As Worksheet
    Dim errorNumber As Long
    Dim errorText As String

    On Error GoTo HandleError

    Set target = ThisWorkbook.Worksheets("Summary")
    MsgBox "The Summary sheet is available.", vbInformation
    Exit Sub

HandleError:
    errorNumber = Err.Number
    errorText = Err.Description
    MsgBox "Could not find or access the Summary sheet. Error " _
        & CStr(errorNumber) & ": " & errorText, vbExclamation
End Sub

ThisWorkbook refers to the workbook containing the code, which is often safer than ActiveWorkbook if the user can switch between open workbooks. The handler stores the error number and description before displaying them. Capturing those values early is useful because later code, including another operation that fails, can change the information available in Err.

The example reports the problem but does not repair it. If the worksheet is essential, stopping with a clear explanation may be the right behavior. If it is optional, the macro might instead skip a section or create the sheet. The appropriate response depends on whether continuing would produce a correct result.

Using the Err object

When an error occurs, VBA exposes details through the global Err object. Its most commonly used properties are:

Err.Number: the numeric error identifier. Zero indicates that no current error is recorded in the object.
Err.Description: a description of the error, which can help explain what failed.
Err.Source: the name of the object or application that reported the error, when available.

These properties help with messages, logs, and decisions about whether an operation can be retried. A message intended for a workbook user should explain the action that failed and what they can do next; a raw number by itself is rarely enough. During development, keeping the number and description is valuable because they help identify the underlying cause.

You can also raise an error intentionally when an input or state is invalid. For application-defined errors, VBA code commonly uses a number based on vbObjectError so it does not casually overlap with standard VBA error numbers. A caller can then handle that error just as it would handle an error raised by an object or runtime operation. Do not use an error as a substitute for ordinary control flow when a simple condition check is clearer.

Resume, recovery, and cleanup

After jumping to a handler, you can decide whether to stop, recover, or resume execution. The Resume statement returns execution to the line that caused the error. Resume Next, when used inside a handler, continues with the statement after the one that failed. Resume SomeLabel continues at a named label. These are different from the On Error Resume Next statement, which changes how later errors are handled.

Resuming is safe only when the code knows what the failed operation left behind. If a required file did not open, continuing to read from its workbook object is not recovery. A valid recovery might be to ask the user to choose another file, use a documented default, or abandon the operation with an explanatory message.

Cleanup matters when a procedure has opened files, created temporary resources, or changed application settings. Put normal cleanup on a clear exit path, and consider how the error path will release the same resources. If the cleanup itself might fail, handle that separately and avoid replacing the original error details with a secondary cleanup error. For lengthy procedures, small routines with specific responsibilities can make both cleanup and error reporting easier to reason about.

One practical design is for a lower-level procedure to detect an error and pass it upward, while a top-level macro shows the user-facing message. Another is for a routine to handle an expected, recoverable problem locally. Whichever approach you choose, make it clear whether a procedure has completed successfully; a caller should not have to guess from a blank return value or a message box.

When Resume Next is appropriate

On Error Resume Next is useful for a small, expected failure, such as checking whether an optional worksheet can be retrieved. Keep the enabled interval as short as possible, inspect Err.Number immediately after the operation, and then restore normal handling with On Error GoTo 0 or your intended handler.

For instance, a macro might try to get a sheet that is optional and proceed differently if it is absent. The important point is not the specific lookup: it is that the code checks the result before carrying on. If several unrelated statements run while Resume Next is active, any of them can fail and execution will continue, making the original cause harder to identify.

Do not use Resume Next as a project-wide “make errors go away” switch. It can conceal misspelled names, invalid object references, permission problems, and other failures that should stop the task. Also, checking only whether Err.Number is nonzero after many statements does not reliably tell you which operation failed. Narrow scope gives you a meaningful check and limits unintended side effects.

Common mistakes and how to avoid them

Falling into the handler: Add Exit Sub or Exit Function before a handler label when normal flow should bypass it.
Leaving Resume Next enabled: Turn it off promptly with On Error GoTo 0, or restore the intended handler. Otherwise, later failures may be ignored.
Ignoring the actual error: A handler that merely continues can leave partial results. Decide whether the procedure can safely recover, and report or propagate errors it cannot handle.
Using an unhelpful message: “An error occurred” gives little guidance. Include the operation, relevant context, and—where useful—the error number and description.
Assuming a handler covers every procedure: Error handling is set within procedures. Plan how errors from called routines will be handled rather than expecting one label to catch everything throughout a project.
Overwriting diagnostic details: Save the original error information before performing cleanup or other operations that might raise a new error.

Another frequent misconception is that Err.Clear and On Error GoTo 0 do the same thing. They do not: Err.Clear clears the properties of the Err object, while On Error GoTo 0 disables the active error-handling arrangement for the procedure. Choose the statement that matches what your code needs, and do not use either as a replacement for understanding the failure.

Choosing a pattern and testing it

Choose the pattern based on what the procedure should do when something goes wrong:

Use a labeled handler when failure should stop the current operation, trigger cleanup, or produce a clear report.
Use a short Resume Next section when an individual operation is expected to fail sometimes and the code can check the outcome immediately.
Let an error reach a caller when that caller has the context to choose the right response. Document or otherwise make that behavior clear.

Test both the successful path and realistic failure paths. For a worksheet lookup, try the expected sheet name and a name that does not exist. For file work, consider a missing file or a file that cannot be opened. Confirm that the macro gives an accurate message, does not claim success, and leaves the workbook in a sensible state. Avoid testing destructive operations on the only copy of important data.

In the Visual Basic Editor, the Error Trapping setting can affect how execution behaves while debugging. Under Tools > Options > General, the Error Trapping choices include breaking on all errors, breaking in class modules, and breaking on unhandled errors. If the editor stops at an error even though you expected a handler, check this setting and the active code path. During development, breaking at the source of a problem can be more useful than suppressing it; the setting does not replace sound error handling in the finished macro.

Frequently Asked Questions

What is the simplest VBA error handler?
Use On Error GoTo HandlerLabel, put an Exit Sub before the label, and add the label and response at the end of the procedure. Capture Err.Number and Err.Description if you need to explain what failed.

What is the difference between On Error GoTo 0 and Resume Next?
On Error GoTo 0 disables the procedure’s current handler. On Error Resume Next makes execution continue after each statement that causes an error. The latter needs a prompt check so failures are not silently overlooked.

Does On Error GoTo catch errors in every VBA procedure?
No. Error handling is set in the procedure where the On Error statement appears. A called routine can handle its own error or allow it to reach a caller that has an appropriate handler.

How do I find out what went wrong?
In a handler, inspect Err.Number and Err.Description, and consider recording the procedure or operation involved. Save those details before cleanup or other work that could produce another error.

Can I undo a macro after an error?
Usually, you cannot reverse a macro’s workbook changes with Ctrl+Z. Save a copy before running code that changes data, and design macros to validate inputs and avoid leaving partial work where possible.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.