Vba error handling in vba in Excel
"On Error" Statement is used to handle errors in Excel VBA.
There are two versions of "On Error"; One, just ignore the error and continue and Two, Do something when an error is encountered
The first case uses "On Error Resume Next" and the second one use "On Error Goto
Syntax 1
- For Each
In - On Error Resume Next
- Next
Syntax 2
- For Each
In - On Error Goto
- Next
- Exit Sub
: Code - Resume Next
The following example iterates through an array of numbers and calculates the square root of each number
- Sub onerrorresume()
- Dim i(3) As Integer
- i(0) = 4
- i(1) = -8
- i(2) = 225
- For Each j In i
- On Error Resume Next
- MsgBox Sqr(j)
- Next j
- End Sub
- Sub onerrorgoto()
- Dim i(3) As Integer
- i(0) = 4
- i(1) = -8
- i(2) = 225
- For Each j In i
- On Error GoTo l1
- MsgBox Sqr(j)
- Next j
- Exit Sub
- l1: MsgBox "Cannot calculate squareroot of negative numbers"
- Resume Next
- End Sub
The array contains a negative number (-8) which will result in error
The first Sub ignores this error and continues with the next number(225)
Whereas the second example, the execution is shifted to the line labled as "l1" when an error occurs.
Note that the statements "Resume Next" and "Exit Sub" are must in this syntax.
Result
Note that, the code jumps to "l1" when the error occurs and then executes the next iteration