Introduction                                       1   (10)
  Part I Some Essential Background
    Chapter 1 Excel 2010: Where It Came From       11  (12)
      A Brief History of Spreadsheets              11  (9)
        It all started with VisiCalc               11  (1)
        Lotus 1-2-3                                12  (2)
        Quattro Pro                                14  (1)
        Microsoft Excel                            15  (5)
        Current Competition                        20  (1)
      Why Excel Is Great for Developers            20  (2)
      Excel's Role in Microsoft's Strategy         22  (1)
    Chapter 2 Excel in a Nutshell                  23  (30)
      Thinking in Terms of Objects                 23  (1)
      Workbooks                                    24  (4)
        Worksheets                                 24  (2)
        Chart sheets                               26  (1)
        XLM macro sheets                           26  (2)
        Excel 5/95 dialog sheets                   28  (1)
      Excel's User Interface                       28  (9)
        About the Ribbon                           28  (6)
        Shortcut menus and the Mini Toolbar        34  (1)
        Dialog boxes                               35  (1)
        Keyboard shortcuts                         36  (1)
        Smart Tags                                 36  (1)
        Task pane                                  36  (1)
      Customizing the Display                      37  (1)
      Data Entry                                   38  (1)
      Formulas, Functions, and Names               38  (2)
      Selecting Objects                            40  (1)
      Formatting                                   40  (2)
      Protection Options                           42  (2)
        Protecting formulas from being             42  (1)
        overwritten
        Protecting a workbook's structure          43  (1)
        Applying password protection to a          43  (1)
        workbook
        Protecting VBA code with a password        43  (1)
      Charts                                       44  (1)
      Shapes and SmartArt                          45  (1)
      Database Access                              46  (1)
        Worksheet databases                        46  (1)
        External databases                         47  (1)
      Internet Features                            47  (1)
      Analysis Tools                               48  (2)
      Add-Ins                                      50  (1)
      Macros and Programming                       50  (1)
      File Format                                  50  (1)
      Excel's Help System                          51  (2)
    Chapter 3 Formula Tricks and Techniques        53  (24)
      About Formulas                               53  (1)
      Calculating Formulas                         54  (1)
      Cell and Range References                    55  (3)
        Why use references that aren't relative?   55  (1)
        About R1C1 notation                        56  (1)
        Referencing other sheets or workbooks      57  (1)
      Using Names                                  58  (7)
        Naming cells and ranges                    59  (1)
        Applying names to existing references      60  (1)
        Intersecting names                         61  (1)
        Naming columns and rows                    61  (1)
        Scoping names                              61  (1)
        Naming constants                           62  (1)
        Naming formulas                            63  (2)
        Naming objects                             65  (1)
      Formula Errors                               65  (1)
      Array Formulas                               66  (3)
        An array formula example                   66  (1)
        An array formula calendar                  67  (1)
        Array formula pros and cons                68  (1)
      Counting and Summing Techniques              69  (2)
        Counting formula examples                  70  (1)
        Summing formula examples                   70  (1)
        Other counting tools                       71  (1)
      Working with Dates and Times                 71  (3)
        Entering dates and times                   72  (1)
        Using pre-1900 dates                       73  (1)
      Creating Megaformulas                        74  (3)
    Chapter 4 Understanding Excel Files            77  (24)
      Starting Excel                               77  (3)
      File Types                                   80  (3)
        Excel file formats                         80  (1)
        Text file formats                          81  (1)
        Database file formats                      81  (1)
        Other file formats                         82  (1)
      Working with Template Files                  83  (4)
        Viewing templates                          83  (1)
        Creating templates                         84  (2)
        Creating workbook templates                86  (1)
      Inside an Excel File                         87  (4)
        Dissecting a file                          87  (4)
        Why is the file format important?          91  (1)
      The OfficeUI File                            91  (1)
      The XLB File                                 92  (1)
      Add-In Files                                 93  (1)
      Excel Settings in the Registry               94  (7)
        About the Registry                         94  (1)
        Excel's settings                           95  (6)
  Part II Excel Application Development
    Chapter 5 What Is a Spreadsheet Application?   101 (10)
      Spreadsheet Applications                     101 (1)
      The Developer and the End-User               102 (3)
        Who are developers? What do they do?       102 (2)
        Classifying spreadsheet users              104 (1)
        The audience for spreadsheet               104 (1)
        applications
      Solving Problems with Excel                  105 (1)
      Basic Spreadsheet Types                      106 (5)
        Quick-and-dirty spreadsheets               106 (1)
        For-your-eyes-only spreadsheets            107 (1)
        Single-user applications                   107 (1)
        Spaghetti applications                     107 (1)
        Utility applications                       108 (1)
        Add-ins that contain worksheet functions   108 (1)
        Single-block budgets                       109 (1)
        What-if models                             109 (1)
        Data storage and access spreadsheets       109 (1)
        Database front ends                        110 (1)
        Turnkey applications                       110 (1)
    Chapter 6 Essentials of Spreadsheet            111 (24)
    Application Development
      Steps for Application Development            111 (1)
      Determining User Needs                       112 (1)
      Planning an Application That Meets User      113 (2)
      Needs
      Determining the Most Appropriate User        115 (7)
      Interface
        Customizing the Ribbon                     118 (1)
        Customizing shortcut menus                 118 (1)
        Creating shortcut keys                     119 (1)
        Creating custom dialog boxes               119 (1)
        Using ActiveX controls on a worksheet      120 (2)
        Executing the development effort           122 (1)
      Concerning Yourself with the End User        122 (7)
        Testing the application                    122 (2)
        Making the application bulletproof         124 (2)
        Making the application aesthetically       126 (1)
        appealing and intuitive
        Creating a user Help system                127 (1)
        Documenting the development effort         128 (1)
        Distributing the application to the user   128 (1)
        Updating the application when necessary    129 (1)
      Other Development Issues                     129 (6)
        The user's installed version of Excel      130 (1)
        Language issues                            130 (1)
        System speed                               130 (1)
        Video modes                                131 (4)
  Part III Understanding Visual Basic for
  Applications
    Chapter 7 Introducing Visual Basic for         135 (58)
    Applications
      Getting Some BASIC Background                135 (1)
      Delving in to VBA                            136 (1)
        Object models                              136 (1)
        VBA versus XLM                             136 (1)
      Covering the Basics of VBA                   137 (3)
      Introducing the Visual Basic Editor          140 (3)
        Displaying Excel's Developer tab           141 (1)
        Activating the VBE                         141 (1)
        The VBE windows                            142 (1)
      Working with the Project Explorer            143 (2)
        Adding a new VBA module                    144 (1)
        Removing a VBA module                      145 (1)
        Exporting and importing objects            145 (1)
      Working with Code Windows                    145 (8)
        Minimizing and maximizing windows          146 (1)
        Storing VBA code                           146 (1)
        Entering VBA code                          147 (6)
      Customizing the VBE Environment              153 (6)
        Using the Editor tab                       154 (2)
        Using the Editor Format tab                156 (1)
        Using the General tab                      157 (1)
        Using the Docking tab                      158 (1)
      The Macro Recorder                           159 (8)
        What the macro recorder actually records   160 (1)
        Relative or absolute recording?            161 (3)
        Recording options                          164 (1)
        Cleaning up recorded macros                165 (2)
      About Objects and Collections                167 (2)
        The object hierarchy                       167 (1)
        About collections                          168 (1)
        Referring to objects                       168 (1)
      Properties and Methods                       169 (3)
        Object properties                          169 (1)
        Object methods                             170 (2)
      The Comment Object: A Case Study             172 (8)
        Viewing Help for the Comment object        173 (1)
        Properties of a Comment object             174 (1)
        Methods of a Comment object                175 (1)
        The Comments collection                    175 (1)
        About the Comment property                 176 (1)
        Objects within a Comment object            177 (1)
        Determining whether a cell has a comment   178 (1)
        Adding a new Comment object                179 (1)
      Some Useful Application Properties           180 (2)
      Working with Range Objects                   182 (6)
        The Range property                         182 (2)
        The Cells property                         184 (3)
        The Offset property                        187 (1)
      Things to Know about Objects                 188 (5)
        Essential concepts to remember             188 (1)
        Learning more about objects and,           189 (4)
        properties
    Chapter 8 VBA Programming Fundamentals         193 (48)
      VBA Language Elements: An Overview           193 (2)
      Comments                                     195 (2)
      Variables, Data Types, and Constants         197 (13)
        Defining data types                        198 (3)
        Declaring variables                        201 (2)
        Scoping variables                          203 (3)
        Working with constants                     206 (3)
        Working with strings                       209 (1)
        Working with dates                         209 (1)
      Assignment Statements                        210 (3)
      Arrays                                       213 (2)
        Declaring arrays                           213 (1)
        Declaring multidimensional arrays          214 (1)
        Declaring dynamic arrays                   214 (1)
      Object Variables                             215 (1)
      User-Defined Data Types                      216 (1)
      Built-in Functions                           217 (3)
      Manipulating Objects and Collections         220 (3)
        With-End With constructs                   220 (1)
        For Each-Next constructs                   221 (2)
      Controlling Code Execution                   223 (18)
        GoTo statements                            224 (1)
        If-Then constructs                         224 (5)
        Select Case constructs                     229 (3)
        Looping blocks of instructions             232 (9)
    Chapter 9 Working with VBA Sub Procedures      241 (40)
      About Procedures                             241 (3)
        Declaring a Sub procedure                  242 (1)
        Scoping a procedure                        243 (1)
      Executing Sub Procedures                     244 (11)
        Executing a procedure with the Run         245 (1)
        Sub/UserForm command
        Executing a procedure from the Macro       245 (1)
        dialog box
        Executing a procedure with a               246 (1)
        Ctrl+shortcut key combination
        Executing a procedure from the Ribbon      247 (1)
        Executing a procedure from a customized    247 (1)
        shortcut menu
        Executing a procedure from another         248 (5)
        procedure
        Executing a procedure by clicking an       253 (1)
        object
        Executing a procedure when an event        254 (1)
        occurs
        Executing a procedure from the             254 (1)
        Immediate window
      Passing Arguments to Procedures              255 (4)
      Error-Handling Techniques                    259 (5)
        Trapping errors                            259 (2)
        Error-handling examples                    261 (3)
      A Realistic Example That Uses Sub            264 (17)
      Procedures
        The goal                                   264 (1)
        Project requirements                       264 (1)
        What you know                              265 (1)
        The approach                               265 (1)
        What you need to know                      266 (1)
        Some preliminary recording                 266 (2)
        Initial setup                              268 (1)
        Code writing                               269 (1)
        Writing the Sort procedure                 270 (4)
        More testing                               274 (1)
        Fixing the problems                        275 (4)
        Utility availability                       279 (1)
        Evaluating the project                     279 (2)
    Chapter 10 Creating Function Procedures        281 (44)
      Sub Procedures versus Function Procedures    281 (1)
      Why Create Custom Functions?                 282 (1)
      An Introductory Function Example             282 (5)
        Using the function in a worksheet          283 (1)
        Using the function in a VBA procedure      284 (1)
        Analyzing the custom function              285 (2)
      Function Procedures                          287 (5)
        A function's scope                         288 (1)
        Executing function procedures              288 (4)
      Function Arguments                           292 (1)
      Function Examples                            293 (15)
        Functions with no argument                 293 (2)
        A function with one argument               295 (3)
        A function with two arguments              298 (1)
        A function with an array argument          299 (1)
        A function with optional arguments         300 (2)
        A function that returns a VBA array        302 (3)
        A function that returns an error value     305 (2)
        A function with an indefinite number of    307 (1)
        arguments
      Emulating Excel's SUM function               308 (3)
      Extended Date Functions                      311 (2)
      Debugging Functions                          313 (1)
      Dealing with the Insert Function Dialog      314 (5)
      Box
        Using the MacroOptions method              315 (2)
        Specifying a function category             317 (1)
        Adding a function description manually     318 (1)
      Using Add-ins to Store Custom Functions      319 (1)
      Using the Windows API                        320 (5)
        Windows API examples                       321 (1)
        Determining the Windows directory          321 (1)
        Detecting the Shift key                    322 (1)
        Learning more about API functions          323 (2)
    Chapter 11 VBA Programming Examples and        325 (74)
    Techniques
      Learning by Example                          325 (1)
      Working with Ranges                          326 (27)
        Copying a range                            326 (2)
        Moving a range                             328 (1)
        Copying a variably sized range             328 (2)
        Selecting or otherwise identifying         330 (2)
        various types of ranges
        Prompting for a cell value                 332 (1)
        Entering a value in the next empty cell    333 (1)
        Pausing a macro to get a user-selected     334 (2)
        range
        Counting selected cells                    336 (1)
        Determining the type of selected range     337 (2)
        Looping through a selected range           339 (3)
        efficiently
        Deleting all empty rows                    342 (1)
        Duplicating rows a variable number of      342 (2)
        times
        Determining whether a range is             344 (1)
        contained in another range
        Determining a cell's data type             345 (1)
        Reading and writing ranges                 346 (1)
        A better way to write to a range           347 (2)
        Transferring one-dimensional arrays        349 (1)
        Transferring a range to a variant array    349 (1)
        Selecting cells by value                   350 (2)
        Copying a noncontiguous range              352 (1)
      Working with Workbooks and Sheets            353 (4)
        Saving all workbooks                       354 (1)
        Saving and closing all workbooks           354 (1)
        Hiding all but the selection               354 (2)
        Synchronizing worksheets                   356 (1)
      VBA Techniques                               357 (8)
        Toggling a Boolean property                357 (1)
        Determining the number of printed pages    358 (1)
        Displaying the date and time               358 (2)
        Getting a list of fonts                    360 (2)
        Sorting an array                           362 (1)
        Processing a series of files               363 (2)
      Some Useful Functions for Use in Your Code   365 (5)
        The FileExists function                    365 (1)
        The FileNameOnly function                  365 (1)
        The PathExists function                    366 (1)
        The RangeNameExists function               366 (2)
        The SheetExists function                   368 (1)
        The WorkbooklsOpen function                368 (1)
        Retrieving a value from a closed           368 (2)
        workbook
      Some Useful Worksheet Functions              370 (16)
        Returning cell formatting information      370 (2)
        A talking worksheet                        372 (1)
        Displaying the date when a file was        372 (1)
        saved or printed
        Understanding object parents               373 (1)
        Counting cells between two values          374 (1)
        Determining the last non-empty cell in     375 (2)
        a column or row
        Does a string match a pattern?             377 (1)
        Extracting the nth element from a string   378 (1)
        Spelling out a number                      379 (1)
        A multifunctional function                 380 (1)
        The SheetOffset function                   381 (1)
        Returning the maximum value across all     381 (2)
        worksheets
        Returning an array of nonduplicated        383 (1)
        random integers
        Randomizing a range                        384 (2)
      Windows API Calls                            386 (13)
        Determining file associations              386 (1)
        Determining disk drive information         387 (1)
        Determining default printer information    388 (1)
        Determining video display information      389 (1)
        Adding sound to your applications          390 (2)
        Reading from and writing to the Registry   392 (7)
  Part IV Working with UserForms
    Chapter 12 Custom Dialog Box Alternatives      399 (20)
      Before You Create That UserForm              399 (1)
      Using an Input Box                           399 (5)
        The VBA InputBox function                  400 (2)
        The Excel InputBox method                  402 (2)
      The VBA MsgBox Function                      404 (5)
      The Excel GetOpenFilename Method             409 (3)
      The Excel GetSaveAsFilename Method           412 (1)
      Prompting for a Directory                    413 (1)
      Displaying Excel's Built-in Dialog Boxes     413 (3)
      Displaying a Data Form                       416 (3)
        Making the data form accessible            416 (2)
        Displaying a data form by using VBA        418 (1)
    Chapter 13 Introducing UserForms               419 (36)
      How Excel Handles Custom Dialog Boxes        419 (1)
      Inserting a New UserForm                     420 (1)
      Adding Controls to a UserForm                421 (1)
      Toolbox Controls                             422 (4)
        CheckBox                                   423 (1)
        ComboBox                                   423 (1)
        CommandButton                              423 (1)
        Frame                                      423 (1)
        Image                                      423 (1)
        Label                                      423 (1)
        ListBox                                    424 (1)
        MultiPage                                  424 (1)
        OptionButton                               424 (1)
        RefEdit                                    424 (1)
        ScrollBar                                  424 (1)
        SpinButton                                 424 (1)
        TabStrip                                   425 (1)
        TextBox                                    425 (1)
        ToggleButton                               426 (1)
      Adjusting UserForm Controls                  426 (1)
      Adjusting a Control's Properties             426 (6)
        Using the Properties window                428 (1)
        Common properties                          429 (1)
        Accommodating keyboard users               430 (2)
      Displaying a UserForm                        432 (2)
        Displaying a modeless UserForm             433 (1)
        Displaying a UserForm based on a           433 (1)
        variable
        Loading a UserForm                         433 (1)
        About event-handler procedures             433 (1)
      Closing a UserForm                           434 (1)
      Creating a UserForm: An Example              435 (7)
        Creating the UserForm                      436 (2)
        Writing code to display the dialog box     438 (1)
        Testing the dialog box                     439 (1)
        Adding event-handler procedures            440 (1)
        Validating the data                        441 (1)
        The finished dialog box                    442 (1)
      Understanding UserForm Events                442 (6)
        Learning about events                      442 (1)
        UserForm events                            443 (1)
        SpinButton events                          444 (2)
        Pairing a SpinButton with a TextBox        446 (2)
      Referencing UserForm Controls                448 (2)
      Customizing the Toolbox                      450 (2)
        Adding new pages to the Toolbox            450 (1)
        Customizing or combining controls          450 (1)
        Adding new ActiveX controls                451 (1)
      Creating UserForm Templates                  452 (1)
      A UserForm Checklist                         453 (2)
    Chapter 14 UserForm Examples                   455 (38)
      Creating a UserForm "Menu"                   455 (2)
        Using CommandButtons in a UserForm         455 (1)
        Using a ListBox in a UserForm              456 (1)
      Selecting Ranges from a UserForm             457 (2)
      Creating a Splash Screen                     459 (2)
      Disabling a UserForm's Close Button          461 (1)
      Changing a UserForm's Size                   462 (2)
      Zooming and Scrolling a Sheet from a         464 (2)
      UserForm
      ListBox Techniques                           466 (19)
        Adding items to a ListBox control          467 (5)
        Determining the selected item in a         472 (1)
        ListBox
        Determining multiple selections in a       472 (2)
        ListBox
        Multiple lists in a single ListBox         474 (1)
        ListBox item transfer                      474 (2)
        Moving items in a ListBox                  476 (2)
        Working with multicolumn ListBox           478 (2)
        controls
        Using a ListBox to select worksheet rows   480 (2)
        Using a ListBox to activate a sheet        482 (3)
      Using the MultiPage Control in a UserForm    485 (1)
      Using an External Control                    486 (3)
      Animating a Label                            489 (4)
    Chapter 15 Advanced UserForm Techniques        493 (50)
      A Modeless Dialog Box                        493 (4)
      Displaying a Progress Indicator              497 (10)
        Creating a stand-alone progress            498 (4)
        indicator
        Showing a progress indicator by using a    502 (3)
        MultiPage control
        Showing a progress indicator without       505 (2)
        using a MultiPage control
      Creating Wizards                             507 (6)
        Setting up the MultiPage control for       508 (1)
        the wizard
        Adding the buttons to the wizard's         508 (1)
        UserForm
        Programming the wizard's buttons           508 (2)
        Programming dependencies in a wizard       510 (2)
        Performing the task with the wizard        512 (1)
      Emulating the MsgBox Function                513 (4)
        MsgBox emulation: MyMsgBox code            514 (1)
        How the MyMsgBox function works            515 (1)
        Using the MyMsgBox function                516 (1)
      A UserForm with Movable Controls             517 (1)
      A UserForm with No Title Bar                 518 (1)
      Simulating a Toolbar with a UserForm         519 (2)
      A Resizable UserForm                         521 (5)
      Handling Multiple UserForm Controls with     526 (3)
      One Event Handler
      Selecting a Color in a UserForm              529 (2)
      Displaying a Chart in a UserForm             531 (1)
        Saving a chart as a GIF file               532 (1)
        Changing the Image control Picture         532 (1)
        property
      Making a UserForm Semitransparent            532 (2)
      An Enhanced Data Form                        534 (3)
        About the Enhanced Data Form               536 (1)
        Installing the Enhanced Data Form add-in   537 (1)
      A Puzzle on a UserForm                       537 (1)
      Video Poker on a UserForm                    538 (5)
  Part V Advanced Programming Techniques
    Chapter 16 Developing Excel Utilities with     543 (22)
    VBA
      About Excel Utilities                        543 (1)
      Using VBA to Develop Utilities               544 (1)
      What Makes a Good Utility?                   545 (1)
      Text Tools: The Anatomy of a Utility         545 (18)
        Background for Text Tools                  546 (1)
        Project goals for Text Tools               547 (1)
        The Text Tools workbook                    547 (1)
        How the Text Tools utility works           548 (1)
        The UserForm for the Text Tools utility    548 (2)
        The Module1 VBA module                     550 (2)
        The UserForm1 code module                  552 (2)
        Making the Text Tools utility efficient    554 (1)
        Saving the Text Tools utility settings     555 (2)
        Implementing Undo                          557 (2)
        Displaying the Help file                   559 (1)
        Adding the RibbonX code                    560 (2)
        Post-mortem of the project                 562 (1)
        Understand the Text Tools utility          562 (1)
      More about Excel Utilities                   563 (2)
    Chapter 17 Working with Pivot Tables           565 (18)
      An Introductory Pivot Table Example          565 (6)
        Creating a pivot table                     566 (2)
        Examining the recorded code for the        568 (1)
        pivot table
        Cleaning up the recorded pivot table       568 (3)
        code
      Creating a More Complex Pivot Table          571 (5)
        The code that created the pivot table      573 (1)
        How the more complex pivot table works     574 (2)
      Creating Multiple Pivot Tables               576 (3)
      Creating a Reverse Pivot Table               579 (4)
    Chapter 18 Working with Charts                 583 (56)
      Getting the Inside Scoop on Charts           583 (3)
        Chart locations                            584 (1)
        The macro recorder and charts              584 (1)
        The Chart object model                     585 (1)
      Creating an Embedded Chart                   586 (2)
      Creating a Chart on a Chart Sheet            588 (1)
      Using VBA to Activate a Chart                589 (1)
      Moving a Chart                               590 (1)
      Using VBA to Deactivate a Chart              591 (1)
      Determining Whether a Chart Is Activated     592 (1)
      Deleting from the ChartObjects or Charts     593 (1)
      Collection
      Looping through All Charts                   594 (2)
      Sizing and Aligning ChartObjects             596 (2)
      Exporting a Chart                            598 (2)
        Exporting all graphics                     599 (1)
      Changing the Data Used in a Chart            600 (6)
        Changing chart data based on the active    601 (2)
        cell
        Using VBA to determine the ranges used     603 (3)
        in a chart
      Using VBA to Display Arbitrary Data          606 (3)
      Labels on a Chart
      Displaying a Chart in a UserForm             609 (2)
      Understanding Chart Events                   611 (7)
        An example of using Chart events           612 (3)
        Enabling events for an embedded chart      615 (1)
        Example: Using Chart events with an        616 (2)
        embedded chart
      Discovering VBA Charting Tricks              618 (7)
        Printing embedded charts on a full page    619 (1)
        Hiding series by hiding columns            619 (2)
        Creating unlinked charts                   621 (1)
        Displaying text with the MouseOver event   622 (3)
      Animating Charts                             625 (6)
        Scrolling a chart                          626 (2)
        Creating a hypocycloid chart               628 (1)
        Creating a "clock" chart                   629 (2)
      Creating an Interactive Chart without VBA    631 (4)
        Getting the data to create an              632 (1)
        interactive chart
        Creating the Option Button controls for    632 (1)
        an interactive chart
        Creating the city lists for the            632 (1)
        interactive chart
        Creating the interactive chart data        633 (1)
        range
        Creating the interactive chart             634 (1)
      Working with Sparkline Charts                635 (4)
    Chapter 19 Understanding Excel's Events        639 (38)
      What You Should Know about Events            639 (7)
        Understanding event sequences              640 (1)
        Where to put event-handler procedures      640 (2)
        Disabling events                           642 (1)
        Entering event-handler code                643 (1)
        Event-handler procedures that use          644 (2)
        arguments
      Getting Acquainted with Workbook-Level       646 (8)
      Events
        The Open event                             647 (1)
        The Activate event                         648 (1)
        The SheetActivate event                    648 (1)
        The NewSheet event                         649 (1)
        The BeforeSave event                       649 (1)
        The Deactivate event                       650 (1)
        The BeforePrint event                      650 (2)
        The BeforeClose event                      652 (2)
      Examining Worksheet Events                   654 (8)
        The Change event                           654 (1)
        Monitoring a specific range for changes    655 (5)
        The SelectionChange event                  660 (1)
        The BeforeDoubleClick event                661 (1)
        The BeforeRightClick event                 662 (1)
      Checking Out Chart Events                    662 (2)
      Monitoring with Application Events           664 (5)
        Enabling Application-level events          666 (1)
        Determining when a workbook is opened      666 (2)
        Monitoring Application-level events        668 (1)
      Using UserForm Events                        669 (1)
      Accessing Events Not Associated with an      670 (7)
      Object
        The OnTime event                           670 (2)
        The OnKey event                            672 (5)
    Chapter 20 Interacting with Other              677 (26)
    Applications
      Starting an Application from Excel           677 (4)
        Using the VBA Shell function               677 (3)
        Using the Windows ShellExecute API         680 (1)
        function
      Activating an Application with Excel         681 (2)
        Using AppActivate                          681 (1)
        Activating a Microsoft Office              682 (1)
        application
      Running Control Panel Dialog Boxes           683 (1)
      Using Automation in Excel                    684 (11)
        Working with foreign objects using         685 (1)
        automation
        Early versus late binding                  685 (3)
        A simple example of late binding           688 (1)
        Controlling Word from Excel                689 (3)
        Controlling Excel from another             692 (3)
        application
      Sending Personalized E-Mail via Outlook      695 (3)
      Sending E-Mail Attachments from Excel        698 (3)
      Using SendKeys                               701 (2)
    Chapter 21 Creating and Using Add-Ins          703 (30)
      What Is an Add-In?                           703 (3)
        Comparing an add-in with a standard        703 (1)
        workbook
        Why create add-ins?                        704 (2)
      Understanding Excel's Add-In Manager         706 (1)
      Creating an Add-In                           707 (1)
      An Add-In Example                            708 (6)
        Adding descriptive information for the     709 (1)
        example add-in
        Creating an add-in                         710 (1)
        Installing an add-in                       710 (2)
        Testing the add-in                         712 (1)
        Distributing an add-in                     712 (1)
        Modifying an add-in                        713 (1)
      Comparing XLAM and XLSM Files                714 (7)
        XLAM file VBA collection membership        714 (1)
        Visibility of XLSM and XLAM files          715 (1)
        Worksheets and chart sheets in XLSM and    716 (1)
        XLAM files
        Accessing VBA procedures in an add-in      717 (4)
      Manipulating Add-Ins with VBA                721 (5)
        AddIn object properties                    722 (3)
        Accessing an add-in as a workbook          725 (1)
        AddIn object events                        726 (1)
      Optimizing the Performance of Add-ins        726 (1)
      Special Problems with Add-Ins                727 (6)
        Ensuring that an add-in is installed       727 (2)
        Referencing other files from an add-in     729 (1)
        Detecting the proper Excel version for     730 (3)
        your add-in
  Part VI Developing Applications
    Chapter 22 Working with the Ribbon             733 (36)
      Ribbon Basics                                733 (4)
      Using VBA with the Ribbon                    737 (6)
        Accessing a Ribbon control                 738 (2)
        Working with the Ribbon                    740 (2)
        Activating a tab                           742 (1)
      Customizing the Ribbon                       743 (21)
        A simple RibbonX example                   743 (3)
        A simple Ribbon example, take 2            746 (5)
        Another RibbonX example                    751 (3)
        Ribbon controls demo                       754 (7)
        A DynamicMenu Control Example              761 (2)
        More on Ribbon customization               763 (1)
      Creating an Old-Style Toolbar                764 (5)
        Limitations of old-style toolbars in       765 (1)
        Excel 2010
        Code to create a toolbar                   765 (4)
    Chapter 23 Working with Shortcut Menus         769 (20)
      CommandBar Overview                          769 (8)
        CommandBar types                           770 (1)
        Listing shortcut menus                     770 (1)
        Referring to CommandBars                   771 (1)
        Referring to controls in a CommandBar      772 (1)
        Properties of CommandBar controls          773 (1)
        Displaying all shortcut menu items         774 (3)
      Using VBA to Customize Shortcut Menus        777 (6)
        Resetting a shortcut menu                  777 (1)
        Disabling a Shortcut Menu                  778 (1)
        Disabling shortcut menu items              778 (1)
        Adding a new item to the Cell shortcut     779 (2)
        menu
        Adding a submenu to a shortcut menu        781 (2)
      Shortcut Menus and Events                    783 (6)
        Adding and deleting menus automatically    784 (1)
        Disabling or hiding shortcut menu items    785 (1)
        Creating a context-sensitive shortcut      785 (4)
        menu
    Chapter 24 Providing Help for Your             789 (20)
    Applications
      Help for Your Excel Applications             789 (1)
      Help Systems That Use Excel Components       790 (9)
        Using cell comments for help               792 (1)
        Using a text box for help                  793 (1)
        Using a worksheet to display help text     794 (1)
        Displaying help in a UserForm              795 (4)
      Displaying Help in a Web Browser             799 (2)
        Using HTML files                           799 (1)
        Using an MHTML file                        800 (1)
      Using the HTML Help System                   801 (4)
        Using the Help method to display HTML      804 (1)
        Help
      Associating a Help File with Your            805 (4)
      Application
        Associating a Help topic with a VBA        805 (4)
        function
    Chapter 25 Developing User-Oriented            809 (16)
    Applications
      What is a User-Oriented Application?         809 (1)
      The Loan Amortization Wizard                 809 (11)
        Using the Loan Amortization Wizard         810 (2)
        The Loan Amortization Wizard workbook      812 (1)
        structure
        How the Loan Amortization Wizard works     813 (7)
        Potential enhancements for the Loan        820 (1)
        Amortization Wizard
      Application Development Concepts             820 (5)
  Part VII Other Topics
    Chapter 26 Compatibility Issues                825 (14)
      What Is Compatibility?                       825 (1)
      Types of Compatibility Problems              826 (1)
      Avoid Using New Features                     827 (1)
      But Will It Work on a Mac?                   828 (2)
      Dealing with 64-bit Excel                    830 (1)
      Creating an International Application        831 (8)
        Multilanguage applications                 832 (2)
        VBA language considerations                834 (1)
        Using local properties                     834 (1)
        Identifying system settings                834 (3)
        Date and time settings                     837 (2)
    Chapter 27 Manipulating Files with VBA         839 (32)
      Performing Common File Operations            839 (9)
        Using VBA file-related statements          840 (5)
        Using the FileSystemObject object          845 (3)
      Displaying Extended File Information         848 (2)
      Working with Text Files                      850 (4)
        Opening a text file                        851 (1)
        Reading a text file                        852 (1)
        Writing a text file                        852 (1)
        Getting a file number                      852 (1)
        Determining or setting the file position   853 (1)
        Statements for reading and writing         853 (1)
      Text File Manipulation Examples              854 (11)
        Importing data in a text file              854 (2)
        Exporting a range to a text file           856 (1)
        Importing a text file to a range           857 (1)
        Logging Excel usage                        858 (1)
        Filtering a text file                      859 (1)
        Exporting a range to HTML format           859 (4)
        Exporting a range to an XML file           863 (2)
      Zipping and Unzipping Files                  865 (3)
        Zipping files                              865 (2)
        Unzipping a File                           867 (1)
      Working with ADO                             868 (3)
    Chapter 28 Manipulating Visual Basic           871 (24)
    Components
      Introducing the IDE                          871 (2)
      The IDE Object Model                         873 (3)
        The VBProjects collection                  874 (2)
      Displaying All Components in a VBA Project   876 (1)
      Listing All VBA Procedures in a Workbook     877 (2)
      Replacing a Module with an Updated Version   879 (2)
      Using VBA to Write VBA Code                  881 (2)
      Adding Controls to a UserForm at Design      883 (3)
      Time
        Design-time versus runtime UserForm        884 (1)
        manipulations
        Adding 100 CommandButtons at design time   885 (1)
      Creating UserForms Programmatically          886 (9)
        A simple runtime UserForm example          887 (1)
        A useful (but not so simple) dynamic       888 (7)
        UserForm example
    Chapter 29 Understanding Class Modules         895 (16)
      What is a Class Module?                      895 (1)
      Example: Creating a NumLock Class            896 (5)
        Inserting a class module                   897 (1)
        Adding VBA code to the class module        897 (3)
        Using the NumLockClass class               900 (1)
      More about Class Modules                     901 (3)
        Programming properties of objects          901 (2)
        Programming methods for objects            903 (1)
        Class module events                        904 (1)
      Example: A CSV File Class                    904 (7)
        Class module---level variables for the     905 (1)
        CSVFileClass
        Property procedures for the CSVFileClass   905 (1)
        Method procedures for the CSVFileClass     905 (2)
        Using the CSVFileClass object              907 (4)
    Chapter 30 Working with Colors                 911 (26)
      Specifying Colors                            911 (5)
        The RGB color system                       912 (1)
        The HSL color system                       913 (1)
        Converting colors                          913 (3)
      Understanding Grayscale                      916 (3)
        Converting colors to gray                  916 (2)
        Viewing charts as grayscale                918 (1)
      Experimenting with Colors                    919 (2)
      Understanding Document Themes                921 (6)
        About document themes                      921 (1)
        Understanding document theme colors        921 (4)
        Displaying all theme colors                925 (2)
      Working with Shape Objects                   927 (6)
        A shape's background color                 928 (2)
        Shapes and theme colors                    930 (2)
        Shape examples                             932 (1)
      Modifying Chart Colors                       933 (4)
    Chapter 31 Frequently Asked Questions about    937 (32)
    Excel Programming
      Getting the Scoop on FAQs                    937 (1)
      General Excel Questions                      938 (4)
      The Visual Basic Editor                      942 (2)
      Procedures                                   944 (4)
      Functions                                    948 (3)
      Objects, Properties, Methods, and Events     951 (8)
      UserForms                                    959 (3)
      Add-Ins                                      962 (2)
      User Interface                               964 (5)
  Part VIII Appendixes
      Appendix A Excel Resources Online            969 (8)
        The Excel Help System                      969 (1)
        Microsoft Technical Support                969 (1)
        Support options                            970 (1)
        Microsoft Knowledge Base                   970 (1)
        Microsoft Excel home page                  970 (1)
        Microsoft Office home page                 970 (1)
        Internet Newsgroups                        971 (1)
        Accessing newsgroups by using a            971 (1)
        newsreader
        Accessing newsgroups by using a Web        971 (1)
        browser
        Searching newsgroups                       972 (1)
        Internet Web sites                         973 (1)
        The Spreadsheet Page                       973 (1)
        Daily Dose of Excel                        974 (1)
        Jon Peltier's Excel Page                   974 (1)
        Pearson Software Consulting                974 (1)
        Contextures                                974 (1)
        Pointy Haired Dilbert                      974 (1)
        David McRitchie's Excel Pages              975 (1)
        Mr. Excel                                  975 (2)
      Appendix B VBA Statements and Functions      977 (8)
      Reference
        Invoking Excel functions in VBA            980 (5)
        instructions
      Appendix C VBA Error Codes                   985 (4)
      Appendix D What's on the CD-ROM              989 (18)
        System Requirements                        989 (1)
        Using the CD                               989 (1)
        Files and Software on the CD               990 (1)
        Applications                               990 (1)
        eBook version of Excel 2010 Power          990 (1)
        Programming with VBA
        Sample files for Excel 2010 Power          990 (15)
        Programming with VBA
        Troubleshooting                            1005(2)
Index                                              1007(46)
End-User License Agreement                         1053