Excel VBA On Error MsgBox: A Practical Guide

In Excel VBA, use On Error GoTo HandlerName to send a runtime error to a handler, then show its details with MsgBox and the Err object. For example, MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Macro error" displays the error number and explanation; put it inside a procedure-level error handler, not on its own.

What On Error and MsgBox do

On Error controls what VBA does when a running procedure encounters a runtime error, such as trying to open a file that does not exist or using an invalid worksheet reference. MsgBox displays a dialog to the person using the workbook. Combined, they let a macro stop gracefully and explain what went wrong instead of leaving Excel at a confusing highlighted line.

A message box is not itself an error handler. It cannot catch an error simply because it appears somewhere in the code. VBA must first be directed to a handler, usually with On Error GoTo. Also, this approach handles runtime errors; it does not intercept syntax or compile errors that prevent the procedure from running in the first place.

The examples below are for desktop Excel for Microsoft 365 on Windows. VBA macros do not run in Excel for the web. Excel for Mac also supports many VBA procedures and MsgBox, but code that depends on Windows-only features such as certain API calls, ActiveX controls, or COM automation may need changes.

A basic error handler that displays a message

Put the handler in the same procedure as the operation that might fail. The following example attempts to open a workbook and reports the error number and description if the open operation fails. Replace the illustrative path with a real file path before using it.

Sub OpenSourceWorkbook()
    Dim sourcePath As String

    On Error GoTo ErrorHandler

    ' Replace this example path with the workbook to open.
    sourcePath = "C:\Reports\source.xlsx"
    Workbooks.Open Filename:=sourcePath

    MsgBox "The workbook opened successfully.", vbInformation, "Complete"
    Exit Sub

ErrorHandler:
    MsgBox "The workbook could not be opened." & vbCrLf & _
           "Error " & Err.Number & ": " & Err.Description, _
           vbExclamation, "Open failed"
End Sub

On Error GoTo ErrorHandler activates the named handler. If an error occurs, execution jumps to the ErrorHandler: label. The Exit Sub before that label matters: without it, a successful run would continue into the handler and display a failure message anyway. A label is a location within the procedure, not a separate procedure, so it must be spelled exactly the same in the statement and at the label.

Before running any macro that changes or opens workbook data, save a copy of the workbook. Macro actions cannot be undone with Ctrl+Z, and error handling does not reverse changes already made.

Understanding Err.Number and Err.Description

When VBA raises a runtime error, the built-in Err object provides information about it. Err.Number is the numeric error code, while Err.Description is a human-readable description. Including both is useful for troubleshooting: a message such as “Error 1004” is more specific than a generic “Something went wrong,” and the description can point to an invalid range, unavailable file, or another cause.

Use the Err details promptly in the handler. If the handler calls other code that can itself fail, the error information may no longer describe the original problem. For a more involved handler, store the details in variables before doing cleanup or other work:

Sub ShowSavedErrorDetails()
    Dim savedNumber As Long
    Dim savedDescription As String

    On Error GoTo ErrorHandler

    ' This line illustrates a runtime error for demonstration.
    Err.Raise  vbObjectError + 1000, "ShowSavedErrorDetails", _
               "Example problem for the error handler"
    Exit Sub

ErrorHandler:
    savedNumber = Err.Number
    savedDescription = Err.Description
    MsgBox "Error " & savedNumber & ": " & savedDescription, _
           vbExclamation, "Problem"
End Sub

Err.Raise deliberately creates an error here so the handler can be demonstrated. Remove that line in a real macro and put the operation that might fail in its place. The vbObjectError constant is commonly used as the base for custom VBA error numbers; it does not mean that every error should be replaced with a custom one.

Choosing a message box type and response

The optional buttons argument determines the buttons and icon shown. Common constants include vbOKOnly, vbYesNo, vbExclamation, vbCritical, and vbInformation. You can combine a button choice and an icon, for example vbYesNo + vbQuestion. The optional title argument supplies the text in the dialog’s title bar. If omitted, VBA uses a default title.

For an error that requires no decision, an OK-only message is usually clear. If the user must choose whether to continue, check the result returned by MsgBox; do not assume the user clicked Yes. This example asks before continuing to a potentially consequential step:

Sub AskBeforeContinuing()
    Dim answer As VbMsgBoxResult

    answer = MsgBox("Continue with the update?", _
                    vbYesNo + vbQuestion, "Confirm update")

    If answer = vbYes Then
        MsgBox "The update can proceed.", vbInformation, "Confirmed"
    Else
        MsgBox "No changes were requested.", vbInformation, "Cancelled"
    End If
End Sub

Use this kind of confirmation for a decision, not as a substitute for checking whether an operation succeeded. The message box result is a VbMsgBoxResult, and named constants such as vbYes are easier to understand than comparing the result to an unexplained number.

When On Error Resume Next is appropriate

On Error Resume Next tells VBA to continue with the next statement after an error rather than jumping to a labeled handler. This can be useful for one narrowly defined operation when the code checks immediately whether that operation failed. Used across a long procedure, however, it can conceal errors and allow later statements to act on missing or invalid data.

For example, if a macro checks whether a worksheet exists by attempting to retrieve it, keep the resume-next scope short and inspect Err.Number straight away. The example below illustrates the pattern; it does not create a worksheet.

Sub CheckForSummarySheet()
    Dim summarySheet As Worksheet
    Dim lookupError As Long

    On Error Resume Next
    Set summarySheet = ThisWorkbook.Worksheets("Summary")
    lookupError = Err.Number
    On Error GoTo 0

    If lookupError = 0 Then
        MsgBox "The Summary sheet exists.", vbInformation, "Sheet check"
    Else
        MsgBox "The Summary sheet was not found.", _
               vbExclamation, "Sheet check"
    End If
End Sub

On Error GoTo 0 turns off the current procedure’s active error handling, so later unexpected errors are not silently skipped. This is different from On Error GoTo ErrorHandler, which sends an error to a label. Avoid using resume-next for an entire macro merely to prevent an error dialog: the macro may keep running with incomplete results.

How to choose an error-handling approach

Use a labeled handler for operations where failure should stop the current task and produce a clear explanation. Examples include opening an input file, saving output, or working with a required worksheet. Use On Error Resume Next only when an error is an expected possibility and the next line checks the result before the code proceeds. For optional operations, the handler can explain that the optional step was skipped, but it should still distinguish that case from a failure that makes the whole task unreliable.

Plan what should happen after the message appears. A simple macro may end. A more substantial procedure may need to close a file it opened, restore application settings, or leave temporary data in a known state. Cleanup should be designed deliberately; showing an error message does not automatically restore Excel’s state. If several procedures are involved, remember that error handling is local to each procedure. An error in a called procedure needs suitable handling there or must be allowed to pass back to a caller that can handle it.

Common problems and how to avoid them

The handler runs after a successful operation: add Exit Sub (or Exit Function) before the handler label so normal execution does not fall through into the error code.
The error message is vague: include Err.Number and Err.Description, and mention the operation that failed. Do not claim that a file is missing unless the code has actually checked that condition.
The macro continues in an unsafe state: decide whether it is safe to continue after an error. If not, exit the procedure after reporting the problem rather than resuming arbitrary statements.
Resume Next hides the real fault: limit it to a small section, save or inspect the error immediately, then restore normal handling with On Error GoTo 0.
The code does not compile: check spelling, labels, line-continuation characters, and declarations. A runtime error handler cannot catch a syntax error that stops VBA from compiling the procedure.
The macro cannot be run in the browser: VBA is for desktop Excel, not Excel for the web. Open the workbook in the desktop application to run its VBA code.

One frequent misconception is that a message box makes a macro safe because the user is warned. It does not roll back cell edits, restore deleted data, or make a partially completed task complete. Error handling reports and controls execution; recovery requires additional code or a saved copy.

Testing and using VBA in Excel

To inspect a macro on Windows, open the Visual Basic Editor with Alt+F11, or use Developer > Visual Basic if the Developer tab is available. In the editor, run a procedure from the editor or return to Excel and run it from Developer > Macros. The Developer tab may need to be enabled in Excel’s ribbon settings. On Mac, use Developer > Visual Basic when the Developer tab is shown; the keyboard shortcut can vary with the keyboard and function-key settings, so the ribbon command is a dependable alternative.

Test both the expected path and a controlled failure path. For example, use a copy of the workbook and temporarily supply a path that does not exist to see whether the handler gives a useful message. Then restore the valid path. Do not test a destructive macro on the only copy of important data. Also remember that macro security settings can block a workbook’s code; only enable macros in files from sources you trust.

Frequently Asked Questions

How do I show an error message in Excel VBA?
Use a procedure-level handler such as On Error GoTo ErrorHandler, then put a MsgBox statement below the handler label. Include Err.Number and Err.Description when you want useful diagnostic details.

What does On Error GoTo 0 do?
It disables the active error-handling mode in the current procedure. It is often used after a short On Error Resume Next section so that later errors are not inadvertently ignored.

Why does my VBA error handler show a message after the macro succeeds?
VBA may be falling through into the handler label after completing the normal statements. Add Exit Sub or Exit Function immediately before the handler label.

Can MsgBox catch an error by itself?
No. MsgBox only displays a dialog. An On Error statement must establish how VBA will respond to a runtime error before the message box in the handler can be reached.

Can Excel for the web run an Excel VBA MsgBox?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel to run VBA; browser-based automation uses different tools and is not the same as a VBA MsgBox.

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.