Dealing with Run-Time Errors

DEALING WITH RUN-TIME ERRORS

In many cases, run-time errors are caused by factors outside your control. For example, suppose you have written the following macro to format the numbers in a selected range using the Indian system of lakhs and crores:

    Sub LakhsCrores()
        Dim cell as Object
        For Each cell In Selection
            If Abs(cell.Value) > 10000000 Then
                cell.NumberFormat = "#"",""##"",""##"",""###"
            ElseIf Abs(cell.Value) > 100000 Then
                cell.NumberFormat = "##"",""##"",""###"
            End If
        Next cell
    End Sub

This macro works fine if the person who runs it selects a range containing numbers before running the macro. But if the user selects something else—a chart embedded on the worksheet, for example—VBA displays the error message: "Run-time error'436': Object doesn't support the property or method".

The macro generates a run-time error and enters break mode because the For Each statement has to be applied to a collection or an array, and a chart object is neither. (A range is a collection of cells, so For Each does work with a range.) Even though you can figure out easily enough what the error message means and what you have to do about it (try again with a range selected), the message might still be annoying. If you intend for this macro to be used by someone else, it’s definitely impolite to let that other user see such a message.

You can “trap” an error like this—that is, shield yourself and others from VBA’s run-time error messages—by means of an On Error GoTo statement. The statement must appear before the code that might cause a run-time error, and it has the following syntax, in which label is a name that identifies an error-handling section elsewhere in your program:

    On Error Goto label

If a run-time error occurs, the On Error GoTo statement transfers execution to the errorhandling code. In the case of your LakhsCrores routine, the macro complete with error handling might look like this:

    Sub LakhsCrores()
        'Catch run-time error caused by inappropriate selection
        On Error GoTo ErrorHandler

        For Each cell In Selection
            If Abs(cell.Value) > 10000000 Then
                cell.NumberFormat = "#"",""##"",""##"",""###"
            ElseIf Abs(cell.Value) > 100000 Then
                cell.NumberFormat = "##"",""##"",""###"
            End If
        Next cell

        'Exit sub statement keeps execution from entering
        'error handler if no error occurs
            Exit Sub
    'Error handler
    ErrorHandler:
        MsgBox "Please select a worksheet range"
    End Sub

Notice that the error handler goes at the end of the program, introduced by the label that appeared in the On Error statement. The label must be followed by a colon and must appear on a line by itself. An Exit Sub statement appears before the error handler. This statement terminates the macro when no run-time error occurs; without it, execution would “fall into” the error handler regardless of whether an error occurred. Now when the user runs the macro after selecting a chart object, the user sees a polite message box instead of a rude run-time error message.

The macro still has a problem, however. The code works fine when the selected worksheet range includes numbers, text, or blank cells. If it includes a cell containing an Excel error constant, such as #NA, however, a different run-time error occurs: error number 13, Type Mismatch. The message box generated by the error handler shown previously would not be appropriate for this kind of error.

How do you make your code show one message for a non-range selection and another for a range that includes one or more error values? You use the Number property of the Err object. This property is always set to the most recent run-time error number (or 0, if no procedure is running or if no error has occurred). You could handle both run-time errors (438 and 13) with the following code:

    ErrorHandler:
    If Err.Number=438 Then
        MsgBox "Please select a worksheet range"
    Else
        Msgbox "Please select a range without error values"
    End If

In case the code is susceptible to some other run-time error that you haven’t anticipated, you might want to make the handler look like this:

    ErrorHandler:
    If Err.Number = 438 Then
        MsgBox "Please select a worksheet range"
    ElseIf Err.Number = 13 Then
        MsgBox "Please select a range without error values"
    Else
        MsgBox "Sorry! Unknown error!"
    End If

This isn’t particularly elegant, but at least you’ve got all the bases more or less covered. The foregoing error-handler examples assume that your program should terminate when a run-time error occurs. The purpose of the error handler is to prevent the jolting VBA message from showing up—and to provide the user with a simple explanation of what has gone wrong.

In some cases you’ll want your procedure to continue running after a run-time error occurs. In such a case, your error handler needs to return VBA to the appropriate instruction so that it can continue executing your program. Use either a Resume or Resume Next statement to do this. A Resume statement causes VBA to re-execute the line that caused the error. A Resume Next statement causes VBA to continue at the line that follows the line that caused the error.

By combining On Error with Resume Next, you can tell VBA to ignore any run-time error that might occur and go to the next statement. If you’re sure you’ve anticipated all the kinds of run-time errors that might occur with your program, On Error Resume Next can often be the simplest and most effective way to deal with potential mishaps. In the LakhsCrores macro, for example, you can write the following:

    Sub LakhsCrores()
        'Tell VBA to ignore all run-time errors
        On Error Resume Next

        For Each cell In Selection
            If Abs(cell.Value) > 10000000 Then
                cell.NumberFormat = "#"",""##"",""##"",""###"
            ElseIf Abs(cell.Value) > 100000 Then
                cell.NumberFormat = "##"",""##"",""###"
            End If
        Next cell
    Exit Sub

With this code, if the user selects a chart and runs the macro, the run-time error is ignored, the program moves on to the For Each block, and nothing happens—because nothing can happen. If the user selects a range containing one or more error values, the program skips over those cells that it can’t format and formats the ones it can. In all cases, neither error message nor message box appears, and all is well. This solution is ideal for this particular macro.

Of course, when you use On Error Resume Next, you’re disabling VBA’s run-time checking altogether. You should do this only when you’re sure you’ve thought of everything that could possibly go awry—and the best way to arrive at that serene certainty is to test, test again, and then test some more.