Implement Dynamic Charting Techniques With Excel VBA

Key Concepts in Error Handling

1. On Error Resume Next: This tells VBA to continue executing the next line of code even if an error occurs. It’s useful when you want to skip over certain errors but still run the rest of the program.
2. On Error GoTo [Label]: This directs VBA to jump to a specific part of the code (usually called an error-handling section) if an error occurs. This allows you to handle the error in a controlled manner.
3. On Error GoTo 0: This resets error handling to the default behavior, meaning VBA will stop execution and show the standard error message when an error occurs.
4. Err Object: The Err object contains information about the last error that occurred, such as the error number, description, and source.

Detailed Example of Error Handling

Sub ErrorHandlingExample()
    On Error GoTo ErrorHandler ' Set up error handling
    ' Simulate some code execution with potential errors
    Dim result As Double
    Dim number1 As Double
    Dim number2 As Double
    Dim filePath As String
    number1 = 10
    number2 = 0 ' This will cause a division by zero error
    ' Division by zero: Potential error here
    result = number1 / number2
    ' File I/O operation: Potential error if file doesn't exist
    filePath = "C:\InvalidPath\testfile.txt"
    Open filePath For Input As #1 ' This could cause an error if the file doesn't exist
    ' Code continues here if no errors
    MsgBox "Result is " & result
    ' Exit the sub before the error handler
    Exit Sub
ErrorHandler:
    ' Error handling code
    If Err.Number = 11 Then
        MsgBox "Division by zero error occurred.", vbCritical, "Error"
    ElseIf Err.Number = 53 Then
        MsgBox "File not found. Please check the file path.", vbCritical, "Error"
    Else
        MsgBox "An unknown error occurred. Error Number: " & Err.Number & " - " & Err.Description, vbCritical, "Error"
    End If
    ' Reset error handling to default
    On Error GoTo 0
    ' Clean up (e.g., closing file, resetting variables)
    On Error Resume Next ' Ignore any potential errors during clean-up
    Close #1 ' Close the file if it was opened
    On Error GoTo ErrorHandler ' Return to the error handler
End Sub

Breakdown of the Code:

1. Setting Up Error Handling (On Error GoTo ErrorHandler):
   • This line sets up the error handling by telling VBA to jump to the ErrorHandler label whenever an error occurs in the code.
2. Executing Code with Potential Errors:
   • The code does some arithmetic operations, such as dividing number1 by number2. Since number2 is 0, this will result in a « Division by Zero » error.
   • The code also tries to open a file using a potentially invalid path. If the file doesn’t exist, this will raise an error as well.
3. Error Handler:
   • If an error occurs, the program will jump to the ErrorHandler section.
   • Inside the error handler, we check the error number using Err.Number to determine the type of error and display a meaningful message to the user.
   • You can handle different types of errors based on their error numbers, such as 11 for division by zero and 53 for file not found errors.
   • If the error is not specifically handled, a generic error message is displayed, along with the error number and description.
4. Exiting the Subroutine:
   • Before jumping to the error handler, we use Exit Sub to make sure the error handler doesn’t execute if no error occurs.
5. Resetting Error Handling (On Error GoTo 0):
   • Once the error has been handled, we reset the error handling to its default behavior using On Error GoTo 0. This means that any future errors will stop execution and display a default error message.
6. Clean-up Section:
   • The clean-up section, using On Error Resume Next, ensures that even if there’s an error (like trying to close a file that wasn’t opened), the code won’t break.
   • Finally, On Error GoTo ErrorHandler re-establishes the error handler for any subsequent operations.

Other Error Handling Techniques

1. Using On Error Resume Next: This statement can be used to skip over an error and continue with the next line of code. However, it’s important to note that errors are silently ignored, and you should check for them manually afterward. Here’s an example:
2. On Error Resume Next ‘ Skip over errors
3. result = number1 / number2 ‘ Division by zero will be ignored
4. If Err.Number <> 0 Then
5.     MsgBox « An error occurred:  » & Err.Description
6. End If
7. Clearing the Error (Err.Clear): This can be used to reset the Err object. For instance, if you want to clear the current error before doing another operation, you can use Err.Clear:
8. On Error Resume Next
9. result = number1 / number2
10. If Err.Number <> 0 Then
11.     MsgBox « Error:  » & Err.Description
12.     Err.Clear ‘ Clear the error state
13. End If

Best Practices for Error Handling in VBA

1. Use Meaningful Error Messages: When an error occurs, provide the user with a clear, understandable message. Avoid cryptic system messages.
2. Don’t Overuse On Error Resume Next: While this can be useful, it can also hide real problems. Use it cautiously and only when you’re certain that the error is not critical.
3. Ensure Proper Cleanup: Always clean up resources (e.g., closing files or resetting variables) in the error handling section. This prevents memory leaks or file locks.
4. Test Error Handling Thoroughly: Simulate different errors (like invalid inputs or missing files) and test your error-handling code to ensure it works as expected.