Microsoft Office Tutorials and References
In Depth Information
Error-Handling Techniques
Following is a variation that uses the On Error Resume Next statement to prevent the error
message from appearing:
Sub SelectFormulas2()
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. If
so, the user is informed by a message.
Sub SelectFormulas3()
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 other than 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 isn’t 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
 
Search JabSto ::




Custom Search