About the Authors -- Acknowledgments -- Introduction -- Chapter 1: Primer in Excel VBA -- Using the Macro Recorder -- Recording Macros -- Running Macros -- The Visual Basic Editor -- Other Ways to Run Macros -- User Defined Functions -- Creating a UDF -- What UDFs Cannot Do -- The Excel Object Model -- Objects -- Getting Help -- Experimenting in the Immediate Window -- The VBA Language -- Basic Input and Output -- Calling Functions and Subroutines -- Variable Declaration -- Scope and Lifetime of Variables -- Variable Type -- Object Variables -- Making Decisions -- Looping -- Arrays -- Runtime Error Handling -- Summary -- Chapter 2: Programming in the VBE -- Writing Code -- Programming for People -- Writing Code -- Where Does My Code Go? -- Managing a Project -- Adding Classes -- Modifying Properties -- Importing and Exporting Visual Basic Code -- Editing -- Managing Editor Options -- Running and Debugging Code -- Using Watches -- Using the Object Browser -- Summary -- Chapter 3: The Application Object -- Globals -- The Active Properties -- Display Alerts -- Screen Updating -- Evaluate -- InputBox -- StatusBar -- SendKeys -- OnTime -- OnKey -- Worksheet Functions -- Caller -- Summary -- Chapter 4: Object-Oriented Theory and VBA -- Comparing Classes and Interfaces -- Defining an Interface -- Implementing an Interface -- Defining Methods -- Parameters -- Implementing Recursive Methods -- Eliminating Recursion with Loops -- Defining Fields -- Defining Properties -- Defining Events -- Defining Events in Classes -- Raising Events -- Handling Events -- Information Hiding and Access Modifiers -- Encapsulation, Aggregation, and References -- Summary -- Chapter 5: Event Procedures -- Worksheet Events -- Enable Events -- Worksheet Calculate -- Chart Events -- Before Double Click -- Workbook Events -- Save Changes -- Headers and Footers -- Summary.

Chapter 6: Class Modules -- Creating Your Own Objects -- Using Collections -- Class Module Collection -- Trapping Application Events -- Embedded Chart Events -- A Collection of UserForm Controls -- Referencing Classes Across Projects -- Summary -- Chapter 7: Writing Bulletproof Code -- Using Debug.Print -- Using Debug.Assert -- A Brief Exemplar of PC Debugging -- Creating Reusable Tools with the Debug Object -- Tracing Code Execution -- Trapping Code Execution Paths -- Asserting Application Invariants -- Raising Errors -- Writing Error Handlers -- On Error Goto Line Number -- On Error Resume Next -- On Error GoTo 0 -- Using the Err Object -- Scaffolding -- Writing to the EventLog -- Summary -- Chapter 8: Debugging and Testing -- Stepping Through Code -- Running Your Code -- Stepping into Your Code -- Step Over -- Step Out -- Run to Cursor -- Set Next Statement -- Show Next Statement -- Using Breakpoints -- Using Watches -- Add Watch -- Edit Watch -- Quick Watch -- Locals Windows -- Testing an Expression in the Immediate Window -- Resources for Finding Definitions -- Edit => Quick Info -- Edit => Parameter Info -- Edit => Complete Word -- Edit => List Properties/Methods -- Edit => List Constants -- Edit => Bookmarks -- View => Definition -- View => Object Browser -- Viewing the Call Stack -- Asserting Application Invariants -- Summary -- Chapter 9: UserForms -- Displaying a UserForm -- Creating a UserForm -- Directly Accessing Controls in UserForms -- Stopping the Close Button -- Maintaining a Data List -- Modeless UserForms -- Summary -- Chapter 10: Adding Controls -- The Toolbars -- ActiveX Controls -- Scrollbar Control -- Spin Button Control -- CheckBox Control -- Option Button Controls -- Forms Toolbar Controls -- Dynamic ActiveX Controls -- Controls on Charts -- Summary -- Chapter 11: Data Access with ADO.

An Introduction to Structured Query Language (SQL) -- The SELECT Statement -- The INSERT Statement -- The UPDATE Statement -- The CREATE TABLE Statement -- The DROP TABLE Statement -- An Overview of ADO -- The Connection Object -- The Recordset Object -- The Command Class -- Using ADO in Microsoft Excel Applications -- Using ADO with Microsoft Access -- Using ADO with Microsoft SQL Server -- Using ADO with Non-Standard Data Sources -- Summary -- Chapter 12: Creating and Using Add-ins -- Hiding the Code -- Converting the Workbook to an Add-in -- Closing Add-ins -- Code Changes -- Saving Changes -- Installing an Add-in -- Add-in Install Event -- Removing an Add-in from the Add-ins List -- Summary -- Chapter 13: Automation Addins and COM Addins -- Automation Addins -- Creating a Simple Addin -- Registering Automation Addins with Excel -- Using Automation Addins -- An Introduction to the IDTExtensibility2 Interface -- A Complex Addin-Generating a Unique Random Number -- COM Addins -- IDTExtensibility2 Interface Continued -- Summary -- Chapter 14: Customizing the VBE -- Identifying VBE Objects in Code -- The VBE Object -- The VBProject Object -- The VBComponent Object -- The CodeModule Object -- The CodePane Object -- The Designer Object -- Starting Up -- Adding Menu Items to the VBE -- Table-Driven Menu Creation -- Displaying Built-In Dialogs, UserForms, and Messages -- Working with Code -- Working with UserForms -- Working with References -- Summary -- Chapter 15: Interacting with Other Office Applications -- Establishing the Connection -- Late Binding -- Early Binding -- Opening a Document in Word -- Accessing an Active Word Document -- Creating a New Word Document -- Access and DAO -- Access, Excel and, Outlook -- When Is a Virus not a Virus? -- Summary -- Chapter 16: Programming with the Windows API -- Anatomy of an API Call.

Interpreting C-Style Declarations -- Constants, Structures, Handles, and Classes -- What if Something Goes Wrong? -- Wrapping API Calls in Class Modules -- Some Example Classes -- A High-Resolution Timer Class -- Freeze a UserForm -- A System Info Class -- Modifying UserForm Styles -- Resizable Userforms -- Other Examples -- Summary -- Chapter 17: International Issues -- Changing Windows Regional Settings and the Office XP UI Language -- Responding to Regional Settings and the Windows Language -- Identifying the User's Regional Settings and Windows Language -- VBA Conversion Functions from an International Perspective -- Interacting with Excel -- Sending Data to Excel -- Reading Data from Excel -- Rules for Working with Excel -- Interacting with Users -- The Rules for Working with Your Users -- Excel 2003's International Options -- Features That Don't Play by the Rules -- Responding to Office XP Language Settings -- Where Does the Text Come From? -- Identifying the Office UI Language Settings -- Creating a Multilingual Application -- Working in a Multilingual Environment -- The Rules for Developing a Multilingual Application -- Some Helpful Functions -- Implementing WinToNum Function -- Implementing WinToDate Function -- Implementing FormatDate Function -- Implementing ReplaceHolders Function -- Summary -- Chapter 18: Workbooks and Worksheets -- Using the Workbooks Collection -- Creating a New Workbook -- Saving the ActiveWorkbook -- Activating a Workbook -- Getting a FileName from a Path -- Files in the Same Directory -- Overwriting an Existing Workbook -- Saving Changes -- The Sheets Collection -- Worksheets -- Copy and Move -- Grouping Worksheets -- The Window Object -- Synchronizing Worksheets -- Summary -- Chapter 19: Using Ranges -- Activate and Select -- Range Property -- Shortcut Range References -- Ranges on Inactive Worksheets.

Range Property of a Range Object -- Cells Property -- Cells used in Range -- Ranges of Inactive Worksheets -- More on the Cells Property of the Range Object -- Single-Parameter Range Reference -- Offset Property -- Resize Property -- SpecialCells Method -- CurrentRegion Property -- End Property -- Referring to Ranges with End -- Summing a Range -- Columns and Rows Properties -- Areas -- Union and Intersect Methods -- Empty Cells -- Transferring Values between Arrays and Ranges -- Deleting Rows -- Summary -- Chapter 20: Using Names -- Naming Ranges -- Using the Name Property of the Range Object -- Special Names -- Storing Values in Names -- Storing Arrays -- Hiding Names -- Working with Named Ranges -- Searching for a Name -- Searching for the Name of a Range -- Determining which Names Overlap a Range -- Summary -- Chapter 21: Working with Lists -- Creating a List -- Shortcut Options for Lists -- Sorting and Filtering a List -- Creating a UserForm from a List -- Resizing Lists -- Dragging the resize handle in the bottom corner of the list -- Totaling Rows -- Converting Lists to a Range -- Publishing Lists -- Publishing Your List -- Updating Changes to Your List -- View a List on a SharePoint Server -- Unlinking the List -- Summary -- Chapter 22: PivotTables -- Creating a PivotTable Report -- PivotCaches -- PivotTables Collection -- PivotFields -- CalculatedFields -- PivotItems -- Grouping -- Visible Property -- CalculatedItems -- PivotCharts -- External Data Sources -- Summary -- Chapter 23: Filtered Lists -- Structuring the Data -- Data Form -- AutoFilter -- Custom AutoFilter -- Adding Combo Boxes -- Copying the Visible Rows -- Finding the Visible Rows -- Advanced Filter -- Summary -- Chapter 24: Generating Charts -- Chart Sheets -- Embedded Charts -- Editing Data Series -- Defining Chart Series with Arrays -- Converting a Chart to use Arrays.

Determining the Ranges used in a Chart.