Handling Errors Believe it or not, computer programs don't always work perfectly. Every now and then, you might actually write a macro that doesn't quite do what you want. These errors come in several types. -
Syntax errorsThese are mistakes such as using an opening quotation mark and leaving off the closing quotation mark. When you type a statement into a procedure, the Visual Basic Editor checks the statement for syntax errors as soon as you leave the statement. -
Compiler errorsSome mistakes can't be detected on a single-line basis. For example, you might start a For Each loop but forget to put a Next statement at the end. The first time you try to run a procedure, Visual Basic translates that procedure (along with all the other procedures in the module) into internal computer language. Translating to computer language is called compiling, and errors that Visual Basic detects while translating are called compiler errors. Syntax errors and compiler errors are usually easy to find and fix. | Tip | Visual Basic can check for spelling errors when you use variables. From the Visual Basic Tools menu, select the Options command and then select the Require Variable Declaration check box. After you do this, Visual Basic adds the statement Option Explicit to any new module that you create. When Option Explicit appears at the top of a module, Visual Basic displays a compiler error any time you use a variable that you didn't explicitly declare. | -
Logic errorsThe computer can never detect some mistakes. For example, if you mean to change a workbook caption to My Workbook, but you accidentally spell the caption My Werkbook, the computer will never complain. Or if you compare the new values with the wrong copy of the old values, the computer won't find the error for you. You can toggle breakpoints, step through the procedures, and watch values, but you still have to find the problem on your own. -
Run-time errorsSometimes a statement in a procedure works under some conditions but fails under others. For example, you might have a statement that deletes a file on your hard disk. As long as the file exists and can be deleted, the statement works. If, however, the file doesn't exist, Visual Basic doesn't know what else to do but quit with an error message. These errors can't be detected until you run the procedure, so they're called run-time errors. Some run-time errors indicate problems. Other run-time errors are situations that you can anticipate and program Visual Basic to deal with automatically. Visual Basic has tools that can help you deal with any kind of run-time error. Ignore an Error Suppose you want to create a macro that creates a temporary report worksheet. The macro gives the name Report to the report worksheet and replaces any existing Report worksheet in the active workbook. The Chapter08 workbook contains a macro named MakeReport that creates and names the Report worksheet. Here's the original macro: Sub MakeReport() Dim mySheet As Worksheet Set mySheet = Worksheets.Add mySheet.Name = "Report" End Sub The macro adds a worksheet, assigning a reference to the new worksheet to the mySheet variable. It then changes the Name property of the sheet. -
In the Visual Basic Editor, click in the white space at the bottom of the module, and press F5 to display the Macros dialog box. Select the MakeReport macro, and click Edit. Then press F5 to run it. You should see a new worksheet named Report in the active workbook. The macro works fine. Or at least it seems to work fine. But what happens if you run the macro again?  -
Press F5 again to run the macro a second time. Visual Basic displays an error message informing you that you can't rename a sheet to the name of an existing sheet. The solution is simple: all you have to do is delete the old Report sheet before you rename the new one.  -
Click the End button to remove the error message, and then insert these two statements before the one that renames the worksheet: Application.DisplayAlerts = False Worksheets("Report").Delete Turning off alert messages keeps Excel from asking whether you really want to delete the sheet. Press F8 repeatedly to step through the macro. (If the macro steps through the Random function, stop the macro and clear the formulas from cell A3 and A4 of the Test worksheet. Then step through the macro again.) The macro creates a new worksheet, deletes the old Report worksheet, and then renames the new worksheet. Once again, the macro works fine. Or at least it seems to work fine. What happens if there's no Report worksheet in the workbook? -
Switch to Excel, delete the Report worksheet, switch back to the Visual Basic Editor, and press F5 to run the macro. Once again, you get an error message, this time informing you that the subscript is out of range. In other words, there's no item named Report in the Worksheets collection.  The interesting thing about this error is that you really don't care. You were just going to delete the worksheet anyway. If it already doesn't exist, so much the better. -
Click the End button to clear the error message, and insert this statement above the one that deletes the worksheet: On Error Resume Next This statement tells Visual Basic to ignore any run-time errors and simply continue with the next statement. -
Press F5 to test the macro. Test it again now that the Report worksheet exists. Finally the macro seems to work properly. Some errors deserve to be ignored. Ignore an Error Safely When you use an On Error Resume Next statement, Visual Basic ignores all run-time errors until you turn error checking back on or until Visual Basic gets to an End Sub or End Function statement. When you tell Visual Basic to ignore errors, you should be careful that you don't ignore errors you didn't mean to ignore. -
In the MakeReport macro you created in the previous section, remove the quotation marks from around the word "Report" in the statement that gives the worksheet a new name. Removing these quotation marks creates a run-time error. The revised, erroneous statement should now look like this: mySheet.Name = Report | Important | If the statement Option Explicit appears at the top of the module, delete it. | -
Press F5 to test the macro.  The macro appeared to run just fine, but you don't have a Report worksheet when you're done. Visual Basic interpreted the word Report, without the quotation marks, as a new (empty) variable and was unable to assign that empty name to the worksheet. Unfortunately, because you told Visual Basic to ignore errors, it didn't even warn you of a problem. (Of course, if you had inserted Option Explicit at the top of the module, Visual Basic would have complained about using an undefined variable.) The best way to ignore errors for just one or two statements is to put the statements into a Sub procedure of their own. When Visual Basic gets to an End Sub or End Function statement, it cancels the effect of the On Error Resume Next statement. -
Create a new Sub procedure named DeleteSheet. When you're finished, this procedure will quietly delete the Report worksheet if it exists. -
Move the three statements that delete the worksheet into the DeleteSheet macro. The new macro should look like this: Sub DeleteSheet() Application.DisplayAlerts = False On Error Resume Next Worksheets("Report").Delete End Sub The On Error Resume Next statement loses its effect at the End Sub statement, so you just ignore a possible error in the single Delete statement. This is a much safer way to ignore a run-time error. -
In the MakeReport macro, type DeleteSheet where the three statements had been. The revised MakeReport macro (still containing the error) should look like this: Sub MakeReport() Dim mySheet As Worksheet Set mySheet = Worksheets.Add DeleteSheet mySheet.Name = Report End Sub The MakeReport macro no longer contains an On Error Resume Next statement, so Visual Basic should be able to alert you to the error. -
Press F5 to run the macro, and click the End button to close the error box.  -
Replace the quotation marks around the sheet name in the last line of the MakeReport macro, and test the macro when the report file exists as well as when it doesn't. This time, the macro really does work properly. It ignores the error you want to ignore and warns you of other, inadvertent errors. Generalize the DeleteSheet Routine The DeleteSheet macro you created in the previous section quietly deletes the Report worksheet if it happens to exist. Unfortunately, it deletes only the Report worksheet. What if you sometimes need to delete a sheet named Report and other times need to delete a sheet named Analysis? This DeleteSheet macro has too much potential to limit it to deleting only one specific sheet. You can add an argument to generalize the DeleteSheet routine, in much the same way that you added an argument to the Random function earlier in this chapter. -
Type SheetName as an argument name between the parentheses after the DeleteSheet macro name. -
Replace "Report" with SheetName in the body of the DeleteSheet macro. SheetName shouldn't have quotation marks around it. -
Type "Report" after DeleteSheet in the MakeReport macro. Here's what the two revised macros should look like: Sub MakeReport() Dim mySheet As Worksheet Set mySheet = Worksheets.Add DeleteSheet "Report" mySheet.Name = "Report" End Sub Sub DeleteSheet(SheetName) Application.DisplayAlerts = False On Error Resume Next Worksheets(SheetName).Delete End Sub The DeleteSheet macro now knows absolutely nothing about the name of the sheet it will delete. It will simply delete whatever sheet it's given, without asking any questions and without complaining if it discovers its services aren't really needed. -
Press F5 to test the MakeReport macro. -
Create a new macro named MakeAnalysis. Make it an exact copy of the MakeReport macro, except have it create a sheet named Analysis. The macro should look like this: Sub MakeAnalysis() Dim mySheet As Worksheet Set mySheet = Worksheets.Add DeleteSheet "Analysis" mySheet.Name = "Analysis" End Sub -
Test the MakeAnalysis macro.  The DeleteSheet macro now not only avoids the inconveniences associated with deleting a worksheet but is also a generalized tool-an enhancement to the built-in capabilities of Excel-that you can use from any macro you want. Check for an Error When you use the On Error Resume Next statement in a macro, Visual Basic allows you to do more than merely ignore the error. Visual Basic contains a special debugging object named Err. The Err object has properties that you can check to see whether an error has occurred and, if so, what the error is. Suppose that you want to create a Report worksheet but you don't want to delete any existing Report sheets. Instead, you want to add a suffix to the worksheet name, much as Excel does when you add a new worksheet. The Chapter08 workbook includes a macro named MakeNextReport. This macro creates a sheet named Report1. Here's the original MakeNextReport macro: Sub MakeNextReport() Dim mySheet As Worksheet Dim myBase As String Dim mySuffix As Integer Set mySheet = Worksheets.Add myBase = "Report" mySuffix = 1 On Error Resume Next mySheet.Name = myBase & mySuffix End Sub This macro creates a new worksheet and then tries to name it using Report as the base name and 1 as the suffix. The On Error Resume Next statement tells Visual Basic not to stop if Excel is unable to rename the sheet. -
Go to the MakeNextReport macro, and then press F8 repeatedly to watch the macro work. The new worksheet should rename properly.  -
Step through the macro a second time. The second time, the macro quietly fails, leaving the new sheet with the wrong name.  It would be nice if this macro were smart enough to increment the suffix if the initial rename fails. While a macro is ignoring errors, the Number property of the Err object is the key to knowing whether a statement failed. -
Add the following statements immediately before the End Sub statement: If Err.Number <> 0 Then mySuffix = mySuffix + 1 mySheet.Name = myBase & mySuffix End If The If statement checks to see whether the error number is something other than 0. A nonzero error number indicates that a statement failed. If a statement failed-that is, if the statement that attempted to rename the sheet failed-the macro increments the suffix and tries again. -
Step through the macro. The code detects the failed rename and tries again, successfully renaming the sheet to Report2. If you run the macro again, however, it silently fails.  It would be nice if this macro were smart enough to keep incrementing the suffix until it finds one that works. That sounds like a job for a loop structure, and since you can't know when the loop begins or how many times you'll have to repeat the loop, you should use a Do loop. 177 -
Replace the word If with Do Until, remove the word Then at the end of the statement, and change the not-equal sign (<>) to an equal sign (=). Then change End If to Loop. The last few lines of the macro should look like this: On Error Resume Nex tmySheet.Name = myBase & mySuffix Do Until Err.Number = 0 mySuffix = mySuffix + 1 mySheet.Name = myBase & mySuffix Loop The loop checks to see whether the rename occurred successfully. If not, the loop increments the suffix, tries the rename again, and checks again until there's no error-at least, that's what you want it to do. -
Press F8 repeatedly to step through the macro. The first time the macro tries to name the report sheet, it fails because Report1 already exists. As a result, the macro proceeds into the loop. At the end of the loop, the macro tries again to rename the sheet but fails again because Report2 already exists, so the macro reenters the loop a second time. At the end of the loop, the macro tries a third time to rename the sheet. This time the sheet renames properly. -
Keep stepping through the macro. Something's wrong. The macro goes into the loop again, renaming the sheet as Report4 and then as Report5. This renaming could go on forever.  The macro doesn't realize that the error is over. The value of Err.Number didn't automatically change back to 0 just because the macro successfully renamed the worksheet. You need to tell the macro that the error is no longer relevant. Reset  -
Click the Reset button to stop the macro. Then, on the line immediately following the Do statement, type the statement Err.Clear. Clear is the name of a method for the Err object. Clear resets the error number to 0 and makes Visual Basic forget that an error ever occurred. | Important | Some macro statements change the Err.Number value back to 0 when they complete successfully. Others don't. To be safe, you should clear the Err object before a statement that you want to check and then inspect the value of Err.Number immediately after that statement executes. | -
Press F5 to test the macro. Test it again. And again. The macro is now able to create a new report sheet, incrementing as much as necessary-but no more!  Checking the value of Err.Number is meaningful only after you use an On Error Resume Next statement. Errors that occur above the On Error Resume Next statement cause Visual Basic to halt the macro with an error message box. Looking at the properties of the Err object is a good way to gain control over the way your macro handles errors. Trap an Error So far, you've seen three ways to handle a run-time error: you can let Visual Basic display a standard error dialog box, you can ignore the error altogether, or you can check for a nonzero error number after each statement. Having Visual Basic display an error message might not be a bad alternative if you're writing macros for yourself, but if you want to give a macro to someone else, you'll probably want more control over what the error message says. You should ignore errors only in special circumstances. Checking for a nonzero error value after every statement, however, can make your macros hard to read. Fortunately, Visual Basic can monitor the error value for you in a process called trapping an error. Suppose, for example, that you had a macro that opens, prints, and closes several workbooks. It's possible that one of the workbooks might be missing when the macro runs. The Chapter08 workbook contains a macro named CheckFiles that opens and closes several of the practice workbooks that came with this book. (In the interest of conserving trees, the macro doesn't actually print the workbooks.) One of the workbook file names has been misspelled. Here's the original macro: Sub CheckFiles() Workbooks.Open "Graphics" ActiveWorkbook.Close Workbooks.Open "Ranges" ActiveWorkbook.Close Workbooks.Open "Bad File Name" ActiveWorkbook.Close Workbooks.Open "Budget" ActiveWorkbook.Close End Sub Naturally, you can't tell which of the files won't be found until the macro actually runs. If you run this macro, you'll see the standard Visual Basic error message. If necessary, click the End button to close the dialog box.  Here are the steps you follow to add special code that Visual Basic will run whenever an error occurs. -
At the end of the macro, type the statement ErrorHandler: just before the End Sub statement. The statement ErrorHandler: is called a label. A label consists of a single word followed by a colon. (You can indent the label if you want, but you might prefer to keep it lined up with the Sub and End Sub statements because it behaves like an appendix to the macro.) A label must always end with a colon. | Tip | You can use any name you want for a label within the macro. You might want to always use the same name, such as ErrorHandler, as the error label in all your macros. That makes it easy to copy error-handling code from one macro to another. | -
After the error-handler label, type the statement MsgBox Err.Number. The statements below the label are the ones that the macro executes when it detects an error. These statements are called an error handler. The simplest error handler is a message box that displays the number of the error. -
Immediately before the error-handler label, type the statement Exit Sub. You don't want the statements in the error handler to execute if the macro completes normally. If the macro gets to the Exit Sub statement, no error was detected. -
At the top of the macro, just under the Sub statement, type the statement On Error GoTo ErrorHandler. This statement tells Visual Basic that if it sees a run-time error, it should drop whatever it's doing and jump immediately to the label you specify. You don't put a colon after the label name here. You use a colon only when you create the actual label. -
Press F5 to test the macro. Visual Basic should display a simple message box showing only the message number. Click OK to close the message box.  You can make the message more elaborate. The Err object has a Description property that gives a longer text description of the error. That description is often a useful addition to an error message box. You can even add to the description with text of your own. -
Delete the statement MsgBox Err.Number, and replace it with this statement: MsgBox "Please notify Reed Jacobson of error " _ & Err.Number & vbCrLf & vbCrLf _ & Err.Description You can string many pieces of text together to form an error message. Just put an ampersand between each piece. The word vbCrLf is a built-in Visual Basic constant that means 'Carriage Return/Line Feed.' Carriage Return/Line Feed is an archaic computer term for a new line and was often abbreviated as CRLF. You can put vbCrLf into a string anytime you want to force the message to go to a new line. (When you create your own macros, please substitute your name in the error message. Thanks.) -
Press F5 to run the macro and see the more elaborate error message.  The finished macro should look like this: Sub CheckFiles() On Error GoTo ErrorHandler Workbooks.Open "Graphics" ActiveWorkbook.Close Workbooks.Open "Ranges" ActiveWorkbook.Close Workbooks.Open "Bad File Name" ActiveWorkbook.Close Workbooks.Open "Budget" ActiveWorkbook.Close Exit Sub ErrorHandler : MsgBox "Please notify Reed Jacobson of error " _ & Err.Number & vbCrLf & vbCrLf _ & Err.Description End Sub If you're creating an application for someone else to use and you don't want that person ever to see the Visual Basic default error dialog box, you should always include an error handler in every macro that the user launches directly. If you have some statements for which the error should be handled differently-either ignored or checked on a statement- by-statement basis-put those statements into a separate procedure and use an On Error Resume Next statement within that procedure. Visual Basic automatically restores the error handler when the procedure ends. Errors are a frustrating but inevitable part of life when working with computers-that's especially the case when the human error factor compounds the likelihood of other types of errors. Of course, not all errors are equal. Some are serious, some are trivial, and some are even useful. It's fortunate that Visual Basic provides flexible tools for dealing with all kinds of errors. |
