Introduction 1 Part I: Some Essential Background Chapter 1: Excel in a Nutshell 11 About Excel 11 Thinking in Terms of Objects 11 Workbooks 12 Excel''s User Interface 16 Data Entry 25 Formulas, Functions, and Names 25 Selecting Objects 27 Formatting 28 Protection Options 29 Charts 32 Shapes and SmartArt 33 Database Access 33 Internet Features 35 Analysis Tools 36 Add-Ins 36 Macros and Programming 37 File Format 37 Excel''s Help System 38 Chapter 2: Formula Tricks and Techniques 39 About Formulas 39 Calculating Formulas 40 Cell and Range References 41 Using Names 45 Formula Errors 51 Excel Auditing Tools 52 Array Formulas 61 Counting and Summing Techniques 64 Lookup Formulas 66 Working with Dates and Times 69 Creating Megaformulas 71 Chapter 3: Understanding Excel Files 75 Starting Excel 75 File Types 76 Excel File Compatibility 80 Protected View 81 Using AutoRecover 82 Working with Template Files 83 Inside an Excel File 86 The OfficeUI File 91 The XLB File 92 Add-In Files 93 Excel Settings in the Registry 93 Chapter 4: Essentials of Spreadsheet Application Development 97 What Is a Spreadsheet Application? 97 Steps for Application Development 98 Determining User Needs 99 Planning an Application That Meets User Needs 100 Determining the Most Appropriate User Interface 102 Concerning Yourself with the End User 106 Other Development Issues 113 Part II: Understanding Visual Basic for Applications Chapter 5: Introducing Visual Basic for Applications 119 Getting Some BASIC Background 119 Delving into VBA 120 Covering the Basics of VBA 121 Introducing Visual Basic Editor 124 Working with Project Explorer 127 Working with Code Windows 129 Customizing the VBE Environment 137 The Macro Recorder 143 About Objects and Collections 151 Properties and Methods 153 The Comment Object: A Case Study 156 Some Useful Application Properties 164 Working with Range Objects 166 Things to Know about Objects 173 Chapter 6: VBA Programming Fundamentals 177 VBA Language Elements: An Overview 177 Comments 179 Variables, Data Types, and Constants 181 Assignment Statements 195 Arrays 197 Object Variables 199 User-Defined Data Types 201 Built-in Functions 202 Manipulating Objects and Collections 205 Controlling Code Execution 208 Chapter 7: Working with VBA Sub Procedures 227 About Procedures 227 Executing Sub Procedures 230 Passing Arguments to Procedures 241 Error-Handling Techniques 245 A Realistic Example That Uses Sub Procedures 250 Chapter 8: Creating Function Procedures 269 Sub Procedures versus Function Procedures 269 Why Create Custom Functions? 270 An Introductory Function Example 271 Function Procedures 275 Function Arguments 281 Function Examples 282 Emulating Excel''s SUM function 298 Extended Date Functions 302 Debugging Functions 304 Dealing with the Insert Function Dialog Box 305 Using Add-ins to Store Custom Functions 311 Using the Windows API 311 Chapter 9: VBA Programming Examples and Techniques 317 Learning by Example 317 Working with Ranges 318 Working with Workbooks and Sheets 348 VBA Techniques 353 Some Useful Functions for Use in Your Code 361 Some Useful Worksheet Functions 367 Windows API Calls 385 Part III: Working with UserForms Chapter 10: Custom Dialog Box Alternatives 395 Before You Create That UserForm 395 Using an Input Box 395 The VBA MsgBox Function 401 The Excel GetOpenFilename Method 406 The Excel GetSaveAsFilename Method 410 Prompting for a Directory 410 Displaying Excel''s Built-In Dialog Boxes 411 Displaying a Data Form 413 Chapter 11: Introducing UserForms 417 How Excel Handles Custom Dialog Boxes 417 Inserting a New UserForm 418 Adding Controls to a UserForm 419 Toolbox Controls 419 Adjusting UserForm Controls 424 Adjusting a Control''s Properties 425 Displaying a UserForm 429 Closing a UserForm 432 Creating a UserForm: An Example 433 Understanding UserForm Events 440 Referencing UserForm Controls 446 Customizing the Toolbox 448 Creating UserForm Templates 450 A UserForm Checklist 451 Chapter 12: UserForm Examples 453 Creating a UserForm "Menu" 453 Selecting Ranges from a UserForm 456 Creating a Splash Screen 458 Disabling a UserForm''s Close Button 460 Changing a UserForm''s Size 460 Zooming and Scrolling a Sheet from a UserForm 462 ListBox Techniques 464 Using the MultiPage Control in a UserForm 483 Using an External Control 484 Animating a Label 486 Chapter 13: Advanced UserForm Techniques 491 A Modeless Dialog Box 492 Displaying a Progress Indicator 496 Creating Wizards 505 Emulating the MsgBox Function 511 A UserForm with Movable Controls 515 A UserForm with No Title Bar 517 Simulating a Toolbar with a UserForm 519 Emulating a Task Pane with a UserForm 521 A Resizable UserForm 522 Handling Multiple UserForm Controls with One Event Handler 527 Selecting a Color in a UserForm 530 Displaying a Chart in a UserForm 532 Making a UserForm Semitransparent 533 An Enhanced Data Form 535 A Puzzle on a UserForm 538 Video Poker on a UserForm 539 Part IV: Advanced Programming Techniques Chapter 14: Developing Excel Utilities with VBA 543 About Excel Utilities 543 Using VBA to Develop Utilities 544 What Makes a Good Utility? 545 Text Tools: The Anatomy of a Utility 545 More about Excel Utilities 563 Chapter 15: Working with Pivot Tables 565 An Introductory Pivot Table Example 565 Creating a More Complex Pivot Table 571 Creating Multiple Pivot Tables 576 Creating a Reverse Pivot Table 579 Chapter 16: Working with Charts 583 Getting the Inside Scoop on Charts 583 Creating an Embedded Chart 586 Creating a Chart on a Chart Sheet 588 Modifying Charts 589 Using VBA to Activate a Chart 591 Moving a Chart 592 Using VBA to Deactivate a Chart 592 Determining Whether a Chart Is Activated 593 Deleting from the ChartObjects or Charts Collection 594 Looping through All Charts 596 Sizing and Aligning ChartObjects 598 Creating Lots of Charts 600 Exporting a Chart 603 Changing the Data Used in a Chart 605 Using VBA to Display Arbitrary Data Labels on a Chart 611 Displaying a Chart in a UserForm 615 Understanding Chart Events 617 Discovering VBA Charting Tricks 625 Animating Charts 632 Creating an Interactive Chart without VBA 637 Working with Sparkline Charts 641 Chapter 17: Understanding Excel''s Events 645 What You Should Know about Events 645 Getting Acquainted with Workbook-Level Events 653 Examining Worksheet Events 661 Checking Out Chart Events 670 Monitoring with Application Events 672 Using UserForm Events 676 Accessing Events Not Associated with an Object 678 Chapter 18: Interacting with Other Applications 685 Starting an Application from Excel 685 Activating an Application with Excel 690 Running Control Panel Dialog Boxes 691 Using Automation in Excel 693 Sending Personalized E-Mail via Outlook 704 Sending E-Mail Attachments from Excel 707 Chapter 19: Creating and Using Add-Ins 711 What Is an Add-In? 711 Understanding Excel''s Add-In Manager 714 Creating an Add-in 716 An Add-In Example 717 Comparing XLAM and XLSM Files 723 Manipulating Add-Ins with VBA 729 Optimizing the Performance of Add-ins 735 Special Problems with Add-Ins 736 Part V: Developing Applications Chapter 20: Working with the Ribbon 743 Ribbon Basics 743 Using VBA with the Ribbon 747 Customizing the Ribbon 753 Creating an Old-Style Toolbar 775 Chapter 21: Working with Shortcut Menus 779 CommandBar Overview 779 Using VBA to Customize Shortcut Menus 786 Shortcut Menus and Events 796 Chapter 22: Providing Help for Your Applications 801 Help for Your Excel Applications 801 Help Systems That Use Excel Components 804 Displaying Help in a Web Browser 812 Using the HTML Help System 814 Chapter 23: Developing User-Oriented Applications 821 What Is a User-Oriented Application? 821 the Loan Amortization Wizard 822 Application Development Concepts 832 Part VI: Other Topics Chapter 24: Compatibility Issues 837 What Is Compatibility? 837 Types of Compatibility Problems 838 Avoid Using New Features 839 But Will It Work on a Mac? 841 Dealing with 64-bit Excel 842 Creating an International Application 843 Chapter 25: Manipulating Files with VBA 851 Performing Commo.