List of Figures

Chapter 1: Writing Your First Macro

Figure 1-1: Defining a new style

Figure 1-2: Form for recording macros

Figure 1-3: The form for changing menus and toolbars

Figure 1-4: Modifying a tool

Figure 1-5: Button editor

Figure 1-6: The button on the right toggles between absolute and relative recording

Figure 1-7: A simple database application

Figure 1-8: The control toolbox (left); the table window (middle); the properties window with settings for the selected button (right)

Figure 1-9: Interest on monthly deposits

Figure 1-10: Formulation of validation rules for the interest table Templates

Figure 1-11: Vocabulary list with lookup and correct answer information ( columns C through F)

Figure 1-12: Form for the vocabulary tutor

Figure 1-13: Constructing a Form

Figure 1-14: A calendar produced with Excel

Figure 1-15: Toolbar for the euro conversion tool

Figure 1-16: A user -designed form

Figure 1-17: An intelligent form

Figure 1-18: An automated chart

Figure 1-19: This form is linked to a small database application

Figure 1-20: Analysis of survey data

Figure 1-21: A simple pivot table

Figure 1-22: Controlling external programs with Excel

Figure 1-23: Controlling Excel via ActiveX automation

Chapter 2: What's New in Excel 2000

Figure 2-1: Example of FormulaLabel

Chapter 3: The Development Environment

Figure 3-1: The VBA development environment

Figure 3-2: The Object Browser

Figure 3-3: The beginning of macro recording

Chapter 4: VBA Concepts

Figure 4.1: The Object Browser

Figure 4.2: The References form

Figure 4.3: Selecting the Worksheet event procedure in the code window

Figure 4.4: The three levels of virus security

Figure 4.5: Add-ins are considered safe in the default setting

Chapter 5: Techniques of Programming

Figure 5-1: Cell D6 is doubly selected.

Figure 5-2: Four-digit years as the global default setting

Figure 5-3: The calculational basis for the holiday function

Figure 5-4: The calendar for the year 2000

Figure 5-5: Information on all accessible drives

Figure 5-6: Input of the column breakpoints in the text import wizard for importing the file german.txt

Figure 5-7: Setting the date format in importing scientific.txt

Figure 5-8: The result of the importation

Figure 5-9: Short description of the Discount function

Figure 5-10: The user-defined function "Discount" has been associated with the function category "Financial."

Figure 5-11: The three levels of protection ( cells , sheet, workbook)

Figure 5-12: New Protection Options in Excel 2002

Figure 5-13: Protection of VBA code from prying eyes and unwanted changes

Figure 5-14: The registry editor in Windows 2000

Figure 5-15: Files in the Xlstart folder are considered safe.

Figure 5-16: Selection from the installed templates

Figure 5-17: A reference to the EuroTool add-in

Figure 5-18: Excel asks somewhat cryptically whether the workbook with the euro add-in should be loaded.

Figure 5-19: Excel maintains that Euroconvert is not defined in Eurotool.xla.

Figure 5-20: Error alert if the euro add-in has not been installed

Figure 5-21: Two worksheets before euro conversion

Figure 5.22: Only cell has been marked for subsequent conversion to euros

Figure 5-23: The table after euro conversion

Figure 5-24: The table after formatting is complete

Chapter 6: Debugging, Protection When Errors Arise

Figure 6-1: The call stack

Figure 6-2: Defining a watch expression

Figure 6-3: The Watches window

Figure 6-4: The Application object in the Watches window

Chapter 7: Forms (Microsoft Forms Library)

Figure 7-1: Dialog box for arranging windows

Figure 7-2: Save As dialog (GetSaveAsFilename)

Figure 7-3: VBA input box

Figure 7-4: Message box

Figure 7-5: Excel input box

Figure 7-6: The creation of our first form

Figure 7-7: The first test

Figure 7-8: The form for setting the tab order

Figure 7-9: List of additional controls installed on the author's computer

Figure 7-10: Some representative forms of the label box

Figure 7-11: Various forms of text boxes

Figure 7-12: The three types of listbox

Figure 7-13: A multicolumn listbox whose content is linked to a worksheet

Figure 7-14: A listbox for switching into another worksheet

Figure 7-15: Check box and option button

Figure 7-16: MS Forms buttons

Figure 7-17: Two frames with different zoom factors

Figure 7-18: A simple multipage form

Figure 7-19: The second page of the form

Figure 7-20: Three scroll bars

Figure 7-21: Test program for the SpecialEffect property

Figure 7-22: Input of a range of cells in a RefEdit control

Figure 7-23: The shrunken control

Figure 7-24: Result of the selection

Figure 7-25: Command Central for Userform.xls

Figure 7-26: Form for the input of a number between 0 and 100

Figure 7-27: A three-step cascade of forms (all three forms can be made visible at one time)

Figure 7-28: A three-stage chain of forms (only one form at a time is visible)

Figure 7-29: A dynamic form; left: in its initial state; right: expanded

Figure 7-30: Applications of spin buttons

Chapter 8: Menus and Toolbars

Figure 8-1: Customizing menus and toolbars

Figure 8-2: The long list of predefined commands

Figure 8-3: Editing menu items and tools via the pop-up menu

Figure 8-4: The image editor

Figure 8-5: The new toolbar "test" has been attached to the current Excel file

Figure 8-6: A checked menu item

Figure 8-7: A listbox for changing sheets using a toolbar

Figure 8-8: A new menu is inserted before the help menu in the main menu bar

Figure 8-9: The example program has its own standard menu

Chapter 9: Templates, Smart Forms

Figure 9-1: Two dialog sheets for data validation

Figure 9-2: Conditional formatting

Figure 9-3: The two most important steps of the template wizard

Figure 9-4: Dialog for saving a new file based on a template

Figure 9-5: Template for the invoice form for the "Speedy" company

Figure 9-6: Page preview of a "Speedy" invoice

Figure 9-7: Smart form for an invoice of a car-sharing club

Figure 9-8: Sample printout of an invoice of a car-sharing club

Chapter 10: Charts and Drawing Objects ( Shapes )

Figure 10-1: Three examples of trend lines

Figure 10-2: The most important objects of a chart

Figure 10-3: The Y-axis uses thousandths as scaling unit.

Figure 10-4: The form for input of the data range

Figure 10-5: The construction of the daily files for the measurement data

Figure 10-6: A daily report

Figure 10-7: A page from the three-page monthly report

Figure 10-8: Some of the predefined autoshapes

Figure 10-9: A star of colored arrows

Chapter 11: Data Management in Excel

Figure 11-1: An employee database

Figure 11-2: The Excel database form

Figure 11-3: Dialog for sorting a database

Figure 11-4: Dialog for searching the data

Figure 11-5: Dialog for setting a custom autofilter

Figure 11-6: Dialog for setting an advanced filter

Figure 11-7: Some search criteria for the employee database

Figure 11-8: The result of copying with an advanced filter

Figure 11-9: A form letter in Word

Figure 11-10: The application of database functions

Figure 11-11: Frequency distribution with database functions

Figure 11-12: Consolidation of measurement data

Figure 11-13: The smart form appearing in the application DB_Share.xls

Figure 11-14: Data on club members

Figure 11-15: Fleet management

Figure 11-16: Monthly balance sheet

Chapter 12: Access to External Data

Figure 12-1: Relations for managing the order data

Figure 12-2: Data for an order are divided among four tables

Figure 12-3: Tables and relations of the Northwind example database

Figure 12-4: Selecting a data source

Figure 12-5: Selecting the table fields

Figure 12-6: Final dialog of the MS Query Wizard

Figure 12-7: Inserting data into Excel

Figure 12-8: Data imported into a worksheet

Figure 12-9: The program MS Query with the Orders table

Figure 12-10: A listing of all Northwind orders

Figure 12-11: Filter criterion for the orders

Figure 12-12: A listing of orders of the employee King

Figure 12-13: MS Query can execute calculations for each column

Figure 12-14: A listing of the total sales of each employee

Figure 12-15: SQL code for the query of Figure 12-14

Figure 12-16: External Data Range Properties

Figure 12-17: The Access import wizard during the importation of an Excel table

Figure 12-18: The result of our introductory example

Figure 12-19: Connections between objects

Figure 12-20: The data fields of the employee table

Figure 12-21: Example questionnaire

Figure 12-22: Evaluation of the questionnaires

Figure 12-23: The internal structure of survey.xls

Figure 12-24: The table surveydata under construction

Figure 12-25: Some data records from the table surveydata

Figure 12-26: Testing an SQL query in Access

Chapter 13: Data Analysis in Excel

Figure 13-1: The product database was grouped by category

Figure 13-2: The product database has been divided into four price groups

Figure 13-3: A database with two pivot tables

Figure 13-4: Above, the pivot table toolbar of Excel 2000; below, the new, still empty, pivot table

Figure 13-5: Above, the pivot table toolbar of Excel 2002; below, the new, still empty, pivot table

Figure 13-6: One step on the way to pivot table 1

Figure 13-7: et another step

Figure 13-8: Representing a sum as a percentage of the total

Figure 13-9: Dialog for altering the layout of a pivot table

Figure 13-10: In the listbox individual fields can be deactivated.

Figure 13-11: Sales figures for 1995 grouped by month

Figure 13-12: Grouping of sales figures by year and quarter

Figure 13-13: Making detailed results visible ( drilldown )

Figure 13-14: Sales figures for August 1996

Figure 13-15: Development of a complex query with MS Query

Figure 13-16: The OLAP cube wizard

Figure 13-17: Pivot table based on an OLAP cube

Figure 13-18: Pivot table options

Figure 13-19: A pivot table with a pivot chart

Figure 13-20: The result of the procedure code1.btnCreatePivot1_Click

Figure 13-21: Converting long character strings into Visual Basic Syntax

Figure 13-22: Ranges of a pivot table

Figure 13-23: Showing or hiding individual rows (PivotItems) of a pivot field

Figure 13-24: Result of the procedure code2.btnCreatePivot1_Click

Figure 13-25: Analysis of the PivotCache object in the Watches wi

Chapter 14: VBA Programming for Pros

Figure 14-1: Left: the xlDialogSendMail dialog; right: Excel with EnvelopeVisible=True

Figure 14-2: Importation from an HTML file

Figure 14-3: One of the three exportation dialogs

Figure 14-4: A small range of cells that was exported with interactivity (that is, as a web component)

Figure 14-5: Smart tags in Excel 2002

Figure 14-6: Web service example application

Figure 14-7: Adding classes for using a web service

Figure 14-8: The first page of the report printed by Access

Figure 14-9: Display of an HTML file in Internet Explorer

Figure 14-10: A Visual Basic program that relies on Excel's chart functions

Figure 14-11: ActiveX automation for an OLE-object

Figure 14-12: ActiveX Automation for a VB.NET program

Chapter 15: Object Reference

Figure 15-1: Connections among objects

Appendix: Excel 2003 Beta 2

Figure A-1: XML importation via VBA code