Error-Handling Techniques | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

When a VBA procedure is running, errors can occur, as you undoubtedly know. These include either syntax errors (which you must correct before you can execute a procedure) or runtime errors (which occur while the procedure is running). This section deals with runtime errors.

Caution

For error-handling procedures to work, the Break on All Errors setting must be turned off. In the VBE, choose Tools Options and click the General tab in the Options dialog box. If Break on All Errors is selected, VBA ignores your error-handling code. You'll usually want to use the Break on Unhandled Errors option.

Normally, a runtime error causes VBA to stop, and the user sees a dialog box that displays the error number and a description of the error. A good application doesn't make the user deal with these messages. Rather, it incorporates error-handling code to trap errors and take appropriate actions. At the very least, your error-handling code can display a more meaningful error message than the one VBA pops up.

CROSS-REFERENCE

Appendix C lists all the VBA error codes and descriptions.

Trapping errors

You can use the On Error statement to specify what happens when an error occurs. Basically, you have two choices:

Ignore the error and let VBA continue. Your code can later examine the Err object to determine what the error was and then take action if necessary.
Jump to a special error-handling section of your code to take action. This section is placed at the end of the procedure and is also marked by a label.

To cause your VBA code to continue when an error occurs, insert the following statement in your code:

 On Error Resume Next

Some errors are inconsequential and can be ignored without causing a problem. But you may want to determine what the error was. When an error occurs, you can use the Err object to determine the error number. The VBA Error function can be used to display the text that corresponds to the Err.Number value. For example, the following statement displays the same information as the normal Visual Basic error dialog box (the error number and the error description):

 MsgBox "Error " & Err & ": " & Error(Err.Number)

Figure 9-6 shows a VBA error message, and Figure 9-7 shows the same error displayed in a message box. You can, of course, make the error message a bit more meaningful to your end users by using more descriptive text.

Figure 9-6: VBA error messages aren't always user friendly.

Figure 9-7: You can create a message box to display the error code and description.

Note	Referencing `Err` is equivalent to accessing the `Number` property of the `Err` object. Therefore, the following two statements have the same effect: MsgBox Err MsgBox Err.Number

You also use the On Error statement to specify a location in your procedure to jump to when an error occurs. You use a label to mark the location. For example:

 On Error GoTo ErrorHandler

Error-handling examples

The first example demonstrates an error that can safely be ignored. The SpecialCells method selects cells that meet a certain criterion.

Note	The `SpecialCells` method is equivalent to choosing the Home Editing Find & Select Go To Special command. The Go To Special dialog box provides you with a number of choices. For example, you can select cells that contain a numeric constant (non-formula).

In the example that follows , the SpecialCells method selects all the cells in the current range selection that contain a formula that returns a number. If no cells in the selection qualify, VBA displays the error message shown in Figure 9-8. Using the On Error Resume Next statement simply prevents the error message from appearing.

Figure 9-8: The SpecialCells method generates this error if no cells are found.

 Sub SelectFormulas()     On Error Resume Next     Selection.SpecialCells(xlFormulas, xlNumbers).Select     On Error GoTo 0 '   ...[more code goes here] End Sub

The On Error GoTo 0 statement restores normal error handling for the remaining statements in the procedure.

The following procedure uses an additional statement to determine whether an error did occur:

 Sub SelectFormulas2()     On Error Resume Next     Selection.SpecialCells(xlFormulas, xlNumbers).Select     If Err.Number = 1004 Then MsgBox "No formula cells were found."     On Error GoTo 0 '   ...[more code goes here] End Sub

If the Number property of Err is equal to anything except 0, then an error occurred. The If statement checks to see if Err.Number is equal to 1004 and displays a message box if it is. In this example, the code is checking for a specific error number. To check for any error, use a statement like this:

 If Err.Number <> 0 Then MsgBox "An error occurred."

The next example demonstrates error handling by jumping to a label.

 Sub ErrorDemo()     On Error GoTo Handler     Selection.Value = 123     Exit Sub Handler:     MsgBox "Cannot assign a value to the selection." End Sub

The procedure attempts to assign a value to the current selection. If an error occurs (for example, a range is not selected or the sheet is protected), the assignment statement results in an error. The On Error statement specifies a jump to the Handler label if an error occurs. Notice the use of the Exit Sub statement before the label. This prevents the error-handling code from being executed if no error occurs. If this statement is omitted, the error message is displayed even if an error does not occur.

Sometimes, you can take advantage of an error to get information. The example that follows simply checks whether a particular workbook is open . It does not use any error handling.

 Sub CheckForFile1()     Dim FileName As String     Dim FileExists As Boolean     Dim book As Workbook     FileName = "BUDGET.XLSX"     FileExists = False '   Cycle through all workbooks     For Each book In Workbooks         If UCase(book.Name) = FileName Then FileExists = True     Next book '   Display appropriate message     If FileExists Then         MsgBox FileName & " is open."     Else         MsgBox FileName & " is not open."     End If End Sub

Here, a For Each-Next loop cycles through all objects in the Workbooks collection. If the workbook is open, the FileExists variable is set to True . Finally, a message is displayed that tells the user whether the workbook is open.

The preceding routine can be rewritten to use error handling to determine whether the file is open. In the example that follows, the On Error Resume Next statement causes VBA to ignore any errors. The next instruction attempts to reference the workbook by assigning the workbook to an object variable (by using the Set keyword). If the workbook is not open, an error occurs. The If-Then-Else structure checks the value property of Err and displays the appropriate message.

 Sub CheckForFile()     Dim FileName As String     Dim  As Workbook     FileName = "BUDGET.XLSX"     On Error Resume Next     Set  = Workbooks(FileName)     If Err = 0 Then         MsgBox FileName & " is open."     Else         MsgBox FileName & " is not open."     End If     On Error GoTo 0 End Sub

CROSS-REFERENCE

Chapter 11 presents several additional examples that use error handling.