Dealing with Run-Time Errors

In many cases, run-time errors are caused by factors outside your control. For example, suppose you write 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 = "#"", ""##"", ""##"", ""###"         Elself 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 an error message:

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 still might 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 such as this-that is, shield yourself and others from VBA's runtime 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 procedure:

 On Error GoTo label 

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

 Sub LakhsCrores()     'Catch run-time error caused by inappropriate selection     'On Error GoTo ErrorHandler     Dim cell As Object     For Each cell In Selection         If Abs(cell.Value) > 10000000 Then             cell.NumberFormat = "#"", ""##"", ""##"", ""###"         Elself 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 continue 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 range includes numbers, text, or blank cells. However, if it includes a cell containing an Excel error constant, such as #NA, a different run-time error occurs: error 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 nonrange 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 can handle both run-time errors (438 and 13) with the following code, for example:

 ErrorHandler: If Err.Number=438 Then     MsgBox "Please select a worksheet range." Elself 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 have all your bases more or less covered.

The foregoing error handler examples assume 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 went 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 it can continue executing your program. Use either a Resume statement or a Resume Next statement to do this. A Resume statement causes VBA to reexecute the line that caused the error. A Resume Next statement causes VBA to continue at the line after the line that caused the error.

By combining On Error with Resume Next, you can tell VBA to ignore any run-time errors 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 EakhsCrores macro, for example, you can write the following:

 Sub LakhsCrores()     'Tell VBA to ignore all run-time errors     On Error Resume Next     Dim cell As Object     For Each cell In Selection         If Abs(cell.Value) > 10000000 Then             cell.NumberFormat = "#"",""##"",""##"",""###"         Elself 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, VBA ignores the run-time error, 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 works for this particular macro.

Of course, when you use On Error Resume Next, you're disabling the VBA 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.