Handling Errors | Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))

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 Errors

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.

Compiler Errors

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.

Tip

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.

Logic Errors

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.

Run-Time Errors

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.

Ignore an 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.

Troubleshooting

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.

Ignore an Error Safely by Using a Subroutine

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 
```
Troubleshooting
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.

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

Add Arguments to Generalize a Subroutine

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.

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

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

Loop Until an Error Goes Away

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.

Trap an Error

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.

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.

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.

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