dmw | consultancy
tel 01732 833085
tonbridge · kent · UK

Excel Macro Error Trapping and Handling

Code to include in all macros to intercept and process errors if and when they occur.

Last updated on 2018-08-30 by David Wallis.


Preface

Errors occur during the execution of a macro due to a variety of reasons including the use of incorrect code and the macro running under circumstances for which it was not intended.

Including error trapping in all your macros allows you to determine what happens in the event of any error. You gain control of the error and are in a position to take appropriate action without your users getting wind of there being anything wrong.

Failure to include error handling may result in unwelcome and undesirable Excel behaviour. At worst your users could be faced with loss of recent changes to their spreadsheets or of data, or with Excel freezing and refusing to function at all. Or of all the aforementioned.

What you definitely want to avoid is leaving your users to choose their own responses to messages like this one:

Excel error message


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 YourMacroName()

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Error Goto errHandler

your macro 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 macro 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 macro 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 YourMacroName()

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Error Goto errHandler

your macro 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 incuding other Cases accordingly. In the following refinement of the macro, the messages to users are made specific:

Sub YourMacroName()

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Error Goto errHandler

Dim msg$, title$, icon&

your macro 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 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

MsgBox Err.Number & ": " & Err.Description

Resume procDone

End Sub

If when your macro completes its run you want the user to be given a message, then here is a further refinement of your code:

Sub YourMacroName()

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Error Goto errHandler

Dim msg$, title$, icon&

your macro 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


Give DMW’s Tips Your Support

ContributionIf you intend making use of Tips in any of your professional or commercial work and would like to make a donation in recognition of the time I spend publishing them, then please click Contribution and tell me how much you wish to donate. In response I’ll email you an invoice for payment by PayPal or card.

Thanks, in anticipation.


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 workbook.

“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.

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

Author unknown