Introduction                                       1  (6)
      Getting Results with VBA                     1  (1)
      What Is in This Book?                        1  (3)
        Reduce the Learning Curve                  1  (1)
        Excel VBA Power                            2  (1)
        Techie Stuff Needed to Produce             2  (1)
        Applications
        Does This Book Teach Excel?                2  (2)
      The Future of VBA and Windows Versions of    4  (1)
      Excel
        Versions of Excel                          4  (1)
      Special Elements and Typographical           5  (1)
      Conventions
      Code Files                                   6  (1)
      Next Steps                                   6  (1)
    1 Unleash the Power of Excel with VBA          7  (26)
      The Power of Excel                           7  (1)
      Barriers to Entry                            7  (2)
        The Macro Recorder Doesn't Work!           7  (1)
        Visual Basic Is Not Like BASIC             8  (1)
        Good News: Climbing the Learning Curve     8  (1)
        Is Easy
        Great News: Excel with VBA Is Worth the    8  (1)
        Effort
      Knowing Your Tools: The Developer Tab        9  (1)
      Macro Security                               10 (2)
        Adding a Trusted Location                  10 (1)
        Using Macro Settings to Enable Macros      11 (1)
        in Workbooks Outside of Trusted
        Locations
        Using Disable All Macros with              12 (1)
        Notification
      Overview of Recording, Storing, and          12 (2)
      Running a Macro
        Filling Out the Record Macro Dialog        13 (1)
      Running a Macro                              14 (4)
        Creating a Macro Button on the Ribbon      14 (1)
        Creating a Macro Button on the Quick       15 (1)
        Access Toolbar
        Assigning a Macro to a Form Control,       16 (2)
        Text Box, or Shape
      Using New File Types in Excel 2010           18 (1)
      Understanding the VB Editor                  19 (2)
        VB Editor Settings                         19 (1)
        The Project Explorer                       20 (1)
        The Properties Window                      21 (1)
      Understanding Shortcomings of the Macro      21 (11)
      Recorder
        Examining Code in the Programming Window   23 (2)
        Running the Macro on Another Day           25 (1)
        Produces Undesired Results
        Possible Solution: Use Relative            26 (4)
        References When Recording
        Never Use the AutoSum Button While         30 (1)
        Recording a Macro
        Three Tips When Using the Macro Recorder   31 (1)
      Next Steps                                   32 (1)
    2 This Sounds Like BASIC, So Why Doesn't It    33 (32)
    Look Familiar?
      I Can't Understand This Code                 33 (1)
      Understanding the Parts of VBA "Speech"      34 (3)
      VBA Is Not Really Hard                       37 (2)
        VBA Help Files: Using F1 to Find           37 (2)
        Anything
        Using Help Topics                          39 (1)
      Examining Recorded Macro Code: Using the     39 (7)
      VB Editor and Help
        Optional Parameters                        41 (1)
        Defined Constants                          41 (5)
        Properties Can Return Objects              46 (1)
      Using Debugging Tools to Figure Out          46 (10)
      Recorded Code
        Stepping Through Code                      46 (3)
        More Debugging Options: Breakpoints        49 (1)
        Backing Up or Moving Forward in Code       49 (1)
        Not Stepping Through Each Line of Code     50 (1)
        Querying Anything While Stepping           50 (5)
        Through Code
        Using a Watch to Set a Breakpoint          55 (1)
        Using a Watch on an Object                 55 (1)
      Object Browser: The Ultimate Reference       56 (2)
      Seven Tips for Cleaning Up Recorded Code     58 (6)
        Tip 1 Don't Select Anything                58 (1)
        Tip 2 Cells (2, 5) Is More Convenient      59 (1)
        Than Range ("E2")
        Tip 3 Ride the Range from the Bottom to    59 (1)
        Find Last Row
        Tip 4 Use Variables to Avoid               60 (1)
        Hard-Coding Rows and Formulas
        Tip 5 R1C1 Formulas That Make Your Life    61 (1)
        Easier
        Tip 6 Learn to Copy and Paste in a         61 (1)
        Single Statement
        Tip 7 Use With...End With to Perform       61 (3)
        Multiple Actions
      Next Steps                                   64 (1)
    3 Referring to Ranges                          65 (14)
      The Range Object                             65 (1)
      Syntax to Specify a Range                    66 (1)
      Named Ranges                                 66 (1)
      Shortcut for Referencing Ranges              66 (1)
      Referencing Ranges in Other Sheets           67 (1)
      Referencing a Range Relative to Another      68 (1)
      Range
      Use the Cells Property to Select a Range     68 (1)
        Using the Cells Property in the Range      69 (1)
        Property
      Use the Offset Property to Refer to a        69 (2)
      Range
      Use the Resize Property to Change the        71 (1)
      Size of a Range
      Using the Columns and Rows Properties to     72 (1)
      Specify a Range
      Use the Union Method to Join Multiple        72 (1)
      Ranges
      Use the Intersect Method to Create a New     73 (1)
      Range from Overlapping Ranges
      Use the ISEMPTY Function to Check Whether    73 (1)
      a Cell Is Empty
      Use the Current Region Property to Select    74 (3)
      a Data Range
      Use the Areas Collection to Return a         77 (1)
      Noncontiguous Range
      Referencing Tables                           77 (1)
      Next Steps                                   78 (1)
    4 User-Defined Functions                       79 (28)
      Creating User-Defined Functions              79 (2)
      Sharing UDFs                                 81 (1)
      Useful Custom Excel Functions                82 (23)
        Set the Current Workbook's Name in a       82 (1)
        Cell
        Set the Current Workbook's Name and        82 (1)
        File Path in a Cell
        Check Whether a Workbook Is Open           83 (1)
        Check Whether a Sheet in an Open           83 (1)
        Workbook Exists
        Count the Number of Workbooks in a         84 (1)
        Directory
        Retrieve USERID                            85 (1)
        Retrieve Date and Time of Last Save        86 (1)
        Retrieve Permanent Date and Time           87 (1)
        Validate an E-mail Address                 88 (1)
        Sum Cells Based on Interior Color          89 (1)
        Count Unique Values                        90 (1)
        Remove Duplicates from a Range             91 (2)
        Find the First Nonzero-Length Cell in a    93 (1)
        Range
        Substitute Multiple Characters             94 (1)
        Retrieve Numbers from Mixed Text           95 (1)
        Convert Week Number into Date              96 (1)
        Separate Delimited String                  96 (1)
        Sort and Concatenate                       97 (2)
        Sort Numeric and Alpha Characters          99 (1)
        Search for a String Within Text            100(1)
        Reverse the Contents of a Cell             101(1)
        Multiple Max                               101(1)
        Return Hyperlink Address                   102(1)
        Return the Column Letter of a Cell         103(1)
        Address
        Static Random                              103(1)
        Using Select Case on a Worksheet           104(1)
      Next Steps                                   105(2)
    5 Looping and Flow Control                     107(20)
      For...Next Loops                             107(6)
        Using Variables in the For Statement       110(1)
        Variations on the For...Next Loop          110(1)
        Exiting a Loop Early After a Condition     111(1)
        Is Met
        Nesting One Loop Inside Another Loop       112(1)
      Do Loops                                     113(4)
        Using the While or Until Clause in Do      115(2)
        Loops
        While...Wend Loops                         117(1)
      VBA Loop: For Each                           117(3)
        Object Variables                           117(3)
      Flow Control: Using If...Then...Else and     120(6)
      Select Case
        Basic Flow Control: If...Then...Else       121(1)
        Conditions                                 121(1)
        If...Then...End If                         121(1)
        Either/Or Decisions:                       122(1)
        If...Then...Else...End If
        Using If...Else If...End If for            122(1)
        Multiple Conditions
        Using Select Case...End Select for         123(1)
        Multiple Conditions
        Complex Expressions in Case Statements     124(1)
        Nesting If Statements                      124(2)
      Next Steps                                   126(1)
    6 R1C1-Style Formulas                          127(56)
      Referring to Cells: A1 Versus R1C1           127(1)
      References
      Switching Excel to Display R1C1-Style        128(1)
      References
      The Miracle of Excel Formulas                129(3)
        Enter a Formula Once and Copy 1,000        129(1)
        Times
        The Secret: It's Not That Amazing          130(2)
      Explanation of R1C1 Reference Style          132(5)
        Using R1C1 with Relative References        132(1)
        Using R1C1 with Absolute References        133(1)
        Using R1C1 with Mixed References           133(1)
        Referring to Entire Columns or Rows        134(1)
        with R1C1 Style
        Replacing Many A1 Formulas with a          134(2)
        Single R1C1 Formula
        Remembering Column Numbers Associated      136(1)
        with Column Letters
      Array Formulas Require R1C1 Formulas         137(1)
      Next Steps                                   138(30)
        Workbook_Open()                            161(1)
        Workbook_BeforeSave(ByVal SaveAsUI As      162(1)
        Boolean, Cancel As Boolean)
        Workbook_BeforePrint(Cancel As Boolean)    163(1)
        Workbook_BeforeClose(Cancel As Boolean)    163(1)
        Workbook_NewSheet(ByVal Sh As Object)      164(1)
        Workbook_WindowResize (ByVal Wn As         164(1)
        Window)
        Workbook_WindowActivate (ByVal Wn As       165(1)
        Window)
        Workbook_WindowDeactivate (ByVal Wn As     165(1)
        Window)
        Workbook_AddInInstall()                    165(1)
        Workbook_AddInUninstall                    165(1)
        Workbook_Sync (ByVal SyncEventType As      165(1)
        Office.MsoSyncEventType)
        Workbook_PivotTableCloseConnection         165(1)
        (ByVal Target As PivotTable)
        Workbook_PivotTableOpenConnection          165(1)
        (ByVal Target As PivotTable)
        Workbook_RowsetComplete (ByVal             165(1)
        Description As String, ByVal Sheet As
        String, ByVal Success As Boolean)
        Workbook_BeforeXmlExport (ByVal Map As     166(1)
        XmlMap, ByVal Url As String, Cancel As
        Boolean)
        Workbook_AfterXmlExport (ByVal Map As      166(1)
        XmlMap, ByVal Url As String, ByVal
        Result As XlXmlExportResult)
        Workbook_BeforeXmlImport (ByVal Map As     166(1)
        XmlMap, ByVal Url As String, ByVal
        IsRefresh As Boolean, Cancel As Boolean)
        Workbook_AfterXmlImport (ByVal Map As      166(1)
        Xm1Map, ByVal IsRefresh As Boolean,
        ByVal Result As XlXmlImportResult)
        Workbook Level Sheet and Chart Events      166(2)
      Worksheet Events                             168(4)
        Worksheet_Activate()                       168(1)
        Worksheet_Deactivate()                     168(1)
        Worksheet_BeforeDoubleClick (ByVal         168(1)
        Target As Range, Cancel As Boolean)
        Worksheet_BeforeRightClick (ByVal          169(1)
        Target As Range, Cancel As Boolean)
        Worksheet_Calculate()                      169(1)
        Worksheet_Change (ByVal Target As Range)   170(1)
        Worksheet_SelectionChange (ByVal Target    170(1)
        As Range)
        Worksheet_FollowHyperlink (ByVal Target    171(1)
        As Hyperlink)
        Worksheet_PivotTableUpdate (ByVal          172(1)
        Target As PivotTable)
      Chart Sheet Events                           172(4)
        Embedded Charts                            172(1)
        Chart_Activate()                           173(1)
        Chart_BeforeDoubleClick (ByVal             173(1)
        ElementID As Long, ByVal Arg1 As Long,
        ByVal Arg2 As Long, Cancel As Boolean)
        Chart_BeforeRightClick (Cancel As          173(1)
        Boolean)
        Chart_Calculate()                          173(1)
        Chart_Deactivate()                         173(1)
        Chart_MouseDown (ByVal Button As Long,     174(1)
        ByVal Shift As Long, ByVal x As Long,
        ByVal y As Long)
        Chart_MouseMove (ByVal Button As Long,     174(1)
        ByVal Shift As Long, ByVal x As Long,
        ByVal y As Long)
        Chart_MouseUp (ByVal Button As Long,       174(1)
        ByVal Shift As Long, ByVal x As Long,
        ByVal y As Long)
        Chart_Resize()                             174(1)
        Chart_Select (ByVal Element ID As Long:    174(1)
        ByVal Argl As Long, ByVal Args2 As Long)
        Chart_SeriesChange (ByVal SeriesIndex      175(1)
        As Long, ByVal PointIndex As Long
        Chart_DragOver()                           175(1)
        Chart_DragPlot±()                   175(1)
      Application-Level Events                     176(6)
        AppEvent_AfterCalculate()                  176(1)
        AppEvent_NewWorkbook(ByVal Wb As           177(1)
        Workbook)
        AppEvent_ProtectedviewWindowActivate       177(1)
        (ByVal Pvw As ProtectedViewWindow)
        AppEvent_ProtectedViewWindowBeforeClose    177(1)
        (ByVal Pvw As ProtectedViewWindow)
        ByVal Reason As                            177(1)
        XlProtectedViewCloseReason, Cancel As
        Boolean)
        AppEvent_ProtectedViewWindowDeactivate     177(1)
        (ByVal Pvw As ProtectedViewWindow)
        AppEvent_ProtectedViewWindowOpen (ByVal    177(1)
        Pvw As ProtectedViewWindow)
        AppEvent_ProtectedViewWindowResize         177(1)
        (ByVal Pvw As ProtectedViewWindow)
        AppEvent_SheetActivate (ByVal Sh As        177(1)
        Object)
        AppEvent_SheetBeforeDoubleClick (ByVal     178(1)
        Sh As Object, ByVal Target As Range,
        Cancel As Boolean)
        AppEvent_SheetBeforeRightClick (ByVal      178(1)
        Sh As Object ByVal Target As Range,
        Cancel As Boolean)
        AppEvent_SheetCalculate (ByVal Sh As       178(1)
        Object)
        AppEvent_SheetChange (ByVal Sh As          178(1)
        Object, ByVal Target As Range)
        AppEvent_SheetDeactivate (ByVal Sh As      178(1)
        Object)
        AppEvent_SheetFollowHyperlink (ByVal Sh    178(1)
        As Object, ByVal Target As Hyperlink)
        AppEvent_SheetSelectionChange (ByVal Sh    178(1)
        As Object, ByVal Target As Range)
        AppEvent_Sheet PivotTableUpdate (ByVal     178(1)
        Sh As Object, ByVal Target As
        PivotTable)
        AppEvent_WindowActivate (ByVal Wb As       179(1)
        Workbook, ByVal Wn As Window)
        AppEvent_WindowDeactivate (ByVal Wb As     179(1)
        Workbook, ByVal Wn As Window)
        AppEvent_WindowResize (ByVal Wb As         179(1)
        Workbook, ByVal Wn As Window)
        AppEvent_WorkbookActivate (ByVal Wb As     179(1)
        Workbook)
        AppEvent_WorkbookAddinInstall (ByVal Wb    179(1)
        As Workbook)
        AppEvent_WorkbookAddinUninstall (ByVal     179(1)
        Wb As Workbook)
        AppEvent_WorkbookBeforeClose (ByVal Wb     179(1)
        As Workbook, Cancel As Boolean)
        AppEvent_WorkbookBeforePrint (ByVal Wb     180(1)
        As Workbook, Cancel As Boolean)
        AppEvent_WorkbookBeforeSave (ByVal Wb      180(1)
        As Workbook, ByVal SaveAsUI As Boolean,
        Cancel As Boolean)
        AppEvent_WorkbookNewSheet (ByVal Wb As     180(1)
        Workbook, ByVal Sh As Object)
        AppEvent_WorkbookOpen (ByVal Wb As         180(1)
        Workbook)
        AppEvent_WorkbookPivotTableCloseConnecti   180(1)
        on (ByVal Wb As Workbook, ByVal Target
        As PivotTable)
        AppEvent_WorkbookPivotTableOpenConnectio   180(1)
        n (ByVal Wb As Workbook, ByVal Target
        As PivotTable)
        AppEvent_WorkbookRowsetComplete (ByVal     181(1)
        Wb As Workbook, ByVal Description As
        String, ByVal Sheet As String, ByVal
        Success As Boolean)
        AppEvent_WorkbookSync (ByVal Wb As         181(1)
        Workbook, ByVal SyncEventType As
        Office.MsoSyncEventType)
        AppEvent_Workbook_BeforeXmlExport          181(1)
        (ByVal Wb As Workbook, ByVal Map As
        XmlMap, ByVal Url As String, Cancel As
        Boolean)
        AppEvent_WorkbookAfterXmlExport (ByVal     181(1)
        Wb As Workbook_ByVal Map As XmlMap,
        ByVal Url As String, ByVal Result As
        XlXmlExportResult)
        AppEvent_Workbook_BeforeXmlImport          181(1)
        (ByVal Wb As Workbook, ByVal Map As
        XmlMap, ByVal Url As String, ByVal
        IsRefresh As Boolean, Cancel As Boolean)
        AppEvent_WorkbookAfterXmlImport (ByVal     181(1)
        Wb As Workbook, ByVal Map As XmlMap,
        ByVal IsRefresh As Boolean, ByVal
        Result As XlXmlImportResult)
      Next Steps                                   182(1)
    10 Userforms: An Introduction                  183(20)
      User Interaction Methods                     183(1)
        Input Boxes                                183(1)
        Message Boxes                              184(1)
      Creating a Userform                          184(2)
      Calling and Hiding a Userform                186(1)
      Programming the Userform                     186(2)
        Userform Events                            186(2)
      Programming Controls                         188(1)
      Using Basic Form Controls                    189(11)
        Using Labels, Text Boxes, and Command      189(2)
        Buttons
        Deciding Whether to Use List Boxes or      191(3)
        Combo Boxes in Forms
        Adding Option Buttons to a Userform        194(1)
        Adding Graphic to a Userform               195(1)
        Using a Spin Button on a Userform          196(2)
        Using the MultiPage Control to Combine     198(2)
        Forms
      Verifying Field Entry                        200(1)
      Illegal Window Closing                       200(1)
      Getting a Filename                           201(1)
      Next Steps                                   202(1)
    11 Creating Chart                              203(46)
      Charting in Excel 2010                       203(1)
      Referencing Charts and Chart Objects in      203(1)
      VBA Code
      Creating a Chart                             204(4)
        Specifying the Size and Location of a      204(2)
        Chart
        Later Referring to a Specific Chart        206(2)
      Recording Commands from the Layout or        208(5)
      Design Tabs
        Specifying a Built-in Chart Type           208(2)
        Specifying a Template Chart Type           210(1)
        Changing a Chart's Layout or Style         211(2)
      Using Set Element to Emulate Changes on      213(5)
      the Layout Tab
      Changing a Chart Title Using VBA             218(1)
      Emulating Changes on the Format Tab          218(16)
        Using the Format Method to Access          218(16)
        Formatting Options
      Creating Advanced Charts                     234(10)
        Creating True Open-High-Low-Close Stock    235(1)
        Charts
        Creating Bins for a Frequency Chart        236(3)
        Creating a Stacked Area Chart              239(5)
      Exporting a Chart as a Graphic               244(2)
        Creating a Dynamic Chart in a Userform     244(2)
      Creating Pivot Charts                        246(2)
      Next Steps                                   248(1)
    12 Data Mining with Advanced Filter            249(38)
      Replacing a Loop with AutoFilter             249(8)
        Using New AutoFilter Techniques            251(4)
        Selecting Visible Cells Only               255(2)
      Advanced Filter Is Easier in VBA Than in     257(1)
      Excel
        Using the Excel Interface to Build an      258(1)
        Advanced Filter
      Using Advanced Filter to Extract a Unique    258(7)
      List of Values
        Extracting a Unique List of Values with    259(1)
        the User Interface
        Extracting a Unique List of Values with    260(3)
        VBA Code
        Getting Unique Combinations of Two or      263(2)
        More Fields
      Using Advanced Filter with Criteria Ranges   265(10)
        Joining Multiple Criteria with a           267(1)
        Logical OR
        Joining Two Criteria with a Logical AND    267(1)
        Other Slightly Complex Criteria Ranges     267(1)
        The Most Complex Criteria: Replacing       268(7)
        the List of Values with a Condition
        Created as the Result of a Formula
      Using Filter in Place in Advanced Filter     275(1)
        Catching No Records When Using Filter      276(1)
        in Place
        Showing All Records After Filter in        276(1)
        Place
      The Real Workhorse: xlFilterCopy with All    276(7)
      Records Rather Than Unique Records Only
        Copying All Columns                        277(1)
        Copying a Subset of Columns and            278(5)
        Reordering
      Using Filter in Place with Unique Records    283(2)
      Only
        Excel in Practice: Turning Off a Few       285(1)
        Drop-Downs in the AutoFilter
      Next Steps                                   285(2)
    13 Using VBA to Create Pivot Tables            287(42)
      Introducing Pivot Tables                     287(1)
      Understanding Versions                       287(3)
        New in Excel 2010                          288(1)
        New Beginning with Excel 2007              288(2)
      Creating a Vanilla Pivot Table in the        290(4)
      Excel Interface
        Understanding Compact Layout               293(1)
      Building a Pivot Table in Excel VBA          294(8)
        Defining the Pivot Cache                   295(1)
        Creating and Configuring the Pivot Table   295(1)
        Adding Fields to the Data Area             296(3)
        Learning Why You Cannot Move or Change     299(1)
        Part of a Pivot Report
        Determining Size of a Finished Pivot       299(3)
        Table to Convert the Pivot Table to
        Values
      Using Advanced Pivot Table Features          302(10)
        Using Multiple Value Fields                302(1)
        Counting the Number of Records             303(1)
        Grouping Daily Dates to Months,            303(2)
        Quarters, or Years
        Changing the Calculation to Show           305(3)
        Percentages
        Eliminating Blank Cells in the Values      308(1)
        Area
        Controlling the Sort Order with AutoSort   308(1)
        Replicating the Report for Every Product   309(3)
      Filtering a Data Set                         312(12)
        Manually Filtering Two or More Items in    312(1)
        a Pivot Field
        Using the Conceptual Filters               313(3)
        Using the Search Filter                    316(3)
        Setting Up Slicers to Filter a Pivot       319(2)
        Table
        Filtering an OLAP Pivot Table Using        321(3)
        Named Sets
      Using Other Pivot Table Features             324(3)
        Calculated Data Fields                     324(1)
        Calculated Items                           325(1)
        Using ShowDetail to Filter a Recordset     325(1)
        Changing the Layout from the Design Tab    325(1)
        Suppressing Subtotals for Multiple Row     326(1)
        Fields
      Next Steps                                   327(2)
    14 Excel Power                                 329(38)
      File Operations                              329(4)
        List Files in a Directory                  329(2)
        Import CSV                                 331(1)
        Read Entire TXT to Memory and Parse        332(1)
      Combining and Separating Workbooks           333(4)
        Separate Worksheets into Workbooks         333(1)
        Combine Workbooks                          334(1)
        Filter and Copy Data to Separate           335(1)
        Worksheets
        Export Data to Word                        336(1)
      Working with Cell Comments                   337(5)
        List Comments                              337(2)
        Resize Comments                            339(1)
        Resize Comments with Centering             340(1)
        Place a Chart in a Comment                 341(1)
      Utilities to Wow Your Clients                342(7)
        Using Conditional Formatting to            342(2)
        Highlight Selected Cell
        Highlight Selected Cell Without Using      344(1)
        Conditional Formatting
        Custom Transpose Data                      345(2)
        Select/Deselect Noncontiguous Cells        347(2)
      Techniques for VBA Pros                      349(13)
        Pivot Table Drill-Down                     349(1)
        Speedy Page Setup                          350(3)
        Calculating Time to Execute Code           353(1)
        Custom Sort Order                          354(1)
        Cell Progress Indicator                    355(1)
        Protected Password Box                     356(3)
        Change Case                                359(1)
        Selecting with SpecialCells                360(1)
        ActiveX Right-Click Menu                   360(2)
      Cool Applications                            362(3)
        Historical Stock/Fund Quotes               362(1)
        Using VBA Extensibility to Add Code to     363(2)
        New Workbooks
      Next Steps                                   365(2)
    15 Data Visualizations and Conditional         367(24)
    Formatting
      Introduction to Data Visualizations          367(1)
      VBA Methods and Properties for Data          368(1)
      Visualizations
      Adding Data Bars to a Range                  369(5)
      Adding Color Scales to a Range               374(1)
      Adding Icon Sets to a Range                  375(3)
        Specifying an Icon Set                     376(1)
        Specifying Ranges for Each Icon            377(1)
      Using Visualization Tricks                   378(4)
        Creating an Icon Set for a Subset of a     378(2)
        Range
        Using Two Colors of Data Bars in a Range   380(2)
      Using Other Conditional Formatting Methods   382(7)
        Formatting Cells That Are Above or         383(1)
        Below Average
        Formatting Cells in the Top 10 or          383(1)
        Bottom 5
        Formatting Unique or Duplicate Cells       384(1)
        Formatting Cells Based on Their Value      385(1)
        Formatting Cells That Contain Text         386(1)
        Formatting Cells That Contain Dates        386(1)
        Formatting Cells That Contain Blanks or    387(1)
        Errors
        Using a Formula to Determine Which         387(1)
        Cells to Format
        Using the New NumberFormat Property        388(1)
      Next Steps                                   389(2)
    16 Reading from and Writing to the Web         391(20)
      Getting Data from the Web                    391(8)
        Manually Creating a Web Query and          392(3)
        Refreshing with VBA
        Using VBA to Update an Existing Web        395(1)
        Query
        Building Many Web Queries with VBA         396(3)
      Using Application. OnTime to Periodically    399(5)
      Analyze Data
        Scheduled Procedures Require Ready Mode    400(1)
        Specifying a Window of Time for an         400(1)
        Update
        Canceling a Previously Scheduled Macro     400(1)
        Closing Excel Cancels All Pending          401(1)
        Scheduled Macros
        Scheduling a Macro to Run x Minutes in     401(1)
        the Future
        Scheduling a Verbal Reminder               402(1)
        Scheduling a Macro to Run Every 2          403(1)
        Minutes
      Publishing Data to a Web Page                404(6)
        Using VBA to Create Custom Web Pages       406(1)
        Using Excel as a Content Management        407(2)
        System
        Bonus: FTP from Excel                      409(1)
      Next Steps                                   410(1)
    17 Dashboarding with Sparklines in Excel       411(22)
    2010
      Creating Sparklines                          412(2)
      Scaling the Sparklines                       414(4)
      Formatting Sparklines                        418(9)
        Using Theme Colors                         418(3)
        Using RGB Colors                           421(2)
        Formatting Sparkline Elements              423(3)
        Formatting Win/Loss Charts                 426(1)
      Creating a Dashboard                         427(5)
        Observations About Sparklines              428(1)
        Creating 100's of Individual Sparklines    428(4)
        in a Dashboard
      Next Steps                                   432(1)
    18 Automating Word                             433(20)
      Early Binding                                433(3)
        Compile Error: Can't Find Object or        435(1)
        Library
      Late Binding                                 436(1)
      Creating and Referencing Objects             437(2)
        The New Keyword                            437(1)
        CreateObject Function                      438(1)
        GetObject Function                         438(1)
      Using Constant Values                        439(2)
        Using the Watch Window to Retrieve the     440(1)
        Real Value of a Constant
        Using the Object Browser to Retrieve       440(1)
        the Real Value of a Constant
      Understanding Word's Objects                 441(9)
        Document Object                            442(1)
        Selection Object                           443(1)
        Range Object                               444(4)
        Bookmarks                                  448(2)
      Controlling Form Fields in Word              450(2)
      Next Steps                                   452(1)
    19 Arrays                                      453(10)
      Declare an Array                             453(2)
        Multidimensional Arrays                    454(1)
      Fill an Array                                455(1)
      Empty an Array                               456(1)
      Arrays Make It Easier to Manipulate Data,    457(2)
      but Is That All?
      Dynamic Arrays                               459(1)
      Passing an Array                             460(1)
      Next Steps                                   461(2)
    20 Text File Processing                        463(12)
      Importing from Text Files                    463(10)
        Importing Text Files with Fewer Than       463(7)
        1,048,576 Rows
        Reading Text Files with More Than          470(3)
        1,048,576 Rows
      Writing Text Files                           473(1)
      Next Steps                                   474(1)
    21 Using Access as a Back End to Enhance       475(18)
    Multiuser Access to Data
      ADO Versus DAO                               476(2)
      The Tools of ADO                             478(2)
      Adding a Record to the Database              480(1)
      Retrieving Records from the Database         481(2)
      Updating an Existing Record                  483(2)
      Deleting Records via ADO                     485(1)
      Summarizing Records via ADO                  485(2)
      Other Utilities via ADO                      487(3)
        Checking for the Existence of Tables       487(1)
        Checking for the Existence of a Field      488(1)
        Adding a Table On the Fly                  489(1)
        Adding a Field On the Fly                  489(1)
      SQL Server Examples                          490(1)
      Next Steps                                   491(2)
    22 Creating Classes, Records, and              493(18)
    Collections
      Inserting a Class Module                     493(1)
      Trapping Application and Embedded Chart      494(3)
      Events
        Application Events                         494(1)
        Embedded Chart Events                      495(2)
      Creating a Custom Object                     497(1)
      Using a Custom Object                        498(1)
      Using Property Let and Property Get to       499(2)
      Control How Users Utilize Custom Objects
      Collections                                  501(5)
        Creating a Collection in a Standard        501(1)
        Module
        Creating a Collection in a Class Module    502(4)
      User-Defined Types                           506(3)
      Next Steps                                   509(2)
    23 Advanced Userform Techniques                511(24)
      Using the UserForm Toolbar in the Design     511(1)
      of Controls on Userforms
      More Userform Controls                       511(8)
        Check Boxes                                512(1)
        Tab Strips                                 513(2)
        RefEdit                                    515(2)
        Toggle Buttons                             517(1)
        Using a Scrollbar As a Slider to Select    517(2)
        Values
      Controls and Collections                     519(2)
      Modeless Userforms                           521(1)
      Using Hyperlinks in Userforms                522(1)
      Adding Controls at Runtime                   523(6)
        Resizing the Userform On-the-fly           524(1)
        Adding a Control On-the-fly                525(1)
        Sizing On-the-fly                          525(1)
        Adding Other Controls                      525(1)
        Adding an Image On-the-fly                 526(1)
        Putting It All Together                    527(2)
      Adding Help to the Userform                  529(4)
        Showing Accelerator Keys                   529(1)
        Adding Control Tip Text                    530(1)
        Creating the Tab Order                     530(1)
        Coloring the Active Control                530(3)
      Transparent Forms                            533(1)
      Next Steps                                   534(1)
    24 Windows API                                 535(14)
      What Is the Windows API?                     535(1)
      Understanding an API Declaration             536(1)
      Using an API Declaration                     537(1)
      API Examples                                 537(10)
        Retrieve the Computer Name                 538(1)
        Check Whether an Excel File Is Open on     539(1)
        a Network
        Retrieve Display-Resolution Information    540(1)
        Custom About Dialog                        541(1)
        Disable the X for Closing a Userform       541(1)
        Running Timer                              542(1)
        Playing Sounds                             543(1)
        Retrieving a File Path                     543(4)
      Finding More API Declarations                547(1)
      Next Steps                                   547(2)
    25 Handling Errors                             549(14)
      What Happens When an Error Occurs?           549(3)
        Debug Error Inside Userform Code Is        551(1)
        Misleading
      Basic Error Handling with the On Error       552(2)
      GoTo Syntax
      Generic Error Handlers                       554(3)
        Handling Errors by Choosing to Ignore      554(2)
        Them
        Suppressing Excel Warnings                 556(1)
        Encountering Errors on Purpose             556(1)
      Train Your Clients                           557(1)
      Errors While Developing Versus Errors        557(2)
      Months Later
        Runtime Error 9: Subscript Out of Range    557(1)
        RunTime Error 1004: Method Range of        558(1)
        Object Global Failed
      The Ills of Protecting Code                  559(1)
      More Problems with Passwords                 560(1)
      Errors Caused by Different Versions          561(1)
      Next Steps                                   562(1)
    26 Customizing the Ribbon to Run Macros        563(24)
      Out with the Old, In with the New            563(2)
        Where to Add Your Code: customui Folder    564(1)
        and File
      Creating the Tab and Group                   565(1)
      Adding a Control to Your Ribbon              566(5)
      Accessing the File Structure                 571(1)
      Understanding the RELS File                  571(1)
      Renaming the Excel File and Opening the      572(1)
      Workbook
        Custom Ul Editor Tool                      572(1)
      Using Images on Buttons                      572(5)
        Microsoft Office Icons                     573(1)
        Custom Icon Images                         574(3)
      Troubleshooting Error Messages               577(3)
        The Attribute "Attribute Name" on the      577(1)
        Element "customui Ribbon" Is Not
        Defined in the DTD/Schema
        Illegal Qualified Name Character           578(1)
        Element "customui Tag Name" Is             578(1)
        Unexpected According to Content Model
        of Parent Element "customui Tag Name"
        Excel Found Unreadable Content             579(1)
        Wrong Number of Arguments or Invalid       580(1)
        Property Assignment
        Nothing Happens                            580(1)
      Other Ways to Run a Macro                    580(5)
        Keyboard Shortcut                          580(1)
        Attach a Macro to a Command Button         581(1)
        Attach a Macro to a Shape                  582(1)
        Attach a Macro to an ActiveX Control       583(1)
        Running a Macro from a Hyperlink           584(1)
      Next Steps                                   585(2)
    27 Creating Add-Ins                            587(10)
      Characteristics of Standard Add-Ins          587(1)
      Converting an Excel Workbook to an Add-In    588(3)
        Using Save As to Convert a File to an      589(1)
        Add-In
        Using the VB Editor to Convert a File      590(1)
        to an Add-In
      Having Your Client Install the Add-In        591(2)
        Standard Add-Ins Are Not Secure            592(1)
        Closing Add-Ins                            593(1)
        Removing Add-Ins                           593(1)
      Using a Hidden Workbook as an Alternative    593(2)
      to an Add-In
      Next Steps                                   595(2)
Index                                              597