Errors are bound to happen. You can test and retest your code, but 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. Always be thinking of what unexpected things could someday happen that would make your code not work.
If VBA encounters an error and you have no error-checking code in place, the program stops and you (or your client) will be presented with the “Continue, End, Debug, Help” error message, as shown in Figure 25.1.
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. You can hover your cursor over any variable to see the current value of the variable. This provides a lot of information about what could have caused the error (see Figure 25.2).
Excel is notorious for returning errors that are not very meaningful. For example, dozens of situations can cause a 1004 error. Being able to see the offending line highlighted in yellow, plus being able to examine the current value of any variables, will help you to 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.
Caution
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 25.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. However, immediately after you click OK, you are returned to the Excel user interface instead of being left in the VB Editor. For as often as this message happens, it would be much more convenient if you could be returned to the VB Editor after clicking OK.
The line highlighted as the error after you click Debug can be misleading in one situation. Suppose that you call a macro. This macro then 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.
After an error message box is shown as in Figure 25.5, click the Debug button.
You will see that the error allegedly occurred on a line that shows a userform, as shown in Figure 25.6. Because you’ve read this chapter, you will know that this is not the line in error.
Press F8 to execute the
Showmethod. Instead of getting an error, you are taken into theUserform_Initializeprocedure.Keep pressing F8 until you get the error message again. You must stay alert. As soon as you encounter the error, the error message box is displayed. Click Debug and you are returned to the
userform.Showline. 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.
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, I get incredibly bored pressing F8 a million times and forget to pay attention to which line caused the error. As soon as the error happens, I am thrown back to the Debug message, which returns me 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.
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:
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.After the
Exit Subline, add a label. A label is a name followed by a colon. For example, you might create a label called MyErrorHandler:.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. This is very nonintuitive and tends to confuse people. The code to cancel any special error handling is On Error Goto 0. There is no label named 0. 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. Do you see why it is important to cancel the error handling? In the following code, we’ve written a special error handler to handle the necessary action if the file has been moved or is missing. We definitely do not want this error handler invoked for another error later in the macro, such as a division by zero:
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, VbMsgBoxStyle:=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 don't 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 SubSome developers like to direct any error to a generic error handler. They 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:
On Error GoTo HandleAny
Sheets(9).Select
Exit Sub
HandleAny:
Msg = "We encountered " & Err.Number & " - " & Err.Description
MsgBox Msg
Exit SubSome errors can simply be ignored. Suppose, for instance, that 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. Is this something you need to worry about? You are trying to delete the file. Who cares if someone already deleted it before running the macro? In this case, you should tell Excel to simply 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 SubYou need to be careful with On Error Resume Next. It can be used selectively in situations where you know that the error can be ignored. You should immediately return error checking to normal after the line that might cause an error with On Error GoTo 0.
If you attempt to have On Error Resume Next skip an error that cannot be skipped, the macro immediately steps out of the current macro. If you have a situation where MacroA calls MacroB and MacroB encounters a nonskippable error, the program jumps out of MacroB and continues with the next line in MacroA. This is rarely a good thing.
Some messages appear even if you have set Excel to ignore errors. Try to delete a worksheet using code and you will still get the message “Data may exist in the sheet(s) selected for deletion.” To permanently delete the data, click Delete. This is annoying. We don’t want our clients to have to answer this warning. It turns out that 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 SubBecause programmers hate errors, this concept might seem counterintuitive, but errors are not always bad. Sometimes it is faster to simply encounter an error.
Let’s say 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 would loop through 128 times before deciding that the data worksheet is missing.
The alternative is to simply 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 not Err.Number = 0 then Sheets.Add.Name = "Data"
On Error GoTo 0This code runs much faster. Errors usually make me cringe; in this case, however, and in many other cases, they are perfectly acceptable.
You might be 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 either instance, you might find yourself remotely trying to debug code while you are on the telephone with the client.
It is important to train clients about the difference between an error and a simple MsgBox. A MsgBox is a planned message. It still appears out of the blue with a beep. Teach your users that error messages are bad but not everything that pops up is an error message. I had a client keep 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. You can then get the error number and the description, and ask them to click Debug and tell you the module name, the 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’ll be able to figure out 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.
When you have just written code and are running it 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 with an error. This seems perplexing; the code has been working for months. Why did it suddenly stop working today?
It is easy to blame this on the client; but 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.
You’ve set up an application for the client. You have provided a Menu worksheet where you store some settings. One day, the client reports the error message shown in Figure 25.8.
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:
Sub GetSettings()
ThisWorkbook.Worksheets("Menu").Select
x = Range("A1").Value
End SubThis is a classic situation where you can’t believe the client would do something so crazy. After you’ve 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 SubYou 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:
Sub SetReportInItalics()
TotalRow = Cells(Rows.Count,1).End(xlUp).Row
FinalRow = TotalRow - 1
Range("A1:A" & FinalRow).Font.Italic = True
End SubThen one day, the client calls with the error message shown in Figure 25.9.
Upon examination of the code, you discover that something bizarre went wrong when the text file was FTP’ed 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. When we assumed the last detail row was TotalRow - 1, this set our code up to attempt to format Row 0, which clearly does not exist.
After this episode, you will 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 SubIt is possible to lock a VBA project so that it cannot be viewed. I do not recommend this at all. 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. So, understand that office VBA code is not secure and get over it.
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. A lot of people are still using Excel 97. As your application is given to more employees in a company, you invariably find one employee with Excel 97. Of course, that user comes up with some runtime error. However, if you’ve locked the project in Excel 2002 or newer, you cannot unlock the project in Excel 97, and therefore cannot debug in Excel 97.
Bottom line: Locking code causes more trouble than it is worth.
Note
If you are using a combination of Excel 2003 and Excel 2007, the passwords transfer easily back and forth. This holds true even if the file is saved as an XLSM file and opened in Excel 2003 using the file converter. You can change code in Excel 2003, save the file, and successfully round-trip back to Excel 2007.
Microsoft does improve VBA in every version of Excel. Pivot table creation was drastically improved between Excel 97 and Excel 2000 and new features were added in Excel 2007. 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. If you write code in Excel 2007 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. Consider this application with 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 as soon as 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. A lot of clients are hanging on to Excel 97, but it is frustrating when you find someone who doesn’t 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. It is close, but annoyingly different. Certainly, anything that you do with the Windows API is not going to work on a Mac.
This chapter discussed how to make your code more bullet-proof for your clients. In Chapter 26, “Customizing the Ribbon to Run Macros,” you will learn how to customize the Ribbon to allow your clients to enjoy a professional user interface.