25. 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 25.1.

Figure 25.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 the line that caused the error in yellow. When you hover the cursor over any variable, you will see the current value of the variable, which provides a lot of information about what could have caused the error (see Figure 25.2).

Figure 25.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 will help you discover the real cause of an error.

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 25.3.

Figure 25.3. The Reset button looks like the Stop button in the set of three buttons that resemble a VCR control panel.

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 25.5 is displayed, click the Debug button.
   

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

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

   

   Figure 25.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 25.7.
   

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

Imagine trying to step through the code in Figure 25.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 25.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 will be 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.

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 may 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.

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 them from getting a Debug message:

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 16, “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:

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 will still get the message “Data may exist in the sheet(s) selected for deletion. If you want to delete the data permanently, click Delete.” This is annoying. You do not want your clients to have to answer this warning. 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:

To see a demo of using DisplayAlerts, search for Excel VBA 25 at YouTube.

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:

This takes eight lines of code. If your workbook has 128 worksheets, the program would loop 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 allows you to get the error number and description. You also can ask the client to click Debug and tell you the module name, procedure name, and the line 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 may 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 25.8.

Figure 25.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. As soon as you tried to select the sheet, you received an error:

This is a classic situation where you cannot believe 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:

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 italics.

The following code works fine for months:

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

Figure 12.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 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 will find yourself writing code that preemptively looks for this situation:

The Ills of Protecting Code

It is possible to lock a VBA project so that it cannot be viewed. However, this is not recommend. 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.

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. Many people are still using 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 will not be 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 are new in Excel 2010. Certain chart features were improved between Excel 97 and Excel 2000, and charting was completely rewritten in Excel 2007. Excel started supporting XML in Excel 2003 and stopped supporting interactivity in saved web pages in Excel 2007.

The TrailingMinusNumbers parameter was new in Excel 2002. This means that if you write code in Excel 2010 and then send the code to a client with Excel 2000, that user will get 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 will get 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 2004 is close to Excel 97 VBA but annoyingly different. Excel 2008 for the Mac uses AppleScript instead of supporting VBA. However, VBA will be back in the next version of Excel for the Mac. For this reason, 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 bullet-proof for your clients. In Chapter 26, “Customizing the Ribbon to Run Macros,” you learn how to customize the ribbon to allow your clients to enjoy a professional user interface.