When running a VBA program, things not always go as expected. Sometimes errors will occur, and it is necessary to know how to deal with them.
In VBA 3 types of errors can be found:
These are errors that occur when the syntax of any line is incorrect.
E.g.: If statement without Then; or typos, such as Thn instead of Then.
No execution is required to identify this type of error. Normally the VBE itself sends out a warning as soon as the line is changed (with 
or the mouse).
To correct this error, simply adjust the syntax, noting if everything was spelled correctly in the expected order.
These are errors that occur when the syntax of the lines is correct, but the logical set of the code is faulty.
Examples:
An If statement within a loop with the End If statement outside the loop
For i = 1 To 10
'Tasks...
If i = 9 Then
Next i
End If
An End If statement after the End Sub
Sub example()
If i = 10 Then
End Sub
End If
A warning is displayed when a code with this type of error is executed.
To deal with this type of error it is enough to correct the order of the lines. The correct order will guarantee that any statement that needs a counterpart (Ex: For, If, Sub) has its closure before another statement that also needs a counterpart.
These are errors that occur when the program, during execution, can not run a line of code because of improper associations or incompatibilities.
E.g.: 1/0 will result in a 'Division by zero' error.
There are two main approaches (which may be complementary) to handling with this type of errors:
Next, we will focus on the handling of Runtime Errors with the statement On Error.
On Error is the main form of handling a VBA "Runtime Error". This informs VBA what to do when such an error occurs.
On Error can be used in three ways:
When using an On Error statement, VBA will set a new default action to deal with errors for the next lines of code that will be executed.
To reset to the Excel standard use On Error GoTo 0.
When an error occurs, the execution will jump to a defined line, usually at the end of the code:
Sub ErroGoTo()
On Error GoTo EH
X = 1 / 0 'Causes an error
'
' Sub Code
'
Exit Sub
EH: 'Error Handling
MsgBox "The following error occurred: " & Err.Description
End Sub
In this example, when the error is detected, VBA shifted its execution to the line with the label (EH).
Notice that in order to not execute the error handling without any error occurred, the instruction Exit Sub was added before EH:
The variable Err.Description will contain the description of the error, if it occurs. Division by zero, in this example.
EH is a label used in this example. You can use any name for this label, as if it were a variable name.
To continue the execution of the code even in case of an error, use the code On Error Resume Next.
On Error Resume Next
X = 1 / 0 'Causes an error, but execution will not stop
If X needs to have a value, it can be established as follow.
On Error Resume Next
X = 1 / 0 'Causes an error, but execution will not stop
If Err.Number <> 0 Then
X = 1
End If
Notice that Err.Number will return a number other than 0 if an error occurs.
Whenever you use the On Error Resume Next statement, make sure that the code is predicting the missing variables or lines of code that may cause problems. This is done to not get unpredictable results.
On Error Goto 0 is the default option of VBA. In case of error, the execution will be interrupted and an error message will be displayed:
X = 1 / 0 'Causes an error
This statement is generally used when you want to reset to the default form of VBA error handling after a previous specification:
On Error Resume Next
X = 1 / 0 'Causes an error, but execution will not stop
If Err.Number <> 0 Then
X = 1
End If
'Until now errors have been bypassed
On Error Goto 0 'From here on errors will be displayed
Y = 1 / 0
'It will display the error message for Y
SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.
Excel ® is a registered trademark of the Microsoft Corporation.
© 2024 SuperExcelVBA | ABOUT
