Handling Errors

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

When an error occurs in a procedure that does not have error handling enabled, VBA responds by displaying an error message and terminating the application. While this behavior may be acceptable when you are writing and debugging code, it is never acceptable when your users are running your application.

A hallmark of the well-written application is its ability to anticipate and handle any error that may occur. In the best of cases, you have designed the error handler to anticipate the error and recover from it quickly and transparently to the user. No developer, no matter how diligent or experienced, will anticipate every error that can occur. In the worst case, the well-designed error handler will gracefully terminate the application and perhaps record information about the error to an error log.

You do not need an error handler in every procedure you write, and every error handler you write need not operate the same way. The key to effective error handling is knowing when to trap an error and what to do with it once you've caught it.

Whether you are handling errors in VBA or in script behind an HTML page, there are two basic tools you can use. One is the On Error statement, which you use to "enable" error handling in a procedure. The other is the Err object, which contains information about an error that has already occurred.

When a run-time error occurs, your error handler may be able to fix the error directly or give the user a chance to fix the error. If your error handler is unable to fix the error so that the code can continue to execute, it should allow the program to fail gracefully.

Once execution has passed to the error-handling routine, your code must determine which error has occurred and either fix the error or raise the error back to the calling procedure. If an error occurs within an error handler, VBA will handle the error (because error handling is no longer enabled), unless you call another procedure to handle such errors. You may consider writing a generic error-handling routine that can be used to handle errors generated within error handlers. VBA can have only one error handler active at a time in any procedure, but it can have more than one error handler active within the current procedure stack.