List of Figures

---

Chapter 1: What's New in Excel 2003

Figure 1-1: You can find synonyms, look up words in encyclopedias, and translate words into foreign languages using the tools on the Research task pane.

Figure 1-2: Data lists enhance your ability to create and display data collections in Excel 2003.

Chapter 2: Exploring Excel

Figure 2-1: The Scenario Manager dialog box helps you manage and present alternative data sets.

Figure 2-2: Use the controls in the Format Cells dialog box to present your data effectively.

Figure 2-3: Use the Define Name dialog box to manage your named ranges.

Figure 2-4: The Excel interface offers quick access to the program's diverse capabilities.

Figure 2-5: The Insert Function dialog box helps you find the function you want and guides you through the creation process.

Chapter 3: Exploring Visual Basic for Applications

Figure 3-1: The Record Macro dialog box provides an interface for managing the macros in your workbook.

Figure 3-2: You can view the code behind a macro in the Visual Basic Editor.

Figure 3-3: A more flexible macro that works on any set of cells, not just the cells selected when the macro was recorded.

Figure 3-4: The Visual Basic Editor shows you which line of code will be executed next.

Figure 3-5: Breakpoints let you run your code to a specific point before you step through it line by line.

Figure 3-6: The Select Certificate dialog box lists the certificates available for digitally signing a workbook.

Chapter 4: VBA Programming Starter Kit

Figure 4-1: The Visual Basic Editor organizes your programming elements to make them easier to manage.

Figure 4-2: The Visual Basic Editor Code Window lets you pick objects and procedures to work on.

Figure 4-3: The Visual Basic Editor Form Editor takes over when you move from writing VBA code to creating user forms.

Figure 4-4: The default locations for the windows in the Visual Basic Editor appear here, but you can move the windows around to optimize your working environment.

Figure 4-5: The Editor tab of the Options dialog box gives you the tools to change how code, errors, and tips are displayed in the Visual Basic Editor.

Figure 4-6: The Editor Format tab lets you determine everything from the font in which your code is displayed to the text color used to highlight important program elements.

Figure 4-7: The General tab of the Options dialog box contains the controls that don't fit well into the other tab pages in the dialog box.

Figure 4-8: The Add Procedure dialog box lets you determine the basic outline of your procedure.

Figure 4-9: Creating a new module gives you a place to put your code.

Figure 4-10: It's probably a good idea to take the opportunity presented by the Export File dialog box to save deleted code in a text file, just in case.

Figure 4-11: The mouse pointer changes to indicate a valid drop-off point when copying modules.

Figure 4-12: If you want a variable to be available to all procedures in a module, you need to declare the variable in the declaration section for modules.

Figure 4-13: It's easy to create message boxes with brief welcome or informational messages.

Figure 4-14: It's not that much harder to create a simple message box with two lines of text.

Figure 4-15: Creating an input box would allow users to enter personal information, such as their name.

Chapter 5: Creating Sub and Function Procedures

Figure 5-1: As soon as you click OK in the Add Procedure dialog box, the skeleton of your new procedure appears in the active code module.

Figure 5-2: Using the Public and Private keywords helps limit the availability of your macros when appropriate.

Figure 5-3: Adding the Option Private Module line to a module's declaration section hides all procedures in a module from view.

Figure 5-4: The References dialog box lists all the available resources to which you can link and subsequently call procedures.

Figure 5-5: The Visual Basic Editor helps you create effective procedures by listing the expected arguments.

Chapter 6: The Application Object

Figure 6-1: The Microsoft Office 2003 Excel Object Model is the repository of all knowledge in Excel.

Figure 6-2: The ActiveCell property lets you make changes to the active cell without writing a specific cell or range reference.

Figure 6-3: The ActiveChart property lets you streamline your chart creation and manipulation.

Figure 6-4: This macro creates a new worksheet based on an existing template but adds the correct weekday and date for the upcoming month as well as the unchanging hour column headings.

Figure 6-5: The Intersect method finds the cells representing the intersection of two ranges.

Chapter 7: Workbooks and Worksheets

Figure 7-1: You can open a file that contains comma-delimited data directly into Excel.

Figure 7-2: When delimiters appear in unexpected places, data chaos ensues.

Figure 7-3: You can create procedures that let your users decide whether to hide drawing objects or leave them visible.

Figure 7-4: The Favorites list contains links to files and hyperlinks you want to remember and access quickly.

Figure 7-5: The PrintOut method includes all of the important controls from the Print dialog box.

Chapter 8: Ranges and Cells

Figure 8-1: This sample worksheet lets you calculate the components of a mortgage loan.

Figure 8-2: The Loan Calculator requires a number of specific inputs-here are those descriptions.

Figure 8-3: Adding the Button Control to the form.

Figure 8-4: The number of digits visible at 100% Zoom…

Figure 8-5: …might not be the same as the number of digits visible at 200% Zoom

Figure 8-6: This procedure creates names in the Y2001ByQuarter.xls workbook.

Chapter 9: Manipulating Data with VBA

Figure 9-1: You can change the format of your cell to any of the formats in the Custom list.

Figure 9-2: The SEARCH and MID functions, among others, let you reach into lengthy text strings to extract the interesting parts.

Chapter 10: Formatting Excel Objects

Figure 10-1: The active sheet's name is displayed with a view to readability, whereas inactive sheet tabs let the tab's color take precedence.

Figure 10-2: AutoFormats make data labels stand out so your worksheet data is easier to read.

Chapter 11: Creating Add-Ins and COM Add-Ins

Figure 11-1: The Add-Ins dialog box dialog box controls which add-ins are loaded into Excel.

Figure 11-2: The title of the add-in should be specified along with any other properties of the workbook.

Figure 11-3: Selecting Microsoft Office Excel Add-In (*.xla) as the file type allows you to save the workbook as an add-in.

Figure 11-4: Locate your new add-in by looking for the value you specified for the title of the workbook.

Figure 11-5: The AddIns collection contains information about each add-in known to Excel.

Figure 11-6: Choose a meaningful name for your add-in project.

Figure 11-7: Fill in the proper information for your add-in.

Figure 11-8: The DiscountedPrice function in the automation add-in works exactly as the one created for the Excel add-in.

Figure 11-9: You can verify the entries made in the Windows registry by using the RegEdit utility.

Chapter 12: Understanding and Using Events

Figure 12-1: The components for each VBA Project are displayed in an expandable list.

Figure 12-2: The VB Editor is the best way to create a new event procedure.

Figure 12-3: This is how to create a new Worksheet event procedure.

Chapter 13: Manipulating Files

Figure 13-1: The ListSearchScopeOptions procedure result. Your result might vary slightly depending on how your computer is configured.

Figure 13-2: Here is the ListScopeFolderObjects procedure result. Your result might vary depending on how your computer is configured.

Chapter 14: Developing Class Modules

Figure 14-1: Use the Properties Window to change the Name property associated with the class.

Figure 14-2: You can easily build a simple class in the Visual Basic Editor.

Chapter 15: Charts

Figure 15-1: The Name Box displays the name of the ChartObject object when it's selected, rather than active on the screen.

Figure 15-2: The selected data series is indicated by marks on the corresponding chart elements.

Figure 15-3: The Catalog series is displayed with the SERIES formula in the Formula Bar.

Figure 15-4: The labels have been programmed with formulas to point to a cell in the worksheet.

Figure 15-5: The sample formatting for the Garden Supply Company Monthly Sales Chart.

Chapter 16: PivotTables and PivotCharts

Figure 16-1: A simple two-dimensional report contains summary data indexed by two fields.

Figure 16-2: A PivotTable allows a user to combine facts in many different ways.

Figure 16-3: A PivotTable contains summary information based on the underlying data supplied.

Figure 16-4: A PivotChart contains drag and drop areas similar to a PivotTable.

Figure 16-5: A PivotChart is a graphical representation of a PivotTable.

Figure 16-6: The parts of a PivotTable are represented by a collection of different types of objects.

Figure 16-7: This PivotTable is the result of running the subroutine shown on page 358.

Figure 16-8: This is the starting layout for your PivotTable manipulations.

Figure 16-9: Putting the Hour field in the fourth position in the Row area generates this result.

Figure 16-10: Excel records your PivotTable position for future use.

Chapter 17: Command Bars

Figure 17-1: The CommandBar set of objects gives VBA programmers a lot of flexibility to integrate their applications into Excel.

Figure 17-2: The Index, Enabled, Visible, Type, and Name properties for each CommandBar object are copied to an Excel worksheet.

Figure 17-3: A floating command bar is not docked with the other command bars.

Figure 17-4: You can combine multiple types of buttons into a single command bar to create a toolbar.

Chapter 18: Customizing Dialog Boxes

Figure 18-1: The Go To dialog box appears when called using the xlDialogFormulaGoto intrinsic constant.

Figure 18-2: Displaying the Go To Special dialog box requires you to use the intrinsic constant for that dialog box.

Figure 18-3: You can display the Format Cells dialog box, albeit one tab page at a time.

Figure 18-4: Both check boxes in the Cell Protection dialog box can be selected by default using the proper VBA code.

Figure 18-5: The Object Browser displays the search results for the built-in dialog boxes available in Excel.

Figure 18-6: The Alignment dialog box with the Horizontal and Vertical alignment set to centered, as well as Wrap Text set to true.

Chapter 19: Creating User Forms

Figure 19-1: A UserForm object allows a VBA programmer to design a graphical interface to his application.

Figure 19-2: You add controls to a form control by selecting a control and dragging and dropping it on the form.

Chapter 20: Creating Advanced User Forms

Figure 20-1: A simple worksheet tracks customer information such as name and address and the date the customer was added.

Figure 20-2: Add TextBox and Label controls for each column in the worksheet and their properties as directed.

Figure 20-3: Finishing the form layout.

Figure 20-4: The user can switch between the form and Excel if the form is displayed using vbModeless value.

Figure 20-5: Step 1 of the wizard allows the user to select from multiple options using the OptionButton controls.

Figure 20-6: The user can enter information into multiple text boxes in step 2 of the wizard.

Figure 20-7: Step 3 of the wizard lets the user review all of her choices before clicking Finish.

Figure 20-8: Right-click over the tab area, and select New Page from the popup menu to add a new Page to the MultiPage control.

Figure 20-9: A Frame control surrounds a set of OptionButtons.

Figure 20-10: A Frame control surrounds a collection of label and text box controls.

Figure 20-11: A summary of the options selected is displayed in the Review text box.

Chapter 21: Excel and Other Office Applications

Figure 21-1: You can use a Start command in the Run dialog box to open a presentation file.

Figure 21-2: The Outlook Calendar with the newly created Spring Sales Initiative Meeting on the morning of March 7, 2005.

Figure 21-3: The References dialog box displays all available references. To activate a library file, select the reference and click OK.

Figure 21-4: Warning message you get when executing code that interrogates your Contacts folder in Outlook.

Figure 21-5: Here's a sample workbook, displaying the basic setup of data used to create the chart.

Chapter 22: Excel and the Structured Query Language

Figure 22-1: A database table is constructed as a series of rows and columns.

Figure 22-2: Databases are typically organized as a database client talking to a database server.

Chapter 23: Introducing ADO

Figure 23-1: A data consumer makes requests to a data provider, which returns a response about the request to the data consumer.

Figure 23-2: A few key objects comprise the ADO object model.

Chapter 24: Excel Query Program

Figure 24-1: The user interface to the query program is through a new command bar added to Excel.

Figure 24-2: Use the References dialog box to add a reference to the ADO database library.

Figure 24-3: The user supplies the database connection information through the Database Properties dialog box.

Figure 24-4: Each page in the MultiPage control contains connection information unique to each particular database.

Figure 24-5: The DBQuery form is essentially a large textbox that allows the user to enter his query.

Figure 24-6: Choose Access (Jet) in the drop-down box, and then enter the path in the database.

Figure 24-7: Enter the query you want to run.

Figure 24-8: View the results from the database query as a normal worksheet.

Chapter 25: Excel and the Web

Figure 25-1: The Save as Web Page dialog box is similar to the Save dialog box with several enhancements for Web page functionality.

Figure 25-2: The Publish as a Web Page dialog box displays the options available before the save operation is complete. You can set up the location, file name, and other options from this dialog box.

Figure 25-3: The Add Network Place wizard will guide you through creating a new network location for your Web page.

Figure 25-4: The U.S. Treasury site shows sample data laid out in a table format. This information will be evaluated in the next sections.

Figure 25-5: The New Web Query dialog box shows the table of exchange rates ready to be imported into the Excel spreadsheet.

Figure 25-6: The Default Web Site Properties dialog box is used to configure IIS. To post Web pages to your Web server, ensure a check mark is placed in the Read and Write check boxes.

Chapter 26: Excel and the Extensible Markup Language (XML)

Figure 26-1: A data list has column headers and rows of data, with each row representing a complete set of column values.

Figure 26-2: The Create List dialog box lets you determine the structure of your data list.

Figure 26-3: This worksheet has existing column labels and is ready for a list.

Figure 26-4: XML schemas are detailed, but not overly complicated.

Figure 26-5: You can map an entire element (with all its subelements) to a worksheet at once.

Figure 26-6: The XML Map Properties dialog box gives you more tools to control your XML data.

Figure 26-7: The AssignElementsToRanges procedure maps schema elements to cell ranges on your worksheet, creating lists.

Figure 26-8: Assigning schema elements to column headers in an existing data list gives you full list functionality and a data map.

---