Error Handling
Programming can be a complex and tricky business so whether you are a pro or a novice programmer you will, at some
point, experience some kind of programming error.
When your Excel VBA macro runs into an error, without any error handling methods in place, the program will stop and
display a runtime error (normally error 1004). This is bad news especially if you have written a program for someone
who has very little to no Excel VBA experience.
Common coding errors include: division by zero, data type mismatch, exceeding the length of an array in a loop, setting a
variable to the name of an object that does not exist anymore etc.
To prevent your program from crashing when such an error occurs, VBA has various error handling techniques you can
use. Below are three different ways to handle errors:
1. Basic Error Handling
2. Generic Error Handling Using the Err Object
3. Handling Errors by ignoring them
1. Basic Error Handling
With this method when your VBA code encounters an error you then tell it to jump to another section of code. The error
can then be “handled” or displayed to the user in such a way that it does not cause the program to crash.
Below is a basic code template for error handling:
Sub ErrorHandling()
On Error GoTo {user defined name}
‘Some code that could cause an error
On Error GoTo 0 ‘Close out the special error handler
Exit Sub
‘No more code, other than error handling code, beneath this point
{user defined name}:
‘Some action to be taken after an error is found
Resume Next ‘If action is satisfactory else you can place Exit Sub at this point if error cannot be resolved
End Sub
INTERNAL
Explanation of Code:
On Error GoTo {user defined name}
This code is the syntax used to open the special error handler, any code that causes an error after this statement will
immediately jump to the ‘{user defined name}:’ location.
On Error GoTo 0
This code closes out the special error handler. If this code is not added, all errors that occur after the error handler is
open will jump to the ‘{user defined name}:’ location.
Exit Sub
This code is used to exit the subroutine and should not be confused with End Sub. In this case, Exit Sub is used to
separate your working code from your error handling code. If Exit Sub was not used and the program ran without any
errors the error handling code will still run as the subroutine only ends when it reaches End Sub or Exit Sub.
{user defined name}:
This is the point in the program you wish to jump to if an error occurs.
Resume Next
This code allows you to continue the program immediately after the line of code, which caused an error.
2. Generic Error Handling Using the Err Object
The Err object can be very useful when it comes to debugging your code, as it can provide you with an error number as
well as an error description.
How you would use the Err function is as follows:
Consider the basic error-handling template, now where you would normally code in countermeasures after an error has
occurred you can also display a message describing the error.
Sub ErrorHandling()
On Error GoTo {user defined name}
‘Some code that could cause an error
On Error GoTo 0 ‘Close out the special error handler
Exit Sub
‘No more code, other than error handling code, beneath this point
{user defined name}:
MsgBox "Something went wrong " & Err.Number & " - " & Err.Description
Resume Next ‘If action is satisfactory else you can place Exit Sub at this point if error cannot be resolved
End Sub
INTERNAL
Explanation of Code:
Err.Number and Err.Description
This code outputs the number and description of the error experienced. For example if you were to divide by zero, the
following message would be displayed:
Something went wrong 11 – Division by zero
3. Handling Errors by ignoring them
The simplest way to handle coding errors is to just ignore them, this does come with some risks though as not all errors
can be ignored. You should only use this method when you know any error experienced can be ignored.
Here is a code template where any error will be ignored running the next line of code instead:
Sub ErrorHandling()
On Error GoTo Next
‘Some code that could cause an error
On Error GoTo 0
End Sub
INTERNAL
