VBA Error Handling: On Error, Resume, and More

VBA error handling is the code you write to intercept runtime errors and control what your macro does when something goes wrong, instead of letting the program crash and show a default message. You implement it with the On Error statement, which tells Excel (or Access, Word, etc.) to jump to a specific label, resume from the next line, or treat an error as if it didn’t happen. The most common pattern is On Error GoTo ErrorHandler at the top of a procedure, followed by an error-handling block near the end.

Formula

The formulas here are simple: Average pace = finish time (in minutes) ÷ distance (in km), giving minutes per kilometer. Average speed = distance (km) ÷ (finish time ÷ 60), giving kilometers per hour. The division by 60 converts minutes to hours. If you work in miles, replace the distance unit with miles and the result is minutes per mile and miles per hour—the math is identical.

Worked Example

Using the default values from the calculator: finish time = 50 minutes, distance = 10 km.

1. Average pace: 50 ÷ 10 = 5.0 min/km.
2. Average speed: 10 ÷ (50 ÷ 60) = 10 ÷ 0.8333 = 12.0 km/h.

The calculator shows 5.00 min/km for pace and 12.00 km/h for speed, matching the hand calculation.

What VBA Error Handling Actually Does

VBA distinguishes between three kinds of errors: syntax errors (caught at compile time, like a misspelled keyword), runtime errors (happen while the code runs, like dividing by zero or trying to open a missing file), and logic errors (no error message, but the result is wrong). Error handling deals only with runtime errors. When a runtime error occurs and no handler is active, VBA shows a dialog box with the error number and description, and the macro stops. With a handler, you decide what happens instead.

The core statement has three forms:

• On Error GoTo Label — jumps to a line label when an error occurs.
• On Error Resume Next — ignores the error and continues with the next line.
• On Error GoTo 0 — turns off any active handler in the current procedure, restoring default behavior.

How to Structure a Handler

A typical error-handling block sits at the end of a procedure, separated by a label and an Exit Sub (or Exit Function) so it doesn’t run when there’s no error. Example:

Sub SafeDivide()
    On Error GoTo ErrHandler
    Dim a As Double, b As Double
    a = 10
    b = InputBox("Enter a divisor")
    MsgBox a / b
    Exit Sub
ErrHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Next
End Sub

Here, if the user enters 0, the division raises error 11 (division by zero). The handler shows the message and then Resume Next sends execution back to the line after the one that failed—in this case, the End Sub. You can also use Resume to retry the same line, or Resume Label to jump to a specific line.

Choosing the Right Strategy

There’s no one-size-fits-all. The choice depends on what you want the user to experience and how recoverable the error is.

• Use On Error GoTo when you want to log the error, show a friendly message, or clean up (close files, release objects) before exiting.
• Use On Error Resume Next when you expect an error and want to skip it—for example, checking if a worksheet exists by trying to set it, then testing if it’s Nothing.
• Use On Error GoTo 0 to re-enable default handling inside a procedure after you’ve temporarily set Resume Next. This prevents masking unexpected errors in the rest of the code.

A common misconception is that Resume Next makes the error disappear. It doesn’t—it just continues. If you don’t check Err.Number after a line that might fail, you may end up with a variable that holds Nothing or a wrong value, causing a logic error later. The classic pattern is:

On Error Resume Next
Set ws = ThisWorkbook.Sheets("Data")
On Error GoTo 0
If ws Is Nothing Then
    MsgBox "Sheet not found."
    Exit Sub
End If

Common Problems and Pitfalls

Several mistakes trip up beginners and experienced developers alike:

• Not resetting the handler. If you use On Error Resume Next and never turn it off, the entire procedure runs with errors suppressed. A typo in a variable name becomes a silent bug.
• Putting the handler where it runs normally. If you don’t place an Exit Sub before the label, the handler code executes even when no error occurred.
• Using Resume Next inside a loop. If an error occurs in a loop, Resume Next continues with the next iteration, but you may lose the context of which iteration failed. Log Err.Number and the loop counter.
• Forgetting that Err is global. The Err object holds the last error. If you call another procedure that doesn’t clear it, your handler may see a stale error number.
• Relying on error handling for control flow. It’s tempting to use errors to check if a file exists or a sheet is present, but it’s slower and less clear than using Dir() or a For Each loop.

Error Handling in Different Hosts

VBA runs in Excel, Access, Word, Outlook, and other Office applications. The syntax is identical, but the errors differ. For example, Excel raises error 9 (subscript out of range) when you reference a non-existent sheet; Access raises error 94 (invalid use of Null) when you try to use a Null value in an expression. The same On Error structure works everywhere, but you’ll need to know the specific error numbers for your host. The Err.Description property gives a human-readable message, which is often enough to identify the problem.

One important difference: in Access, you often work with recordsets and database objects that must be closed even when an error occurs. A good pattern is to open the recordset, then in the error handler check if it’s still open and close it before exiting. In Excel, the most common cleanup is restoring Application.EnableEvents or ScreenUpdating after an error interrupts a routine that turned them off.

Alternatives and Best Practices

Error handling isn’t the only line of defense. You can prevent many errors upfront with validation: check that a file exists before opening it, verify that a variable is numeric before using it in math, or test if an object is Nothing before calling its methods. This is often called “defensive programmingâ€� and reduces the need for error handlers. But you can’t catch everything—disk full, user cancels a dialog, network drive disconnected—so a handler is still necessary.

Another alternative is to use the IsError() function, but that works only on expressions that return a variant, not on statements. For example, IsError(Application.Match(...)) can check if a lookup failed without raising an error. Many developers also use a global error logger: a function that writes Err.Number, Err.Description, and the procedure name to a text file or the Windows Event Log. That’s a good practice for long-running macros where you want a trail of what went wrong.

Best practice summary:

• Always include an Exit Sub or Exit Function before your error label.
• Use Err.Number and Err.Description to log or display details.
• Turn off On Error Resume Next as soon as you’ve checked the result.
• Don’t use error handling to hide bugs—use it to handle expected, recoverable conditions.
• Test your error handler by deliberately causing the error (e.g., enter 0 in a divisor) to confirm it works.

Frequently Asked Questions

What does “On Error Resume Next� do exactly?
It tells VBA to ignore any runtime error and continue with the next line of code. The error information is stored in the Err object, so you can check it afterward. If you don’t check it, the error is effectively invisible.

How do I show the error message to the user?
Use MsgBox Err.Description inside your error handler. You can also include Err.Number for technical support. For example: MsgBox "Error " & Err.Number & ": " & Err.Description.

What is the difference between “Resume� and “Resume Next�?
Resume retries the line that caused the error. Resume Next continues with the line after the one that failed. Use Resume when the error is transient (e.g., a file is temporarily locked) and you’ve fixed the cause.

Can I use error handling in a function that returns a value?
Yes. The same On Error statement works in Function procedures. Just make sure the function has a default return value set before the error occurs, or set one in the handler, so the caller doesn’t receive an empty variant.

Is there a way to catch compile errors?
No. On Error only handles runtime errors. Compile errors (like a misspelled keyword or a mismatched type) are caught when you run the code or choose Debug → Compile VBA Project from the menu. You must fix those manually.

What happens if an error occurs inside an error handler?
If you’re already in an error handler and another error occurs, VBA stops and shows the default dialog. To avoid this, keep the handler simple—just log and exit. Don’t perform complex operations there unless you’ve added a nested On Error statement.

Related

• Beta Error Calculator: Type II Error & Power
• Bench Press Calculator Strength Level
• Belt Size Calculation Formula & How to Use It

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0 | 100000
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2