×

Uh-oh, it looks like your Internet Explorer is out of date.

For a better shopping experience, please upgrade now.

Microsoft Excel Version 2002 Inside Out
     

Microsoft Excel Version 2002 Inside Out

by Mark Dodge, Craig Stinson, Stinson
 

Hey, you know your way around Excel—so now dig into Version 2002 and really put your spreadsheets to work! This supremely organized reference packs hundreds of timesaving solutions, troubleshooting tips, and handy workarounds in concise, fast-answer format—it’s all muscle and no fluff. Discover the best and fastest ways to perform everyday tasks,

Overview

Hey, you know your way around Excel—so now dig into Version 2002 and really put your spreadsheets to work! This supremely organized reference packs hundreds of timesaving solutions, troubleshooting tips, and handy workarounds in concise, fast-answer format—it’s all muscle and no fluff. Discover the best and fastest ways to perform everyday tasks, and challenge yourself to new levels of Excel mastery!

  • Build on what you already know about Excel and quickly dive into what’s new
  • Create more-powerful worksheets—from adding graphics to inserting hyperlinks
  • Perform financial, statistical, and what-if data analysis
  • Custom-build charts and PivotTable® Reports
  • Import online data and save it in XML format
  • Manage data and lists
  • Integrate Excel with Microsoft® Access, PowerPoint®, and Word
  • Create spreadsheet-driven Web pages
  • Work with Microsoft Visual Basic® for Applications (VBA)
  • Apply macros and custom functions to your worksheets

CD-ROM FEATURES:

  • Intuitive HTML interface
  • Extensive collection of Microsoft add-ins and third-party utilities, demos, and trials
  • Macros and code samples
  • Complete eBook—easy to browse and print!
  • Sample chapters from other INSIDE OUT Office XP books
  • Web links to Microsoft Office Tools on the Web, online troubleshooters, and product support
  • Microsoft Visio® customizable auto-demos
  • Interactive tutorials
  • Additional files and templates

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.

Editorial Reviews

Booknews
This book/CD-ROM package covers everyday tasks and advanced material, in sections on the Excel environment, building worksheets, formatting and editing worksheets, adding graphics and printing, creating formulas and performing data analysis, collaboration and the Internet, integration with other applications, creating charts, managing databases and lists, and working with Visual Basic and macros. The CD- ROM contains an intuitive HTML interface, Microsoft add-ins and third- party utilities, macros and code samples, demos, interactive tutorials, and additional files and templates. Dodge is coauthor of eight popular Microsoft Excel titles. Stinson is contributing editor for . Annotation c. Book News, Inc., Portland, OR (booknews.com)

Product Details

ISBN-13:
9780735612815
Publisher:
Microsoft Press
Publication date:
05/28/2001
Series:
Microsoft Inside Out Series
Edition description:
BK&CD-ROM
Pages:
1104
Product dimensions:
7.34(w) x 9.32(h) x 1.85(d)
Age Range:
13 Years

Read an Excerpt

Chapter 34: Debugging Macros and Custom Functions

If you have made it through the last three chapters, you now have at least a smattering of Microsoft Visual Basic for Applications (VBA) at your command—as well as, we hope, an appetite for learning more. The best ways to acquire more expertise in this versatile programming language are to read a text on the subject (such as Microsoft Excel 2000/Visual Basic for Applications Fundamentals, from Microsoft Press) and to experiment. As you do your everyday work in Microsoft Excel, look for chores that are ripe for automating. When you come across something macro-worthy, record your actions. Then inspect the code generated by the macro recorder. Make sure you understand what the recorder has given you (read the Help text for any statements you don't understand), and see whether you can find ways to make the code more efficient. Eliminate statements that appear unnecessary, and then see whether the code still does what you expect it to do. Look for statements that select ranges or other objects, and see whether you can make your code perform the essential tasks without first selecting those objects.

As you experiment, and as you create larger and more complex macros and functions, you will undoubtedly produce some code that either doesn't run at all or doesn't give you the result you're looking for. Missteps of this kind are an inevitable part of the learning process. (They're also an inherent part of programming, even for experts.) Fortunately, the VBA language and the Visual Basic Editor (VBE) provide tools to help you trap errors and root out bugs. Those tools are the subject of this chapter.

In this chapter, you look at two kinds of error-catching tools: those that help you at design time, when you're creating or editing code, and those that work at run time, while the code is running.

Using Design-Time Tools

The VBE's design-time error tools let you correct mistakes in VBA syntax and catch misspellings of variable names. They also let you follow the "flow" of a macro or function (seeing each line of code as it is executed) and monitor the values of variables during the course of a procedure's execution.

Catching Syntax Errors

If you enter a worksheet formula incorrectly in Excel, Excel alerts you to the error and refuses to accept the entry. The VBA compiler (the system component that converts your English-like VBA code into machine language) normally performs the same service for you if you enter a VBA expression incorrectly. If you omit a required parenthesis, for example, the compiler beeps as soon as you press Enter. It also presents an error message and displays the offending line of code in a contrasting color (red, by default).

Certain kinds of syntax errors don't become apparent to the compiler until you attempt to run your code. For example, if you write the following:

With Selection.Border
        .Weight = xlThin
        .LineStyle = xlAutomatic
and attempt to run this code without including an End With statement, you will see this error message.

(Image unavailable)

Your procedure will halt, and you will be in break mode. (You can tell you're in break mode by the appearance of the word break, in brackets, in the VBE title bar. The line that the compiler was attempting to execute will be highlighted—by default, in yellow.) Break mode lets you fix your code and then continue running it. For example, if you omit an End With statement, you can add that statement while in break mode, and then press F5 (or choose Run, Continue) to go on with the show. If you want to exit from break mode, rather than continue with the execution of your procedure, choose Run, Reset.

If you don't like having the compiler complain about obvious syntax errors the moment you commit them, you can turn that functionality off. Choose Tools, Options, click the Editor tab (shown in Figure 34-1), and clear the Auto Syntax Check check box. With automatic syntax checking turned off, your syntax errors will still be flagged when you try to run your code.

Figure 34-1.  Clear Auto Syntax Check if you don't want to know about syntax errors until you run your code. (Image unavailable)


TIP: Change color with the Options dialog box

You can also use the Options dialog box to change the color that the VBE uses to highlight syntax errors. If you don't like red, click the Editor Format tab, select Syntax Error Text in the code Colors list, and choose a different color.


Auto Syntax Check is on by default. So are three other "auto" options—Auto List Members, Auto Quick Info, and Auto Data Tips. These are all useful, and you should leave them on, especially if you're a relative newcomer to VBA. Auto List Members and Auto Quick Info help you complete a line of VBA code by displaying options available at the current insertion point or the names of arguments required by the function you're currently entering. Auto Data Tips is relevant only in break mode. If you hover your mouse cursor over a variable name in break mode, the Auto Data Tips feature displays the current value of that variable as a ScreenTip.

Catching Misspelled Variable Names

The VBA compiler doesn't care about the capitalization style of your variable names. MyVar, myVar, and myvar are identical names as far as the compiler is concerned. (If you're inconsistent about the capitalization of a variable name, the VBE adjusts all instances of that variable to make them look the same.) If you change the spelling of a variable name in mid-program, however, the compiler creates a new variable—and havoc for your program. An error in programming introduced by a misspelled variable can be especially treacherous because the program might appear to behave normally.

You can virtually eliminate the possibility of inconsistently spelled variable names in a module by adding a single statement at the top of that module (above any Sub or Function statements):

Option Explicit

The Option Explicit statement forces you to declare any variables used in the current module. You declare variables with Dim statements. (For the complete details about Dim, type Dim in a module and press F1.) With Option Explicit in place, if you use a variable without first declaring it, you get a Compile Error at run time. If you accidentally misspell a variable name somewhere in your program, the misspelled variable will be flagged by the compiler as an undeclared variable, and you'll be able to fix the problem forthwith.

You can add Option Explicit to every new module you create by choosing Tools, Options, clicking the Editor tab, and selecting Require Variable Declaration. This option is off by default, but it's good programming practice to turn it on. Option Explicit will do more for you than eliminate misspelled variable names. By forcing you to declare your variables, it will also encourage you to think ahead as you work.

Stepping Through Code

The VBE's step commands cause the compiler to execute either a single instruction or a limited set of instructions and then pause in break mode, highlighting the next instruction that will be executed. Execution is suspended until you take another action—such as issuing another step command, resuming normal execution, or terminating execution. By issuing step commands repeatedly, you can follow the procedure's execution path. You can see, for example, which way the program branches when it comes to an If statement, or which of the alternative paths it takes when it encounters a Select Case structure. (A Select Case structure causes the program to execute one of a set of alternative statements, depending on the value of a particular variable. For details, type case in a module and press F1.) You can also examine the values of variables at each step along the way.


TIP: Monitor variable value

You can monitor the value of variables by displaying the Watch window or the Quick Watch dialog box, or by hovering your mouse cursor over particular variables while in break mode. For information about using the Watch window, see "Using the Watch Window to Monitor Variable Values and Object Properties" on page 893.


You have four step commands at your disposal. You'll find these commands—and their keyboard shortcuts—on the Debug menu:

  • Step Into executes the next instruction only.
  • Step Over works like Step Into unless the next instruction is a call to another procedure. In that case, Step Into executes the entire called procedure as a unit.
  • Step Out executes the remaining steps of the current procedure.
  • Run To Cursor executes everything up to the current cursor position.

You can run an entire procedure one step at a time by pressing F8 (the keyboard shortcut for Debug, Step Into) repeatedly. To begin stepping through a procedure at a particular instruction, move your cursor to that instruction and press Ctrl+F8 (the shortcut for Debug, Run To Cursor). Alternatively, you can force the compiler to enter break mode when it reaches a particular instruction, and then use any of the step commands.

Setting Breakpoints with the Toggle Breakpoint Command

A breakpoint is an instruction that causes the compiler to halt execution and enter break mode. The simplest way to set a breakpoint is to put your cursor where you want the breakpoint and choose Debug, Toggle Breakpoint (or press F9). Choose this command a second time to clear a breakpoint. You can set as many breakpoints in a procedure as you like using this method. The Toggle Breakpoint command sets an unconditional breakpoint—one that will always occur when execution arrives at the breakpoint. To set a conditional breakpoint—one that takes effect only under a specified condition—see the next section.

As Figure 34-2 on the next page shows, the VBE highlights a line where you've set a breakpoint in a contrasting color and displays a large bullet in the left margin of the Code window. To customize the highlighting color, choose Tools, Options, click the Editor Format tab, and select Breakpoint Text.

Figure 34-2.  The VBE uses highlighting to mark breakpoint lines. (Image unavailable)

Setting Conditional Breakpoints Using Debug.Assert

With the Assert method of the Debug object, you can cause the VBA compiler to enter break mode only if a particular expression generates a FALSE result. Figure 34-3 provides a simple example.

Figure 34-3.  This Debug.Assert statement puts the compiler in break mode when the value of x becomes 9 or greater. (Image unavailable)

The Debug.Assert statement in this otherwise useless bit of code asserts that x is less than 9. As long as that assertion is true, the procedure runs. When it becomes false, the compiler enters break mode. As the Watch window in Figure 34-3 shows, the compiler enters break mode when x is equal to 9. (The Watch window is discussed next.)

You can also use the Watch window to set conditional breakpoints. See "Setting Conditional Breakpoints with the Watch Window" on page 895.

Using the Watch Window to Monitor Variable Values and Object Properties

The Watch window shows the current values of selected variables or expressions and the current property settings for selected objects. You can use the Watch window to monitor the status of variables and objects as you step through a procedure.

In Figure 34-3, the Watch window shows a single variable, x. Figure 34-4 shows a Watch window monitoring eight variables. The first four of these are object variables—variables set to various worksheet ranges. Because objects have properties, the Watch window displays outline controls beside the names of these variables. Opening an outline control reveals an object's property settings, as Figure 34-5 shows on the next page.

Figure 34-4.  You can monitor the status of variables and objects in the Watch window.  (Image unavailable)

Figure 34-5.  Opening an outline control associated with an object or object variable reveals that object's properties. (Image unavailable)

To display the Watch window, choose View, Watch Window. (To close the window, click its Close button.) To add a variable or object to the Watch window, you can select it in the Code window and drag it into the Watch window. You can add expressions, such as a + 1, to the Watch window in this manner. Alternatively, you can add something to the Watch window by choosing Debug, Add Watch. In the Expression box of the Add Watch dialog box (see Figure 34-6), type a variable name or other valid VBA expression.

Figure 34-6.  You can use the Add Watch dialog box to add a watch variable or set a conditional breakpoint. (Image unavailable)

Setting Conditional Breakpoints with the Watch Window

As Figure 34-6 shows, you can use the Add Watch dialog box to set a conditional breakpoint. Choose Debug, Add Watch, specify the name of a variable or a VBA expression, and then select either Break When Value Is True or Break When Value Changes. Selecting Break When Value Is True for an expression is comparable to using a Debug.Assert statement to set a conditional breakpoint. The difference is that Debug.Assert causes a break when an expression becomes false, and the Break When Value Is True option does the opposite.

Using Quick Watch to Monitor a Variable or Add a Watch Item

In break mode you can select any variable name or expression in your code and choose Debug, Quick Watch (or press Shift + F9) to see the current value of the selected item. If you decide you want to monitor that item continuously, you can click Add in the Quick Watch dialog box. The VBE then adds the item to the Watch window.

Using the Immediate Window

While in break mode, or before running a procedure, you can execute any VBA statement in the Immediate window. (If the Immediate window isn't visible, choose View, Immediate Window or press Ctrl+G.) For example, you can find out the value of a variable x by typing Print x in the Immediate window. (As a shortcut, you can type ?x. The question-mark character is a synonym for Print in VBA.)

You can also use the Immediate window to monitor action in a procedure while that procedure is running. You do this by inserting Debug.Print statements into the procedure. The statement Debug.Print x, for example, displays the current value of x in the Immediate window.

The Immediate window can be a handy place to test VBA statements while you're still wrestling with the syntax of this programming language. If you're not sure that a particular statement will have the effect that you intend, you can try it out in the Immediate window and see what happens....

Meet the Author

Mark Dodge has coauthored several popular books on Excel®, including Microsoft® Office Excel® 2003 Inside Out. He is a former senior technical writer for the Microsoft® Office User Assistance group.

Craig Stinson is a journalist and author. He has written or coauthored more than 20 books including Windows Vista® Inside Out, Deluxe Edition and Microsoft® Office Excel® 2007 Inside Out.

Customer Reviews

Average Review:

Post to your social network

     

Most Helpful Customer Reviews

See all customer reviews