Error Handling with On Error Statements

1. Understanding Runtime Errors

Before we can handle errors, we need to be clear about what we're handling. In VBA, there are three main types of errors, but our focus today is squarely on the third.

• Syntax Errors: Typos or grammatical mistakes in a single line of code, which the VBA editor usually flags in red immediately (e.g., For i = 1 To).
• Compile Errors: Structural problems that span multiple lines of code, found when VBA checks the entire project before running (e.g., a For loop without a matching Next).
• Runtime Errors: Errors that only occur while the code is actually running. These are often caused by external factors or unexpected data, such as trying to divide by a zero that a user entered, opening a file that has been moved, or performing a calculation on text.

The following video provides a clear distinction between these error types and introduces the concept of runtime errors, which are the focus of our lesson.

2. The On Error Statement: Your Control Center

VBA gives you a powerful command to dictate how it should behave when a runtime error is encountered: the On Error statement. Before we dive in, it's crucial to ensure your VBA editor is set up correctly.

Go to Tools -> Options in the VBE menu. On the General tab, under "Error Trapping", make sure "Break on Unhandled Errors" is selected. This ensures that your error handling code will actually run, and VBA will only stop for errors you haven't handled.

There are three primary ways to use the On Error statement to control the flow of your code.

Let's break down the two most important of these statements.

On Error Resume Next: The Cautious Approach

This statement tells VBA: "If you hit an error, just ignore it and move to the very next line of code." While this sounds simple, it can be dangerous. Ignoring an error doesn't make it go away; your program might continue with incorrect data, leading to unpredictable results down the line.

However, it has specific, legitimate uses. A classic example is when you want to perform an action that might fail for a known reason, and the failure itself is an acceptable outcome. For instance, you want to delete a temporary worksheet before your code runs. If the sheet doesn't exist, VBA will throw an error. Using On Error Resume Next allows the code to attempt the deletion and simply continue if it fails, because the end goal (the sheet being gone) is achieved either way.

Sub SafelyDeleteSheet()
    ' Turn on error-ignoring mode
    On Error Resume Next
    
    ' Attempt to delete the sheet. If it doesn't exist, an error occurs,
    ' but VBA will just move to the next line.
    Application.DisplayAlerts = False ' Prevent "Are you sure?" prompt
    Worksheets("TempSheet").Delete
    Application.DisplayAlerts = True
    
    ' IMPORTANT: Turn normal error handling back on!
    On Error GoTo 0
    
    ' ... rest of your code ...
End Sub

Notice the On Error GoTo 0 at the end. This is crucial. It resets VBA to its default behavior (stopping on errors). You should always limit the scope of On Error Resume Next to only the specific lines of code where you expect and can safely ignore an error.

On Error GoTo [label]: The Professional Standard

This is the most robust and recommended method for error handling. It tells VBA: "If you hit an error, stop what you're doing and jump to a specific part of my code that I've prepared." This gives you full control to analyze the error, inform the user, and end the program gracefully.

The structure looks like this:

Sub StandardErrorHandler()
    ' 1. Activate the error handler at the beginning
    On Error GoTo MyErrorHandler

    ' ... Your main code goes here ...
    ' For example, a calculation that might fail
    Dim result As Double
    result = 10 / 0 ' This will trigger the error

    ' ... More of your main code ...
    MsgBox "This line will never be reached."

' 2. IMPORTANT: Exit before the handler if no error occurs
Exit Sub

' 3. The error handling block, identified by the label
MyErrorHandler:
    MsgBox "A runtime error occurred!" & vbCrLf & _
           "Description: " & Err.Description
End Sub

Let's analyze the three key parts:

1. On Error GoTo MyErrorHandler: This line at the top activates the handler. Any error after this point will redirect execution.
2. Exit Sub: This is absolutely critical. It ensures that if your main code runs successfully without any errors, it exits the subroutine before reaching the error handling block. Without it, your error message would appear every single time!
3. MyErrorHandler:: This is a label (the name followed by a colon). It marks the beginning of your error handling code. Here, we use the built-in Err object, which automatically contains information about the error that just occurred. Err.Description gives a textual explanation of the error.

3. Making Your Error Handler Intelligent

A generic "An error occurred" message is better than a crash, but we can do much more. The Err object gives you the tools to provide specific, helpful feedback.

• Err.Number: A unique number for each type of error (e.g., 13 for "Type mismatch," 11 for "Division by zero").
• Err.Description: A short string explaining the error.

You can use these properties within your handler to take different actions depending on the error.

4. Resuming and Resetting

Sometimes, you might want to fix an error and continue the procedure. The Resume statement gives you this power.

• Resume: Retries the line of code that caused the error. This is useful if your error handler fixes the underlying problem.
• Resume Next: Skips the line that caused the error and continues execution from the line immediately following it.

The image below shows a sophisticated example that handles two different types of errors and uses Resume to retry an operation.

*This code demonstrates how to handle multiple potential errors.

1. On Error GoTo Err1 is set.
2. The line sum = a + b fails because b is text ("ab4"), causing a "Type mismatch" error.
3. Execution jumps to the Err1: label.
4. Inside Err1, the error is reported, the value of b is corrected to 200, and the Resume statement sends execution back to the line sum = a + b, which now succeeds.
5. The code continues, but a new error handler is set: On Error GoTo Err2.
6. The line result = 10 / Number fails with a "Division by zero" error.
7. Execution jumps to Err2:, which reports the new error and then uses Exit Sub to terminate the procedure.*

This example illustrates a key concept: an On Error statement remains active until the sub ends or another On Error statement replaces it.

Sometimes you may have multiple potential points of failure and need to reset the error handler after each one. You can use multiple labels and On Error GoTo statements, but remember to include an Exit Sub before each new handler block to maintain proper control flow.

Conclusion

You have now learned the fundamental principles of making your VBA code resilient and professional. By anticipating and managing runtime errors, you create a better experience for the end-user and build more reliable automation tools—a hallmark of a skilled freelance developer.

Key Takeaways:

• Runtime errors occur while code is running, often due to invalid data or external issues.
• The On Error GoTo [label] statement is the standard, professional way to handle errors by redirecting code flow to a dedicated handler block.
• Always use Exit Sub before your error handler label to prevent it from running when no error has occurred.
• The Err object (Err.Number, Err.Description) provides vital information about the error, allowing you to create specific and helpful responses.
• Use On Error Resume Next sparingly and only for specific, expected errors where ignoring the error is a safe and valid outcome. Always reset it with On Error GoTo 0 immediately after.

In our next lesson, we will explore the other side of the coin: debugging. While error handling catches errors, debugging is the art of finding and fixing their root cause. You'll learn how to step through your code line by line, inspect variables, and pinpoint the exact source of a problem.