VBA Error Handling Tutorial: Errors, Handlers, and Cleanup

VBA error handling lets a macro respond to a runtime problem instead of stopping unexpectedly. Start with On Error GoTo HandlerName for a procedure that needs a clear recovery or cleanup path; reserve On Error Resume Next for a small, specific operation whose failure you check immediately.

What VBA error handling does

VBA reports a runtime error when a statement cannot complete—for example, if code refers to a worksheet that does not exist, tries to open an unavailable file, or uses an object variable that has not been set. Without an active handler, execution stops and VBA displays an error dialog. That can be useful while developing, but a finished macro should usually give the user a meaningful explanation and leave Excel in a sensible state.

Error handling does not make an operation succeed, and it should not conceal a defect. Its job is to determine what happens after a failure: record or display useful information, close resources that were opened, skip an optional operation, or stop the procedure safely. A handler is local to the procedure where it is enabled. If a called procedure does not handle an error, the error can pass back to its caller, which may handle it there.

The three main On Error forms

VBA provides three commonly used forms of the On Error statement. Understanding their different effects prevents many confusing bugs:

On Error GoTo LabelName enables the procedure’s error handler at the named line. If a later statement raises a runtime error, VBA transfers control to that label.
On Error Resume Next tells VBA to continue at the statement immediately after the one that failed. The failure is not automatically reported, so inspect Err right away.
On Error GoTo 0 disables the procedure’s enabled handler. A later unhandled error will stop execution in the usual way.

These statements affect runtime errors, not compile-time problems such as a misspelled keyword or a missing End If. Fix compile errors before testing runtime handling. Also, On Error GoTo 0 does not jump to the start of a procedure; it changes the active error-handling behavior from that point onward.

A basic procedure-level handler

A useful first pattern is to put the normal work before a clearly named handler, then report the error at that handler. This example looks for a worksheet called Summary. Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z, and the copy gives you a way back if the code changes workbook contents.

Sub CheckRequiredSheet()
    Dim ws As Worksheet
    Dim message As String

    On Error GoTo HandleError

    Set ws = ThisWorkbook.Worksheets("Summary")
    MsgBox "Found: " & ws.Name
    Exit Sub

HandleError:
    message = "Error " & CStr(Err.Number) & ": " & Err.Description
    MsgBox message, vbExclamation, "Macro stopped"
End Sub

Exit Sub is important: it prevents successful execution from continuing into the handler label. If the worksheet is missing, VBA jumps to HandleError, where the message includes the error number and description. For a larger macro, the handler may log details, clean up, or re-raise the error rather than showing a dialog immediately.

Using Resume Next safely

On Error Resume Next can be appropriate when a failure is expected and has a specific meaning. For example, a macro might attempt to find an optional worksheet and create it if it is absent. The key is to keep the suppressed-error region short and check the outcome before doing unrelated work.

After a statement that might fail, inspect Err.Number. Zero means there is no recorded error; a nonzero value indicates a failure. Save any error details you need before running more statements that could fail, because a later error can replace the information in the Err object. Once the expected failure has been checked, use On Error GoTo 0 to restore ordinary error behavior.

A common mistake is to put On Error Resume Next at the top of a long procedure and leave it in effect. Then a failed worksheet reference, calculation, or save operation may be followed by code that assumes it worked. The macro can produce incomplete or misleading results without showing the original cause. Use a regular handler for broad procedure-level recovery, and use Resume Next only around a narrowly defined operation.

Cleanup when a macro fails

Macros often open workbooks, create temporary files, or change application settings. If an error interrupts the normal path, those resources may be left open or settings may remain changed. A cleanup section gives both successful and failed runs a common route for restoring state. The following Windows-and-Mac-friendly example tries to open a workbook and closes it if it was opened.

Save a separate copy before running this sample as well. It opens a file named source.xlsx in the same folder as the macro workbook; change that filename or location to match your own test setup.

Sub OpenAndCloseSource()
    Dim sourceBook As Workbook
    Dim errorNumber As Long
    Dim errorDescription As String
    Dim sourcePath As String

    On Error GoTo HandleError

    sourcePath = ThisWorkbook.Path & Application.PathSeparator & "source.xlsx"
    Set sourceBook = Workbooks.Open(Filename:=sourcePath, ReadOnly:=True)

    ' Put the workbook-processing steps here.
    GoTo CleanUp

HandleError:
    errorNumber = Err.Number
    errorDescription = Err.Description
    Resume CleanUp

CleanUp:
    ' Ignore a close failure so it does not hide the original error.
    On Error Resume Next
    If Not sourceBook Is Nothing Then sourceBook.Close SaveChanges:=False
    On Error GoTo 0

    If errorNumber <> 0 Then
        MsgBox "Error " & CStr(errorNumber) & ": " & errorDescription
    End If
End Sub

The error number and description are copied before cleanup begins so they remain available for the message. Application.PathSeparator avoids hard-coding a slash style in the path. The brief Resume Next section is limited to closing the workbook: if closing itself fails, that secondary problem will not replace the original message. In production code, you may prefer to log cleanup failures separately rather than ignore them.

Reading Err and raising useful errors

The built-in Err object provides information about the most recent runtime error. Err.Number identifies it, Err.Description gives a text explanation, and Err.Source can identify the procedure or component that raised it. Capture these values near the handler when they matter. A message that says only “something went wrong” is less useful than one that includes the operation, workbook or file involved, and the error details.

Code can also raise an error deliberately when it detects invalid input or a condition that would make later work unsafe. Err.Raise allows a procedure to report a meaningful failure to its caller. Custom error numbers should use the vbObjectError constant as a base to distinguish application-defined errors from standard VBA errors.

Sub ValidateAmount()
    Dim amount As Double

    On Error GoTo HandleError

    amount = -1
    If amount < 0 Then
        Err.Raise Number:=vbObjectError + 1000, _
                  Source:="ValidateAmount", _
                  Description:="Amount cannot be negative."
    End If
    Exit Sub

HandleError:
    Debug.Print Err.Number, Err.Source, Err.Description
End Sub

This example reports a validation failure to the Immediate window in the VBA editor. In a larger application, the caller might catch the custom error and decide whether to show a message, request corrected input, or stop a sequence of related operations. Avoid using a custom error as a substitute for checking ordinary values that can be handled directly.

Choosing a recovery strategy

The right handler depends on what the macro can safely do after a failure. Consider these questions before choosing a pattern:

Is the failure expected? An optional worksheet may be absent by design; a required input file may not be. Handle the optional case locally, but report a missing required resource clearly.
Can the procedure continue correctly? Continue only when the failed action is independent of later work or a fallback is defined. Otherwise stop rather than creating partial output.
Does the macro need cleanup? If it opened a workbook or changed settings, route both success and error paths through restoration code.
Who can make the next decision? A low-level helper can raise an error to its caller, while a top-level macro may be the right place to display a user-facing message.

For example, skipping an unavailable optional note sheet may be harmless if the rest of the report is complete. Skipping a required source-data workbook is not equivalent: calculations based on missing data could look valid while being wrong. Error handling should reflect that difference rather than treating every error as permission to continue.

Debugging and common mistakes

During development, use the VBA editor’s debugging tools to find the statement that failed. Breakpoints, stepping through code, and inspecting variables can reveal whether the problem is a bad file path, an unexpected cell value, or an object that was never assigned. The Immediate window is useful for diagnostic output such as the Debug.Print statement in the validation example. Do not rely on a broad handler to replace debugging; it may hide the original cause.

Watch for these recurring problems:

Forgetting to exit before the handler: normal execution can fall through into the handler label. Use Exit Sub or Exit Function before it.
Using the wrong resume behavior: Resume Next continues after a failed statement, whereas Resume retries the statement that failed. Retrying is not automatically safe; for example, it may repeat an operation with side effects.
Assuming an error was cleared by moving on: inspect or clear error state deliberately when using Resume Next, and do not depend on stale Err details later in the procedure.
Handling an error inside the handler: an error that occurs while a handler is already handling another error is not handled by that same active handler. Keep handler code simple and protect delicate cleanup separately.
Suppressing errors globally: this can allow later statements to use invalid data or objects. Narrow the scope and restore normal handling as soon as the expected condition is checked.

One related misconception is that On Error GoTo 0 sends execution back to normal code or clears every problem in the project. It only disables the current procedure’s active error handler. It does not repair data, undo a statement, or replace a deliberate recovery path.

Frequently Asked Questions

What is the best error-handling pattern in VBA?
For most procedures, use On Error GoTo HandlerName, keep the handler near the end, and add an explicit exit before the label. Add a cleanup route when the procedure opens resources or changes application state.

What is the difference between On Error GoTo and On Error Resume Next?
On Error GoTo transfers control to a named handler. On Error Resume Next continues at the next statement after the failure, so the code must check whether that operation succeeded before proceeding.

How do I display the VBA error message?
In a handler, use Err.Number and Err.Description to build a message, as in the first example. Capture those values before cleanup or other potentially failing statements if you need to preserve the original error.

Does On Error GoTo 0 clear an error?
It disables the current procedure’s enabled error handler; it is not a recovery action and does not undo the failed operation. Use it after a narrowly scoped Resume Next section when you want later errors to behave normally.

Can VBA macros be undone with Ctrl+Z?
No. Excel’s undo history does not reverse changes made by a VBA macro. Save a copy of the workbook before running unfamiliar or data-changing code, and test on sample data first.

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.