Chapter 12

Error-Handling Techniques

In This Chapter

Understanding the difference between programming errors and run-time errors

Trapping and handling run-time errors

Using the VBA on Error and Resume statements

Finding out how you can use an error to your advantage

To err is human. To anticipate errors is divine. When working with VBA, you should be aware of two broad classes of errors: programming errors and run-time errors. This chapter is all about run-time errors. I cover programming errors, also known as bugs, in Chapter 13.

A well-written program handles errors the way Fred Astaire danced: gracefully. Fortunately, VBA includes several tools to help you identify errors — and then handle them gracefully.

Types of Errors

If you’ve tried any of the examples in this book, you have probably encountered one or more error messages. Some of these errors result from bad VBA code. For example, you may spell a keyword incorrectly or type a statement with the wrong syntax. If you make such an error, you won’t even be able to execute the procedure until you correct it.

This chapter does not deal with those types of errors. Instead, I discuss run-time errors — the errors that occur while Excel executes your VBA code. More specifically, this chapter covers the following fascinating topics:

Identifying errors

Doing something about the errors that occur

Recovering from errors

Creating intentional errors (Yes, sometimes an error can be a good thing.)

The ultimate goal of error handling is to write code that avoids displaying Excel’s error messages as much as possible. In other words, you want to anticipate potential errors and deal with them before Excel has a chance to rear its ugly head with a (usually) less-than-informative error message.

An Erroneous Example

To get things started, I developed a short and simple VBA macro. A macro this simple couldn’t produce any errors, right?

Activate the VBE, insert a module, and enter the following code:

Sub EnterSquareRoot()

    Dim Num As Double

'   Prompt for a value

    Num = InputBox("Enter a value")

'   Insert the square root

    ActiveCell.Value = Sqr(Num)

End Sub

As shown in Figure 12-1, this procedure asks the user for a value. It then performs a magical calculation and enters the square root of that value into the active cell.

Figure 12-1: The InputBox function displays a dialog box that asks the user for a value.

You can execute this procedure directly from the VBE by pressing F5. Alternatively, you may want to add a button to a worksheet (use Developer⇒Controls⇒Insert and select the Form controls button to do this) and then assign the macro to the button. (Excel prompts you for the macro to assign.) Then you can run the procedure by simply clicking the button.

The macro’s not quite perfect

Execute the code a couple of times to try it out. It works pretty well, doesn’t it? Now try entering a negative number when you are prompted for a value. Oops. Trying to calculate the square root of a negative number is illegal on this planet.

Excel responds to the request to calculate the square root of a negative number by displaying the run-time error message shown in Figure 12-2. For now, just click the End button. If you click the Debug button, Excel suspends the macro, so you can use the debugging tools to help track down the error. (I describe the debugging tools in Chapter 13.)

Figure 12-2: Excel displays this error message when the procedure attempts to calculate the square root of a negative number.

Most folks don’t find the Excel error messages (for example, Invalid procedure call or argument) particularly helpful. To improve the procedure, you need to anticipate this error and handle it more gracefully. In other words, you need to add some error-handling code.

Here’s a modified version of EnterSquareRoot:

Sub EnterSquareRoot2()

    Dim Num As Double

'   Prompt for a value

    Num = InputBox("Enter a value")

'   Make sure the number is nonnegative

    If Num < 0 Then

        MsgBox "You must enter a positive number."

        Exit Sub

    End If

'   Insert the square root

    ActiveCell.Value = Sqr(Num)

End Sub

An If-Then structure checks the value contained in the Num variable. If Num is less than 0, the procedure displays a message box containing information that humans can actually understand. Then the procedure ends with the Exit Sub statement, so the run-time error never has a chance to occur.

The macro is still not perfect

So the modified EnterSquareRoot procedure is perfect, right? Not really. Try entering text rather than a value. Or click the Cancel button in the input box. Both of these actions generate an error (Type mismatch). This simple little procedure needs still more error-handling code.

The following modified code uses the IsNumeric function to make sure that Num contains a numeric value. If the user doesn’t enter a number, the procedure displays a message and then stops. Also, notice that the Num variable is now declared as a Variant. If it were declared as a Double, the code would generate an unhandled error if the user entered a nonnumeric value into the input box.

Sub EnterSquareRoot3()

    Dim Num As Variant

'   Prompt for a value

    Num = InputBox("Enter a value")

'   Make sure Num is a number

    If Not IsNumeric(Num) Then

        MsgBox "You must enter a number."

        Exit Sub

    End If

'   Make sure the number is nonnegative

    If Num < 0 Then

        MsgBox "You must enter a positive number."

        Exit Sub

    End If

'   Insert the square root

    ActiveCell.Value = Sqr(Num)

End Sub

Is the macro perfect yet?

Now this code is absolutely perfect, right? Not quite. Try running the procedure while the active sheet is a chart sheet. Yikes, another run-time error; this time it’s the dreaded Number 91 (see Figure 12-3). This error occurs because there is no active cell when a chart sheet is active, or when something other than a range is selected.

Figure 12-3: Running the procedure when a chart is selected generates this error.

The following listing uses the TypeName function to make sure that the selection is a range. If anything other than a range is selected, this procedure displays a message and then exits:

Sub EnterSquareRoot4()

    Dim Num As Variant

'   Make sure a worksheet is active

    If TypeName(Selection) <> "Range" Then

        MsgBox "Select a cell for the result."

        Exit Sub

    End If

'   Prompt for a value

    Num = InputBox("Enter a value")

'   Make sure Num is a number

    If Not IsNumeric(Num) Then

        MsgBox "You must enter a number."

        Exit Sub

    End If

'   Make sure the number is nonnegative

    If Num < 0 Then

        MsgBox "You must enter a positive number."

        Exit Sub

    End If

'   Insert the square root

    ActiveCell.Value = Sqr(Num)

End Sub

Giving up on perfection

By now, this procedure simply must be perfect. Think again, pal.

Protect the worksheet (using the Review⇒Changes⇒Protect Sheet command) and then run the code. Yep, a protected worksheet generates yet another error. And I probably haven’t thought of all the other errors that can occur. Keep reading for another way to deal with errors — even those you can’t anticipate.

Handling Errors Another Way

How can you identify and handle every possible error? Often, you can’t. Fortunately, VBA provides another way to deal with errors.

Revisiting the EnterSquareRoot procedure

Examine the following code. I modified the routine from the previous section by adding an all-purpose On Error statement to trap all errors and then checking to see whether the InputBox was cancelled.

Sub EnterSquareRoot5()

    Dim Num As Variant

    Dim Msg As String

'   Set up error handling

    On Error GoTo BadEntry

'   Prompt for a value

    Num = InputBox("Enter a value")

'   Exit if cancelled

    If Num = "" Then Exit Sub

'   Insert the square root

    ActiveCell.Value = Sqr(Num)

    Exit Sub

BadEntry:

    Msg = "An error occurred." & vbNewLine & vbNewLine

    Msg = Msg & "Make sure a range is selected, "

    Msg = Msg & "the sheet is not protected, "

    Msg = Msg & "and you enter a nonnegative value."

    MsgBox Msg, vbCritical

End Sub

---

---

This routine traps any type of run-time error. After trapping a run-time error, the revised EnterSquareRoot procedure displays the message box shown in Figure 12-4. This message box describes the most likely causes for the error.

Figure 12-4: A run-time error in the procedure generates this semi-helpful error message.

About the On Error statement

Using an On Error statement in your VBA code lets you bypass Excel’s built-in error handling and use your own error-handling code. In the previous example, a run-time error causes macro execution to jump to the statement labeled BadEntry. As a result, you avoid Excel’s unfriendly error messages, and you can display your own (friendlier, I hope) message to the user.

Notice that the example uses an Exit Sub statement right before the BadEntry label. This statement is necessary because you don’t want to execute the error-handling code if an error does not occur.

Handling Errors: The Details

You can use the On Error statement in three ways, as shown in Table 12-1.

Table 12-1 Using the On Error Statement

Syntax	What It Does
On Error GoTo label	After executing this statement, VBA resumes execution at the specified line. You must include a colon after the label so that VBA recognizes it as a label.
On Error GoTo 0	After executing this statement, VBA resumes its normal error-checking behavior. Use this statement after using one of the other On Error statements, or when you want to remove error handling in your procedure.
On Error Resume Next	After executing this statement, VBA simply ignores all errors and resumes execution with the next statement.

Resuming after an error

In some cases, you simply want the routine to end gracefully when an error occurs. For example, you may display a message describing the error and then exit the procedure. (The EnterSquareRoot5 example uses this technique.) In other cases, you want to recover from the error, if possible.

To recover from an error, you must use a Resume statement. This clears the error condition and lets you continue execution at some location. You can use the Resume statement in three ways, as shown in Table 12-2.

Table 12-2 Using the Resume Statement

Syntax	What It Does
Resume	Execution resumes with the statement that caused the error. Use this if your error-handling code corrects the problem, and it’s okay to continue.
Resume Next	Execution resumes with the statement immediately following the statement that caused the error. This essentially ignores the error.
Resume label	Execution resumes at the label you specify.

The following example uses a Resume statement after an error occurs:

Sub EnterSquareRoot6()

    Dim Num As Variant

    Dim Msg As String

    Dim Ans As Integer

TryAgain:

'   Set up error handling

    On Error GoTo BadEntry

'   Prompt for a value

    Num = InputBox("Enter a value")

    If Num = "" Then Exit Sub

'   Insert the square root

    ActiveCell.Value = Sqr(Num)

    Exit Sub

BadEntry:

    Msg = Err.Number & ": " & Error(Err.Number)

    Msg = Msg & vbNewLine & vbNewLine

    Msg = Msg & "Make sure a range is selected, "

    Msg = Msg & "the sheet is not protected, "

    Msg = Msg & "and you enter a nonnegative value."

    Msg = Msg & vbNewLine & vbNewLine & "Try again?"

    Ans = MsgBox(Msg, vbYesNo + vbCritical)

    If Ans = vbYes Then Resume TryAgain

End Sub

This procedure has another label: TryAgain. If an error occurs, execution continues at the BadEntry label, and the code displays the message shown in Figure 12-5. If the user responds by clicking Yes, the Resume statement kicks in, and execution jumps back to the TryAgain label. If the user clicks No, the procedure ends.

Notice that the error message also includes the error number, along with the “official” error description. I threw that in because I write about it later in the section “Identifying specific errors.”

Figure 12-5: If an error occurs, the user can decide whether to try again.

Remember that the Resume statement clears the error condition before continuing. To see what I mean, try substituting the following statement for the second-to-last statement in the preceding example:

If Ans = vbYes Then GoTo TryAgain

The code doesn’t work correctly if you use GoTo rather than Resume. To demonstrate, enter a negative number: You get the error prompt. Click Yes to try again and then enter another negative number. This second error is not trapped because the original error condition was not cleared.

This example is available on this book’s website.

Error handling in a nutshell

To help you keep all this error-handling business straight, I’ve prepared a quick-and-dirty summary. A block of error-handling code has the following characteristics:

It begins immediately after the label specified in the On Error statement.

It should be reached by your macro only if an error occurs. This means that you must use a statement such as Exit Sub or Exit Function immediately before the label.

It may require a Resume statement. If you choose not to abort the procedure when an error occurs, you must execute a Resume statement before returning to the main code.

Knowing when to ignore errors

In some cases, it’s perfectly okay to ignore errors. That’s when the On Error Resume Next statement comes into play.

The following example loops through each cell in the selected range and converts the value to its square root. This procedure generates an error message if any cell in the selection contains a negative number or text:

Sub SelectionSqrt()

    Dim cell As Range

    If TypeName(Selection) <> "Range" Then Exit Sub

    For Each cell In Selection

        cell.Value = Sqr(cell.Value)

    Next cell

End Sub

In this case, you may want to simply skip any cell that contains a value you can’t convert to a square root. You can create all sorts of error-checking capabilities by using If-Then structures, but you can devise a better (and simpler) solution by simply ignoring the errors that occur.

The following routine accomplishes this by using the On Error Resume Next statement:

Sub SelectionSqrt()

    Dim cell As Range

    If TypeName(Selection) <> "Range" Then Exit Sub

    On Error Resume Next

    For Each cell In Selection

        cell.Value = Sqr(cell.Value)

    Next cell

End Sub

In general, you can use an On Error Resume Next statement if you consider the errors harmless or inconsequential to your task.

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.

Every type of error has an official number. When an error occurs, Excel stores the error number in an Error object named Err. This object’s Number property contains the error number, and its Description property contains a description of the error. For example, the following statement displays the error number, a colon, and a description of the error:

MsgBox Err.Number & ": " & Err.Description

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

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.) Attempting to write to a locked cell in a protected worksheet 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

ErrorHandler:

    Select Case Err.Number

       Case 5 'Negative number

         Resume Next

       Case 13 'Type mismatch

         Resume Next

       Case 1004 'Locked cell, protected sheet

         MsgBox "Cell is locked. Try again.", vbCritical, cell.Address

         Exit Sub

       Case Else

         ErrMsg = Error(Err.Number)

         MsgBox "ERROR: " & ErrMsg, vbCritical, cell.Address

         Exit Sub

    End Select

End Sub

When a run-time error occurs, execution jumps to the code beginning at the ErrorHandler label. The Select Case structure (I discuss that structure in Chapter 10) 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 catchall for unanticipated errors, traps all other errors and displays the actual error message.

An Intentional Error

Sometimes you can use an error to your advantage. For example, suppose you have a macro that works only if a particular workbook is open. How can you determine whether that workbook is open? One way is to write code that loops through the Workbooks collection checking to determine if the workbook you’re interested in is in that collection.

Here’s an easier way: a general-purpose function that accepts one argument (a workbook name) and returns True if the workbook is open, False if it’s not.

Here’s the function:

Function WorkbookIsOpen(book As String) As Boolean

    Dim WBName As String

    On Error GoTo NotOpen

    WBName = Workbooks(book).Name

    WorkbookIsOpen = True

    Exit Function

NotOpen:

    WorkbookIsOpen = False

End Function

This function takes advantage of the fact that Excel generates an error if you refer to a workbook that is not open. For example, the following statement generates an error if a workbook named MyBook.xlsx is not open:

WBName = Workbooks("MyBook.xlsx").Name

In the WorkbookIsOpen function, the On Error statement tells VBA to resume the macro at the NotOpen statement if an error occurs. Therefore, an error means that the workbook is not open, and the function returns False. If the workbook is open, no error occurs, and the function returns True.

Here’s another variation on the WorkbookIsOpen function. This version uses On Error Resume Next to ignore the error. But the code checks Err’s Number property. If Err.Number is 0, no error occurred, and the workbook is open. If Err.Number is anything else, it means that an error occurred (and the workbook is not open).

Function WorkbookIsOpen(book) As Boolean

    Dim WBName As String

    On Error Resume Next

    WBName = Workbooks(book).Name

    If Err.Number = 0 Then WorkbookIsOpen = True _

      Else WorkbookIsOpen = False

End Function

The following example demonstrates how to use this function in a Sub procedure:

Sub UpdatePrices()

    If Not WorkbookIsOpen("Prices.xlsx") Then

        MsgBox "Please open the Prices workbook first!"

        Exit Sub

    End If

'   [Other code goes here]

End Sub

The UpdatePrices procedure (which must be in the same workbook as WorkbookIsOpen) calls the WorkbookIsOpen function and passes the workbook name (Prices.xlsx) as an argument. The WorkbookIsOpen function returns either True or False. Therefore, if the workbook is not open, the procedure informs the user of that fact. If the workbook is open, the macro continues.

Error handling can be a tricky proposition — after all, many different errors can occur, and you can’t anticipate all of them. In general, you should trap errors and correct the situation before Excel intervenes, if possible. Writing effective error-trapping code requires a thorough knowledge of Excel and a clear understanding of how the VBA error handling works. Subsequent chapters contain more examples of error handling.