Introduction xxvii
Part I: Introduction to Excel VBA
Chapter 1: Essentials of Spreadsheet Application Development 3
What Is a Spreadsheet Application? 3
Steps for Application Development 4
Determining User Needs 5
Planning an Application That Meets User Needs 6
Determining the Most Appropriate User Interface 8
Concerning Yourself with the End User 12
Other Development Issues 17
Chapter 2: Introducing Visual Basic for Applications 19
Getting a Head Start with the Macro Recorder 19
Working with the Visual Basic Editor 32
VBA Fundamentals 42
Deep Dive: Working with Range Objects 47
Essential Concepts to Remember 53
Don’t Panic—You Are Not Alone55
Chapter 3: VBA Programming Fundamentals 61
VBA Language Elements: An Overview 61
Comments 63
Variables, Data Types, and Constants 64
Assignment Statements 75
Arrays 77
Object Variables79
User-Defined Data Types 80
Built-In Functions 81
Manipulating Objects and Collections 84
Controlling Code Execution 87
Chapter 4: Working with VBA Sub Procedures 105
About Procedures 105
Executing Sub Procedures 108
Passing Arguments to Procedures 119
Error-Handling Techniques 123
A Realistic Example That Uses Sub Procedures 127
Chapter 5: Creating Function Procedures 143
Sub Procedures versus Function Procedures 143
Why Create Custom Functions? 144
An Introductory Function Example 144
Function Procedures 148
Function Arguments 153
Function Examples 153
Emulating Excel’s SUM Function 168
Extended Date Functions 171
Debugging Functions 173
Dealing with the Insert Function Dialog Box 174
Using Add-Ins to Store Custom Functions 178
Using the Windows API 179
Chapter 6: Understanding Excel’s Events 183
What You Should Know about Events 183
Getting Acquainted with Workbook-Level Events 190
Examining Worksheet Events 197
Monitoring with Application Events 206
Accessing Events Not Associated with an Object 210
Chapter 7: VBA Programming Examples and Techniques 217
Learning by Example 217
Working with Ranges 218
Working with Workbooks and Sheets 246
VBA Techniques 251
Some Useful Functions for Use in Your Code 259
Some Useful Worksheet Functions 264
Windows API Calls 280
Part II: Advanced VBA Techniques
Chapter 8: Working with Pivot Tables 291
An Introductory Pivot Table Example 291
Creating a More Complex Pivot Table 297
Creating Multiple Pivot Tables 301
Creating a Reverse Pivot Table 304
Chapter 9: Working with Charts 307
Getting the Inside Scoop on Charts 307
Creating an Embedded Chart 310
Creating a Chart on a Chart Sheet 311
Modifying Charts 312
Using VBA to Activate a Chart 313
Moving a Chart 314
Using VBA to Deactivate a Chart 315
Determining Whether a Chart Is Activated 316
Deleting from the ChartObjects or Charts Collection 316
Looping through All Charts 318
Sizing and Aligning ChartObjects 320
Creating Lots of Charts 321
Exporting a Chart 324
Changing the Data Used in a Chart 326
Using VBA to Display Arbitrary Data Labels on a Chart 331
Displaying a Chart in a UserForm 335
Understanding Chart Events 337
Discovering VBA Charting Tricks 344
Working with Sparkline Charts 351
Chapter 10: Interacting with Other Applications 355
Understanding Microsoft Office Automation 355
Automating Access from Excel 359
Automating Word from Excel 361
Automating PowerPoint from Excel 364
Automating Outlook from Excel 369
Starting Other Applications from Excel 373
Chapter 11: Working with External Data and Files 379
Working with External Data Connections 379
Using ADO and VBA to Pull External Data 387
Working with Text Files 394
Text File Manipulation Examples 398
Performing Common File Operations 402
Zipping and Unzipping Files 410
Part III: Working with UserForms
Chapter 12: Leveraging Custom Dialog Boxes 415
Before You Create That UserForm 415
Using an Input Box415
The VBA MsgBox Function 421
The Excel GetOpenFilename Method 426
The Excel GetSaveAsFilename Method 429
Prompting for a Directory 430
Displaying Excel’s Built-In Dialog Boxes 430
Displaying a Data Form 433
Chapter 13: Introducing UserForms 435
How Excel Handles Custom Dialog Boxes 435
Inserting a New UserForm 436
Adding Controls to a UserForm 437
Toolbox Controls 437
Adjusting UserForm Controls 442
Adjusting a Control’s Properties 443
Displaying a UserForm 449
Closing a UserForm 451
Creating a UserForm: An Example 453
Understanding UserForm Events 459
Referencing UserForm Controls 465
Customizing the Toolbox 466
Creating UserForm Templates 469
A UserForm Checklist 469
Chapter 14: UserForm Examples 471
Creating a UserForm “Menu” 471
Selecting Ranges from a UserForm 474
Creating a Splash Screen 476
Disabling a UserForm's Close Button 478
Changing a UserForm's Size 479
Zooming and Scrolling a Sheet from a UserForm 480
ListBox Techniques 482
Using the MultiPage Control in a UserForm 503
Using an External Control 504
Animating a Label 506
Chapter 15: Advanced UserForm Techniques 511
A Modeless Dialog Box 512
Displaying a Progress Indicator 516
Creating Wizards 527
Emulating the MsgBox Function 533
A UserForm with Movable Controls 537
A UserForm with No Title Bar 538
Simulating a Toolbar with a UserForm 540
Emulating a Task Pane with a UserForm 542
A Resizable UserForm 543
Handling Multiple UserForm Controls with One Event Handler 547
Selecting a Color in a UserForm 550
Displaying a Chart in a UserForm 552
Making a UserForm Semitransparent 554
A Puzzle on a UserForm 556
Video Poker on a UserForm 557
Part IV: Developing Excel Applications
Chapter 16: Creating and Using Add-Ins 561
What Is an Add-In? 561
Understanding Excel’s Add-In Manager 564
Creating an Add-in 566
An Add-In Example 567
Comparing XLAM and XLSM Files 572
Manipulating Add-Ins with VBA 578
Optimizing the Performance of Add-Ins 584
Special Problems with Add-Ins 585
Chapter 17: Working with the Ribbon 589
Ribbon Basics 589
Customizing the Ribbon 591
Creating a Custom Ribbon 596
Using VBA with the Ribbon 617
Creating an Old-Style Toolbar 621
Chapter 18: Working with Shortcut Menus 625
CommandBar Overview 625
Using VBA to Customize Shortcut Menus 632
Shortcut Menus and Events 642
Chapter 19: Providing Help for Your Applications 647
Help for Your Excel Applications 647
Help Systems That Use Excel Components 649
Displaying Help in a Web Browser 657
Using the HTML Help System 659
Chapter 20: Leveraging Class Modules 665
What Is a Class Module? 665
Creating a NumLock Class 667
Coding Properties, Methods, and Events 671
Exposing a QueryTable Event 674
Creating a Class to Hold Classes 677
Chapter 21: Understanding Compatibility Issues 685
What Is Compatibility? 685
Types of Compatibility Problems 686
Avoid Using New Features 687
But Will It Work on a Mac? 689
Dealing with 64-Bit Excel 690
Creating an International Application 691
Part V: Appendix
Appendix A: VBA Statements and Function Reference 701
Index 709
