DMW logo
tel 01732 833085
Tonbridge · Kent · UK · UK

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.

Excel error message

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:

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 —

Invoice

Thanks, in anticipation.

SSL Cerification

“If debugging is the art of removing bugs, then programming must be the art of inserting them”

Author unknown