List of Figures

Chapter 1: The Basics

Figure 1-1: The standard Excel spreadsheet screen

Figure 1-2: The standard Visual Basic window

Figure 1-3: Code window for the Workbook_Open event

Figure 1-4: Code to display Hello World message box using Workbook_NewSheet event

Figure 1-5: Hello World message box on spreadsheet

Figure 1-6: VBA code to display a message box when Sheet1 is activated

Chapter 3: Modules, Functions, and Subroutines

Figure 3-1: Creating a simple multiply function

Chapter 4: Programming Basics: Decisions and Looping

Figure 4-1: Code for conditional If statement

Chapter 5: Strings and Functions and Message Boxes

Figure 5-1: A simple message box

Chapter 7: Debugging

Figure 7-1: Example of instant watch

Figure 7-2: How to call a subroutine and show which procedure called it

Figure 7-3: Adding a watch on a variable

Figure 7-4: Monitoring the value of a variable from within a watch window

Chapter 9: Dialogs

Figure 9-1: Preparing a UserForm

Figure 9-2: The color palette for the BackColor property

Figure 9-3: The Font property

Figure 9-4: The code window for the UserForm showing events

Figure 9-5: The Toolbox window

Figure 9-6: A list box on a UserForm

Chapter 10: Common Dialog Control

Figure 10-1: Adding the Common Dialog control

Figure 10-2: The Open dialog

Figure 10-3: The Color dialog

Figure 10-4: The Font dialog

Figure 10-5: The Print dialog

Figure 10-6: Using the Excel default Print dialog

Chapter 11: Command Bars and Buttons

Figure 11-1: An additional menu structure created on Excel

Figure 11-2: Placing controls directly onto a spreadsheet

Chapter 12: The Excel Object Model

Figure 12-1: Displaying names of all worksheets within the Worksheet collection

Figure 12-2: Message box displaying names of worksheets

Figure 12-3: The Excel object model in the Object Browser

Figure 12-4: Recording a macro

Chapter 14: Using Excel to Interact with Other Office Programs

Figure 14-1: Selecting the Microsoft Word Object Library

Figure 14-2: Selecting the Object Library file for Microsoft Outlook

Figure 14-3: Selecting the Excel Library file

Chapter 15: Charts and Graphs

Figure 15-1: A data range suitable for a pie chart

Figure 15-2: A pie chart created using the VBA example

Figure 15-3: A chart incorporated into a spreadsheet using VBA

Chapter 16: Working with Databases

Figure 16-1: Setting up a data source name

Figure 16-2: Selecting an ODBC driver for the data source

Figure 16-3: Final steps for setting up a DSN

Figure 16-4: Putting in a reference to Active Data Objects

Figure 16-5: Results of running the example to draw data from a database table

Chapter 17: API Calls

Figure 17-1: Getting the total space from a disk drive

Chapter 18: Class Modules

Figure 18-1: Using the Add Procedure dialog to enter a property

Figure 18-2: Data used for the Pname collection

Chapter 19: Animation

Figure 19-1: Setting up an animation object and a control button

Chapter 20: Converting Labels to Numbers and Numbers to Labels

Figure 20-1: Changing labels to numbers in a spreadsheet

Chapter 21: Transposing a Range of Cells

Figure 21-1: A block of data ready to transpose

Figure 21-2: The same block of data transposed

Chapter 22: Adding Formula Details into Comments

Figure 22-1: Example of a formula shown in a comments box

Chapter 25: Who Created the Workbook?

Figure 25-1: Example of using the WHO function

Chapter 26: Evaluating a Cell

Figure 26-1: Example of EVAL function

Chapter 28: Replacing Characters in a String

Figure 28-1: Example of replacement function

Chapter 30: Auto Totaling a Matrix of Numbers

Figure 30-1: Results of matrix addition

Chapter 31: Absolute and Relative Formulas

Figure 31-1: Setting up the UserForm to provide the GUI interface

Figure 31-2: The GUI interface displayed

Chapter 32: Coloring Alternate Rows and Columns of the Spreadsheet

Figure 32-1: A Common Dialog control on a UserForm

Figure 32-2: The result of the alternate colored lines macro (horizontal lines)

Figure 32-3: The result of the alternate colored columns macro (vertical columns)

Chapter 33: Coloring Cells Containing Formulas

Figure 33-1: Example of coloring cells containing formulas

Chapter 34: Summing Cells by Reference to a Master Cell

Figure 34-1: Example of SUMCELLSBYREF in action

Chapter 35: Globally Changing a Range of Values

Figure 35-1: Defining a UserForm for setting global values

Chapter 36: Displaying Hidden Sheets Without a Password

Figure 36-1: Designing a UserForm for viewing hidden sheets

Figure 36-2: The hidden sheets UserForm in action

Chapter 37: Searching Multiple Sheets and Workbooks

Figure 37-1: Designing the UserForm for the Search facility

Figure 37-2: Example of the Search All facility

Chapter 38: Brighten Up Your Comments

Figure 38-1: Designing the UserForm for providing graphic comments

Figure 38-2: The Comment Enhancer UserForm in action

Figure 38-3: An example of a comment enhanced to a cube graphic

Chapter 39: An Alternative to Message Boxes

Figure 39-1: Placing a command button on the spreadsheet to call the Office Assistant

Figure 39-2: A demonstration of programming the Office Assistant

Chapter 40: Working with Shapes

Figure 40-1: Example of a VBA-defined shape in Excel

Chapter 41: Turning Your VBA Code into an Add-In

Figure 41-1: Example of creation of a pop-up submenu

Figure 41-2: Setting a password to protect your add-in

Figure 41-3: Saving your file as an add-in

Figure 41-4: Installing your add-in