You are previewing Excel® 2010 Power Programming with VBA.
O'Reilly logo
Excel® 2010 Power Programming with VBA

Book Description

All the methods and tools you need to successfully program with Excel.

John Walkenbach's name is synonymous with excellence in computer books that decipher complex technical topics. With this comprehensive guide, “Mr. Spreadsheet” shows you how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf.

Featuring a complete introduction to Visual Basic for Applications and fully updated for the new features of Excel 2010, this essential reference includes an analysis of Excel application development and is packed with procedures, tips, and ideas for expanding Excel's capabilities with VBA.

  • Offers an analysis of Excel application development and a complete introduction to Visual Basic for Applications (VBA)

  • Features invaluable advice from “Mr. Spreadsheet” himself (bestselling author John Walkenbach), who demonstrates all the techniques you need to create large and small Excel applications

  • Provides tips, tricks, and techniques for expanding Excel's capabilities with VBA that you won't find anywhere else

  • Includes a CD with templates and worksheets from the book

  • This power-user's guide is packed with procedures, tips, and ideas for expanding Excel's capabilities with VBA.

    Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

    Table of Contents

    1. Cover
    2. Table of Contents
    3. Title Page
    4. Introduction
    5. Part I: Some Essential Background
      1. Chapter 1: Excel 2010: Where It Came From
        1. A Brief History of Spreadsheets
        2. Why Excel Is Great for Developers
        3. Excel's Role in Microsoft's Strategy
      2. Chapter 2: Excel in a Nutshell
        1. Thinking in Terms of Objects
        2. Workbooks
        3. Excel's User Interface
        4. Customizing the Display
        5. Data Entry
        6. Formulas, Functions, and Names
        7. Selecting Objects
        8. Formatting
        9. Protection Options
        10. Charts
        11. Shapes and SmartArt
        12. Database Access
        13. Internet Features
        14. Analysis Tools
        15. Add-Ins
        16. Macros and Programming
        17. File Format
        18. Excel's Help System
      3. Chapter 3: Formula Tricks and Techniques
        1. About Formulas
        2. Calculating Formulas
        3. Cell and Range References
        4. Using Names
        5. Formula Errors
        6. Array Formulas
        7. Counting and Summing Techniques
        8. Working with Dates and Times
        9. Creating Megaformulas
      4. Chapter 4: Understanding Excel Files
        1. Starting Excel
        2. File Types
        3. Working with Template Files
        4. Inside an Excel File
        5. The OfficeUI File
        6. The XLB File
        7. Add-In Files
        8. Excel Settings in the Registry
    6. Part II: Excel Application Development
      1. Chapter 5: What Is a Spreadsheet Application?
        1. Spreadsheet Applications
        2. The Developer and the End User
        3. Solving Problems with Excel
        4. Basic Spreadsheet Types
      2. Chapter 6: Essentials of Spreadsheet Application Development
        1. Steps for Application Development
        2. Determining User Needs
        3. Planning an Application That Meets User Needs
        4. Determining the Most Appropriate User Interface
        5. Concerning Yourself with the End User
        6. Other Development Issues
    7. Part III: Understanding Visual Basic for Applications
      1. Chapter 7: Introducing Visual Basic for Applications
        1. Getting Some BASIC Background
        2. Delving in to VBA
        3. Covering the Basics of VBA
        4. Introducing the Visual Basic Editor
        5. Working with the Project Explorer
        6. Working with Code Windows
        7. Customizing the VBE Environment
        8. The Macro Recorder
        9. About Objects and Collections
        10. Properties and Methods
        11. The Comment Object: A Case Study
        12. Some Useful Application Properties
        13. Working with Range Objects
        14. Things to Know about Objects
      2. Chapter 8: VBA Programming Fundamentals
        1. VBA Language Elements: An Overview
        3. Variables, Data Types, and Constants
        4. Assignment Statements
        5. Arrays
        6. Object Variables
        7. User-Defined Data Types
        8. Built-in Functions
        9. Manipulating Objects and Collections
        10. Controlling Code Execution
      3. Chapter 9: Working with VBA Sub Procedures
        1. About Procedures
        2. Executing Sub Procedures
        3. Passing Arguments to Procedures
        4. Error-Handling Techniques
        5. A Realistic Example That Uses Sub Procedures
      4. Chapter 10: Creating Function Procedures
        1. Sub Procedures versus Function Procedures
        2. Why Create Custom Functions?
        3. An Introductory Function Example
        4. Function Procedures
        5. Function Arguments
        6. Function Examples
        7. Emulating Excel's SUM function
        8. Extended Date Functions
        9. Debugging Functions
        10. Dealing with the Insert Function Dialog Box
        11. Using Add-ins to Store Custom Functions
        12. Using the Windows API
      5. Chapter 11: VBA Programming Examples and Techniques
        1. Learning by Example
        2. Working with Ranges
        3. Working with Workbooks and Sheets
        4. VBA Techniques
        5. Some Useful Functions for Use in Your Code
        6. Some Useful Worksheet Functions
        7. Windows API Calls
    8. Part IV: Working with UserForms
      1. Chapter 12: Custom Dialog Box Alternatives
        1. Before You Create That UserForm . . .
        2. Using an Input Box
        3. The VBA MsgBox Function
        4. The Excel GetOpenFilename Method
        5. The Excel GetSaveAsFilename Method
        6. Prompting for a Directory
        7. Displaying Excel's Built-In Dialog Boxes
        8. Displaying a Data Form
      2. Chapter 13: Introducing UserForms
        1. How Excel Handles Custom Dialog Boxes
        2. Inserting a New UserForm
        3. Adding Controls to a UserForm
        4. Toolbox Controls
        5. Adjusting UserForm Controls
        6. Adjusting a Control's Properties
        7. Displaying a UserForm
        8. Closing a UserForm
        9. Creating a UserForm: An Example
        10. Understanding UserForm Events
        11. Referencing UserForm Controls
        12. Customizing the Toolbox
        13. Creating UserForm Templates
        14. A UserForm Checklist
      3. Chapter 14: UserForm Examples
        1. Creating a UserForm “Menu”
        2. Selecting Ranges from a UserForm
        3. Creating a Splash Screen
        4. Disabling a UserForm's Close Button
        5. Changing a UserForm's Size
        6. Zooming and Scrolling a Sheet from a UserForm
        7. ListBox Techniques
        8. Using the MultiPage Control in a UserForm
        9. Using an External Control
        10. Animating a Label
      4. Chapter 15: Advanced UserForm Techniques
        1. A Modeless Dialog Box
        2. Displaying a Progress Indicator
        3. Creating Wizards
        4. Emulating the MsgBox Function
        5. A UserForm with Movable Controls
        6. A UserForm with No Title Bar
        7. Simulating a Toolbar with a UserForm
        8. A Resizable UserForm
        9. Handling Multiple UserForm Controls with One Event Handler
        10. Selecting a Color in a UserForm
        11. Displaying a Chart in a UserForm
        12. Making a UserForm Semitransparent
        13. An Enhanced Data Form
        14. A Puzzle on a UserForm
        15. Video Poker on a UserForm
    9. Part V: Advanced Programming Techniques
      1. Chapter 16: Developing Excel Utilities with VBA
        1. About Excel Utilities
        2. Using VBA to Develop Utilities
        3. What Makes a Good Utility?
        4. Text Tools: The Anatomy of a Utility
        5. More about Excel Utilities
      2. Chapter 17: Working with Pivot Tables
        1. An Introductory Pivot Table Example
        2. Creating a More Complex Pivot Table
        3. Creating Multiple Pivot Tables
        4. Creating a Reverse Pivot Table
      3. Chapter 18: Working with Charts
        1. Getting the Inside Scoop on Charts
        2. Creating an Embedded Chart
        3. Creating a Chart on a Chart Sheet
        4. Using VBA to Activate a Chart
        5. Moving a Chart
        6. Using VBA to Deactivate a Chart
        7. Determining Whether a Chart Is Activated
        8. Deleting from the ChartObjects or Charts Collection
        9. Looping through All Charts
        10. Sizing and Aligning ChartObjects
        11. Exporting a Chart
        12. Changing the Data Used in a Chart
        13. Using VBA to Display Arbitrary Data Labels on a Chart
        14. Displaying a Chart in a UserForm
        15. Understanding Chart Events
        16. Discovering VBA Charting Tricks
        17. Animating Charts
        18. Creating an Interactive Chart without VBA
        19. Working with Sparkline Charts
      4. Chapter 19: Understanding Excel's Events
        1. What You Should Know about Events
        2. Getting Acquainted with Workbook-Level Events
        3. Examining Worksheet Events
        4. Checking Out Chart Events
        5. Monitoring with Application Events
        6. Using UserForm Events
        7. Accessing Events Not Associated with an Object
      5. Chapter 20: Interacting with Other Applications
        1. Starting an Application from Excel
        2. Activating an Application with Excel
        3. Running Control Panel Dialog Boxes
        4. Using Automation in Excel
        5. Sending Personalized E-Mail via Outlook
        6. Sending E-Mail Attachments from Excel
        7. Using SendKeys
      6. Chapter 21: Creating and Using Add-Ins
        1. What Is an Add-In?
        2. Understanding Excel's Add-In Manager
        3. Creating an Add-in
        4. An Add-In Example
        5. Comparing XLAM and XLSM Files
        6. Manipulating Add-Ins with VBA
        7. Optimizing the Performance of Add-ins
        8. Special Problems with Add-Ins
    10. Part VI: Developing Applications
      1. Chapter 22: Working with the Ribbon
        1. Ribbon Basics
        2. Using VBA with the Ribbon
        3. Customizing the Ribbon
        4. Creating an Old-Style Toolbar
      2. Chapter 23: Working with Shortcut Menus
        1. CommandBar Overview
        2. Using VBA to Customize Shortcut Menus
        3. Shortcut Menus and Events
      3. Chapter 24: Providing Help for Your Applications
        1. Help for Your Excel Applications
        2. Help Systems That Use Excel Components
        3. Displaying Help in a Web Browser
        4. Using the HTML Help System
        5. Associating a Help File with Your Application
      4. Chapter 25: Developing User-Oriented Applications
        1. What is a User-Oriented Application?
        2. The Loan Amortization Wizard
        3. Application Development Concepts
    11. Part VII: Other Topics
      1. Chapter 26: Compatibility Issues
        1. What Is Compatibility?
        2. Types of Compatibility Problems
        3. Avoid Using New Features
        4. But Will It Work on a Mac?
        5. Dealing with 64-bit Excel
        6. Creating an International Application
      2. Chapter 27: Manipulating Files with VBA
        1. Performing Common File Operations
        2. Displaying Extended File Information
        3. Working with Text Files
        4. Text File Manipulation Examples
        5. Zipping and Unzipping Files
        6. Working with ADO
      3. Chapter 28: Manipulating Visual Basic Components
        1. Introducing the IDE
        2. The IDE Object Model
        3. Displaying All Components in a VBA Project
        4. Listing All VBA Procedures in a Workbook
        5. Replacing a Module with an Updated Version
        6. Using VBA to Write VBA Code
        7. Adding Controls to a UserForm at Design Time
        8. Creating UserForms Programmatically
      4. Chapter 29: Understanding Class Modules
        1. What is a Class Module?
        2. Example: Creating a NumLock Class
        3. More about Class Modules
        4. Example: A CSV File Class
      5. Chapter 30: Working with Colors
        1. Specifying Colors
        2. Understanding Grayscale
        3. Experimenting with Colors
        4. Understanding Document Themes
        5. Working with Shape Objects
        6. Modifying Chart Colors
      6. Chapter 31: Frequently Asked Questions about Excel Programming
        1. Getting the Scoop on FAQs
        2. General Excel Questions
        3. The Visual Basic Editor
        4. Procedures
        5. Functions
        6. Objects, Properties, Methods, and Events
        7. UserForms
        8. Add-Ins
        9. User Interface
    12. Part VIII: Appendixes
      1. Appendix A: Excel Resources Online
        1. The Excel Help System
        2. Microsoft Technical Support
        3. Internet Newsgroups
        4. Internet Web sites
      2. Appendix B: VBA Statements and Functions Reference
        1. Invoking Excel functions in VBA instructions
      3. Appendix C: VBA Error Codes
      4. Appendix D: What's on the CD-ROM
        1. System Requirements
        2. Using the CD
        3. Files and Software on the CD
        4. Troubleshooting