Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step Series)

Overview

Teach yourself how to use Microsoft® Visual Basic® for Applications (VBA) to take command of Microsoft Excel Version 2002. Choose your own best starting point in this self-paced guide to learn how to automate spreadsheets, write your own functions and procedures, customize menus and toolbars, and more. Easy-to-follow lessons with real-world scenarios and examples show you exactly how to maximize the built-in programming power in Microsoft Excel 2002. Numerous screenshots and a CD full of practice files help you ...

See more details below
Available through our Marketplace sellers.
Other sellers (Paperback)
  • All (19) from $1.99   
  • New (2) from $13.96   
  • Used (17) from $1.99   
Close
Sort by
Page 1 of 1
Showing All
Note: Marketplace items are not eligible for any BN.com coupons and promotions
$13.96
Seller since 2014

Feedback rating:

(20)

Condition:

New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

New
INCLUDES OPENED CD!!! SOLID BOOK AND A GREAT VALUE!!! NO REMAINDER MARK!!! Ships Super Fast with FREE TRACKING???..:) Professionally Packaged in a Weather Resistant Poly Bubble ... Mailer. This Book appears Brand New, selling Like New as the Cover shows signs of minimal wear along the edges and corners and the CD has been opened but is included. Pages are Crisp and Clean and the Binding is Tight. Pics Upon Request Read more Show Less

Ships from: Hanover, PA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
$59.99
Seller since 2008

Feedback rating:

(172)

Condition: New
0735613591 BRAND NEW NEVER USED IN STOCK 125,000+ HAPPY CUSTOMERS SHIP EVERY DAY WITH FREE TRACKING NUMBER

Ships from: fallbrook, CA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
Page 1 of 1
Showing All
Close
Sort by
Sending request ...

Overview

Teach yourself how to use Microsoft® Visual Basic® for Applications (VBA) to take command of Microsoft Excel Version 2002. Choose your own best starting point in this self-paced guide to learn how to automate spreadsheets, write your own functions and procedures, customize menus and toolbars, and more. Easy-to-follow lessons with real-world scenarios and examples show you exactly how to maximize the built-in programming power in Microsoft Excel 2002. Numerous screenshots and a CD full of practice files help you master step-by-step programming procedures. Find out how to create custom solutions with Microsoft Excel and this book—then keep it nearby as an ongoing desktop reference to VBA functions and features. Learn at your own pace how to:

  • Use macros to automate simple and complex tasks
  • Manipulate workbooks and worksheets
  • Explore range objects
  • Work with graphical objects
  • Build and manipulate PivotTable® objects
  • Create loops and conditional statements with Visual Basic
  • Use dialog box controls on worksheets
  • Create and use custom functions and handle macro errors
  • Create custom toolbar buttons, menu commands, command buttons, event handlers, and dialog boxes

A Note Regarding the CD or DVD

The print version of this book ships with a CD or DVD. For those customers purchasing one of the digital formats in which this book is available, we are pleased to offer the CD/DVD content as a free download via O'Reilly Media's Digital Distribution services. To download this content, please visit O'Reilly's web site, search for the title of this book to find its catalog page, and click on the link below the cover image (Examples, Companion Content, or Practice Files). Note that while we provide as much of the media content as we are able via free download, we are sometimes limited by licensing restrictions. Please direct any questions or concerns to booktech@oreilly.com.

Read More Show Less

Product Details

  • ISBN-13: 9780735613591
  • Publisher: Microsoft Press
  • Publication date: 9/28/2001
  • Series: Microsoft Step by Step Series
  • Edition description: BK&CD-ROM
  • Pages: 336
  • Product dimensions: 7.34 (w) x 9.28 (h) x 0.98 (d)

Meet the Author

Reed Jacobson is a senior architect in Hitachi Consulting’s Business Intelligence practice. He has authored several training courses and books, including Microsoft SQL Server 2000 Analysis Services Step by Step.

Read More Show Less

Table of Contents

Introduction
Getting Help
Using the Book's CD-ROM
Checking Your Configuration
Conventions and Features
Pt. 1 Automating Everyday Tasks
1 Make a Macro Do Simple Tasks 1
2 Make a Macro Do Complex Tasks 24
Pt. 2 Exploring Objects
3 Explore Workbooks and Worksheets 50
4 Explore Graphical Objects 68
5 Explore Range Objects 88
6 Explore PivotTable Objects 120
Pt. 3 Exploring Visual Basic
7 Control Visual Basic 140
8 Extend Excel and Visual Basic 162
Pt. 4 Making Macros Easy to Use
9 Launch Macros with Events 182
10 Use Dialog Box Controls on a Worksheet 208
11 Create a Custom Form 224
12 Create an Enterprise Information System 254
Index 295
Read More Show Less

First Chapter

Chapter 8.
Extend Excel and Visual Basic
    • Getting Started
  • Creating Custom Functions
  • Handling Errors

Chapter 8   Extend Excel and Visual Basic

After completing this chapter, you will be able to:

  • Create and use custom functions.
  • Handle errors that occur while a macro is running.

A bacteria cell doesn't have a nucleus. A cell without a nucleus is called a prokaryote. Prokaryotes are very important. The first known fossils, dating from three and a half billion years ago, are all prokaryotes. Prokaryotes are also very small. In 1999, Heide Schulz discovered the prokaryote species Thiomargarita namibiensis whose individual organisms grow to be as large as 3/4 millimeter—about the size of the head of a fruit-fly. This is astonishingly large for a bacteria but doesn't seem very large compared to, say, a puppy.

Probably the largest single-celled organism is Caulerpa taxifolia. It is a type of seaweed—algae, really— that has been an uncontrollable weed in the Mediterranean Sea since 1984 and was discovered off the coast near San Diego, California, in June 2000. A single Caulerpa plant—consisting of a single cell— can grow to just under a meter in length. In the grand scheme of things, a single-celled Caulerpa is not very big, but it is orders of magnitude larger than a single-celled Thiomargarita. The difference is due to structure. The Caulerpa has a nucleus. It has appendages and vacuoles and rhizomes. Structure enables functional size.

Recorded macros are like prokaryotes. The macro recorder puts everything you do into a single, unstructured procedure. And, like prokaryotes, single-procedure macros should be small. To get a large, sophisticated application to work properly, you must give it an internal structure—you must break it up into smaller procedures. And just as large, complex organisms need an immune system to deal with diseases, sophisticated applications need a mechanism for dealing with error conditions. In this chapter, you'll learn how to create custom functions, use arguments in procedures, and handle errors—tools you'll need to make more powerful applications.

This chapter uses the practice file Function.xls that you installed from the book's CD-ROM. For details about installing the practice files, see "Using the Book's CD-ROM" at the beginning of this book.

Getting Started

  1. Start Microsoft Excel, and change to the folder containing the practice files for this book.
  2. Open the Function workbook, and save a copy as Chapter08.

Creating Custom Functions

Once you assign a value to a variable, you can use that value in any expression. For example, after you assign the number 25 to the variable myAge, the value of the conditional expression myAge > 20 would be True because 25 is greater than 20. You use the variable as if it were the value that it contains.

A function is like a variable, except that a function is smarter. A function is a variable that figures out its own value whenever you use it. For example, Microsoft Visual Basic has a function named Time. When you use the conditional expression Time > #8:00 PM#, the Time function checks the time on your computer's clock each time you use the expression.

Visual Basic has many built-in functions. Excel also has many built-in functions. Those functions are useful, but they aren't customizable. Even if you find a Visual Basic function that's "this close" to what you need, you can't worm your way into the innards of Visual Basic to change the way it works. You can, however, create a function of your own. Because your function can take advantage of any of the Excel or Visual Basic built-in functions, and because you can customize your function however you want, you get the same benefit you would get if you could tweak the built-in functions directly.

Use a Custom Function from a Worksheet

Both Excel and Visual Basic have functions that return a random number between 0 and 1. The Excel function is named RAND(), and the Visual Basic function is named Rnd. You can use the Excel function in a worksheet cell, but you can use the Visual Basic function only in a macro.

You can't customize the Visual Basic Rnd function or the Excel RAND()function, but you can create a custom random-number function—let's call it Random—that you can use from Excel. Why would you want to create your own random-number function when you could use Excel's built-in one for free? Because you want your Random function to behave just a little differently than Excel's. Once you create your own function, you can make it do whatever you want.

  1. Enter the formula =Random() into cell A3 on the TestFunction sheet.
  2. Excel displays the #NAME? error value because the Random function doesn't exist yet.

    (Image Unavailable)

  3. Click the Run Macro button, type Random in the Macro Name box, and then click Create.
  4. Double-click the word Sub at the beginning of the macro, and replace it with Function.
  5. The End Sub statement changes to End Function. You've now created a function. Next you need to tell Excel what to use as the value of the function.

    (Image Unavailable)

  6. Type the statement Random = Rnd as the body of the function. The revised function should look like this:
  7. Function Random()
    Random = RndEnd
    Function

    The way you tell a function what value to return is by assigning a value to the name of the function, as if the function name were a variable. This function simply takes the value of the Visual Basic Rnd function and assigns it to the Random function.

  8. Switch back to Excel, select cell A3, and then click the Insert Function button next to the formula bar. Excel displays the Function Arguments window, which explains that the Random function doesn't take any arguments. Click OK to enter the random number into cell A3.
  9. (Image Unavailable)

That's all there is to creating a simple worksheet function. In the Visual Basic Editor, you replace the word Sub with Function, and then somewhere in the function, you assign a value to the function name. In Excel, you put the function name into a formula, followed by parentheses.

Add Arguments to a Custom Function

Suppose that you want random whole numbers equal to 100 plus or minus 25. Or that you want random whole numbers equal to 1000 plus or minus 100. The Excel RAND()function can't give you that kind of random number. Neither, for that matter, can yours, but because yours is a custom function, you can add capabilities to it by adding arguments.

To specify random-number ranges such as those just mentioned, you need three arguments: one to specify the midpoint, one to specify the plus or minus range, and one to specify whether or not to round the final number. You can add those arguments to your function.

  1. In the Visual Basic Editor, type Midpoint, Range, Round between the parentheses after the name of the function. The statement that contains the function name and its arguments is called the function declaration statement. In this statement, you declare the name of the function and also the names of all the arguments. The revised function declaration statement should look like this:
  2. Function Random(Midpoint, Range, Round)
    

    These three words are arguments to the function. You can use them inside the function as variables that have been prefilled with values.

  3. Change the statement that assigns a value to the function name to this:
  4. Random = Rnd * (Range * 2) + (Midpoint – Range)
    

    The Rnd function returns a random number between 0 and 1. If Range is equal to 25, that means you want numbers from 25 below the midpoint to 25 above the midpoint, for a total range of 50. Multiplying Rnd by Range * 2 would then give you a random number between 0 and 50. If the target midpoint is 100, you need to add 75 (that is, 100 – 25), to the random number. That's what this statement does.

  5. Insert these three statements to round the number if requested:
  6. If Round Then
    Random = CLng(Random)
    End If

    In Visual Basic, a Long is a whole number that can include large numbers. The Visual Basic function CLng converts a number to a Long, rounding it along the way. You round the random number only if the value of the Round argument is True. (Because the value of the Round argument already equals True or False, you don't need to compare it to anything to get a conditional expression.) The complete function should look like this:

    Function Random(Midpoint, Range, Round)
    Random = Rnd * (Range * 2) + (Midpoint – Range)
    If Round Then
    Random = CLng(Random)
    End If
    End Function

    TIP:
    To see other functions that convert between data types, click CLng and press F1.
  7. In Excel, enter 100 into cell B3, 25 into cell C3, and TRUE into cell D3. You'll use these values for the Midpoint, Range, and Round arguments of your function.
  8. Select cell A3, and click the Insert Function button next to the formula bar.
  9. The Function Arguments window appears, showing you the three new arguments of your function.

    (Image Unavailable)

  10. Click in the Midpoint box, and then click in cell B3. Click in the Range box, and then click in cell C3. Click in the Round box, and then click in cell D3. Then click OK.
  11. (Image Unavailable)

    After adjusting the formula, cell A3 contains a random number between 75 and 125. You use arguments to pass values to a function.

  12. Change cell B3 to 1000 and cell C3 to 100. The value of cell A3 changes to a random number between 900 and 1100. Whenever you change the value of a cell that the function refers to, the function calculates a new answer. Adding arguments is a way to make functions more flexible.
  13. (Image Unavailable)

Make a Function Volatile

Most functions recalculate only when the value of a cell that feeds into the function changes. Other functions (such as Excel's RAND()function), called volatile functions, recalculate whenever any cell on the worksheet changes or whenever you press F9. You can make your function volatile; it will then calculate a new random number whenever you press F9.

  1. In Excel, press F9 repeatedly to see that the random number in cell A3 doesn't change.
  2. In the Visual Basic Editor, insert this statement after the statement containing the name of the function:
  3. Application.Volatile True
    
  4. Switch back to Excel, and press F9.
  5. The random number in cell A3 changes. Press F9 several times to verify that the function generates random numbers in the appropriate range.

Most of the time, you don't want custom functions to be volatile. You want the function to recalculate only when a value that feeds into it changes. For those few cases in which you do want the formula to recalculate, just use the Application object's Volatile method with True as an argument.

Make Arguments Optional

The only problem with your new enhanced Random function is that it's now more complicated to use in those simple cases in which you don't need the new arguments. If you put =Random() into a cell, omitting the arguments, Excel displays the #VALUE! error value. To avoid this error, you can tell Visual Basic that you want the arguments to be optional. Then you specify default values to use if the argument isn't supplied.

  1. In the Visual Basic Editor, type the word Optional in front of each of the three argument names. The revised statement should look like this:
  2. Function Random(Optional Midpoint, _
    Optional Range, Optional Round)

    You don't have to make all the arguments optional, but once you make one argument optional, all the arguments that follow it must be optional as well. In other words, you place optional arguments at the end of the argument list.

  3. Type = 0.5 after the word Midpoint, = 0.5 after the word Range, and = False after the word Round. Break the statement into two lines after the first comma. The resulting statement should look like this:
  4. Function Random(Optional Midpoint = 0.5, _
    Optional Range = 0.5, Optional Round = False)

    You can specify a default value for any optional argument. You assign the default value to the argument name in the same way you would assign a value to a variable—by using a simple equal sign.

  5. In Excel, enter =Random() into cell A4. A random number between 0 and 1 appears.
  6. Delete the formulas in cells A3 and A4 so that you can step through other macros later in the chapter without stepping through the custom function.

Optional arguments allow you to add powerful features to a function while keeping it easy to use in cases in which you don't need the extra features. To make an argument optional, add Optional before the argument name. To add a default value for an optional argument, assign the value to the argument name the same way you would if it were a variable.

Use a Custom Function from a Macro

You can use a custom function from a macro just as easily as you can use it from a worksheet cell.

  1. In the Visual Basic Editor, type Sub TestRandom at the bottom of the module, and then press the Enter key to start creating a macro.
  2. Type MsgBox and a space.
  3. Visual Basic shows the Quick Info box with the arguments for MsgBox.

  4. Press Ctrl+Spacebar to show the list of global methods and properties, and then press R to scroll down to the words that begin with an R.
  5. (Image Unavailable)

    Your Random function is automatically included in the list. Your function has the icon for a method next to it. Excel methods are simply functions built into Excel. You create new global methods simply by writing new functions.

  6. Press the Tab key to insert the function name into the statement, and then type an opening parenthesis to begin the argument list. Visual Basic displays the Quick Info box with the arguments for your custom function. The Quick Info box even shows the default values for the optional arguments.
  7. (Image Unavailable)

  8. Type 200, 5, True as the list of arguments, and then type a closing parenthesis.
  9. Press F5 to run the macro. Click OK when your random number appears.
  10. (Image Unavailable)

    A function is a procedure like a Sub procedure, except that it returns a value that you can use either in a cell in Excel or from a macro.


Important:
A function used in a worksheet cell can include only those actions that can be executed while Excel is recalculating a worksheet. (Remember that some cells might even recalculate more than once.) Actions such as opening files or displaying message boxes can be included in functions that are called from macros, but if you include them in a function that's called from a worksheet, the function simply returns the #VALUE! error value.

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

  1. 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.
  2. 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?

    (Image Unavailable)

  3. Press F5 again to run the macro a second time.
  4. 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.

    (Image Unavailable)

  5. Click the End button to remove the error message, and then insert these two statements before the one that renames the worksheet:
  6. 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?

  7. Switch to Excel, delete the Report worksheet, switch back to the Visual Basic Editor, and press F5 to run the macro.
  8. 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.

    (Image Unavailable)

    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.

  9. Click the End button to clear the error message, and insert this statement above the one that deletes the worksheet:
  10. On Error Resume Next
    

    This statement tells Visual Basic to ignore any run-time errors and simply continue with the next statement.

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

  1. 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.
  2. 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.
  3. Press F5 to test the macro.
  4. (Image Unavailable)

    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.

  5. Create a new Sub procedure named DeleteSheet. When you're finished, this procedure will quietly delete the Report worksheet if it exists.
  6. Move the three statements that delete the worksheet into the DeleteSheet macro. The new macro should look like this:
  7. 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.

  8. In the MakeReport macro, type DeleteSheet where the three statements had been. The revised MakeReport macro (still containing the error) should look like this:
  9. 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.

  10. Press F5 to run the macro, and click the End button to close the error box.
  11. (Image Unavailable)

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

  1. Type SheetName as an argument name between the parentheses after the DeleteSheet macro name.
  2. Replace "Report" with SheetName in the body of the DeleteSheet macro. SheetName shouldn't have quotation marks around it.
  3. Type "Report" after DeleteSheet in the MakeReport macro. Here's what the two revised macros should look like:
  4. 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.

  5. Press F5 to test the MakeReport macro.
  6. 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:
  7. Sub MakeAnalysis()
    Dim mySheet As Worksheet
    Set mySheet = Worksheets.Add
    DeleteSheet "Analysis"
    mySheet.Name = "Analysis"
    End Sub
  8. Test the MakeAnalysis macro.
  9. (Image Unavailable)

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.

  1. Go to the MakeNextReport macro, and then press F8 repeatedly to watch the macro work.
  2. The new worksheet should rename properly.

    (Image Unavailable)

  3. Step through the macro a second time.
  4. The second time, the macro quietly fails, leaving the new sheet with the wrong name.

    (Image Unavailable)

    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.

  5. Add the following statements immediately before the End Sub statement:
  6. 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.

  7. Step through the macro.
  8. 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.

    (Image Unavailable)

    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.

  9. 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:
  10. On Error Resume Next
    mySheet.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.

  11. 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.
  12. 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.
  13. (Image Unavailable)

    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.

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

  15. 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.
  16. 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!
  17. (Image Unavailable)

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.

(Image Unavailable)

Here are the steps you follow to add special code that Visual Basic will run whenever an error occurs.

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

  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. Press F5 to test the macro.
  7. Visual Basic should display a simple message box showing only the message number. Click OK to close the message box.

    (Image Unavailable)

    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.

  8. Delete the statement MsgBox Err.Number, and replace it with this statement:
  9. 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.)

  10. Press F5 to run the macro and see the more elaborate error message.
  11. (Image Unavailable)

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.

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing 1 Customer Reviews
  • Anonymous

    Posted September 29, 2003

    Excellent Reference Book

    This book is very insightful and can add exceptional functionality to your personal or professional excel spreadsheet. A must buy!

    Was this review helpful? Yes  No   Report this review
Sort by: Showing 1 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)