SINGAPORE
Law
Welcome Guest | ログイン
検索
詳細検索
VBA and Macros : Microsoft Excel 2010 (Mrexcel Library): Jelen, Bill/ Syrstad, Tracy: BOOKS KINOKUNIYA
詳細
VBA and Macros : Microsoft Excel 2010 (Mrexcel Library)
VBA and Macros : Microsoft Excel 2010 (Mrexcel Library)
出版社 : Que Pub
出版年月 : 2010/06
Binding : Paperback
ISBN : 9780789743145

BookWeb価格 : S$ 67.27
会員価格 : S$ 60.55

在庫情報 : 専門取次会社からお取り寄せします。
通常、3週間以内に発送いたします。
言語 : English

内容情報
Source: ENG
Academic Descriptors: A93905800 A48401631
Place of Publication: United States
Continuations: Monograph Series,any number
Textual Format: Computer Applications
Academic Level: Extracurricular
Table of Contents
 
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
 

AUTOMATE REPORTS BUILD FUNCTIONS VISUALIZE DATA WRITE FAST, RELIABLE SCRIPTS Microsoft Excel 2010 VBA AND MACROS: SAVE TIME AND SUPERCHARGE EXCEL 2010 WITH VBA AND MACROS! Use Excel 2010 VBA and macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then learn how to make Excel do things you thought were simply impossible! This book reveals scripting techniques you won't find anywhere else and shows you how to create automated reports that are amazingly powerful and useful. It helps you instantly visualize information so you can understand and act on it. It also shows you how to capture data from anywhere and use it anywhere, and helps you automate Excel 2010's most powerful new features. Learning advanced Excel scripting has never been easier. You'll find simple, step-by-step instructions, real-world examples and case studies, and 50 workbooks packed with bonus examples, macros, and solutions, straight from MrExcel.* Work efficiently with ranges, cells, and R1C1-style formulas * Build super-fast applications with arrays * Customize the Excel 2010 Ribbon to run your macros * Write Excel 2010 VBA code that works on older versions of Excel * Create custom dialog boxes to collect information from your users * Use error handling to make your VBA scripts more resilient * Use Web queries to import data from virtually any online source * Master advanced techniques such as classes and collections * Use Excel VBA to control other Office programs...even control Windows itself, via the Windows API * Create add-ins to share or sell your programs About MrExcel Library: Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will * Dramatically increase your productivity-saving you 50 hours a year or more * Present proven, creative strategies for solving real-world problems * Show you how to get great results, no matter how much data you have * Help you avoid critical mistakes that even experienced users make

Contents
Introduction Chapter 1 Unleash the Power of Excel with VBA The Power of Excel Barriers to Entry The Macro Recorder Doesn't Work Visual Basic Is Not Like BASIC Good News: Climbing the Learning Curve Is Easy Great News: Excel with VBA Is Worth the Effort Knowing Your Tools: The Developer Tab Macro Security Adding a Trusted Location Using Macro Settings to Enable Macros in Workbooks Outside of Trusted Locations Using Disable All Macros with Notification Overview of Recording, Storing, and Running a Macro Filling Out the Record Macro Dialog Running a Macro Creating a Macro Button on the Ribbon Creating a Macro Button on the Quick Access Toolbar Assigning a Macro to a Form Control, Text Box, or Shape Using New File Types in Excel 2010 Understanding the VB Editor VB Editor Settings The Project Explorer The Properties Window Understanding Shortcomings of the Macro Recorder Examining Code in the Programming Window Running the Macro on Another Day Produces Undesired Results Possible Solution: Use Relative References When Recording Never Use the AutoSum Button While Recording a Macro Three Tips When Using the Macro Recorder Next Steps Chapter 2 This Sounds Like BASIC, So Why Doesn't It Look Familiar? I Can't Understand This Code Understanding the Parts of VBA "Speech" VBA Is Not Really Hard VBA Help Files: Using F1 to Find Anything Using Help Topics Examining Recorded Macro Code: Using the VB Editor and Help Optional Parameters Defined Constants Properties Can Return Objects Using Debugging Tools to Figure Out Recorded Code Stepping Through Code More Debugging Options: Breakpoints Backing Up or Moving Forward in Code Not Stepping Through Each Line of Code Querying Anything While Stepping Through Code Using a Watch to Set a Breakpoint Using a Watch on an Object Object Browser: The Ultimate Reference Seven Tips for Cleaning Up Recorded Code Tip 1: Don't Select Anything Tip 2: Cells(2,5) Is More Convenient Than Range("E2") Tip 3: Ride the Range from the Bottom to Find Last Row Tip 4: Use Variables to Avoid Hard-Coding Rows and Formulas Tip 5: R1C1 Formulas That Make Your Life Easier Tip 6: Learn to Copy and Paste in a Single Statement Tip 7: Use With...End With to Perform Multiple Actions Next Steps Chapter 3 Referring to Ranges The Range Object Syntax to Specify a Range Named Ranges Shortcut for Referencing Ranges Referencing Ranges in Other Sheets Referencing a Range Relative to Another Range Use the Cells Property to Select a Range Using the Cells Property in the Range Property Use the Offset Property to Refer to a Range Use the Resize Property to Change the Size of a Range Use the Union Method to Join Multiple Ranges Use the ISEMPTY Function to Check Whether a Cell Is Empty Use the Intersect Method to Create a New Range from Overlapping Ranges Use the ISEMPTY Function to Check Whether a Cell Is Empty Use the CurrentRegion Property to Select a Data Range Use the Areas Collection to Return a Noncontiguous Range Referencing Tables Next Steps Chapter 4 User-Defined Functions Creating User-Defined Functions Sharing UDFs Useful Custom Excel Functions Set the Current Workbook's Name in a Cell Set the Current Workbook's Name and File Path in a Cell Check Whether a Workbook Is Open Check Whether a Sheet in an Open Workbook Exists Count the Number of Workbooks in a Directory Retrieve USERID Retrieve Date and Time of Last Save Retrieve Permanent Date and Time Validate an E-mail Address Sum Cells Based on Interior Color Count Unique Values Remove Duplicates from a Range Find the First Nonzero-Length Cell in a Range Substitute Multiple Characters Retrieve Numbers from Mixed Text Convert Week Number into Date Separate Delimited String Sort and Concatenate Sort Numeric and Alpha Characters Search for a String Within Text Reverse the Contents of a Cell Multiple Max Return Hyperlink Address Return the Column Letter of a Cell Address Static Random Using Select Case on a Worksheet Next Steps Chapter 5 Looping and Flow Control Using Variables in the For Statement Variations on the For...Next Loop Exiting a Loop Early After a Condition Is Met Nesting One Loop Inside Another Loop Do Loops Using the While or Until Clause in Do Loops While...Wend Loops VBA Loop: For Each Object Variables Flow Control: Using If...Then...Else and Select Case Basic Flow Control: If...Then...Else Conditions If...Then...End If Either/Or Decisions: If...Then...Else...End If Using If...Else If...End If for Multiple Conditions Using Select Case...End Select for Multiple Conditions Complex Expressions in Case Statements Nesting If Statements Next Steps Chapter 6 R1C1-Style Formulas Referring to Cells: A1 Versus R1C1 References Switching Excel to Display R1C1-Style References The Miracle of Excel Formulas Enter a Formula Once and Copy 1,000 Times The Secret: It's Not That Amazing Explanation of R1C1 Reference Style Using R1C1 with Relative References Using R1C1 with Absolute References Using R1C1 with Mixed References Referring to Entire Columns or Rows with R1C1 Style Replacing Many A1 Formulas with a Single R1C1 Formula Remembering Column Numbers Associated with Column Letters Array Formulas Require R1C1 Formulas Next Steps Chapter 7 What Is New in Excel 2010 and What Has Changed. If It Has Changed in the Front End, It Has Changed in VBA The Ribbon Charts Pivot Tables Slicers Conditional Formatting Tables Sorting SmartArt Learning the New Objects and Methods Compatibility Mode Version Excel8CompatibilityMode Next Steps Chapter 8 Create and Manipulate Names in VBA Excel Names Global Versus Local Names Adding Names Deleting Names Adding Comments Types of Names Formulas Strings Numbers Tables Using Arrays in Names Reserved Names Hiding Names Checking for the Existence of a Name Next Steps Chapter 9 Event Programming Levels of Events Using Events Event Parameters Enabling Events Workbook Events Workbook_Activate() Workbook_Deactivate() Workbook_Open() Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Workbook_BeforePrint(Cancel As Boolean) Workbook_BeforeClose(Cancel As Boolean) Workbook_NewSheet(ByVal Sh As Object). Workbook_WindowResize(ByVal Wn As Window). Workbook_WindowActivate(ByVal Wn As Window) Workbook_WindowDeactivate(ByVal Wn As Window). Workbook_AddInInstall(). Workbook_AddInUninstall. Workbook_Sync(ByVal SyncEventType As Office.MsoSyncEventType). Workbook_PivotTableCloseConnection(ByVal Target As PivotTable) Workbook_PivotTableOpenConnection(ByVal Target As PivotTable). Workbook_RowsetComplete(ByVal Description As String, ByVal Sheet As String, ByVal Success As Boolean). Workbook_BeforeXmlExport(ByVal Map As XmlMap, ByVal Url As String, Cancel As Boolean) Workbook_AfterXmlExport(ByVal Map As XmlMap, ByVal Url As String, ByVal Result As XlXmlExportResult). Workbook_BeforeXmlImport(ByVal Map As XmlMap, ByVal Url As String, ByVal IsRefresh As Boolean, Cancel As Boolean). Workbook_AfterXmlImport(ByVal Map As XmlMap, ByVal IsRefresh As Boolean, ByVal Result As XlXmlImportResult). Workbook Level Sheet and Chart Events. Worksheet Events Worksheet_Activate() Worksheet_Deactivate() Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean). Worksheet_Calculate() Worksheet_Change(ByVal Target As Range) Worksheet_SelectionChange(ByVal Target As Range). Worksheet_FollowHyperlink(ByVal Target As Hyperlink). Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Chart Sheet Events. Embedded Charts Chart_Activate() Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean) Chart_BeforeRightClick(Cancel As Boolean). Chart_Calculate() Chart_Deactivate(). Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long). Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long). Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long). Chart_Resize(). Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long). Chart_SeriesChange(ByVal SeriesIndex As Long, ByVal PointIndex As Long). Chart_DragOver() Chart_DragPlot() Application-Level Events. AppEvent_AfterCalculate() AppEvent_NewWorkbook(ByVal Wb As Workbook) AppEvent_ProtectedViewWindowActivate(ByVal Pvw As ProtectedViewWindow). AppEvent_ProtectedViewWindowBeforeClose(ByVal Pvw As ProtectedViewWindow, ByVal Reason As XlProtectedViewCloseReason, Cancel As Boolean). AppEvent_ProtectedViewWindowDeactivate(ByVal Pvw As ProtectedViewWindow) AppEvent_ProtectedViewWindowOpen(ByVal Pvw As ProtectedViewWindow). AppEvent_ProtectedViewWindowResize(ByVal Pvw As ProtectedViewWindow) AppEvent_SheetActivate (ByVal Sh As Object). AppEvent_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean). AppEvent_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean). AppEvent_SheetCalculate(ByVal Sh As Object) AppEvent_SheetChange(ByVal Sh As Object, ByVal Target As Range). AppEvent_SheetDeactivate(ByVal Sh As Object). AppEvent_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) AppEvent_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range). AppEvent_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable) AppEvent_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window) AppEvent_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window). AppEvent_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window). AppEvent_WorkbookActivate(ByVal Wb As Workbook) AppEvent_WorkbookAddinInstall(ByVal Wb As Workbook) AppEvent_WorkbookAddinUninstall(ByVal Wb As Workbook). AppEvent_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean) AppEvent_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean) AppEvent_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean) AppEvent_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object). AppEvent_WorkbookOpen(ByVal Wb As Workbook) AppEvent_WorkbookPivotTableCloseConnection(ByVal Wb As Workbook, ByVal Target As PivotTable) AppEvent_WorkbookPivotTableOpenConnection(ByVal Wb As Workbook, ByVal Target As PivotTable) AppEvent_WorkbookRowsetComplete(ByVal Wb As Workbook, ByVal Description As String, ByVal Sheet As String, ByVal Success As Boolean). AppEvent_WorkbookSync(ByVal Wb As Workbook, ByVal SyncEventType As Office.MsoSyncEventType). AppEvent_WorkbookBeforeXmlExport(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal Url As String, Cancel As Boolean) AppEvent_WorkbookAfterXmlExport(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal Url As String, ByVal Result As XlXmlExportResult) AppEvent_WorkbookBeforeXmlImport(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal Url As String, ByVal IsRefresh As Boolean, Cancel As Boolean). AppEvent_WorkbookAfterXmlImport(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal IsRefresh As Boolean, ByVal Result As XlXmlImportResult) Next Steps Chapter 10 Userforms: An Introduction User Interaction Methods Input Boxes. Message Boxes Creating a Userform Calling and Hiding a Userform Programming the Userform Userform Events Programming Controls Using Basic Form Controls Using Labels, Text Boxes, and Command Buttons Using a Spin Button on a Userform Using the MultiPage Control to Combine Forms Verifying Field Entry Illegal Window Closing Getting a Filename Next Steps Chapter 11 Creating Charts Charting in Excel 2010 Referencing Charts and Chart Objects in VBA Code. Creating a Chart. Specifying the Size and Location of a Chart Later Referring to a Specific Chart Recording Commands from the Layout or Design Tabs Specifying a Built-in Chart Type Specifying a Template Chart Type Changing a Chart's Layout or Style Using SetElement to Emulate Changes on the Layout Tab Using SetElement to Emulate Changes on the Layout Tab Using SetElement to Emulate Changes on the Layout Tab Using SetElement to Emulate Changes on the Layout Tab Changing a Chart Title Using VBA Emulating Changes on the Format Tab Using the Format Method to Access Formatting Options Creating Advanced Charts Creating True Open-High-Low-Close Stock Charts Creating Bins for a Frequency Chart Creating a Stacked Area Chart Exporting a Chart as a Graphic Creating a Dynamic Chart in a Userform Creating Pivot Charts. Next Steps. Chapter 12 Data Mining with Advanced Filter Replacing a Loop with AutoFilter. Using New AutoFilter Techniques Selecting Visible Cells Only Advanced Filter Is Easier in VBA Than in Excel Using the Excel Interface to Build an Advanced Filter. Using Advanced Filter to Extract a Unique List of Values Extracting a Unique List of Values with the User Interface. Extracting a Unique List of Values with VBA Code Getting Unique Combinations of Two or More Fields. Using Advanced Filter with Criteria Ranges. Joining Multiple Criteria with a Logical OR. Joining Two Criteria with a Logical AND. Other Slightly Complex Criteria Ranges. The Most Complex Criteria: Replacing the List of Values with a Condition Created as the Result of a Formula Using Filter in Place in Advanced Filter Catching No Records When Using Filter in Place Showing All Records After Filter in Place The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only Copying All Columns Copying a Subset of Columns and Reordering The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only Using Filter in Place with Unique Records Only Excel in Practice: Turning Off a Few Drop-Downs in the AutoFilter Next Steps Chapter 13 Using VBA to Create Pivot Tables Introducing Pivot Tables Understanding Versions New in Excel 2010 New Beginning with Excel 2007 Creating a Vanilla Pivot Table in the Excel Interface Understanding Compact Layout Building a Pivot Table in Excel VBA Defining the Pivot Cache Creating and Configuring the Pivot Table Adding Fields to the Data Area Learning Why You Cannot Move or Change Part of a Pivot Report Determining Size of a Finished Pivot Table to Convert the Pivot Table to Values Using Advanced Pivot Table Features Using Multiple Value Fields Counting the Number of Records Grouping Daily Dates to Months, Quarters, or Years Changing the Calculation to Show Percentages Eliminating Blank Cells in the Values Area Controlling the Sort Order with AutoSort Replicating the Report for Every Product Filtering a Data Set Manually Filtering Two or More Items in a Pivot Field Using the Conceptual Filters Using the Search Filter Setting Up Slicers to Filter a Pivot Table Filtering an OLAP Pivot Table Using Named Sets Using Other Pivot Table Features Calculated Data Fields Calculated Items Using ShowDetail to Filter a Recordset Changing the Layout from the Design Tab Suppressing Subtotals for Multiple Row Fields Next Steps Chapter 14 Excel Power File Operations List Files in a Directory Import CSV Read Entire TXT to Memory and Parse Combining and Separating Workbooks Separate Worksheets into Workbooks Combine Workbooks Filter and Copy Data to Separate Worksheets Export Data to Word Working with Cell Comments List Comments Resize Comments Resize Comments with Centering Place a Chart in a Comment Utilities to Wow Your Clients Using Conditional Formatting to Highlight Selected Cell Highlight Selected Cell Without Using Conditional Formatting Custom Transpose Data Select/Deselect Noncontiguous Cells Techniques for VBA Pros Pivot Table Drill-Down Speedy Page Setup Calculating Time to Execute Code Custom Sort Order Cell Progress Indicator Protected Password Box Change Case Selecting with SpecialCells ActiveX Right-Click Menu Cool Applications Historical Stock/Fund Quotes Using VBA Extensibility to Add Code to New Workbooks Next Steps. Chapter 15 Data Visualizations and Conditional Formatting Introduction to Data Visualizations VBA Methods and Properties for Data Visualizations Adding Data Bars to a Range Adding Color Scales to a Range Adding Icon Sets to a Range Specifying an Icon Set Specifying Ranges for Each Icon Using Visualization Tricks Creating an Icon Set for a Subset of a Range Using Two Colors of Data Bars in a Range Using Other Conditional Formatting Methods Formatting Cells That Are Above or Below Average Formatting Cells in the Top 10 or Bottom 5 Formatting Unique or Duplicate Cells Formatting Cells Based on Their Value Formatting Cells That Contain Text Formatting Cells That Contain Dates Formatting Cells That Contain Blanks or Errors Using a Formula to Determine Which Cells to Format Using the New NumberFormat Property Next Steps Chapter 16 Reading from and Writing to the Web Getting Data from the Web Manually Creating a Web Query and Refreshing with VBA Using VBA to Update an Existing Web Query Building Many Web Queries with VBA Using Application.OnTime to Periodically Analyze Data Scheduled Procedures Require Ready Mode Specifying a Window of Time for an Update Canceling a Previously Scheduled Macro Closing Excel Cancels All Pending Scheduled Macros Scheduling a Macro to Run x Minutes in the Future Scheduling a Verbal Reminder Scheduling a Macro to Run Every 2 Minutes Publishing Data to a Web Page Using VBA to CreateCustom Web Pages Using Excel as a Content Management System Bonus: FTP from Excel Next Steps Chapter 17 Dashboarding with Sparklines in Excel 2010 Creating Sparklines Scaling the Sparklines Formatting Sparklines Using Theme Colors Using RGB Colors Formatting Sparkline Elements Formatting Win/Loss Charts Creating a Dashboard Observations About Sparklines Creating 100's of Individual Sparklines in a Dashboard Next Steps Chapter 18 Automating Word Early Binding Compile Error: Can't Find Object or Library Late Binding Creating and Referencing Objects The New Keyword CreateObject Function GetObject Function Using Constant Values Using the Watch Window to Retrieve the Real Value of a Constant Using the Object Browser to Retrieve the Real Value of a Constant Understanding Word's Objects Document Object Selection Object Range Object Bookmarks Controlling Form Fields in Word Next Steps Chapter 19 Arrays Declare an Array Multidimensional Arrays Fill an Array Empty an Array Arrays Make It Easier to Manipulate Data, but Is That All? Dynamic Arrays Passing an Array Next Steps Chapter 20 Text File Processing Importing from Text Files Importing Text Files with Fewer Than 1,048,576 Rows Reading Text Files with More Than 1,048,576 Rows Writing Text Files Next Steps Chapter 21 Using Access as a Back End to Enhance Multiuser Access to Data ADO Versus DAO The Tools of ADO Adding a Record to the Database Retrieving Records from the Database Updating an Existing Record Deleting Records via ADO Summarizing Records via ADO Other Utilities via ADO Checking for the Existence of Tables Checking for the Existence of a Field Adding a Table On the Fly Adding a Field On the Fly SQL Server Examples Next Steps Chapter 22 Creating Classes, Records, and Collections Inserting a Class Module Trapping Application and Embedded Chart Events Application Events Embedded Chart Events Creating a Custom Object Using a Custom Object Using Property Let and Property Get to Control How Users Utilize Custom Objects Collections Creating a Collection in a Standard Module Creating a Collection in a Class Module User-Defined Types Next Steps Chapter 23 Advanced Userform Techniques Using the UserForm Toolbar in the Design of Controls on Userforms More Userform Controls Check Boxes Tab Strips RefEdit Toggle Buttons Using a Scrollbar As a Slider to Select Values Controls and Collections Modeless Userforms Using Hyperlinks in Userforms Adding Controls at Runtime Resizing the Userform On-the-fly Adding a Control On-the-fly Sizing On-the-fly Adding Other Controls Adding an Image On-the-fly Putting It All Together Adding Help to the Userform Showing Accelerator Keys Adding Control Tip Text Creating the Tab Order Coloring the Active Control Transparent Forms Next Steps Chapter 24 Windows API What Is the Windows API? Understanding an API Declaration Using an API Declaration API Examples Retrieve the Computer Name Check Whether an Excel File Is Open on a Network Retrieve Display-Resolution Information Custom About Dialog Disable the X for Closing a Userform Running Timer Playing Sounds Retrieving a File Path Finding More API Declarations Next Steps Chapter 25 Handling Errors What Happens When an Error Occurs? Debug Error Inside Userform Code Is Misleading Basic Error Handling with the On Error GoTo Syntax Generic Error Handlers Handling Errors by Choosing to Ignore Them Suppressing Excel Warnings Encountering Errors on Purpose Train Your Clients Errors While Developing Versus Errors Months Later Runtime Error 9: Subscript Out of Range RunTime Error 1004: Method Range of Object Global Failed The Ills of Protecting Code More Problems with Passwords Errors Caused by Different Versions Next Steps Chapter 26 Customizing the Ribbon to Run Macros Out with the Old, In with the New Where to Add Your Code: customui Folder and File Creating the Tab and Group Adding a Control to Your Ribbon Accessing the File Structure Understanding the RELS File Renaming the Excel File and Opening the Workbook Custom UI Editor Tool Using Images on Buttons Microsoft Office Icons Custom Icon Images Troubleshooting Error Messages The Attribute "Attribute Name" on the Element "customui Ribbon" Is Not Defined in the DTD/Schema Illegal Qualified Name Character Element "customui Tag Name" Is Unexpected According to Content Model of Parent Element "customui Tag Name" Excel Found Unreadable Content Wrong Number of Arguments or Invalid Property Assignment Nothing Happens Other Ways to Run a Macro Keyboard Shortcut Attach a Macro to a Command Button Attach a Macro to a Shape Attach a Macro to an ActiveX Control Running a Macro from a Hyperlink Next Steps Chapter 27 Creating Add-Ins Characteristics of Standard Add-Ins Converting an Excel Workbook to an Add-In Using Save As to Convert a File to an Add-In Using the VB Editor to Convert a File to an Add-In Having Your Client Install the Add-In Standard Add-Ins Are Not Secure Closing Add-Ins Removing Add-Ins Using a Hidden Workbook as an Alternative to an Add-In Next Steps 9780789743145 TOC 6/1/2010