Learn about VBA Error Handling
In VBA (Visual Basic for Applications), error handling allows you to gracefully handle runtime errors that occur while the program is running. Without error handling, any error that occurs will cause the program to stop abruptly. With error handling, you can catch errors, handle them, and continue the program’s execution or display a user-friendly message.
There are a few different methods in VBA for managing errors:
Basic Error Handling
- On Error Statement
The On Error statement is used to specify how errors are handled. It can be used in different ways depending on the scenario.
On Error GoTo
This is the most common method for handling errors. It directs the program flow to a specific label (error handling routine) when an error occurs.
Example:
Sub BasicErrorHandling() On Error GoTo errorhandler ' Redirect to ErrorHandler if error occurs Dim result As Double result = 10 / 0 ' This will cause a division by zero error MsgBox result Exit Sub ' Ensures the code doesn't accidentally fall into the error handler errorhandler: MsgBox "An error occurred: " & Err.Description, vbCritical, "Error" End Sub
Note:
- On Error GoTo [Label]: Transfers program control to the specified label if an error occurs.
- Err.Description: Provides a description of the error that occurred.
2. On Error Resume Next
This statement is used to ignore the error and resume execution with the next line of code.
Example:
Sub resumnexterrorhandling()
On Error Resume Next ‘Ignore the error and continue with the next line
Dim result As Double
result = 10 / 0 ‘This will cause an error, but execution will continue
MsgBox result
If Err.Number <> 0 Then
MsgBox “An error occurred: ” & Err.Description
End If
End Sub
Note:
- On Error Resume Next: Continues execution with the next line of code even if an error occurs.
- You can check Err.Number to see if an error occurred and take appropriate action.
3. On Error GoTo 0
This statement is used to disable error handling, meaning any error will stop code execution and show the default error message.
Example:
Sub DisableErrorHandling() On Error GoTo ErrorHandler Dim result As Double result = 10 / 0 ' This will cause a division by zero error On Error GoTo 0 ' Disable error handling (reverts to default error behavior) result = 10 / 0 ' This will trigger the default error message Exit Sub ErrorHandler: MsgBox "An error occurred: " & Err.Description End Sub
Note: On Error GoTo 0: Disables the current error handler and reverts to the default behavior of stopping the program.
4. Using the Err Object
The Err object provides information about the last error that occurred, including:
- Err.Number: The error number (a unique code for the error).
- Err.Description: A description of the error.
- Err.Source: The source of the error (typically the name of the procedure).
- Err.Clear: Clears the current error state.
Example:
Sub ErrObjectExample() On Error GoTo ErrorHandler Dim result As Double result = 10 / 0 ' Will cause a division by zero error MsgBox result Exit Sub ErrorHandler: MsgBox "Error Number: " & Err.Number & vbCrLf & _ "Description: " & Err.Description & vbCrLf & _ "Source: " & Err.Source, vbCritical, "Error" End Sub
5. Multiple Error Handlers
You can use multiple On Error statements in different parts of your code for handling different errors in various sections.
Sub MultipleErrorHandlers() On Error GoTo ErrorHandler1 Dim result1 As Double result1 = 10 / 0 ' Will trigger ErrorHandler1 MsgBox result1 Exit Sub ErrorHandler1: MsgBox "Error Handler 1: Division by zero!", vbCritical On Error GoTo ErrorHandler2 Dim result2 As Double result2 = 10 / 2 ' Will trigger ErrorHandler2 MsgBox result2 Exit Sub ErrorHandler2: MsgBox "Error Handler 2: Another error occurred!", vbCritical End Sub
6. Using Err.Clear
After handling an error, you may want to clear the error state using the Err.Clear method. This resets the Err object so that the error does not affect subsequent operations.
Example:
Sub ClearErrorExample() On Error GoTo ErrorHandler Dim result As Double result = 10 / 0 ' Will cause an error MsgBox result ' Clear the error Err.Clear MsgBox "Error cleared. Continuing execution." Exit Sub ErrorHandler: MsgBox "An error occurred: " & Err.Description End Sub
7. Custom Error Messages
Instead of using the default error descriptions, you can provide custom messages based on the error number.
Example:
Sub CustomErrorMessages() On Error GoTo ErrorHandler Dim result As Double result = 10 / 0 ' Will cause an error Exit Sub ErrorHandler: Select Case Err.Number Case 11 MsgBox "Error: Division by zero is not allowed.", vbCritical Case Else MsgBox "An unexpected error occurred: " & Err.Description, vbCritical End Select End Sub
Best Practices for Error Handling
- Always handle errors gracefully:
Don’t let errors cause your program to crash without providing useful information to the user.
2. Use specific error handlers:
You can customize error handlers based on the type of error to provide more specific feedback.
3. Use On Error GoTo 0 when done:
Reset error handling to default when you are done handling errors in your code.
4. Avoid On Error Resume Next unless necessary:
Use On Error Resume Next sparingly, as it can hide important errors that need to be addressed.
Conclusion
Effective error handling in VBA allows you to create robust applications that can gracefully handle unexpected situations. By using On Error, Err.Number, and other error-related methods, you can catch, display, and even log errors without crashing the application. This ensures a better user experience and easier maintenance of your code.
Other related pages to read
- VBA msgbox : In VBA (Visual Basic for Applications), the MsgBox function is used to display a message box to the user. It can include text, buttons, and icons, and it can return a value based on the user’s choice.
- VBA Data Types : In VBA (Visual Basic for Applications), data types define what kind of data a variable can store. Using the correct data type helps optimize memory usage and improves the performance of your code
