Excel® 2010 Power Programming with VBA -- Contents at a Glance -- Table of Contents -- INTRODUCTION -- Topics Covered -- What You Need to Know -- What You Need to Have -- Conventions in This Book -- What the Icons Mean -- How This Book Is Organized -- About the Companion CD-ROM -- About the Power Utility Pak Offer -- How to Use This Book -- Reach Out -- Part I: Some Essential Background -- Chapter 1: Excel 2010: Where It Came From -- A Brief History of Spreadsheets -- 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 -- Excel's User Interface -- Customizing the Display -- Data Entry -- Formulas, Functions, and Names -- Selecting Objects -- Formatting -- Protection Options -- Charts -- Shapes and SmartArt -- Database Access -- Internet Features -- Analysis Tools -- Add-Ins -- Macros and Programming -- File Format -- Excel's Help System -- Chapter 3: Formula Tricks and Techniques -- About Formulas -- Calculating Formulas -- Cell and Range References -- Using Names -- Formula Errors -- Array Formulas -- Counting and Summing Techniques -- Working with Dates and Times -- Creating Megaformulas -- Chapter 4: Understanding Excel Files -- Starting Excel -- File Types -- Working with Template Files -- Inside an Excel File -- The OfficeUI File -- The XLB File -- Add-In Files -- Excel Settings in the Registry -- Part II: Excel Application Development -- Chapter 5: What Is a Spreadsheet Application? -- Spreadsheet Applications -- The Developer and the End User -- Solving Problems with Excel -- Basic Spreadsheet Types -- Chapter 6: Essentials of Spreadsheet Application Development -- Steps for Application Development -- Determining User Needs -- Planning an Application That Meets User Needs -- Determining the Most Appropriate User Interface.

Concerning Yourself with the End User -- Other Development Issues -- Part III: Understanding Visual Basic for Applications -- Chapter 7: Introducing Visual Basic for Applications -- Getting Some BASIC Background -- Delving in to VBA -- Covering the Basics of VBA -- Introducing the Visual Basic Editor -- Working with the Project Explorer -- Working with Code Windows -- Customizing the VBE Environment -- The Macro Recorder -- About Objects and Collections -- Properties and Methods -- The Comment Object: A Case Study -- Some Useful Application Properties -- Working with Range Objects -- Things to Know about Objects -- Chapter 8: VBA Programming Fundamentals -- VBA Language Elements: An Overview -- Comments -- Variables, Data Types, and Constants -- Assignment Statements -- Arrays -- Object Variables -- User-Defined Data Types -- Built-in Functions -- Manipulating Objects and Collections -- Controlling Code Execution -- Chapter 9: Working with VBA Sub Procedures -- About Procedures -- Executing Sub Procedures -- Passing Arguments to Procedures -- Error-Handling Techniques -- A Realistic Example That Uses Sub Procedures -- Chapter 10: Creating Function Procedures -- Sub Procedures versus Function Procedures -- Why Create Custom Functions? -- An Introductory Function Example -- Function Procedures -- Function Arguments -- Function Examples -- Emulating Excel's SUM function -- Extended Date Functions -- Debugging Functions -- Dealing with the Insert Function Dialog Box -- Using Add-ins to Store Custom Functions -- Using the Windows API -- Chapter 11: VBA Programming Examples and Techniques -- Learning by Example -- Working with Ranges -- Working with Workbooks and Sheets -- VBA Techniques -- Some Useful Functions for Use in Your Code -- Some Useful Worksheet Functions -- Windows API Calls -- Part IV: Working with UserForms.

Chapter 12: Custom Dialog Box Alternatives -- Before You Create That UserForm . . . -- Using an Input Box -- The VBA MsgBox Function -- The Excel GetOpenFilename Method -- The Excel GetSaveAsFilename Method -- Prompting for a Directory -- Displaying Excel's Built-In Dialog Boxes -- Displaying a Data Form -- Chapter 13: Introducing UserForms -- How Excel Handles Custom Dialog Boxes -- Inserting a New UserForm -- Adding Controls to a UserForm -- Toolbox Controls -- Adjusting UserForm Controls -- Adjusting a Control's Properties -- Displaying a UserForm -- Closing a UserForm -- Creating a UserForm: An Example -- Understanding UserForm Events -- Referencing UserForm Controls -- Customizing the Toolbox -- Creating UserForm Templates -- A UserForm Checklist -- Chapter 14: UserForm Examples -- Creating a UserForm "Menu" -- Selecting Ranges from a UserForm -- Creating a Splash Screen -- Disabling a UserForm's Close Button -- Changing a UserForm's Size -- Zooming and Scrolling a Sheet from a UserForm -- ListBox Techniques -- Using the MultiPage Control in a UserForm -- Using an External Control -- Animating a Label -- Chapter 15: Advanced UserForm Techniques -- A Modeless Dialog Box -- Displaying a Progress Indicator -- Creating Wizards -- Emulating the MsgBox Function -- A UserForm with Movable Controls -- A UserForm with No Title Bar -- Simulating a Toolbar with a UserForm -- A Resizable UserForm -- Handling Multiple UserForm Controls with One Event Handler -- Selecting a Color in a UserForm -- Displaying a Chart in a UserForm -- Making a UserForm Semitransparent -- An Enhanced Data Form -- A Puzzle on a UserForm -- Video Poker on a UserForm -- 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 -- More about Excel Utilities -- Chapter 17: Working with Pivot Tables -- An Introductory Pivot Table Example -- Creating a More Complex Pivot Table -- Creating Multiple Pivot Tables -- Creating a Reverse Pivot Table -- Chapter 18: Working with Charts -- Getting the Inside Scoop on Charts -- Creating an Embedded Chart -- Creating a Chart on a Chart Sheet -- Using VBA to Activate a Chart -- Moving a Chart -- Using VBA to Deactivate a Chart -- Determining Whether a Chart Is Activated -- Deleting from the ChartObjects or Charts Collection -- Looping through All Charts -- Sizing and Aligning ChartObjects -- Exporting a Chart -- Changing the Data Used in a Chart -- Using VBA to Display Arbitrary Data Labels on a Chart -- Displaying a Chart in a UserForm -- Understanding Chart Events -- Discovering VBA Charting Tricks -- Animating Charts -- Creating an Interactive Chart without VBA -- Working with Sparkline Charts -- Chapter 19: Understanding Excel's Events -- What You Should Know about Events -- Getting Acquainted with Workbook-Level Events -- Examining Worksheet Events -- Checking Out Chart Events -- Monitoring with Application Events -- Using UserForm Events -- Accessing Events Not Associated with an Object -- Chapter 20: Interacting with Other Applications -- Starting an Application from Excel -- Activating an Application with Excel -- Running Control Panel Dialog Boxes -- Using Automation in Excel -- Sending Personalized E-Mail via Outlook -- Sending E-Mail Attachments from Excel -- Using SendKeys -- Chapter 21: Creating and Using Add-Ins -- What Is an Add-In? -- Understanding Excel's Add-In Manager -- Creating an Add-in -- An Add-In Example -- Comparing XLAM and XLSM Files -- Manipulating Add-Ins with VBA -- Optimizing the Performance of Add-ins -- Special Problems with Add-Ins -- Part VI: Developing Applications.

Chapter 22: Working with the Ribbon -- Ribbon Basics -- Using VBA with the Ribbon -- Customizing the Ribbon -- Creating an Old-Style Toolbar -- Chapter 23: Working with Shortcut Menus -- CommandBar Overview -- Using VBA to Customize Shortcut Menus -- Shortcut Menus and Events -- Chapter 24: Providing Help for Your Applications -- Help for Your Excel Applications -- Help Systems That Use Excel Components -- Displaying Help in a Web Browser -- Using the HTML Help System -- Associating a Help File with Your Application -- Chapter 25: Developing User-Oriented Applications -- What is a User-Oriented Application? -- The Loan Amortization Wizard -- Application Development Concepts -- Part VII: Other Topics -- Chapter 26: Compatibility Issues -- What Is Compatibility? -- Types of Compatibility Problems -- Avoid Using New Features -- But Will It Work on a Mac? -- Dealing with 64-bit Excel -- Creating an International Application -- Chapter 27: Manipulating Files with VBA -- Performing Common File Operations -- Displaying Extended File Information -- Working with Text Files -- Text File Manipulation Examples -- Zipping and Unzipping Files -- Working with ADO -- Chapter 28: Manipulating Visual Basic Components -- Introducing the IDE -- The IDE Object Model -- Displaying All Components in a VBA Project -- Listing All VBA Procedures in a Workbook -- Replacing a Module with an Updated Version -- Using VBA to Write VBA Code -- Adding Controls to a UserForm at Design Time -- Creating UserForms Programmatically -- Chapter 29: Understanding Class Modules -- What is a Class Module? -- Example: Creating a NumLock Class -- More about Class Modules -- Example: A CSV File Class -- Chapter 30: Working with Colors -- Specifying Colors -- Understanding Grayscale -- Experimenting with Colors -- Understanding Document Themes -- Working with Shape Objects.

Modifying Chart Colors.