Like any programming language, VBA is also prone to runtime errors that can occur while the code is executing. These errors can halt the execution of a program and cause inconvenience to the user. Hence, the VBA On Error statement is used to handle run-time errors and prevent them from interrupting the execution of the code.
The Purpose of VBA On Error Statement
The purpose of On Error statement is to catch the error and allow the program to continue its execution without interruption. This statement allows the programmer to anticipate and handle errors in a way that is more user-friendly. It also enables them to provide customized error messages or to take alternative actions to handle the errors rather than the default actions.
Syntax of On Error statement
The syntax for On Error statement is as follows:
On Error {GoTo [line number|label]| Resume Next| Prevent [line number|label]}
There are three possible arguments that can be used with the On Error statement:
1. GoTo – This argument allows the program to jump to a specific line of code or a label defined in the code when an error occurs.
2. Resume Next – This argument instructs the program to continue executing the code from the line following the error line.
3. Prevent – This argument is used to prevent a specific line of code or a label from executing when an error occurs.
Examples of VBA On Error Statement
Example 1: Handle a specific type of error
Suppose we have a VBA code that divides two numbers entered by the user. However, when the user enters the value of the second number as 0, it will result in a division by zero error. To handle this error, we can use the On Error statement as follows:
Sub Division()
On Error GoTo errorHandler
Dim num1 As Integer, num2 As Integer, result As Integer
num1 = InputBox("Enter the first number:")
num2 = InputBox("Enter the second number:")
result = num1/num2
MsgBox "Result: " & result & ""
Exit Sub
errorHandler:
MsgBox "An error occurred: Division by zero."
End Sub
In this example, the On Error statement with the ‘GoTo’ argument will jump to the ‘errorHandler’ label when an error occurs. The user will be informed about the division by zero error, and the code will gracefully exit.
Example 2: Ignore the error and continue execution
Sometimes, we may want to ignore a particular error and continue with the execution of the code. In such cases, we can use the ‘Resume Next’ argument of the On Error statement. Let’s consider the same example of dividing two numbers, but this time we want the code to continue executing even if the user enters a non-numeric value for the second number.
Sub Division()
On Error Resume Next
Dim num1 As Integer, num2 As Integer, result As Integer
num1 = InputBox("Enter the first number:")
num2 = InputBox("Enter the second number:")
result = num1/num2
MsgBox "Result: " & result & ""
Exit Sub
MsgBox "An error occurred: Division by zero."
End Sub
Here, the On Error statement with the ‘Resume Next’ argument will allow the code to continue the execution, even if an error occurs. In this case, if the user enters a non-numeric value for the second number, the result will be shown as 0, and the program will not halt.
Example 3: Move error handling to a different section of the code
The VBA On Error statement can also be used to handle errors in a different part of the code. This can be helpful when the error-handling code is too long to be placed immediately after the line of code that may cause an error. Let’s look at an example where we have a lengthy error handling code.
Sub Division()
On Error GoTo errorHandler
Dim num1 As Integer, num2 As Integer, result As Integer
num1 = InputBox("Enter the first number:")
num2 = InputBox("Enter the second number:")
If num2 = 0 Then
MsgBox "A division by zero is not allowed."
Else
result = num1/num2
MsgBox "Result: " & result & ""
End If
Exit Sub
errorHandler:
MsgBox "An error occurred: " & Err.Description
End Sub
In this example, the On Error statement will first jump to the ‘errorHandler’ label if an error occurs before executing the code in the ‘If’ statement. Otherwise, it will execute the code within the ‘If’ statement without any interruption.
Example 4: Determine the error number and description
The ‘Err’ object can be used to return the error number and description of the error for which the On Error statement was activated. This information can be used to provide meaningful error messages to the user. Let’s see an example of this.
Sub Division()
On Error GoTo errorHandler
Dim num1 As Integer, num2 As Integer, result As Integer
num1 = InputBox("Enter the first number:")
num2 = InputBox("Enter the second number:")
result = num1/num2
MsgBox "Result: " & result & ""
Exit Sub
errorHandler:
MsgBox "Error number: " & Err.Number & vbNewLine & "Error description: " & Err.Description
End Sub
In this example, the ‘Err’ object is used to return the error number and description when an error occurs in the ‘Division’ sub-procedure.
Example 5: Disable the error handler temporarily
We can also disable the error handler temporarily using the On Error statement. This can be useful when we want to run the code without any error handling temporarily. Let’s look at an example of this.
Sub Division()
On Error GoTo errorHandler
Dim num1 As Integer, num2 As Integer, result As Integer
'some code that may cause an error
Exit Sub
errorHandler:
MsgBox "An error occurred: " & Err.Description
'disable the error handler
On Error GoTo 0
'continue executing the code without error handling
'...
End Sub
In this example, the error handler is temporarily disabled using the On Error statement with a ‘0’ as the argument, allowing the code to continue executing without any error handling.
Important Notes & Remarks
- The On Error statement should always be used with an error handling routine to handle the errors.
- If there is no error handling routine, the default action for the error will be taken, which is to display a generic runtime error message and end the execution of the code.
- The On Error statement should be used judiciously as turning off error handling can suppress errors that should be dealt with.
- The On Error statement is not meant to be used to fix errors; instead, it is used to provide a graceful way to handle errors.
- The On Error statement is specific to the particular VBA sub-procedure or function in which it is declared. Hence, multiple error handlers can be used in different parts of the code.
In conclusion, the VBA On Error statement is an essential tool for handling errors in a code that may occur during its execution. By using this statement, the programmer can anticipate and handle errors in a more user-friendly way, resulting in a better user experience. However, it is crucial to use the statement judiciously to avoid hiding critical errors that require attention.
I hope this blog post has provided you with a thorough understanding of the purpose, syntax, and usage of the VBA On Error statement.
Do let us know your thoughts and feedback on this post. Have you encountered any challenges while using the On Error statement in your VBA code? Share your experiences in the comments section below.
