24. Handling Errors

Errors are bound to happen. Even when you test and retest your code, after a report is put into daily production and used for hundreds of days, something unexpected will eventually happen. Your goal should be to try to head off obscure errors as you code. For this reason, you should always be thinking of what unexpected things could happen someday that could make your code not work.

What Happens When an Error Occurs?

When VBA encounters an error and you have no error-checking code in place, the program stops and presents you or your client with the “Continue, End, Debug, Help” error message, as shown in Figure 24.1. If Debug is grayed out, then someone has protected the VBA code and you will have to call the developer.

Figure 24.1. An unhandled error in an unprotected module presents you with a choice to end or debug.

When presented with the choice to end or debug, you should click Debug. The VB Editor highlights in yellow the line that caused the error. When you hover the cursor over any variable, you see the current value of the variable, which provides a lot of information about what could have caused the error (see Figure 24.2).

Figure 24.2. After clicking Debug, the macro is in break mode. Hover the cursor over a variable; after a few seconds, the current value of the variable is shown.

Excel is notorious for returning errors that are not very meaningful. For example, dozens of situations can cause a 1004 error. Seeing the offending line highlighted in yellow and examining the current value of any variables helps you discover the real cause of an error. You might note that many error messages in Excel 2013 are more meaningful than the equivalent message in Excel 2010. This extends to the VBA error messages.

After examining the line in error, click the Reset button to stop execution of the macro. The Reset button is the square button under the Run item in the main menu, as shown in Figure 24.3.

Figure 24.3. The Reset button looks like the Stop button in the set of three buttons that resembles a DVD control panel.

If you fail to click Reset to end the macro and then attempt to run another macro, you are presented with the annoying error message shown in Figure 24.4. The message is annoying because you start in Excel, but when this message window is displayed, the screen automatically switches to display the VB Editor. You can see the Reset button in the background, but you cannot click it due to the message box that is displayed. However, immediately after you click OK to close the message box, you are returned to the Excel user interface instead of being left in the VB Editor. Because this error message occurs quite often, it would be more convenient if you could be returned to the VB Editor after clicking OK.

Figure 24.4. This message appears if you forget to click Reset to end a debug session and then attempt to run another macro.

Debug Error Inside Userform Code Is Misleading

After you click Debug, the line highlighted as the error can be misleading in one situation. For example, suppose you call a macro that displays a userform. Somewhere in the userform code, an error occurs. When you click Debug, instead of showing the problem inside the userform code, Excel highlights the line in the original macro that displayed the userform. Follow these steps to find the real error:

1. After the error message box shown in Figure 24.5 is displayed, click the Debug button.

Figure 24.5. Select Debug in response to this error 13.

You see that the error allegedly occurred on a line that shows a userform, as shown in Figure 24.6. Because you have read this chapter, you know that this is not the line in error.

Figure 24.6. The line in error is indicated as the frmChoose.Show line.

2. Press F8 to execute the Show method. Instead of getting an error, you are taken into the Userform_Initialize procedure.

3. Keep pressing F8 until you get the error message again. Stay alert because as soon as you encounter the error, the error message box is displayed. Click Debug and you are returned to the userform.Show line. It is particularly difficult to follow the code when the error occurs on the other side of a long loop, as shown in Figure 24.7.

Figure 24.7. With 25 items to add to the list box, you must press F8 53 times to get through this three-line loop.

Imagine trying to step through the code in Figure 24.7. You carefully press F8 five times with no problems through the first pass of the loop. Because the problem could be in future iterations through the loop, you continue to press F8. If there are 25 items to add to the list box, 48 more presses of F8 are required to get through the loop safely. Each time before pressing F8, you should mentally note that you are about to run some specific line.

At the point shown in Figure 24.7, the next press of the F8 key displays the error and returns you to the frmChoose.Show line back in Module1. This is an annoying situation.

When you click Debug and see that the line in error is a line that displays a userform, you need to start pressing the F8 key to step into the userform code until you get the error. Invariably, you will get incredibly bored pressing F8 a million times and forget to pay attention to which line caused the error. However, as soon as the error happens, you are thrown back to the Debug message, which returns you to the frmChoose.Show line of code.

At that point, you need to start pressing F8 again. If you can recall the general area where the debug error occurred, click the mouse cursor in a line right before that section and use Ctrl+F8 to run the macro up to the cursor. Alternatively, right-click that line and choose Run to Cursor.

Sometimes an error will occur within a loop. Add Debug.Print i inside the loop and use the Immediate Pane (Ctrl+G) to locate which time through the loop caused the problem.

Basic Error Handling with the On Error GoTo Syntax

The basic error-handling option is to tell VBA that in the case of an error you want to have code branch to a specific area of the macro. In this area, you might have special code that alerts users of the problem and enables them to react.

A typical scenario is to add the error-handling routine at the end of the macro. To set up an error handler, follow these steps:

1. After the last code line of the macro, insert the code line Exit Sub. This makes sure that the execution of the macro does not continue into the error handler.

2. After the Exit Sub line, add a label. A label is a name followed by a colon. For example, you might create a label called MyErrorHandler:.

3. Write the code to handle the error. If you want to return control of the macro to the line after the one that caused the error, use the statement Resume Next.

In your macro, just before the line that might likely cause the error, add a line reading On Error GoTo MyErrorHandler. Note that in this line, you do not include the colon after the label name.

Immediately after the line of code that you suspect will cause the error, add code to turn off the special error handler. Because this is not intuitive, it tends to confuse people. The code to cancel any special error handling is On Error GoTo 0. There is no label named 0. Instead, this line is a fictitious line that instructs Excel to go back to the normal state of displaying the End/Debug error message when an error is encountered. This is why it is important to cancel the error handling.

Note that you can have multiple error handler labels at the bottom of your macro. Be sure that each section ends in Exit Sub or Resume Next so that the code from the first error handler does not continue into the second error handler.

Sub HandleAnError()
    Dim MyFile as Variant
    ' Set up a special error handler
    On Error GoTo FileNotThere
    Workbooks.Open Filename:="C:NotHere.xls"
    ' If we get here, cancel the special error handler
    On Error GoTo 0
    MsgBox "The program is complete"

    ' The macro is done. Use Exit sub, otherwise the macro
    ' execution WILL continue into the error handler
    Exit Sub

    ' Set up a name for the Error handler
FileNotThere:
    MyPrompt = "There was an error opening the file. It is possible the"
    MyPrompt = MyPrompt & " file has been moved. Click OK to browse for the "
    MyPrompt = MyPrompt & "file, or click Cancel to end the program"
    Ans = MsgBox(Prompt:=MyPrompt, Buttons:=vbOKCancel)
    If Ans = vbCancel Then Exit Sub

    ' The client clicked OK. Let him browse for the file
    MyFile = Application.GetOpenFilename
    If MyFile = False Then Exit Sub

    ' What if the 2nd file is corrupt? We do not want to recursively throw
    ' the client back into this error handler. Just stop the program
    On Error GoTo 0
    Workbooks.Open MyFile
    ' If we get here, then return the macro execution back to the original
    ' section of the macro, to the line after the one that caused the error.
    Resume Next


End Sub

Generic Error Handlers

Some developers like to direct any error to a generic error handler to make use of the Err object. This object has properties for error number and description. You can offer this information to the client and prevent her from getting a Debug message:

    On Error GoTo HandleAny
    Sheets(9).Select

    Exit Sub

HandleAny:
    Msg = "We encountered " & Err.Number & " - " & Err.Description
    MsgBox Msg
    Exit Sub

Handling Errors by Choosing to Ignore Them

Some errors can simply be ignored. For example, suppose you are going to use the HTML Creator macro from Chapter 18, “Reading from and Writing to the Web.” Your code erases any existing index.html file from a folder before writing out the next file.

The Kill (FileName) statement returns an error if FileName does not exist. This probably is not something about which you need to worry. After all, you are trying to delete the file, so you probably do not care whether someone already deleted it before running the macro. In this case, tell Excel to just skip over the offending line and resume macro execution with the next line. The code to do this is On Error Resume Next:

Sub WriteHTML()
    MyFile = "C:Index.html"
    On Error Resume Next
    Kill MyFile
    On Error Goto 0
    Open MyFile for Output as #1
    ' etc...
End Sub

Suppressing Excel Warnings

Some messages appear even if you have set Excel to ignore errors. For example, try to delete a worksheet using code and you still get the message “You can’t undo deleting sheets, and you might be removing some data. If you don’t need it, click Delete.” This is annoying. You do not want your clients to have to answer this warning; it gives them a chance to choose not to delete the sheet your macro wants to delete. In fact, this is not an error but an alert. To suppress all alerts and force Excel to take the default action, use Application.DisplayAlerts = False:

Sub DeleteSheet()
    Application.DisplayAlerts = False
    Worksheets("Sheet2").Delete
    Application.DisplayAlerts = True
End Sub

Encountering Errors on Purpose

Because programmers hate errors, this concept might seem counterintuitive, but errors are not always bad. Sometimes it is faster to simply encounter an error.

Suppose, for example, that you want to find out whether the active workbook contains a worksheet named Data. To find this out without causing an error, you could code this:

DataFound = False
For Each ws in ActiveWorkbook.Worksheets
    If ws.Name = "Data" then
        DataFound = True
        Exit For
    End if
Next ws
If not DataFound then Sheets.Add.Name = "Data"

This takes eight lines of code. If your workbook has 128 worksheets, the program loops through 128 times before deciding that the data worksheet is missing.

The alternative is to try to reference the data worksheet. If you have error checking set to resume next, the code runs, and the Err object is assigned a number other than zero:

On Error Resume Next
X = Worksheets("Data").Name
If Err.Number <> 0 then Sheets.Add.Name = "Data"
On Error GoTo 0

This code runs much faster. Errors usually make programmers cringe. However, in this case and in many other cases, the errors are perfectly acceptable.

Train Your Clients

Suppose you are developing code for a client across the globe or for the administrative assistant so that he can run the code while you are on vacation. In both cases, you might find yourself trying to debug code remotely while you are on the telephone with the client.

For this reason, it is important to train clients about the difference between an error and a simple MsgBox. Even though a MsgBox is a planned message, it still appears out of the blue with a beep. Teach your users that even though error messages are bad, not everything that pops up is an error message. For example, I had a client who kept reporting to her boss that she was getting an error from my program. In reality, she was getting an informational MsgBox. Both Debug errors and Msgbox messages beep at the user.

When clients get Debug errors, train them to call you while the Debug message is still on the screen. This enables you to get the error number and description. You also can ask the client to click Debug and tell you the module name, the procedure name, and which line is in yellow. Armed with this information, you can usually figure out what is going on. Without this information, it is unlikely that you will be able to resolve the problem. Getting a call from a client saying that there was a 1004 error is of little help—1004 is a catchall error that can mean any number of things.

Errors While Developing Versus Errors Months Later

When you have just written code that you are running for the first time, you expect errors. In fact, you might decide to step through code line by line to watch the progress of the code the first time through.

It is another thing to have a program that has been running daily in production suddenly stop working because of an error. This can be perplexing. The code has been working for months. Why did it suddenly stop working today?

It is easy to blame the client. However, when you get right down to it, it is really the fault of developers for not considering the possibilities.

The following sections describe a couple of common problems that can strike an application months later.

Runtime Error 9: Subscript Out of Range

You set up an application for a client and you provided a Menu worksheet where some settings are stored. Then one day this client reports the error message shown in Figure 24.8.

Figure 24.8. The Runtime Error 9 is often caused when you expect a worksheet to be there and it has been deleted or renamed by the client.

Your code expected there to be a worksheet named Menu. For some reason, the client either accidentally deleted the worksheet or renamed it. When you tried to select the sheet, you received an error:

Sub GetSettings()
    ThisWorkbook.Worksheets("Menu").Select
    x = Range("A1").Value
End Sub

This is a classic situation where you cannot believe that the client would do something so crazy. After you have been burned by this one a few times, you might go to these lengths to prevent an unhandled Debug error:

Sub GetSettings()
    On Error Resume Next
    x = ThisWorkbook.Worksheets("Menu").Name
    If Not Err.Number = 0 Then
        MsgBox "Expected to find a Menu worksheet, but it is missing"
        Exit Sub
    End If
    On Error GoTo 0

    ThisWorkbook.Worksheets("Menu").Select
    x = Range("A1").Value
End Sub

Runtime Error 1004: Method Range of Object Global Failed

You have code that imports a text file each day. You expect the text file to end with a Total row. After importing the text, you want to convert all the detail rows to italic.

The following code works fine for months:

Sub SetReportInItalics()
    TotalRow = Cells(Rows.Count,1).End(xlUp).Row
    FinalRow = TotalRow - 1
    Range("A1:A" & FinalRow).Font.Italic = True
End Sub

Then one day, the client calls with the error message shown in Figure 24.9.

Figure 24.9. The Runtime Error 1004 can be caused by a number of things.

Upon examination of the code, you discover that something bizarre went wrong when the text file was transferred via FTP to the client that day. The text file ended up as an empty file. Because the worksheet was empty, TotalRow was determined to be Row 1. If you assume that the last detail row was TotalRow - 1, the code is set up to attempt to format Row 0, which clearly does not exist.

After an episode like this, you find yourself writing code that preemptively looks for this situation:

Sub SetReportInItalics()
    TotalRow = Cells(Rows.Count,1).End(xlUp).Row
    FinalRow = TotalRow - 1
    If FinalRow > 0 Then
        Range("A1:A" & FinalRow).Font.Italic = True
    Else
        MsgBox "It appears the file is empty today. Check the FTP process"
    End If
End Sub

The Ills of Protecting Code

It is possible to lock a VBA project so that it cannot be viewed. However, this is not recommended. When code is protected and an error is encountered, your user is presented with an error message but no opportunity to debug. The Debug button is there, but it is grayed out. This is useless in helping you discover the problem.

Further, the Excel VBA protection scheme is horribly easy to break. Programmers in Estonia offer $40 software that lets you unlock any project. For this reason, you need to understand that office VBA code is not secure and get over it. If you absolutely need to truly protect your code, invest in Visual Studio and learn how to develop COM add-ins.

More Problems with Passwords

The password scheme for any version of Excel from 2002 forward is incompatible with Excel 97. If you protected code in Excel 2002, you cannot unlock the project in Excel 97. As your application is given to more employees in a company, you will invariably find an employee using Excel 97. Of course, that user will come up with a runtime error. However, if you locked the project in Excel 2002 or newer, you are not able to unlock the project in Excel 97, which means that you cannot debug the program in Excel 97.

Bottom line: Locking code causes more trouble than it is worth.

Errors Caused by Different Versions

Microsoft improves VBA in every version of Excel. Pivot table creation was improved dramatically between Excel 97 and Excel 2000. Sparklines and slicers were new in Excel 2010. The Data Model is introduced in Excel 2013.

The TrailingMinusNumbers parameter was new in Excel 2002. This means that if you write code in Excel 2013 and then send the code to a client with Excel 2000, that user gets a compile error as soon as she tries to run any code in the same module as the offending code. For this reason, you need to consider this application in two modules.

Module1 has macros ProcA, ProcB, and ProcC. Module2 has macros ProcD and ProcE. It happens that ProcE has an ImportText method with the TrailingMinusNumbers parameter.

The client can run ProcA and ProcB on the Excel 2000 machine without problem. As soon as she tries to run ProcD, she gets a compile error reported in ProcD because Excel tries to compile all of Module2 when she tries to run code in that module. This can be incredibly misleading: An error being reported when the client runs ProcD is actually caused by an error in ProcE.

One solution is to have access to every supported version of Excel, plus Excel 97, and test the code in all versions. Note that Excel 97 SR-2 was far more stable than the initial releases of Excel 97. Even though many clients are hanging on to Excel 97, it is frustrating when you find someone who does not have the stable service release.

Macintosh users will believe that their version of Excel is the same as the Excel for Windows. Microsoft promised compatibility of files, but that promise ends in the Excel user interface. VBA code is not compatible between Windows and the Mac. Excel VBA on the Mac in Excel 2011 is close to Excel 2010 VBA but annoyingly different. Excel 2008 for the Mac uses AppleScript instead of supporting VBA. Further, anything you do with the Windows API is not going to work on a Mac.

Next Steps

This chapter discussed how to make your code more bulletproof for your clients. In Chapter 25, “Customizing the Ribbon to Run Macros,” you’ll learn how to customize the ribbon to allow your clients to enjoy a professional user interface.