In Excel VBA, On Error GoTo label tells a procedure to jump to a named error-handling section when a run-time error occurs. Put the statement before code that might fail, add the matching label in the same procedure, and handle the error there; use Exit Sub or Exit Function before the label so normal execution does not fall into the handler.
What On Error GoTo means
VBA executes a procedure from top to bottom. Without an active error-handling instruction, a run-time error normally stops the procedure and displays an error message. With On Error GoTo HandleError, VBA instead transfers control to the line marked HandleError: when an eligible run-time error occurs. The label is a marker inside the same Sub or Function; it is not a separate procedure.
The statement does not prevent errors, repair bad data, or make a macro safe by itself. It gives your code a chance to respond: explain what went wrong, record details, close resources, restore application settings, or exit cleanly. A useful handler addresses a realistic failure and preserves enough information to diagnose it.
Basic syntax and a working example
Here is a small procedure that reads two cells on a worksheet named Data and divides the value in B2 by the value in A2. A missing sheet, a nonnumeric value, or a zero denominator can cause a run-time error, so the procedure routes such failures to its handler.
Public Sub DivideCells()
Dim ws As Worksheet
Dim numerator As Double
Dim denominator As Double
On Error GoTo HandleError
Set ws = ThisWorkbook.Worksheets("Data")
numerator = CDbl(ws.Range("B2").Value)
denominator = CDbl(ws.Range("A2").Value)
ws.Range("C2").Value = numerator / denominator
MsgBox "Calculation completed."
Exit Sub
HandleError:
MsgBox "Calculation failed. Error " & Err.Number & ": " & _
Err.Description, vbExclamation
End Sub
In this example, On Error GoTo HandleError activates the handler. The successful path reaches Exit Sub and ends the procedure. That exit is important: without it, execution would continue into HandleError: even when no error occurred, producing a misleading failure message. The handler displays the error number and description supplied by VBA.
Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, even if the code changes cell values or other workbook content.
How VBA transfers control to a handler
A label is written as a name followed by a colon, such as HandleError:. The name in the On Error GoTo statement must match that label. Keep the handler within the procedure where the error-handling statement appears; a handler cannot be a label in another Sub or Function. Labels are commonly placed near the bottom of a procedure, after the normal exit path.
When an error occurs, VBA jumps to the active handler rather than continuing at the next ordinary statement. This is a control-flow jump, so the handler should be designed deliberately. For instance, if a procedure has opened a file or temporarily changed an application setting, the handler may need to run cleanup before it exits. If there is nothing to clean up, it can report the issue and end the procedure.
A handler is not a general-purpose shield for every later operation. Once VBA is executing an error handler, that same handler is considered active. If another error occurs inside it, the active handler cannot catch that second error; control may pass to a calling procedure that has an enabled handler, or the error may stop execution. Keep handler code simple and avoid putting complicated, failure-prone work inside it.
Choose the right error-handling instruction
VBA provides three commonly used forms, and they do different jobs:
On Error GoTo labelredirects execution to a named handler. Use it when the procedure needs a planned response to a failure.On Error Resume Nexttells VBA to continue with the statement immediately after the one that caused an error. It is useful for narrowly scoped checks where an error is expected, but it can conceal failures if left active across unrelated code.On Error GoTo 0disables the current procedure’s enabled error handler. After this instruction, an unhandled run-time error follows the normal VBA behavior instead of jumping to the previously named label.
Do not confuse On Error Resume Next with the Resume Next statement. The former changes how subsequent errors are handled. The latter is used from an error-handling routine to resume execution at the statement immediately following the one that failed. A handler can also use Resume to retry the failed statement or Resume label to continue at a chosen line, but retrying without fixing the cause can simply trigger the same error again.
For example, On Error Resume Next can be appropriate around one operation whose failure is expected, followed immediately by a check of Err.Number. It is usually a poor choice for a long macro because a failed assignment or worksheet operation could be silently skipped, leaving later code to work with incomplete results.
Read and preserve useful error details
The built-in Err object contains information about the most recent run-time error. Err.Number is its numeric code, and Err.Description is a description. Depending on the problem, Err.Source may also help identify the component that raised it. Capture or report these details while handling the error, rather than showing a generic message that makes diagnosis difficult.
When using On Error Resume Next, check Err.Number immediately after the operation you are allowing to fail. This keeps the check connected to the operation in question. If you intend to continue and perform another independent check, clear the previous error information with Err.Clear before the next operation; otherwise, an old error value can be mistaken for a new failure. Avoid clearing the error before you have recorded details you need.
For a larger macro, a useful message tells the user what the macro could not do, while a log or diagnostic message retains the specific error number and description. Avoid reporting that a task succeeded if it only partially completed. In a batch process, for example, distinguish a failure on one row from a failure that prevented the entire procedure from running.
Common mistakes and how to prevent them
- Forgetting the normal exit. Put
Exit SuborExit Functionbefore the handler label. Otherwise successful execution can run the handler as ordinary code. - Using a mismatched label. The label referenced by
On Error GoTomust exist in the same procedure. A misspelled or missing label is a code error, not a run-time problem the handler can catch. - Hiding errors with Resume Next. If that mode is used, keep it around only the operation that may fail, inspect
Err.Number, and restore normal handling withOn Error GoTo 0when appropriate. - Assuming every problem is catchable. Error handling is for run-time errors. It does not make invalid VBA syntax compile, nor does it guarantee recovery from every condition, such as a severe interruption or an error inside the active handler.
- Displaying too little information. A message such as “Something went wrong” may frustrate users and make debugging harder. Include the failed task and, where useful, the error number and description.
- Failing to clean up. If the procedure changes settings or acquires resources, plan how both success and error paths restore or release them. Keep cleanup code simple because a new error during cleanup can complicate handling.
When a label handler is the better choice
Use On Error GoTo label when an error needs a considered response: a clear message, a safe exit, cleanup, or a recorded diagnostic. It makes the exceptional path visible and gives the procedure one place to deal with failures. This is particularly useful in macros that update multiple cells, work with files, or depend on a particular worksheet being present.
Use On Error Resume Next only when continuing after a specific failure is intentional, such as checking whether an optional object or item can be accessed. It should be narrow and followed by a direct check. If the macro cannot sensibly continue after an error, a named handler is generally easier for a reader to follow than silently skipping the failed statement.
Neither approach replaces ordinary validation. If a user is expected to enter a number, test that input before converting or calculating with it. If a required worksheet is missing, a deliberate check can provide a more helpful message than relying on a generic object error. Validation handles expected conditions; error handling is a fallback for run-time failures that still occur.
Excel platform and macro safety
The VBA error-handling syntax is the same in desktop Excel for Microsoft 365 on Windows and Mac. The VBA editor and some keyboard shortcuts differ, and a workbook must be opened in desktop Excel to run VBA: Excel for the web can open and edit many macro-enabled workbooks but cannot create, run, or edit VBA macros. Macros that depend on Windows-only features, such as certain ActiveX controls or Windows API calls, may need changes to work on Mac; ordinary On Error GoTo code is not itself Windows-specific.
On Windows, Alt+F11 opens the Visual Basic Editor in Excel. On Mac, the shortcut can depend on the keyboard and system settings; Option+F11, sometimes with the Fn key, is commonly used. You can also open the editor from the Developer tab. Do not enable all macros merely to run one file. Only run macros from sources you trust, and follow your organization’s security policy. Error handling does not make untrusted code safe.
Frequently Asked Questions
Where does the error label go in VBA?
Put the label inside the same Sub or Function as the On Error GoTo statement. A common layout places it near the end of the procedure, after an Exit Sub or Exit Function on the normal path.
Can I use On Error GoTo in a VBA Function?
Yes. It works in a Function as well as a Sub. Include a normal return path that assigns the function’s result, and make sure the error path returns an intentional value or otherwise reports the failure appropriately for the function’s purpose.
What is the difference between On Error GoTo 0 and Resume Next?On Error GoTo 0 turns off the enabled handler in the current procedure. Resume Next, used inside an error handler, continues after the statement that failed. On Error Resume Next is different again: it changes how later errors are handled so execution continues automatically.
Can one error handler catch an error in another Sub?
No. A label belongs to its own procedure, so a caller’s label is not a direct target for an error in another Sub. The called procedure can handle the error itself, or allow the error to propagate so a calling procedure with an enabled handler can respond.
Why does my error handler run even when there is no error?
Normal execution may be falling through into the handler label. Add Exit Sub or Exit Function before the label so the successful path ends before VBA reaches the handler code.
Related
- Excel Shortcut to Select Cells to the End of a Column
- Excel Shortcut to Select the Active Cell’s Entire Column
- Excel Shortcut to Select to the Bottom of a Column
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Updating Items in a VBA Dictionary: Examples and TipsTo update a value in a VBA Scripting.Dictionary, assign a new value to its existing key: dict("name") =…
- Excel VBA Global Variables: Scope and ExamplesIn Excel VBA, a global variable is a value that procedures throughout the same VBA project can access.…
- MS Access VBA Error Handling: Guide, Patterns, and TipsIn Microsoft Access VBA, handle runtime errors with an On Error statement: use On Error GoTo HandlerLabel for…
- Named Ranges in Excel VBA: When Do Names Need Quotes?Usually, yes: when you write a named range directly inside a VBA reference such as Range("TaxRate"), put its…
- Mouse Macro Recorder: How It Works and What to ChooseA mouse macro recorder is software or a device feature that records actions such as clicks, pointer movements,…
- VBA Copy PasteSpecial: Values, Formats, and MoreIn Excel VBA, copy a range and call PasteSpecial on the destination to paste only values, formats, formulas,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.