Microsoft Access 2010 Bible

Chapter 23: Handling Errors and Exceptions

825

Almost all error-handling routines in VBA programs require a three-step process:

1. Trap the error.


2. Redirect program flow to an error handler.


3. Direct program flow out of the error handler back to the main body of the procedure, or
   exit the procedure.

Note
All VBA error handling is done locally — that is, each procedure contains its own error-handling code.
Although a procedure’s error handler can call other functions and subroutines, the error handler exists entirely
within the procedure causing the error. In fact, as I discuss later in this chapter, after you start implementing
error handling, you need to include error-handling code in virtually every procedure in an application. An
error that occurs in a procedure without error handling is passed back to the routine that called the procedure,
easily causing confusion as to which procedure actually failed.

How to set a basic error trap

The VBA engine is constantly looking for problems and immediately notifies you when something

unexpected happens. The default error message is generally technical in nature. A single line of

VBA code is all that’s necessary to intercept an error and redirect program flow to an error handler

that provides a more user-friendly approach to error resolution.

The following procedure shows how error handling is implemented in VBA procedures:

Sub RoutineA()

On Error GoTo HandleError ‘Trap the error

MsgBox “Now in routine A”

ExitHere:

MsgBox “Now leaving routine A”

Exit Sub

HandleError: ‘Begin the error handler

MsgBox “Error in routine A”

Resume ExitHere ‘Redirect out of error handler

End Sub

The On Error statement near the top of the procedure sets the error trap for the routine. Code

near the bottom of the routine implements the error-handling mechanism for this subroutine. The

error-handling code in this procedure is the very least you can include in your procedures to han-

dle runtime errors effectively. The error-handling statements in this procedure are a template you

can consistently use in all your VBA programs.

The On Error clause informs VBA that you want to override the default VBA error-handling sys-

tem by sending execution to the location identified by the HandleError label. The GoTo Label

statement is an unconditional branch (in the event of an error) to a label somewhere within the

current procedure.