Trapping runtime errors

As you can see in the preceding example, when a runtime error occurs, you get two pieces of information in the message box that appears (see Figure 12-12). Note the error number (referred to as Err.Number or the Number property of the Err object in programmer lingo). That number is of no value to a typical user, but can be handy for a programmer. The other piece of information that the error message provides is the error description, referred to as Err.Description in VBA. The error description is the text that (vaguely) describes why the error occurred.


Figure 12-12:

Err. Number and Err. Description in a runtime error.


Figure 12-12:

Err. Number and Err. Description in a runtime error.


When you see a runtime error on your screen, two things have happened. The obvious first thing is the message on-screen. But behind the scenes, VBA has raised a runtime error, and the Err object has also received two values that describe that error. Those values are stored in the Number and Description properties of the Err object (expressed as Err.Number and Err.Description in VBA code). Just like you can see the number and description of an error by looking at the message on-screen, VBA can "see" that same information by looking at the contents of the Err.Number and Err.Description properties.

Trapping runtime errors is basically a matter of anticipating which runtime errors might occur when the code runs, and also writing code to gracefully handle each type error without causing the whole procedure to crash. The code that you write to deal with runtime errors is often referred to as an error handler because that's exactly what the code does — it handles the error in some way without causing the whole procedure to crash.

To create an error handler, you first need to add an On Error statement to your code, preferably just after the Sub or Function statement that marks the beginning of the procedure. Use one of the following three different ways to create an On Error statement:

i On Error GoTo label: When an error occurs as a statement runs, code execution jumps to the section of code identified by label within the same procedure.

i On Error Resume Next: If an error occurs as a statement runs, that statement is ignored, and processing just continues with the next line of code in the procedure.

i On Error GoTo 0: Disables any previous OnError GoTo or On Error Resume Next statements so that future runtime errors are handled by VBA rather than by your own code.

The Resume statement can be used in any error-handling code to tell VBA exactly where to resume code execution after the runtime error occurred. The syntax for the Resume statement can take any of the following forms:

i Resume: Causes VBA to reexecute the statement that caused the error. You want to use this statement only if the error-handling code fixed the problem that caused the error in the first place. Otherwise, executing the same statement again just causes the same error.

i Resume Next: Causes execution to resume at the first statement after the statement that caused the error. The statement that caused the error doesn't execute.

i Resume label: Causes execution to resume at the label that's specified.

Code created by Control Wizards in Access 2003 and earlier and macro conversions might already have error-handling code written into it. For example, if you create a macro named CloseCustForm to close a Customers form, you can convert this macro to VBA by highlighting it in the Navigation pane and then clicking the Convert Macros to Visual Basic command in the Macro group of the Database Tools tab. In the dialog box that appears, make sure to check the box to add error handling to the generated functions, and then click Convert. Access creates a module with a function named CloseCustForm(). The macro and the VBA function are shown in Figure 12-13.

In the CloseCustForm() procedure that the conversion created, only the line DoCmd.Close acForm, "Customers" closes the form. Technically, the code would work just fine if DoCmd.Close acForm, "Customers" were the only statement in the entire procedure, but the conversion adds error handling to the code that it generates if you tell it to. And most of the lines in the procedure are there to handle errors in case some problem arises that prevents the form from closing.

Near the top of the procedure, you see the statement On Error GoTo CloseCustForm_Err. When executed, that statement tells VBA, "If a runtime error occurs while this procedure is executing, don't 'crash.' Instead, stop what you're doing and resume execution at the CloseCustForm_Err label."

Figure 12-13:

Macro (top) and converted function with error handling in VBA (bottom).

Figure 12-13:

Macro (top) and converted function with error handling in VBA (bottom).

Then the code tries to execute the next statement, DoCmd.Close acForm, "Customers". If VBA can close the form when executing that statement, no runtime error occurs. Instead, the code execution drops to the next actual statement in the procedure, Exit Function, which ends the procedure. In other words, if no error occurs when DoCmd.Close acForm, "Customers" executes, the code runs and ends normally without calling on any error-handling code.

However, if a runtime error does occur when VBA tries to execute the DoCmd. Close acForm, "Customers" statement, the procedure doesn't crash. Rather, it passes control to the first statement under the CloseCustForm_ Err: label. There, the MsgBox Error$ statement shows the description of the error in a simple message box, and code execution drops to the line that reads Resume CloseCustForm_Exit, which tells the procedure to go to the CloseCustForm_Exit: label and resume execution. The first statement under that label reads Exit Function. When executed, that statement just ends the procedure normally.

A key component of understanding how error handling works is realizing that any line that ends with a colon is a label in code. A label is different from a regular line of code in that it's not an instruction to the computer to do something. Rather, its just a placeholder in code to which GoTo and Resume statements can pass control. The sample CloseCustForm function has two labels, which are pointed out in Figure 12-14.

A label text can be any text at all, as long as it starts with a letter and contains no blank spaces. Using the words Err or Exit somewhere in the label is customary but not required. However, the colon at the end of the label is mandatory because it's the only character that lets the compiler know that the line is a label rather than a regular VBA statement.

Figure 12-14:

Lines ending with a colon (:)are labels, not statements.


12C hap Examples - Converted Macro- CloseCustForm (Code)


(General) CloseCustForm


Option Compare Database


1 CloseCustForm

Function CloseCustForm() On Error GoTo CloseCustForm Err

DoCmd.Close acForm, "Customers"

i-CloseCustForm Exit: Exit Function

1-CloseCustFDrm Err: HsgBox ErrorS Resume CloseCustForm Exit

End Function


= _1 i

Label lines

Was this article helpful?

0 0

Post a comment