How to Include Error Handling in VBA Code
For Microsoft® Access, Excel and Word, how to include error handling in VBA code to intercept and process errors if and when they occur.
Last updated on 2021-01-23 by David Wallis.
Preface
Errors occur during the execution of a VBA function or sub procedure due to a variety of reasons, including the use of incorrect code and the macro running under circumstances for which it was not intended.
Introducing error trapping in all your VBA procedures puts you in control of errors and in a position to direct the code to make appropriate responses.
By managing errors you can spare your users from being thrown into a panic at not knowing how to respond to threatening looking error messages, like this one.
Failure to include error handling may result in unwelcome and undesirable behaviour of the software. Your users could be faced with loss of changes they have made to their data or application, or with their application freezing and refusing to function at all.
Simple Error Handler
There are a number of ways in which you might choose to include code for error handling in a macro. This is the skeleton code for a simple way:
Sub YourProcedureName()
On Error Goto errHandler
your code here
procDone:
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description
Resume procDone
End Sub
The On Error statement turns on error trapping. Information about any error that occurs subsequent to this statement is stored in a VBA error object named Err.
In the event of an error, the On Error Goto errHandler statement instructs the procedure to stop executing your code at the point at which the error occurs and to pick it up again at the errHandler label.
MsgBox Err.Number & ": " & Err.Description sends a message box to the screen displaying information about the nature of the error:
- Err.Number is a unique identification number for the error object drawn from VBA’s library of errors
- Err.Description is a description of that error.
Resume procDone instructs the macro to resume executing at the procDone label.
Refined Error Handling Code
Let’s assume you have wrapped a new procedure in the error-handling code described above. As you test your macro an error results. Therefore you are presented with the message box from which you learn the error number and the nature of the error.
Now you are in a position to revise your error handler to respond to this specific error (in this example the error number 1234):
Sub YourProcedureName()
On Error Goto errHandler
your code here
procDone:
Exit Sub
errHandler:
Select Case Err.Number
Case 1234
your code for handling error 1234
Case Else
' All outstanding errors
MsgBox Err.Number & ": " & Err.Description
End Select
Resume procDone
End Sub
As you test your macro and uncover other potential errors, you can extend the Select Case statement by including other Cases accordingly. In the following refinement of the macro, the messages to users are made specific:
Sub YourProcedureName()
On Error Goto errHandler
Dim msg$, title$, icon&
your code here
procDone:
Exit Sub
errHandler:
icon& = vbOKOnly + vbCritical
Select Case Err.Number
Case 53
title$ = "Missing File"
msg$ = "Macro cannot locate an essential file."
msg$ = msg$ & vbNewline & vbNewLine
msg$ = msg$ & "Please notify IT support."
Case Else
title$ = "Unanticipated Error"
msg$ = Err.Number & ": " & Err.Description
msg$ = msg$ & vbNewline & vbNewLine
msg$ = msg$ & "Please make a note of this message"
End Select
MsgBox Err.Number & ": " & Err.Description
Resume procDone
End Sub
If when your procedure completes its run you want the user to be given a message about any error, then here is a further refinement of your code:
Sub YourProcedureName()
On Error Goto errHandler
Dim msg$, title$, icon&
your code here
title$="Macro Run Completed"
msg$ = "Please continue using the workbook"
icon& = vbOKOnly + vbExclamation
procDone:
MsgBox msg$, icon&, title$
Exit Sub
errHandler:
icon& = vbOKOnly + vbCritical
Select Case Err.Number
Case 53
title$ = "Missing File"
msg$ = "Macro cannot locate an essential file."
msg$ = msg$ & vbNewline & vbNewLine
msg$ = msg$ & "Please notify the developer."
Case Else
title$ = "Unanticipated Error"
msg$ = Err.Number & ": " & Err.Description
msg$ = msg$ & vbNewline & vbNewLine
msg$ = msg$ & "Please make a note of this message."
End Select
Resume procDone
End Sub
For users who are adept at computer usage, as developer you will get targeted information about an error if you refine your error handler even further:
errHandler:
icon& = vbOKOnly + vbCritical
Select Case Err.Number
Case 53
title$ = "Missing File"
msg$ = "Macro cannot locate an essential file."
msg$ = msg$ & vbNewline & vbNewLine
msg$ = msg$ & "Please notify the developer."
Case Else
title$ = "Unanticipated Error"
msg$ = _
"An unexpected error has occurred in your program." & _
vbCrLf & vbCrLf & _
"Please send a screen print of this message to the developer." & _
vbCrLf & vbCrLf & _
"If you're unable to, please make a note of these details:" & _
vbCrLf & vbCrLf & _
"Calling Proc: NameOfYourProcedure" & _
vbCrLf & _
"Error Number: " & Err.Number & _
vbCrLf & _
"Description: " & Err.Description & _
vbCrLf & vbCrLf & _
"Program will now … "
End Select
Resume procDone
The Case Else block responds to any error for which you have not specifically coded. So the bulk of the message it displays is information useful to you, the developer.
NameOfYourProcedure is the name you’ve given to the procedure in which you’ve included this error handler.
Program will now … is your message to the user about what will happen as a result of the error. You’ll adapt this message as you deploy the error handler throughout your code modules.
Take Care: Test to Destruction
Always test your macros fully before putting them into service. Best to test on a back-up copy of your application.
“Pretty good testing is easy to do (that’s partly why some people like to say ‘testing is dead’ — they think testing isn’t needed as a special focus because they note that anyone can find at least some bugs some of the time). Excellent testing is quite hard to do.”
James Bach (Satisfice, Inc.)
Testing takes time. To the time I estimate for writing the code, I add 30% at least for the testing.
Your Support for DMW TIPS
Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
If you’d like an invoice to account for your donation, let me know how much you’re donating —
Thanks, in anticipation.
