6.2 Using On Error Statements.

In VBA, errors are inevitable, but how you handle them can make a big difference in the user experience and the stability of your application. The On Error statement in VBA allows you to control what happens when an error occurs in your code. This helps you handle unexpected situations, such as when the user enters invalid data or when a file is not found, without crashing your program.

There are several ways to use On Error statements in VBA, and each has its purpose.

1. The Basic Syntax of On Error

There are several ways to use the On Error statement in VBA, but they generally follow this basic syntax:

On Error <ErrorMode> <LabelName>

• ErrorMode: Defines how errors are handled. The most common options are:
  • GoTo <LabelName>: Directs execution to a specific line of code (error handler) where the error is managed.
  • Resume Next: Continues execution with the next line of code after the error occurs.
  • GoTo 0: Turns off error handling and allows VBA to handle the error normally.
• LabelName: The label name where control will be transferred in case of an error. It is a line identifier that defines the part of the code where the program should jump in case of an error.

2. Common Types of On Error Statements

2.1 On Error GoTo

This is the most commonly used On Error statement. It tells VBA to jump to a specific label (error handler) in case of an error.

Example:

Sub DivisionExample()
    On Error GoTo ErrorHandler  ' Directs to the ErrorHandler label if an error occurs
    
    Dim result As Double
    result = 10 / 0  ' Division by zero causes a runtime error
    
    Exit Sub  ' Ensures normal code flow skips the error handler
    
ErrorHandler:
    MsgBox "Error: " & Err.Description  ' Display error message
End Sub

How it works:

• If an error occurs (in this case, division by zero), VBA jumps to the ErrorHandler label.
• The Err.Description is used to display the error message in a message box.

2.2 On Error Resume Next

This statement tells VBA to ignore the error and move to the next line of code. It’s useful if you want to skip over non-critical errors or handle the error later in your code.

Example:

Sub IgnoreErrorExample()
    On Error Resume Next  ' Ignore the error and continue with the next line
    
    Dim result As Double
    result = 10 / 0  ' This will generate a runtime error but will be ignored
    
    If Err.Number <> 0 Then
        MsgBox "There was an error, but we ignored it."
    Else
        MsgBox "No errors occurred."
    End If
End Sub

How it works:

• VBA ignores the division by zero error, and the code continues to execute the next lines.
• You can check if an error occurred by checking the Err.Number property after using On Error Resume Next.

2.3 On Error GoTo 0

This statement disables any active error handling and allows VBA to handle errors in the default way (i.e., it will show a standard error message and stop execution).

Example:

Sub DisableErrorHandlerExample()
    On Error GoTo ErrorHandler  ' Set up error handling
    
    Dim result As Double
    result = 10 / 0  ' This will cause an error
    
    On Error GoTo 0  ' Disable error handling - VBA will handle the next error normally
    
    result = 10 / 0  ' This will stop execution and show a standard error message
    
    Exit Sub
    
ErrorHandler:
    MsgBox "Custom Error Handling: " & Err.Description
End Sub

How it works:

• The first division by zero is handled by the custom error handler.
• The second division by zero results in a standard error message since On Error GoTo 0 was used to turn off error handling.

3. Error Handling Example with Multiple Errors

Sometimes, you may need to handle different types of errors or allow for specific error-handling procedures depending on the situation. You can use multiple error-handling blocks in a single procedure.

Example:

Sub MultipleErrorHandlingExample()
    On Error GoTo ErrorHandler  ' Initial error handler
    
    ' Code that may cause different errors
    Dim result As Double
    result = 10 / 0  ' Division by zero (error 1)
    
    Open "nonexistentfile.txt" For Input As #1  ' File not found (error 2)
    
    Exit Sub  ' Exit to prevent going into the second error handler
    
ErrorHandler:
    If Err.Number = 11 Then  ' Division by zero error
        MsgBox "Error 11: Division by zero."
    ElseIf Err.Number = 53 Then  ' File not found error
        MsgBox "Error 53: File not found."
    Else
        MsgBox "Unknown Error: " & Err.Description
    End If
End Sub

How it works:

• If a division by zero occurs, the first Err.Number will match 11 and display the corresponding message.
• If a file is not found, the error handler checks for error 53 and displays the appropriate message.

4. Cleaning Up After Errors

It’s important to clean up after errors to ensure that the program can continue running smoothly after an error occurs. This is especially useful for releasing resources or closing open files.

Example:

Sub CleanUpAfterError()
    On Error GoTo ErrorHandler  ' Setup error handler
    
    ' Code that may cause an error
    Dim fileNumber As Integer
    fileNumber = FreeFile
    Open "nonexistentfile.txt" For Input As fileNumber
    
    ' More code to run after the file operation
    
    Exit Sub
    
ErrorHandler:
    ' Clean-up code
    If fileNumber > 0 Then Close fileNumber  ' Close file if open
    MsgBox "An error occurred. The file was not opened."
End Sub

How it works:

• The error handler ensures that if the file fails to open, any open files are closed to prevent resource leaks.
• The code displays a message indicating the error, and cleanup is handled before the subroutine exits.

5. Summary of On Error Statements

6. Best Practices for Using On Error

• Use Specific Error Handling: Always define a specific error handler for your code, especially for critical operations like file handling or database access.
• Avoid Overusing Resume Next: While Resume Next can be useful for non-critical errors, overusing it can hide issues that might affect your program later.
• Always Clean Up: Ensure that resources (like files or connections) are properly closed in the error handling section to avoid resource leaks.
• Error Logging: Consider logging errors to a file or database for later analysis, especially in complex applications.

Using error handling in VBA allows your programs to run more smoothly, providing a better user experience and making your code more robust.