The On Error Goto Label Statement

The On Error statement tells VBA what to do when a run-time error occurs. The most common form of the statement is:

On Error GoTo label where label is a label. For instance, consider the following code:

Sub example()

On Error GoTo ERR_EXAMPLE

MsgBox Selection.Cells.Count Exit Sub

ERR_EXAMPLE:

MsgBox Err.Description, vbCritical Exit Sub End Sub

The purpose of this procedure is simply to display the number of cells in the current selection. When the current selection is a worksheet range, the Cells property returns the collection of cells in the selection and the Count property then returns the number of cells.

However, if the current selection is not a worksheet range (it might be a drawing object or a chart, for instance), then the Cells property fails. To deal with this possibility in a friendly manner, we add some error checking. The line:

On Error GoTo ERR_EXAMPLE

tells VBA to move execution to the label ERR_EXAMPLE if an error occurs. The code following this label is called the error-handling code. If an error should occur, the next line executed is the

Tea 77Fly®

MsgBox line, in which case the dialog in Figure 7-3 will be displayed. This message gives a description of the error, obtained from the Error object, which we discuss in the next section.

Figure 7-3. An error dialog

Figure 7-3. An error dialog

It is important to note the line just before the ERR_EXAMPLE label:

Exit Sub

Without this statement, the error-handling code will always be executed, even when there is no error! Omitting this line is a common mistake. Note also that labels always end with a colon.

0 0

Post a comment