Errorhandling examples

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

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 x As Workbook FileName = "BUDGET.XLSX" On Error Resume Next Set x = 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- Chapter 11 presents several additional examples that use error handling.

REFERENCE

0 0

Post a comment