Identifying specific errors

All errors are not created equal. Some are serious and some are less serious. Although you may ignore errors you consider inconsequential, you must deal with other, more serious errors. In some cases, you need to identify the specific error that occurred.

When an error occurs, Excel stores the error number in an Error object named Err. This object's Number property contains the error number. You can get a description of the error by using the VBA Error function. For example, the following statement displays the error number and a description:

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

Figure 12-6 shows an example of this. Keep in mind, however, that the Excel error messages are not always very useful — but you already know that.

Figure 12-6:

Displaying an error number and a description.

Figure 12-6:

Displaying an error number and a description.

The following procedure demonstrates how to determine which error occurred. In this case, you can safely ignore errors caused by trying to get the square root of a nonpositive number (that is, error 5) or errors caused by trying to get the square root of a nonnumeric value (error 13). On the other hand, you need to inform the user if the worksheet is protected and the selection contains one or more locked cells. (Otherwise, the user may think the macro worked when it really didn't.) This event causes error 1004.

Sub SelectionSqrt() Dim cell As Range Dim ErrMsg As String

If TypeName(Selection) <> "Range" Then Exit Sub On Error GoTo ErrorHandler For Each cell In Selection cell.Value = Sqr(cell.Value) Next cell Exit Sub


Select Case Err

Case 5 'Negative number

Resume Next Case 13 'Type mismatch

Resume Next Case 1004 'Locked cell, protected sheet

MsgBox "The cell is locked. Try again." Exit Sub Case Else

ErrMsg= Error(Err.Number) MsgBox "ERROR: " & ErrMsg Exit Sub End Select

End Sub

When a run-time error occurs, execution jumps to the ErrorHandler label. The Select Case structure tests for three common error numbers. If the error number is 5 or 13, execution resumes at the next statement. (In other words, the error is ignored.) But if the error number is 1004, the routine advises the user and then ends. The last case, a catch-all for unanticipated errors, traps all other errors and displays the actual error message.

0 0


  • longo
    Is it possible to ignore specific errors in vba?
    8 years ago

Post a comment