Introduction                                       1  (1)
      Getting Results with VBA                     1  (1)
      What Is in This Book                         1  (3)
      The Future of VBA and Windows Versions of    4  (1)
      Excel
      Special Elements and Typographical           5  (1)
      Conventions
      Code Files                                   5  (1)
      Next Steps                                   5  (2)
    Unleash the Power of Excel with VBA            7  (22)
      The Power of Excel                           7  (1)
      Barriers to Entry                            7  (1)
      The Macro Recorder Doesn't Work!             7  (2)
        Visual Basic Is Not Like BASIC             8  (1)
        The Good News---It Is Easy to Climb the    8  (1)
        Learning Curve
        The Great News---Excel with VBA Is         8  (1)
        Worth the Effort
      Knowing Your Tools---The Developer Ribbon    9  (1)
      Macro Security                               10 (3)
        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          13 (1)
      Running a Macro
        Filling Out the Record Macro Dialog        13 (1)
      Running a Macro                              14 (3)
        Creating a Macro Button                    14 (1)
        Assigning a Macro to a Form Control, a     15 (2)
        Text Box, or a Shape
      Using New File Types in Excel 2007           17 (1)
      Understanding the Visual Basic Editor        18 (2)
        VB Editor Settings                         18 (1)
        The Project Explorer                       19 (1)
        The Properties Window                      20 (1)
      Understanding Shortcomings of the Macro      20 (8)
      Recorder
        Preparing to Record the Macro              21 (1)
        Recording the Macro                        22 (1)
        Examining Code in the Programming Window   22 (2)
        Running the Same Macro on Another Day      24 (1)
        Produces Undesired Results
        A Possible Solution: Using Relative        25 (3)
        References When Recording
      Next Steps: Learning VBA Is the Solution     28 (1)
    This Sounds Like BASIC, So Why Doesn't It      29 (32)
    Look Familiar?
      I Can't Understand This Code                 29 (1)
      Understanding the Parts of VBA ``Speech''    30 (3)
      Is VBA Really This Hard? No!                 33 (3)
        VBA Help Files---Using F1 to Find          33 (2)
        Anything
        Using Help Topics                          35 (1)
      Examining Recorded Macro Code---Using the    36 (6)
      VB Editor and Help
        Optional Parameters                        37 (1)
        Defined Constants                          37 (5)
        Properties Can Return Objects              42 (1)
      Using Debugging Tools to Figure Out          42 (10)
      Recorded Code
        Stepping Through Code                      43 (2)
        More Debugging Options---Breakpoints       45 (1)
        Backing Up or Moving Forward in Code       46 (1)
        Not Stepping Through Each Line of Code     46 (1)
        Querying Anything While Stepping           46 (5)
        Through Code
        Using a Watch to Set a Breakpoint          51 (1)
        Using a Watch on an Object                 51 (1)
      The Ultimate Reference to All Objects,       52 (2)
      Methods, Properties
      Five Easy Tips for Cleaning Up Recorded      54 (4)
      Code
        Tip 1: Don't Select Anything               55 (1)
        Tip 2: Ride the Range from the Bottom      55 (1)
        to Find Last Row
        Tip 3: Use Variables to Avoid              56 (1)
        Hard-Coding Rows and Formulas
        Tip 4: Learn to Copy and Paste in a        57 (1)
        Single Statement
        Tip 5: Use With...End With If You Are      57 (1)
        Performing Multiple Actions to the Same
        Cell or Range of Cells
      Putting It All Together---Fixing the         58 (2)
      Recorded Code
        Changing the Recorded Code                 58 (2)
      Next Steps                                   60 (1)
    Referring to Ranges                            61 (14)
      The Range Object                             61 (1)
      Using the Upper-Left and Lower-Right         62 (1)
      Corners of a Selection to Specify a Range
      Named Ranges                                 62 (1)
      Shortcut for Referencing Ranges              62 (1)
      Referencing Ranges in Other Sheets           63 (1)
      Referencing a Range Relative to Another      63 (1)
      Range
      Using the Cells Property to Select a Range   64 (1)
        Using the Cells Property in the Range      65 (1)
        Property
      Using the Offset Property to Refer to a      65 (2)
      Range
      Using the Resize Property to Change the      67 (1)
      Size of a Range
      Using the Columns and Rows Properties to     68 (1)
      Specify a Range
      Using the Union Method to Join Multiple      68 (1)
      Ranges
      Using the Intersect Method to Create a       69 (1)
      New Range from Overlapping Ranges
      Using the ISEMPTY Function to Check          69 (1)
      Whether a Cellls Empty
      Using the CurrentRegion Property to          70 (3)
      Quickly Selecta Data Range
        Using the SpecialCells Method to Select    70 (3)
        Specific Cells
      Using the Areas Collection to Return a       73 (1)
      Noncontiguous Range
      Referencing Tables                           73 (1)
      Next Steps                                   74 (1)
      User-Defined Functions                       75 (1)
    Creating User-Defined Functions                75 (26)
      Custom Functions---Example and Explanation   76 (1)
      Sharing UDFs                                 77 (1)
      Useful Custom Excel Functions                78 (1)
      Set the Current Workbook's Name in a Cell    78 (22)
        Set the Current Workbook's Name and        78 (1)
        File Path in a Cell
        Check Whether a Workbook Is Open           79 (1)
        Check Whether a Sheet in an Open           79 (1)
        Workbook Exists
        Count the Number of Workbooks in a         80 (1)
        Directory
        Retrieve USERID                            81 (1)
        Retrieve Date and Time of Last Save        82 (1)
        Retrieve Permanent Date and Time           82 (1)
        Validate an Email Address                  83 (2)
        Sum Cells Based on the Interior Color      85 (1)
        Count Unique Values                        86 (1)
        Remove Duplicates from a Range             86 (2)
        Find the First Non-Zero-Length Cell in     88 (1)
        a Range
        Substitute Multiple Characters             89 (1)
        Retrieve Numbers from Mixed Text           90 (1)
        Convert Week Number into Date              91 (1)
        Separate Delimited String                  91 (1)
        Sort and Concatenate                       92 (2)
        Sort Numeric and Alpha Characters          94 (1)
        Search for a String within Text            95 (1)
        Reverse the Contents of a Cell             96 (1)
        Multiple Max                               96 (1)
        Return Hyperlink Address                   97 (1)
        Return the Column Letter of a Cell         98 (1)
        Address
        Static Random                              98 (1)
        Using Select Case on a Worksheet           99 (1)
      Next Steps                                   100(1)
    Looping and Flow Control                       101(20)
      For...Next Loops                             101(6)
        Using Variables in the For Statement       104(1)
        Variations on the For...Next Loop          104(1)
        Exiting a Loop Early after a Condition     105(1)
        Is Met
        Nesting One Loop Inside Another Loop       106(1)
      Do Loops                                     107(4)
        Using the While or Until Clause in Do      110(1)
        Loops
        While...Wend Loops                         111(1)
      The VBA Loop: For Each                       111(4)
        Object Variables                           112(1)
        Looping Through All Files in a Directory   113(2)
      Flow Control: Using If... Then... Else       115(5)
      and Select Case
        Basic Flow Control: If... Then... Else     115(1)
        Conditions                                 116(1)
        If...Then...End If                         116(1)
        Either/Or Decisions: If... Then...         116(1)
        Else... End If
        Using If... Else If... End If for          117(1)
        Multiple Conditions
        Using Select Case... End Select for        117(1)
        Multiple Conditions
        Complex Expressions in Case Statements     118(1)
        Nesting If Statements                      118(2)
      Next Steps                                   120(1)
    R1C1-Style Formulas                            121(14)
      Referring to Cells: Al Versus R1C1           121(1)
      References
      Switching Excel to Display R1C1 Style        122(1)
      References
      The Miracle of Excel Formulas                123(2)
        Enter a Formula Once and Copy 1,000        123(1)
        Times
        The Secret---It Is Not That Amazing        124(1)
        Entering A1 Versus R1C1 in VBA             125(1)
      Explanation of R1C1 Reference Style          125(5)
        Using R1C1 with Relative References        125(1)
        Using R1C1 with Absolute References        126(1)
        Using R1C1 with Mixed References           127(1)
        Referring to Entire Columns or Rows        127(1)
        with R1C1 Style
        Replacing Many A1 Formulas with a          128(1)
        Single R1C1 Formula
        Remembering Column Numbers Associated      129(1)
        with Column Letters
      Conditional Formatting---R1C1 Required       130(3)
        Setting Up Conditional Formatting in       130(2)
        the User Interface
        Setting Up Conditional Formats in VBA      132(1)
        Identifying Row with Largest Value in G    133(1)
      Array Formulas Require R1C1 Formulas         133(1)
      Next Steps                                   134(1)
    What's New in Excel 2007 and What's Changed    135(8)
      If Ifs Changed in the Front End, It's        135(3)
      Changed in VBA
        The Ribbon                                 135(1)
        Charts                                     135(1)
        Pivot Tables                               136(1)
        Conditional Formatting                     136(1)
        Tables                                     137(1)
        Sorting                                    137(1)
        SmartArt                                   138(1)
      The Macro Recorder Won't Record Actions      138(1)
      That It Did Record in Earlier Excel
      Versions
      Learning the New Objects and Methods         139(1)
      Compatibility Mode                           140(2)
        Version                                    141(1)
        Excel8CompatibilityMode                    141(1)
      Next Steps                                   142(1)
    Create and Manipulate Names in VBA             143(12)
      Excel Names                                  143(1)
      Global Versus Local Names                    143(1)
      Adding Names                                 144(2)
      Deleting Names                               146(1)
      Adding Comments                              146(1)
      Types of Names                               147(5)
        Formulas                                   147(1)
        Strings                                    148(1)
        Numbers                                    149(1)
        Tables                                     150(1)
        Using Arrays in Names                      150(1)
        Reserved Names                             151(1)
      Hiding Names                                 152(1)
      Checking for the Existence of a Name         152(2)
        Using Named Ranges for VLOOKUP             153(1)
      Next Steps                                   154(1)
    Event Programming                              155(22)
      Levels of Events                             155(1)
      Using Events                                 156(1)
        Event Parameters                           156(1)
        Enabling Events                            157(1)
      Workbook Events                              157(6)
        Workbook_Activate()                        157(1)
        Workbook_Deactivate()                      157(1)
        Workbook_0pen()                            157(1)
        Workbook_BeforeSave(ByVal SaveAsUI As      158(1)
        Boolean, Cancel As Boolean)
        Workbook_BeforePrint(Cancel As Boolean)    159(1)
        Workbook_BeforeClose(Cancel As Boolean)    159(1)
        Workbook_NewSheet(ByVal Sh As Object)      160(1)
        Workbook_WindowResize(ByValWn As Window)   160(1)
        Workbook_WindowActivate(ByValWn As         161(1)
        Window)
        Workbook_WindowDeactivate(ByValWn As       161(1)
        Window)
        Workbook_Addlnlnstall()                    161(1)
        Workbook_AddlnUninstall                    161(1)
        Workbook_SheetActivate(ByValSh As          161(1)
        Object)
        Workbook_SheetBeforeDoubleClick (ByVal     161(1)
        Sh As Object, ByVal Target As Range,
        Cancel As Boolean)
        Workbook_SheetBeforeRightClick(ByVal Sh    161(1)
        As Object, ByVal Target As Range,
        Cancel As Boolean)
        Workbook_SheetCalculate(ByValSh As         162(1)
        Object)
        Workbook_SheetChange (ByVal Sh As          162(1)
        Object, ByVal Target As Range)
        Workbook_Sync(ByVal SyncEventType As       162(1)
        OffkeMsoSyncEventType)
        Workbook_heetDeactivate (ByVal Sh As       162(1)
        Object)
        Workbook_heetFollowHyperiink (ByVal Sh     162(1)
        As Object, ByVal Target As Hyperlink)
        Workbook_SheetSelectionChange(ByVal Sh     162(1)
        As Object, ByVal TargetAs Range)
        Workbook_PivotTableCloseConnection(ByVal   162(1)
        Target As PivotTable)
        Workbook_PivotTableOpenConnection(ByVal    163(1)
        Target As PivotTable)
        Workbook_RowsetComplete(ByVal              163(1)
        Description As String, ByVal Sheet As
        String, ByVal Success As Boolean)
      Worksheet Events                             163(3)
        Worksheet_Activate()                       163(1)
        Worksheet_Deactivate()                     163(1)
        Worksheet_BeforeDoubleClick(ByVal          163(1)
        Target As Range, CancelAs Boolean)
        Worksheet_BeforeRightClick(ByVal Target    164(1)
        As Range, CancelAs Boolean)
        Worksheet_Calculate()                      164(1)
        Worksheet_Change(ByVal Target As Range)    165(1)
        Worksheet_SelectionChange(ByVal Target     165(1)
        As Range)
        Worksheet_FollowHyperlink(ByVal Target     166(1)
        As Hyperlink)
      Quickly Entering Military Time into a Cell   166(1)
      Chart Sheet Events                           167(4)
        Embedded Charts                            167(1)
        Chart_Activate()                           168(1)
        Chart_BeforeDoubleClick(ByVal ElementlO    168(1)
        As Long, ByVal Arg1 As Long, ByVal_Arg2
        As Long, Cancel As Boolean)
        Chart_BeforeRightClick(Cancel As           168(1)
        Boolean)
        Chart_Calculate()                          168(1)
        Chart_Deactivate()                         168(1)
        Chart_MouseDown(ByVal Button As Long,      169(1)
        ByVal Shift As Long, ByVal x As Long,
        ByVal y As Long)
        Chart_MouseMove(ByVal Button As Long,      169(1)
        ByVal Shift As Long, ByVal x As Long,
        ByVal y As Long)
        Chart_MouseUp(ByVal Button As Long,        169(1)
        ByVal Shift As Long, ByVal x As Long,
        ByVal y As Long)
        Chart_Resize()                             169(1)
        Chart_Select(ByVal ElementlD As Long,      169(1)
        ByVal Argi As Long, ByVal Arg2 As Long)
        Chart_SeriesChange(ByVal Serieslndex As    170(1)
        Long, ByVal Pointlndex As Long)
        Chart_DragOver()                           170(1)
        Chart_DragPlot()                           171(1)
      Application-Level Events                     171(6)
        AppEvent_AfterCalculate()                  172(1)
        AppEvent_NewWorkbook(ByValWb As            172(1)
        Workbook)
        AppEvent_SheetActivate (ByVal Sh As        172(1)
        Object)
        AppEvent_SheetBeforeDoubleClick(ByVal      172(1)
        Sh As Object, ByVal Target As Range,
        Cancel As Boolean)
        AppEvent_SheetBeforeRightClick(ByVal Sh    172(1)
        As Object, ByVal Target As Range,
        Cancel As Boolean)
        AppEvent_SheetCalculate(ByValSh As         172(1)
        Object)
        AppEvent_SheetChange(ByVal Sh As           172(1)
        Object, ByVal Target As Range)
        AppEvent_SheetDeactivate(ByValSh As        173(1)
        Object)
        AppEvent_SheetFollowHyperlink(ByVal Sh     173(1)
        As Object, ByVal Target As Hyperlink)
        AppEvent_SheetSelectionChange(ByVal Sh     173(1)
        As Object, ByVal Target As Range)
        AppEvent_WindowActivate(ByVal Wb As        173(1)
        Workbook, ByVal Wn As Window)
        AppEvent_WindowDeactivate(ByVal Wb As      173(1)
        Workbook, ByVal Wn As Window)
        AppEvent_WindowResize(ByVal Wb As          173(1)
        Workbook, ByVal Wn As Window)
        AppEvent_WorkbookActivate(ByValWb As       173(1)
        Workbook)
        AppEvent_WorkbookAddinlnstall(ByValWb      174(1)
        As Workbook)
        AppEvent_WorkbookAddinUninstall(ByValWb    174(1)
        As Workbook)
        AppEvent_WorkbookBeforeClose(ByVal Wb      174(1)
        As Workbook, Cancel As Boolean)
        AppEvent_WorkbookBeforePrint(ByVal Wb      174(1)
        As Workbook, Cancel As Boolean)
        AppEvent_WorkbookBeforeSave(ByVal Wb As    174(1)
        Workbook, ByVal SaveAsUI As Boolean,
        Cancel As Boolean)
        AppEvent_WorkbookNewSheet(ByVal Wb As      174(1)
        Workbook, ByVal Sh As Object)
        AppEvent_WorkbookOpen(ByValWb As           175(1)
        Workbook)
        AppEvent_WorkbookPivotTableCloseConnecti   175(1)
        on(ByVal Wb As Workbook, ByVal Target
        As PivotTable)
        AppEvent_WorkbookPivotTableOpenConnectio   175(1)
        n(ByVal Wb As Workbook, ByVal Target As
        PivotTable)
        AppEvent_WorkbookRowsetComplete(ByVal      175(1)
        Wb As Workbook, ByVal Description As
        String, ByVal Sheet As String, ByVal
        Success As Boolean)
        AppEvent_WorkbookSync(ByVal Wb As          175(1)
        Workbook, ByVal SyncEventType As
        Office.MsoSyncEventType)
        Next Steps                                 175(2)
    Userforms---An Introduction                    177(20)
      User Interaction Methods                     177(1)
        Input Boxes                                177(1)
        Message Boxes                              178(1)
      Creating a Userform                          178(1)
      Calling and Hiding a Userform                179(1)
      Programming the Userform                     180(1)
        Userform Events                            180(1)
      Programming Controls                         181(1)
        Adding Controls to an Existing Form        182(1)
      Using Basic Form Controls                    182(12)
        Using Labels, Text Boxes, and Command      182(2)
        Buttons
        Deciding Whether to Use List Boxes or      184(3)
        Combo Boxes in Forms
        Adding Option Buttons to a Userform        187(2)
        Adding Graphics to a Userform              189(1)
        Using a Spin Button on a Userform          190(1)
        Using the MultiPage Control to Combine     191(3)
        Forms
      Verifying Field Entry                        194(1)
      Illegal Window Closing                       194(1)
      Getting a Filename                           195(1)
      Next Steps                                   196(1)
      Creating Charts                              197(1)
    Charting in Excel 2007                         197(52)
      Coding for New Charting Features in Excel    198(1)
      2007
      Referencing Charts and Chart Objects in      198(1)
      VBA Code
      Creating a Chart                             199(3)
        Specifying the Size and Location of a      199(1)
        Chart
        Later Referring to a Specific Chart        200(2)
      Recording Commands from the Layout or        202(7)
      Design Ribbons
        Specifying a Built-in Chart Type           203(3)
        Specifying a Template Chart Type           206(1)
        Changing a Chart's Layout or Style         207(2)
      Using SetElement to Emulate Changes on       209(6)
      the Layout Ribbon
      Changing a Chart Title Using VBA             215(1)
      Emulating Changes on the Format Ribbon       215(15)
        Using the Format Method to Access New      215(15)
        Formatting Options
      Using the Watch Window to Discover Object    230(3)
      Settings
      Using the Watch Window to Learn Rotation     233(1)
      Settings
      Creating Advanced Charts                     234(10)
        Creating True Open-High-Low-Close Stock    235(2)
        Charts
        Creating Bins for a Frequency Chart        237(2)
        Creating a Stacked Area Chart              239(5)
      Exporting a Chart as a Graphic               244(1)
        Creating a Dynamic Chart in a Userform     244(1)
      Creating Pivot Charts                        245(3)
      Next Steps                                   248(1)
    Data Mining with Advanced Filter               249(32)
      Advanced Filter Is Easier iivVBAThan in      249(1)
      Excel
      Using Advanced filter to Extract a Unique    250(7)
      List of Values
        Extracting a Unique List of Values with    250(2)
        the User Interface
        Extracting a Unique List of Values with    252(3)
        VBA Code
        Getting Unique Combinations of Two or      255(2)
        More Reids
      Using Advanced Filter with Criteria Ranges   257(8)
        Joining Multiple Criteria with a           258(1)
        Logical OR
        Joining Two Criteria with a Logical AND    259(1)
        Other Slightly Complex Criteria Ranges     259(1)
        The Most Complex Criteria---Replacing      259(6)
        the List of Values with a Condition
        Created as the Result of a Formula
      Using Filter in Place in Advanced Filter     265(2)
        Catching No Records When Using Filter      266(1)
        in Place
        Showing All Records after Filter in        267(1)
        Place
        Using Filter in Place with Unique          267(1)
        Records Only
      The Real Workhorse: xlFilterCopy with All    267(7)
      Records Rather than Unique Records Only
        Copying All Columns                        268(1)
        Copying a Subset of Columns and            268(2)
        Reordering
        Utilizing Two Kinds of Advanced Filters    270(4)
        to Create a Report for Each Customer
      Using AutoFilter                             274(6)
        Enabling AutoFilter with Code              274(1)
        Turning Off a Few Drop-Downs in the        275(1)
        AutoFilter
        Filtering a Column Using AutoFilters       275(1)
        Selecting Multiple Values from a Filter    276(1)
        Selecting a Dynamic Date Range Using       277(1)
        AutoFilters
        Filtering Based on Color or Icon           278(1)
        Using AutoFilter to Copy All Records       279(1)
        from Next Week
      Next Steps                                   280(1)
    Using VBA to Create Pivot Tables               281(56)
      Introducing Pivot Tables                     281(1)
      Understanding Versions                       281(3)
        New in Excel 2007                          282(2)
      Creating a Vanilla Pivot Table in the        284(3)
      Excel Interface
        Understanding New Features in Excel        285(2)
        2007 Pivot Tables
      Building a Pivot Table in Excel VBA          287(7)
        Defining the Pivot Cache                   287(1)
        Creating and Configuring the Pivot Table   288(1)
        Getting a Sum Rather Than a Count          289(2)
        Learning Why You Cannot Move or Change     291(1)
        Part of a Pivot Report
        Determining Size of a Finished Pivot       291(3)
        Table
      Creating a Report Showing Revenue by         294(4)
      Product
        Eliminating Blank Cells in the Values      296(1)
        Area
        Ensuring Table Layoutls Utilized           296(1)
        Controlling the Sort Order with AutoSort   296(1)
        Changing Default Number Format             296(1)
        Suppressing Subtotals for Multiple Row     297(1)
        Fields
        Suppressing Grand Total for Rows           297(1)
      Handling Additional Annoyances When          298(7)
      Creating Your Final Report
        Creating a New Workbook to Hold the        298(1)
        Report
        Creating a Summary on a Blank Report       299(1)
        Worksheet
        Filling the Outline View                   299(1)
        Handling Rnal Formatting                   300(1)
        Adding Subtotals                           300(1)
        Putting It All Together                    301(4)
      Addressing Issues with Two or More Data      305(6)
      Fields
        Calculated Data Fields                     307(2)
        Calculated Items                           309(2)
      Summarizing Date Fields with Grouping        311(7)
        Understanding the Group Method in VBA      311(2)
        Group by Week                              313(2)
        Measuring Order Lead time by Grouping      315(3)
        Two Date Fields
      Using Advanced Pivot Table Techniques        318(8)
        Using AutoShow to Produce Executive        318(3)
        Overviews
        Using ShowDetail to Filter a Recordset     321(1)
        Creating Reports for Each Region or        322(4)
        Model
        Manually Filtering Two or More Items in    326(1)
        a Pivot Field
      Controlling the Sort Order Manually          326(1)
      Using Sum, Average, Count, Min, Max, and     327(1)
      More
      Creating Report Percentages                  328(2)
        Percentage of Total                        328(1)
        Percentage Growth from Previous Month      328(1)
        Percentage of a Specific Item              329(1)
        Running Total                              329(1)
      Using New Pivot Table Features in Excel      330(7)
      2007
        Using the New Filters                      330(2)
        Applying a Table Style                     332(1)
        Changing the Layout From the Design        333(1)
        Ribbon
        Applying a Data Visualization              333(2)
        Next Steps                                 335(2)
      01>Excel Power                               337(36)
        File Operations                            337(1)
        List Files in a Directory                  337(2)
        Import CSV                                 339(1)
      Read Entire CSV to Memory and Parse          340(5)
        Combining and Separating Workbooks         341(1)
        Separate Worksheets into Workbooks         341(1)
        Combine Workbooks                          342(1)
        Filter and Copy Data to Separate           343(1)
        Worksheets
        Export Data to Word                        344(1)
      Working with Cell Comments                   345(6)
        List Comments                              345(2)
        Resize Comments                            347(1)
        Resize Comments with Centering             348(1)
        Place a Chart in a Comment                 349(2)
      Utilities to Wow Your Clients                351(6)
        Using Conditional Formatting to            351(1)
        Highlight Selected Cell
        Highlight Selected Cell Without Using      352(1)
        Conditional Formatting
        Custom Transpose Data                      353(2)
        Select/Deselect Noncontiguous Cells        355(2)
      Techniques for VBA Pros                      357(12)
        Pivot Table Drill-Down                     357(1)
        Speedy Page Setup                          358(3)
        Calculating Time to Execute Code           361(1)
        Custom Sort Order                          362(1)
        Cell Progress Indicator                    363(1)
        Protected Password Box                     364(2)
        Change Case                                366(2)
        Selecting with SpecialCells                368(1)
        ActiveX Right-Click Menu                   368(1)
      Cool Applications                            369(3)
        Historical Stock/Fund Quotes               370(1)
        Using VBA Extensibility to Add Code to     371(1)
        New Workbooks
      Next Steps                                   372(1)
    Data Visualizations and Conditional            373(20)
    Formatting
      Introduction to Data Visualizations          373(1)
      New VBA Methods and Properties for Data      374(1)
      Visualizations
      Adding Data Bars to a Range                  375(2)
      Adding Color Scales to a Range               377(2)
      Adding Icon Sets to a Range                  379(2)
        Specifying an Icon Set                     379(2)
        Specifying Ranges for Each Icon T          381(1)
      Using Visualization Tricks                   381(5)
        Creating an Icon Set for a Subset of a     381(2)
        Range
        Using Two Colors of Data Bars in a Range   383(3)
      Using Other Conditional Formatting Methods   386(6)
        Formatting Cells That Are Above or         386(1)
        Below Average
        Formatting Cellf in the Top 10 or          386(1)
        Bottom 5
        Formatting Unique or Duplicate Cells       387(1)
        Formatting Cells Based on Their Value      388(1)
        Formatting Cells That Contain Text         389(1)
        Formatting Cells That Contain Dates        389(1)
        Formatting Cells That Contain Blanks or    390(1)
        Errors
        Using a Formula to Determine Which         390(1)
        Cells to Format
        Using the New NumberFormat Property        391(1)
      Next Steps                                   392(1)
    Reading from and Writing to the Web            393(20)
      Getting Data from the Web                    393(5)
        Manually Creating a Web Query and          394(1)
        Refreshing with VBA
        Using VBA to Update an Existing Web        395(1)
        Query
        Building a New Web Query with VBA          396(2)
      Using Streaming Data                         398(1)
      Using Application.OnTime to Periodically     399(4)
      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 Two        403(1)
        Minutes
      Publishing Data to a Web Page                403(7)
        Using VBA to Create Custom Web Pages       405(1)
        Using Excel as a Content Management        406(3)
        System
        Bonus: FTP from Excel                      409(1)
      Trusting Web Content                         410(2)
      Next Steps                                   412(1)
    XML in Excel 2007                              413(8)
      What Is XML?                                 413(1)
      Simple XML Rules                             414(1)
      Universal File Format                        414(1)
      XML as the New Universal File Format         415(1)
      The Alphabet Soup of XML                     416(1)
      Microsoft's Use of XML as a File Type        417(1)
        How Excel 2007 Stores Workbooks with XML   417(1)
      UsingXMLDatafromAmazon.com                   418(2)
      Next Steps                                   420(1)
    Automating Word                                421(20)
      Early Binding                                421(2)
      Compile Error: Can't Find Object or          423(1)
      Library
      Late Binding                                 424(1)
      Creating and Referencing Objects             425(2)
        Keyword New                                425(1)
        CreateObject Function                      425(1)
        GetObject Function                         425(2)
      Using Constant Values                        427(1)
        Using the Watch Window to Retrieve the     427(1)
        Real Value of a Constant
        Using the Object Browser to Retrieve       427(1)
        the Real Value of a Constant
      Understanding Word's Objects                 428(9)
        Document Object                            429(2)
        Selection Object                           431(1)
        Range Object                               432(3)
        Bookmarks                                  435(2)
      Controlling Word's Form Reids                437(2)
      Next Steps                                   439(2)
      Arrays                                       441(1)
    Declare an Array                               441(8)
      Multidimensional Arrays                      442(2)
        Fill an Array                              443(1)
      Empty an Array                               444(1)
      Arrays Can Make It Easier to Manipulate      445(2)
      Data, But Is That All?
      Dynamic Arrays                               447(1)
      Passing an Array                             448(1)
      Next Steps                                   448(1)
    Text File Processing                           449(12)
      Importing from Text Files                    449(11)
        Importing Text Files with Fewer Than       449(7)
        1,084,576 Rows
        Reading Text Rles with More Than           456(4)
        1,084,576 Rows
      Writing Text Files                           460(1)
      Next Steps                                   460(1)
    Using Access as a Back End to Enhance          461(16)
    Multi-User Access to Data
      ADO Versus DAO                               462(2)
      The Tools of ADO                             464(1)
      Adding a Record to the Database              465(1)
      Retrieving Records from the Database         466(3)
      Updating an Existing Record                  469(2)
      Deleting Records via ADO                     471(1)
      Summarizing Records via ADO                  471(1)
      Other Utilities via ADO                      472(3)
        Checking for Existence of Tables           472(1)
        Checking for Existence of a Field          473(1)
        Adding a Table On the Fly                  474(1)
        Adding a Field On the Fly                  475(1)
      Next Steps                                   475(2)
    Creating Classes, Records, and Collections     477(16)
      Inserting a Class Module                     477(1)
      Trapping Application and Embedded Chart      478(3)
      Events
        Application Events                         478(1)
        Embedded Chart Events                      479(2)
      Creating a Custom Object                     481(1)
      Using a Custom Object                        482(1)
      Using Property Let and Property Get to       483(2)
      Control How Users Utilize Custom Objects
      Collections                                  485(5)
        Creating a Collection in a Standard        485(1)
        Module
        Creating a Collection in a Class Module    486(2)
        Help Buttons                               488(2)
      User-Defined Types (UDTs)                    490(2)
      Next Steps                                   492(1)
    Advanced Userform Techniques                   493(24)
      Using the UserForm Toolbar in the Design     493(1)
      of Controls on Userforms
      More Userform Controls                       493(8)
        Check Boxes                                494(1)
        Tab Strips                                 495(2)
        RefEdit                                    497(2)
        Toggle Buttons                             499(1)
        Using a Scrollbar as a Slider to Select    499(2)
        Values
      Controls and Collections                     501(2)
      Modeless Userforms                           503(1)
      Using Hyperlinks in Userforms                504(1)
      Adding Controls at Runtime                   505(6)
        Resizing the Userform On-the-Fly           506(1)
        Adding a Control On-the-Fly                506(1)
        Sizing On-the-Fly                          507(1)
        Adding Other Controls'                     507(1)
        Adding an Image On-the-Fly                 508(1)
        Putting It All Together                    508(2)
        Adding Help to the Userform                510(1)
        Showing Accelerator Keys                   510(1)
      Adding Control Tip Text                      511(4)
        Creating the Tab Order                     511(1)
        Coloring the Active Control                512(1)
        Multicolumn List Boxes                     513(1)
        Transparent Forms                          514(1)
      Next Steps                                   515(2)
    Windows Application Programming Interface      517(12)
    (API)
      What Is the Windows API?                     517(1)
      Understanding an API Declaration             517(1)
      Using an API Declaration                     518(1)
      API Examples                                 519(9)
        Retrieve the Computer Name                 519(1)
        Check Whether an Excel File Is Open on     520(1)
        a Network
        Retrieve Display-Resolution Information    521(1)
        Custom About Dialog                        522(1)
        Disable the X for Closing a Userform       522(1)
        Running Timer                              523(1)
        Playing Sounds                             524(1)
        Retrieving a File Path                     524(4)
      Rnding More API Declarations                 528(1)
      Next Steps                                   528(1)
    Handling Errors                                529(14)
      What Happens When an Error Occurs            529(3)
        Debug Error Inside Userform Code Is        531(1)
        Misleading
      Basic Error Handling with the On Error       532(2)
      GoTo Syntax
      Generic Error Handlers                       534(2)
        Handling Errors by Choosing to Ignore      534(1)
        Them
        Page Setup Problems Can Often Be Ignored   535(1)
        Suppressing Excel Warnings                 536(1)
        Encountering Errors on Purpose             536(1)
      Train Your Clients                           536(1)
      Errors While Developing Versus Errors        537(2)
      Months Later
        Runtime Error 9: Subscript Out of Range    537(1)
        Run Time Error 1004: Method Range of       538(1)
        Object Global Failed
      The Ills of Protecting Code                  539(1)
      Password Cracking                            539(1)
      More Problems with Passwords                 540(1)
      Errors Caused by Different Versions          541(1)
      Next Steps                                   541(2)
    Customizing the Ribbon to Run Macros           543(26)
      Out with the Old, In with the New            543(1)
      Where to Add Your Code: customui Folder      544(1)
      and File
      Creating the Tab and Group                   545(1)
      Adding a Control to Your Ribbon              546(6)
      Accessing the File Structure                 552(1)
      Understanding the RELS File                  553(1)
      Renaming the Excel File and Opening the      553(1)
      Workbook
        Ribbon Customizer                          554(1)
      Using Images on Buttons                      554(2)
        Microsoft Office icons                     554(1)
        Custom Icon Images                         555(1)
      Converting an Excel 2003 Custom Toolbar      556(2)
      to Excel 2007
      Troubleshooting Error Messages               558(3)
        The Attribute ``Attribute Name'' on the    559(1)
        Element ``customui Ribbon'' Is Not
        Defined in the DTD/Schema
        Illegal Qualified Name Character           559(1)
        Element ``customui Tag Name'' Is           560(1)
        Unexpected According to Content Model
        of Parent Element ``customui Tag Name''
        Excel Found Unreadable Content             560(1)
        Wrong Number of Arguments or Invalid       561(1)
        Property Assignment
        Nothing Happens                            561(1)
      Other Ways to Run a Macro                    561(7)
        Keyboard Shortcut                          562(1)
        Attach a Macro to a Command Button         562(3)
        Attach a Macro to an ActiveX Control       565(2)
        Running a Macro from a Hyperlink           567(1)
      Next Steps                                   568(1)
    Creating Add-Ins                               569(8)
      Characteristics of Standard Add-Ins          569(1)
      Converting an Excel Workbook to an Add-In    570(2)
        Using Save As to Convert a File to an      570(1)
        Add-In
        Using the VB Editor to Convert a File      571(1)
        to an Add-In
      Having Your Client Install the Add-In        572(3)
        Standard Add-Ins Are Not Secure            574(1)
        Closing Add-Ins                            574(1)
        Removing Add-Ins                           574(1)
      Using a Hidden Workbook as an Alternative    575(1)
      to an Add-In
      Using a Hidden Code Workbook to Hold All     575(1)
      Macros and Forms
      Next Steps                                   576(1)
Index                                              577