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()


MsgBox Selection.Cells.Count Exit Sub


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:


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