Savory LifeRECIPES

VBA On Error Goto: Handle Errors Like a Pro

Updated November 5, 2026

Total time
10 min
prep 10 min · cook 0 min
Serves
1
one portion

Jump to recipe

VBA On Error Goto: Handle Errors Like a Pro

VBA On Error Goto is a structured way to catch and handle runtime errors in your macros, letting you redirect the code to a labeled section when something goes wrong. It works like a recipe’s safety net: instead of letting a crash stop your macro mid-way, you control the response. This guide gives you a complete, practical approach to using On Error Goto, including how to write the error handler, log errors, and clean up after them.

Ingredients

  • 1 VBA-enabled application (Excel, Access, or Word)
  • 1 code module (standard module or class module)
  • 1 error-prone procedure (e.g., a sub that opens a file or divides numbers)
  • 1 error handler label (e.g., ErrorHandler:)
  • 1 Exit Sub or Exit Function statement before the handler
  • 1 Resume or Resume Next statement (optional, for recovery)
  • 1 Err object (built-in, no need to add)
  • 1 MsgBox or logging method (for user feedback)

Instructions

  1. Identify the risky code. Look for lines that might fail: file operations, division, array indexing, or database queries. For example, Workbooks.Open "C:\missing.xlsx" will error if the file doesn’t exist.
  2. Add the On Error statement at the start of the procedure. Place On Error Goto ErrorHandler right after the Sub or Function declaration, before any other code. This activates error trapping for the entire procedure.
  3. Write the main code normally. Continue with your regular code. If an error occurs, VBA will jump to the label you specified. For example, a simple division: result = 10 / divisor.
  4. Add an Exit Sub (or Exit Function) before the error handler. After the main code, add Exit Sub so that if no error occurs, the procedure ends without running the error handler. This is critical—otherwise, the handler runs even on success.
  5. Create the error handler label. On a new line, type a label name followed by a colon, like ErrorHandler:. This is the target of the Goto.
  6. Write the error handling code. Inside the handler, decide what to do. Common actions: display a message with MsgBox "Error " & Err.Number & ": " & Err.Description, log the error to a text file, or set a default value. For example, if a file is missing, you might show a friendly message and exit.
  7. Use Resume to continue or exit. After handling, you can use Resume Next to continue from the line after the one that caused the error, or Resume to retry the same line (use with caution to avoid infinite loops). If you cannot recover, use Exit Sub to leave cleanly.
  8. Test with a deliberate error. Run your procedure with a condition that triggers an error (e.g., divide by zero). Confirm the handler runs and the macro doesn’t crash.
  9. Add error handling to every procedure that needs it. Repeat steps 2-8 for each sub or function that might error. For consistency, use a standard error handler pattern across your project.

Tips

  • Use On Error Resume Next for minor, expected errors. If you know a line might fail but you don’t care (like checking if an object exists), use On Error Resume Next before that line, then check Err.Number and reset with Err.Clear. This is lighter than a full Goto handler.
  • Always include Exit Sub before the handler. Forgetting this is the most common mistake—the handler runs even when no error occurs, causing unexpected behavior.
  • Log errors to a file for debugging. Instead of just a MsgBox, write Err.Number, Err.Description, and the procedure name to a text file (e.g., Open "C:\errorlog.txt" For Append As #1). This helps in production.
  • Don’t use On Error Goto 0 unless you want to disable handling. It turns off error trapping in the current procedure, which is rarely needed. If you use it, be sure to re-enable with another On Error statement.

Serving Suggestions

Use this error-handling pattern in any VBA project: Excel macros that process user input, Access forms that query databases, or Word automation that manipulates documents. It’s especially valuable when your macro runs unattended (e.g., scheduled tasks) because it prevents silent failures and gives you a log to review.

Variations

  • Centralized error handler: Instead of repeating code in every procedure, call a shared subroutine like HandleError Err.Number, Err.Description, "MyProc" that logs and displays errors consistently.
  • Retry logic: In the handler, ask the user if they want to retry. If yes, use Resume to re-execute the failing line (but ensure the condition that caused the error is fixed).
  • Error handling in functions: For functions, you can return a default value (e.g., MyFunction = 0) in the handler, then exit, so the caller gets a safe result.

Storage and Reheating

Error handling code doesn’t spoil, but keep your VBA project organized: store error handlers in the same module as the procedure, or in a dedicated standard module for shared handlers. Review your error logs (if you write them) weekly to spot recurring issues. When you update your code, re-test the error paths—they can break if you change variable names or add new risky operations.

Frequently Asked Questions

What does “On Error Goto 0” do? It disables error handling in the current procedure, meaning any error will cause the macro to stop and show the default VBA error dialog. It’s rarely used; usually you want to keep handling enabled.

Can I use multiple On Error Goto statements in one procedure? Yes, you can change the error handler mid-procedure. For example, you might use On Error Resume Next for a section where errors are expected, then switch back to On Error Goto ErrorHandler for the rest. Just be careful to reset it after the section.

What’s the difference between Resume and Resume Next? Resume re-executes the line that caused the error, which can cause an infinite loop if the error isn’t fixed. Resume Next continues from the line after the error, which is safer for most recovery scenarios.

Why does my error handler run even when there’s no error? This happens when you forget to add Exit Sub before the handler label. The code falls through into the handler. Always include an exit statement.

Should I use On Error Goto or On Error Resume Next? Use Goto when you need to handle different errors differently or perform cleanup. Use Resume Next for simple checks where you can ignore the error and test Err.Number immediately after. For most robust macros, Goto is preferred.

How do I handle errors in a loop? Put the On Error statement inside the loop if you want to handle each iteration separately, or outside if you want to exit the loop on first error. For example, On Error Resume Next inside a loop lets you skip bad items and continue.

Related

More recipes to cook

Safety notes: safe minimum internal temperatures from USDA FSIS; raw and undercooked fish guidance from the U.S. FDA. Food safety notes

Spotted an error? Tell us and we will correct it (corrections policy).