Error Trapping and Handling in Excel Macros

Errors occur during the execution of a macro due to a variety of reasons including the use of incorrect code and the macro being executed 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 confusing Excel behaviour. At the very least, your users might be dumped out of their spreadsheet and into your code in the Visual Basic Editor, facing for them some bewildering error messages from Excel.

At worst you could be faced with loss of recent changes to a spreadsheet or with Excel 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:

  1. Sub your_macro_name()
  2. ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  3. On Error Goto ErrorHandler
  4. your macro code here
  5.  
  6. ProcedureDone:
  7. Exit Sub
  8.  
  9. ErrorHandler:
  10. MsgBox Err.Number & ": " & Error.Description
  11. Resume ProcedureDone
  12.  
  13. End Sub

The On Error statement turns on error trapping. Information about any error that occurs subsequent to line 3 is stored in a VBA error object named 'Err'.

In the event of an error, the On Error Goto ErrorHandler statement instructs the macro to stop executing your code at the point at which the error occurs and to pick again at the ErrorHandler label, line 9.

Line 10 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
  • Error.Description is a description of that error.

Line 11 instructs the macro to resume executing at the ProcedureDone label on line 6.

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):

  1. Sub your_macro_name()
  2. ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  3. On Error Goto ErrorHandler
  4. your macro code here
  5.  
  6. ProcedureDone:
  7. Exit Sub
  8.  
  9. ErrorHandler:
  10. Select Case Err.Number
  11. Case 1234
  12. your code for handling error 1234
  13. Case Else
  14. ' All outstanding errors
  15. MsgBox Err.Number & ": " & Error.Description
  16. End Select
  17. Resume ProcedureDone
  18.  
  19. End Sub

As you test your macro and uncover other potential errors, you can extend the Select statement by incuding other Cases accordingly.