Excel 2002 Power Programming with VBA

This book is an excellent resource for getting up to speed using streamlined spreadsheet creation tools, enhanced analysis tools and powerful Web integration. Transform Excel into an application suited to the whole organization so workgroups can work more effectively. Discover how developers can integrate information seamlessly, whether proposal text, financial figures or research data. Whether you are an expert or a novice, Excel 2002 Power Programming with VBA will help you work more efficiently, turning your data into answers you can count on.

Bonus: The book contains a coupon for the Professional Power Utility Pak.

Contents

Part I: Some Essential Background

Chapter 1: Excel 2002: Where It Came From
A Brief History of Spreadsheets
     It all started with VisiCalc
     Lotus 1-2-3
     Quattro Pro
     Microsoft Excel
Spreadsheets Today
Why Excel Is Great for Developers
Excel's Role in Microsoft's Strategy

Chapter 2: Excel in a Nutshell
Thinking in Terms of Objects
Workbooks
     Worksheets
     Chart sheets
     XLM macro sheets
     Excel 5/95 dialog sheets
Excel's User Interface
     Menus
     Dialog boxes
     Toolbars
     Drag-and-drop
     Keyboard shortcuts
Customizing the Display
Data Entry
Selecting Objects
Formatting
     Numeric formatting
     Stylistic formatting
Formulas
Names
Functions
Shapes
Charts
Macros
Database Access
     Worksheet databases
     External databases
Internet Features
Analysis Tools
     Outlines
     Automatic subtotals
     Scenario management
     Analysis ToolPak
     Pivot tables
     Auditing
     Solver
Add-Ins
Compatibility

Chapter 3: Formula Tricks and Techniques
About Formulas
Calculating Formulas
Cell and Range References
     Why use references that aren't relative?
     About R1C1 notation
     Referencing other sheets or workbooks
Using Names
     Naming cells and ranges
     Applying names to existing references
     Intersecting names
     Naming columns and rows
     Scoping names
     Naming constants
     Naming formulas
     Naming objects
Formula Errors
Array Formulas
     An array formula example
     An array formula calendar
     Array formula pros and cons
Counting and Summing Techniques
     Using the COUNTIF or SUMIF function
     Using array formulas to count and sum
     Other counting tools
Working with Dates and Times
     Entering dates and times
     Using pre-1900 dates
Creating Megaformulas

Chapter 4: Understanding Excel's Files
Starting Excel
Excel's File Extensions
Spreadsheet File Formats Supported
     Lotus 1-2-3 spreadsheet files
     Quattro Pro spreadsheet files
     Database file formats
     Text file formats
     Other file formats
Files Written by Excel
     XLS files
     Workspace files
     Template files
     Toolbar files
     Add-in files
Excel and HTML
     So how does it work?
     Adding some complexity
     What about interactivity?
Excel Settings in the Registry
     About the Registry
     Excel's settings

Part II: Excel Application Development

Chapter 5: What Is a Spreadsheet Application?
Spreadsheet Applications
The Developer and the End User
     Who are developers? What do they do?
     Classifying spreadsheet users
     The audience for spreadsheet applications
     Why people use spreadsheets
Solving Problems with a Spreadsheet
Basic Spreadsheet Types
     Quick-and-dirty spreadsheets
     For-your-eyes-only spreadsheets
     Single-user applications
     Spaghetti applications
     Utility applications
     Add-ins that contain worksheet functions
     Single-block budgets
     What-if models
     Data storage and access spreadsheets
     Database front ends
     Turnkey applications

Chapter 6: Essentials of Spreadsheet Application Development
Determining User Needs
Planning an Application That Meets User Needs
Determining the Most Appropriate User Interface
     Creating custom dialog boxes
     Using ActiveX controls on a worksheet
     Customizing menus
     Customizing toolbars
     Creating shortcut keys
     Executing the development effort
Concerning Yourself with the End User
     Testing the application
     Making the application bulletproof
     Making the application aesthetically appealing and intuitive
     Documenting the development effort
     Distributing the application to the user
     Updating the application when necessary
Other Development Issues
     The user's installed version of Excel
     Language issues
     System speed
     Video modes
     Directory structure

Part III: Understanding Visual Basic for Applications

Chapter 7: Introducing Visual Basic for Applications
Some BASIC Background
About VBA
     Object models
     VBA versus XLM
     VBA versus Lotus macros
     VBA versus LotusScript
The Basics of VBA
Introducing the Visual Basic Editor
     Activating the VBE
     The VBE windows
Working with the Project Explorer
     Adding a new VBA module
     Removing a VBA module
     Exporting and importing objects
Working with Code Windows
     Minimizing and maximizing windows
     Storing VBA code
     Entering VBA code
Customizing the VBE Environment
     Using the Editor tab
     Using the Editor Format tab
     Using the General tab
     Using the Docking tab
The Macro Recorder
     What is recorded
     Relative or absolute?
     Recording options
     Cleaning up recorded macros
About Objects and Collections
     The object hierarchy
     About collections
     Object referral
Properties and Methods
     Object properties
     Object methods
The Comment Object: A Case Study
     Online help for the Comment object
     Properties of a Comment object
     Methods of a Comment object
     The Comments collection
     About the Comment property
     Objects within a Comment object
     Determining whether a cell has a comment
     Adding a new Comment object
     Some useful application properties
Working with Range Objects
     The Range property
     The Cells property
     The Offset property
Things to Know about Objects
     Esoteric but essential concepts to remember
     Learn more about objects and properties

Chapter 8: VBA Programming Fundamentals
VBA Language Elements: An Overview
Comments
Variables, Data Types, and Constants
     Defining data types
     Declaring variables
     Scoping variables
     Working with constants
     Working with strings
     Working with dates
Assignment Statements
Arrays
     Declaring arrays
     Declaring multidimensional arrays
Object Variables
User-Defined Data Types
Built-in Functions
Manipulating Objects and Collections
     With-End With constructs
     For Each-Next constructs
Controlling Execution
     GoTo statements
     If-Then constructs
     Select Case constructs
     Looping blocks of instructions

Chapter 9: Working with VBA Sub Procedures
About Procedures
     Declaring a Sub procedure
     Scoping a procedure
Executing Procedures
     Executing a procedure with the Run Í Run Sub/
UserForm command
     Executing a procedure from the Macro dialog box
     Executing a procedure using a Ctrl+shortcut key combination
     Executing a procedure from a custom menu
     Executing a procedure from another procedure
     Executing a procedure from a toolbar button
     Executing a procedure by clicking an object
     Executing a procedure when an event occurs
     Executing a procedure from the Immediate window
Passing Arguments to Procedures
Error-Handling Techniques
     Trapping errors
     Error-handling examples
A Realistic Example
     The goal
     Project requirements
     What you know
     The approach
     What you need to know
     Some preliminary recording
     Initial setup
     Code writing
     Sort procedure writing
     More testing
     Fixing the problems
     Utility availability
     Evaluating the project

Chapter 10: Creating Function Procedures
Sub Procedures versus Function Procedures
Why Create Custom Functions?
An Introductory Example
     A custom function
     Using the function in a worksheet
     Using the function in a VBA procedure
     Analyzing the custom function
Function Procedures
     Declaring a function
     A function's scope
     Executing function procedures
Function Arguments
Function Examples
     A function with no argument
     Another function with no argument
     A function with one argument
     A function with two arguments
     A function with an array argument
     A function with optional arguments
     A function that returns a VBA array
     A function that returns an error value
     A function with an indefinite number of arguments
Emulating Excel's SUM function
Debugging Functions
Dealing with the Insert Function Dialog Box
     Specifying a function category
     Adding a function description
Using Add-ins to Store Custom Functions
Using the Windows API
     Windows API examples
     Determining the Windows directory
     Detecting the Shift key
     Learning more about API functions

Chapter 11: VBA Programming Examples and Techniques
Working with Ranges
     Copying a range
     Moving a range
     Copying a variably sized range
     Selecting or otherwise identifying various types of ranges
     Prompting for a cell value
     Entering a value in the next empty cell
     Pausing a macro to get a user-selected range
     Counting selected cells
     Determining the type of selected range
     Looping through a selected range efficiently
     Deleting all empty rows
     Determining whether a range is contained in another range
     Determining a cell's data type
     Reading and writing ranges
     A better way to write to a range
     Transferring one-dimensional arrays
     Transferring a range to a variant array
     Selecting the maximum value in a range
     Selecting all cells with a particular format
Working with Workbooks and Sheets
     Saving all workbooks
     Saving and closing all workbooks
     Accessing workbook properties
     Synchronizing worksheets
VBA Techniques
     Toggling a Boolean property
     Determining the number of printed pages
     Displaying the date and time
     Getting a list of fonts
     Sorting an array
     Processing a series of files
Some Useful Functions for Use in Your Code
     The FileExists function
     The FileNameOnly function
     The PathExists function
     The RangeNameExists function
     The SheetExists function
     The WorkbookIsOpen function
     Retrieving a value from a closed workbook
Some Useful Worksheet Functions
     Returning cell formatting information
     Displaying the date a file was saved or printed
     Understanding object parents
     Counting cells between two values
     Counting visible cells in a range
     Determining the last nonempty cell in a column or row
     Does a string match a pattern?
     Extracting the nth element from a string
     A multifunctional function
     The SHEETOFFSET function: Version 1
     The SHEETOFFSET function: Version 2
     Returning the maximum value across all worksheets
     Returning an array of nonduplicated random integers
     Randomizing a range
Windows API Calls
     Determining file associations
     Determining default printer information
     Determining the current video mode
     Adding sound to your applications
     Reading from and writing to the Registry

Part IV: Working with UserForms

Chapter 12: Custom Dialog Box Alternatives
Before You Create That UserForm. . .
Using an Input Box
     VBA's InputBox function
     Excel's InputBox method
VBA's MsgBox Function
Excel's GetOpenFilename Method
Excel's GetSaveAsFilename Method
Prompting for a Directory
     Using a Windows API function to select a directory
     Using the FileDialog object to select a directory
Displaying Excel's Built-In Dialog Boxes
     Using the Dialogs collection
     Learning more about built-in dialog boxes
     Using arguments with built-in dialog boxes
     Executing a menu item directly

Chapter 13: Introducing UserForms
How Excel Handles Custom Dialog Boxes
Inserting a New UserForm
Displaying a UserForm
Adding Controls to a UserForm
Controls Available to You
     CheckBox
     ComboBox
     CommandButton
     Frame
     Image control
     Label
     ListBox
     MultiPage
     OptionButton
     RefEdit
     ScrollBar
     SpinButton control
     TabStrip
     TextBox
     ToggleButton
Adjusting UserForm Controls
Adjusting a Control's Properties
     Using the Properties window
     Common properties
     Learning more about properties
     Accommodating keyboard users
Displaying and Closing UserForms
     Displaying a UserForm
     Closing a UserForm
     About event-handler procedures
Creating a UserForm: An Example
     Creating the UserForm
     Writing code to display the dialog box
     Trying it out
     Adding event-handler procedures
     Validating the data
     Now it works
UserForm Events
     Learning about events
     UserForm events
     Example: SpinButton events
     Pairing a SpinButton with a TextBox
Referencing UserForm Controls
Customizing the Toolbox
     Changing icons or tip text
     Adding new pages
     Customizing or combining controls
     Adding new ActiveX controls
Creating UserForm "Templates"
A UserForm Checklist

Chapter 14: UserForm Examples
Creating a UserForm "Menu"
     Using CommandButtons
     Using a ListBox
Selecting Ranges
Creating a "Splash Screen"
Disabling a UserForm's Close Button
Changing a Dialog Box's Size
Zooming and Scrolling a Sheet from a UserForm
ListBox Techniques
     About the ListBox control
     Adding items to a ListBox control
     Determining the selected item
     Determining multiple selections
     Multiple lists in a single ListBox
     ListBox item transfer
     Moving items in a ListBox
     Working with multicolumn ListBox controls
     Using a ListBox to select worksheet rows
     Using a ListBox to activate to a sheet
Using the MultiPage Control

Chapter 15: Advanced UserForm Techniques
Displaying a Progress Indicator
     Creating a standalone progress indicator
     Showing progress using a MultiPage control
     Showing progress without using a MultiPage control
Creating Wizards
     Setting up the MultiPage control
     Adding the buttons
     Programming the buttons
     Programming dependencies
     Performing the task
     Final steps
Emulating the MsgBox Function
     MyMsgBox code
     How it works
     Using the MyMsgBox function
A Modeless Dialog Box
Multiple Buttons, One Event-Handler
     Procedure
     Adapting this technique
A Color Picker Dialog
Displaying a Chart in a UserForm
     Method 1: Save the chart as a file
     Method 2: Use the OWC ChartSpace control
Displaying a Spreadsheet in a UserForm
An Enhanced Data Form
     Description
     Installing the add-in
     Using the Enhanced Data Form

Part V: Advanced Programming Techniques

Chapter 16: Developing Excel Utilities with VBA
About Excel Utilities
Using VBA to Develop Utilities
What Makes a Good Utility?
Text Tools: The Anatomy of a Utility
     Background
     Project goals for Text Tools
     How it works
     The Text Tools workbook
     The FormMain UserForm
     The modMain module
     The ApplyButton_Click procedure
     The "task" procedures
     The undo technique
     The ShowStats procedure
     User help technique
     Create menu and delete menu procedures
     Evaluation of the project
     Understand the Text Tools utility
More About Excel Utilities

Chapter 17: Working with Pivot Tables
An Introductory Example
     Creating a pivot table
     Examining the recorded code
     Cleaning up the recorded code
Creating a More Complex Pivot Table
     The data
     The pivot table
     The code that created the pivot table
     How it works
Creating a Pivot Table from an External Database
Creating Multiple Pivot Tables
Modifying Pivot Tables

Chapter 18: Working with Charts
About Charts
     Chart locations
     The Chart object model
Recording Chart Macros
     Macro recorder output
     The "cleaned up" macro
Common VBA Charting Techniques
     Activating a chart
     Deactivating a chart
     Determining whether a chart is activated
     Deleting from ChartObjects or charts
     Applying chart formatting
     Looping through all charts
     Sizing and aligning ChartObjects
More Charting Examples
     Using names in a SERIES formula
     Specifying the data used by a chart
     Determining a chart's source data: Method 1
     Determining a chart's source data: Method 2
     Displaying arbitrary data labels on a chart
     Displaying a chart in a UserForm
Understanding Chart Events
     An example of using Chart events
     Enabling events for an embedded chart
     Example: Using Chart events with an embedded chart
Charting Tricks
     Printing embedded charts on a full page
     Creating a "dead chart"
     Controlling a data series by hiding data
     Storing multiple charts on a chart sheet
     Using linked pictures in a chart
     Animated charts
     Creating a hypocycloid chart
     Creating a "clock" chart
     Drawing with an XY chart

Chapter 19: Understanding Excel's Events
Event Types That Excel Can Monitor
What You Should Know about Events
     Understanding event sequences
     Where to put event-handler procedures
     Disabling events
     Entering event-handler code
     Event-handler procedures that use arguments
Workbook-Level Events
     The Open event
     The Activate event
     The SheetActivate event
     The NewSheet event
     The BeforeSave event
     The Deactivate event
     The BeforePrint event
     The BeforeClose event
Worksheet Events
     The Change event
     Monitoring a specific range for changes
     The SelectionChange event
     The BeforeRightClick event
Chart Events
Application Events
     Enabling Application-level events
     Determining when a workbook is opened
     Monitoring Application-level events
UserForm Events
Events Not Associated with an Object
     The OnTime event
     The OnKey event

Chapter 20: Interacting with Other Applications
Starting Another Application
Activating Another Application
Running Control Panel Dialog Boxes and Wizards
Automation
     Working with foreign objects
     Early versus late binding
     A simple example
     Controlling Word from Excel
     Controlling Excel from another application
Working with ADO
Using SendKeys

Chapter 21: Creating and Using Add-Ins
What Is an Add-In?
     Comparing an add-in to a standard workbook
     Why create add-ins?
Understanding Excel's Add-In Manager
Creating an Add-In
An Add-In Example
     Setting up the workbook
     Testing the workbook
     Adding descriptive information
     Creating the add-in
     Installing the add-in
     Distributing the add-in
     Modifying the add-in
Comparing XLA and XLS Files
     File size and structure
     Collection membership
     Windows
     Sheets
     Accessing VBA procedures in an add-in
Manipulating Add-Ins with VBA
     The AddIns collection
     AddIn object properties
     AddIn object events
Optimizing the Performance of Add-ins
     Code speed
     File size
Special Problems with Add-Ins
     Ensuring that an add-in is installed
     Referencing other files
     Specifying the proper Excel version

Part VI: Developing Applications

Chapter 22: Creating Custom Toolbars
About Command Bars
Toolbar Manipulations
How Excel Handles Toolbars
     Storing toolbars
     When toolbars don't work correctly
Manipulating Toolbars and Buttons Manually
     About command bar customization mode
     Distributing toolbars
Manipulating the CommandBars Collection
     Command bar types
     Listing all CommandBar objects
     Creating a command bar
     Referring to command bars
     Deleting a command bar
     Properties of command bars
     Referring to controls in a command bar
     Listing the controls on a command bar
     Listing all controls on all toolbars
     Adding a control to a command bar
     Deleting a control from a command bar
     Properties of command bar controls

Chapter 23: Creating Custom Menus
A Few Words about Excel's Menu Bar
What You Can Do with Excel's Menus
     Menu terminology
     Removing menu elements
     Adding menu elements
     Changing menu elements
VBA Examples
     Listing menu information
     Adding a new menu to a menu bar
     Deleting a menu from a menu bar
     Adding menu items to a menu
     Displaying a shortcut key with a menu item
     Fixing a menu that has been reset
Working with Events
     Adding and deleting menus automatically
     Disabling or hiding menus
     Working with checked menu items
The Easy Way to Create Custom Menus
Creating a Substitute Worksheet Menu Bar
Working with Shortcut Menus
     Adding menu items to shortcut menus
     Deleting menu items from shortcut menus
     Disabling shortcut menu items
     Disabling shortcut menus
     Resetting shortcut menus
     Creating new shortcut menus

Chapter 24: Providing Help for Your Applications
Help for Your Excel Applications?
Help Systems That Use Excel Components
     Using cell comments for help
     Using a Text Box for help
     Using a worksheet to display help text
     Displaying help in a UserForm
     Using the Office Assistant to display help
Using the WinHelp and HTML Help Systems
     About WinHelp
     About HTML Help
Associating a Help File with Your Application
Other Ways of Displaying WinHelp or HTML Help
     Using the Help method
     Displaying Help from a message box
     Displaying Help from an input box

Chapter 25: Developing User-Oriented Applications
What is a User-Oriented Application?
the Loan Amortization Wizard
     Using the application
     The workbook structure
     How it works
     Potential enhancements
Application Development Concepts
Some Final Words

Part VII: Other Topics

Chapter 26: Compatibility Issues
What Is Compatibility?
Types of Compatibility Problems
Excel File Formats Supported
Avoid Using New Features
Applications That Use Windows API Calls
But Will It Work on a Mac?
Creating an International Application
     Multilanguage applications
     VBA language considerations
     Using "local" properties
     Identifying system settings
     Date and time settings

Chapter 27: Manipulating Files with VBA
Performing Common File Operations
     VBA file-related commands
     Using the FileSearch object
     Locating files that contain specific text
     Using the FileSystemObject object
Working with Text Files
     Opening a text file
     Reading a text file
     Writing a text file
     Getting a file number
     Determining or setting the file position
     Statements for reading and writing
Text File Manipulation Examples
     Importing data in a text file
     Exporting a range to a text file
     Importing a text file to a range
     Logging Excel usage
     Filtering a text file
     Importing more than 256 columns of data
     Exporting a range to HTML format

Chapter 28: Manipulating Visual Basic Components
Introducing the IDE
The IDE Object Model
     The VBProjects collection
An Introductory Example
Replacing a Module with an Updated Version
Using VBA to Write VBA Code
Adding Controls to a UserForm at Design Time
     Design-time versus runtime UserForm manipulations
     Adding 100 CommandButtons at design time
Creating UserForms Programmatically
     A simple example
     A useful (but not so simple) example

Chapter 29: Understanding Class Modules
What is a Class Module?
Example: Creating a NumLock Class
     Inserting a class module
     Adding the VBA code
     Using the NumLock class
More about Class Modules
     Naming the object class
     Programming properties
     Programming methods
     Class module events
Example: A CSV File Class
     Class module-level variables
     Property procedures
     Method procedures
     Using the CSVFileClass object

Chapter 30: Frequently Asked Questions about Excel Programming
General Excel Questions
The Visual Basic Editor
Procedures
Functions
Objects, Properties, Methods, and Events
UserForms
Add-Ins
CommandBars

Appendix A: Excel Resources Online

Appendix B: VBA Statements and Functions Reference

Appendix C: VBA Error Codes

Appendix D: ANSI Code Reference

Appendix E: What's on the CD-ROM

Index