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.
These are mistakes such as using an opening quotation mark and forgetting to type the closing quotation mark. When you type a statement in a procedure, the Visual Basic editor checks the statement for syntax errors as soon as you leave the statement.
Some 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.
Visual Basic can check for spelling errors when you use variables. On the Visual Basic Tools menu, click the Options command, select the Require Variable Declaration check box, and then click OK. 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.
The 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.
Sometimes 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. However, if 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.
Suppose you want to create a macro that creates a temporary report worksheet. The macro should add a new worksheet named Report to the active workbook, replacing any existing worksheet named Report. The Structure text file contains the MakeReport macro, which 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.
Copy the MakeReport macro from the Structure text file, and paste it into a module in the Chapter08 workbook.
Put the insertion point anywhere in the MakeReport macro, and then press F5 to run the macro.
You should see a new worksheet named Report in the active workbook in Excel. 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
Setting DisplayAlerts to False turns off alert messages so that Excel doesn’t ask 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 any formulas in the workbook that use the Random function (most likely cells A3 and A4 of the TestFunction worksheet). If the macro already deleted the Report worksheet, continue with Step 7 below.
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. But 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 then 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. Then test it again to see what happens when the Report worksheet exists.
Finally the macro seems to work properly. Some errors deserve to be ignored.
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 that 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
If the statement Option Explicit appears at the top of the module, delete it.
Press F5 to test the macro.
The macro appears to run just fine, but you don’t have a Report worksheet when it’s finished. 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 the 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 macro named DeleteSheet.
Move the three statements that delete the worksheet into the DeleteSheet macro.
The new procedure 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 ignore a possible error only in the single Delete statement.
In general, you can think of a macro as anything you can run from the Macros dialog box and use the more general term procedure for anything you have to run from code.
You never want to run the DeleteSheet procedure from the Macros dialog box. Rather, you should always call it from within another procedure-much like a custom function. To keep it out of the Macros dialog box, you can make it private.
Insert the word Private in front of Sub DeleteSheet(). Then move the insertion point outside of any macro and press F5 to verify that it no longer appears in the list.
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. The DeleteSheet procedure is now a subroutine.
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 worksheet exists as well as when it doesn’t.
This time, the macro really does work properly. It ignores the error you want to ignore while still warning you of other, inadvertent errors.
The DeleteSheet procedure that you created in the previous section quietly deletes the Report worksheet if it happens to exist. Unfortunately, it deletes only the Report work-sheet. What if you sometimes need to delete a sheet named Report and other times need to delete a sheet named Analysis? This DeleteSheet procedure has too much potential to limit it to deleting only one specific sheet. You can add an argument to generalize the DeleteSheet procedure, 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 procedure name.
Replace “Report” with SheetName in the body of the DeleteSheet procedure. (SheetName shouldn’t have quotation marks around it.)
In the MakeReport macro, type "Report" after DeleteSheet.
Here’s what the two revised procedures should look like:
Sub MakeReport() Dim mySheet As Worksheet Set mySheet = Worksheets.Add DeleteSheet "Report" mySheet.Name = "Report" End Sub Private Sub DeleteSheet(SheetName) Application.DisplayAlerts = False On Error Resume Next Worksheets(SheetName).Delete End Sub
The DeleteSheet procedure 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 procedure 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.
When you add arguments to a Sub procedure, it no longer appears in the Run Macro dialog box, because there is no way to pass arguments to the procedure from the dialog box.
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 Structure text file 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.
Copy the MakeNextReport macro from the Structure text file into a module in the Chapter08 workbook. Then press F8 repeatedly to watch the macro work.
The macro should rename the new worksheet 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. The Err object-which watches errors even though the macro is ignoring them-has a Number property that 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.
Press F5 to run the macro again.
It silently fails because the error code executes only once.
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.
|See Also|| |
For more information about Do loops, see the section titled “Loop Indefinitely by Using a Do Loop” in Chapter 7, “Control Visual Basic.”
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 Next mySheet.Name = myBase & mySuffix Do Until Err.Number = 0 mySuffix = mySuffix + 1 mySheet.Name = myBase & mySuffix Loop
The Do loop checks to see whether the rename occurred successfully. If it didn’t, the loop increments the suffix, tries the rename again, and checks again until there is no error-at least, that’s what you want it to do.
Be sure to step through the macro, because the macro currently has an infinite loop. If you do run the macro, press Esc to stop the loop. You may need to press Esc more than once.
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 it renames the sheet properly.
Keep stepping through the macro.
Something is 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.
Click the Reset button to stop the macro.
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.
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 immediately before the critical statement and then inspect the value of Err.Number right 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.
So far, you’ve seen three ways to handle a run-time error: 1) you can let Visual Basic display a standard error dialog box, 2) you can ignore the error altogether, or 3) you can check for a nonzero error number after each statement.
The first option-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. The second option-ignoring the error-is something you should do only in special circumstances. The third option-checking for a nonzero error value after every statement-can make your macros hard to read. Fortunately, there is a fourth option: Visual Basic can monitor the error value for you by trapping the 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 Structure text file contains a CheckFiles macro 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.
In Excel, click the Microsoft Office Button, and click Open. Change to the folder containing the practice files for this book, and then click Cancel.
This changes the current directory to the appropriate folder.
Copy the CheckFiles macro from the Structure text file into a module in the Chapter08 workbook, and then press F5 to run the CheckFiles macro.
You’ll see the standard Visual Basic error message.
Click the End button to close the message box.
You still need to add special code that Visual Basic will run whenever an error occurs.
Just before the End Sub statement at the end of the macro, type the statement ErrorHandler:.
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.
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.
Insert a line after the error-handler label, and 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.
Be sure to use the same label name in the On Error statement that you used for the error handler.
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.
The 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 Err.Number End Sub
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.
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.
CLOSE the Chapter08.xlsm workbook.