tel 01732 833085 · e-mail david wallis
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.
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:
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:
Line 11 instructs the macro to resume executing at the ProcedureDone label on line 6.
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):
As you test your macro and uncover other potential errors, you can extend the Select statement by incuding other Cases accordingly.