Introduction                                       1  (10)
      Is This the Right Book?                      1  (1)
      So You Want to Be a Programmer               2  (1)
      Why Bother?                                  3  (1)
      What I Assume about You                      3  (1)
      Obligatory Typographical Conventions         4  (1)
      Section
      Check Your Security Settings                 5  (1)
      How This Book Is Organized                   6  (2)
        Part I Introducing VBA                     6  (1)
        Part II How VBA Works with Excel           6  (1)
        Part III Programming Concepts              7  (1)
        Part IV Communicating with Your Users      7  (1)
        Part V Putting It All Together             7  (1)
        Part VI The Part of Tens                   7  (1)
        But Wait, There's More!                    7  (1)
      Icons Used in This Book                      8  (1)
      Getting the Sample Files                     8  (1)
      Now What?                                    9  (2)
  Part I Introducing VBA                           11 (24)
    Chapter 1 What Is VBA?                         13 (10)
      Okay, So What Is VBA?                        13 (1)
      What Can You Do with VBA?                    14 (2)
        Inserting a bunch of text                  15 (1)
        Automating a task you perform frequently   15 (1)
        Automating repetitive operations           15 (1)
        Creating a custom command                  15 (1)
        Creating a custom button                   16 (1)
        Developing new worksheet functions         16 (1)
        Creating complete, macro-driven            16 (1)
        applications
        Creating custom add-ins for Excel          16 (1)
      Advantages and Disadvantages of VBA          16 (2)
        VBA advantages                             17 (1)
        VBA disadvantages                          17 (1)
      VBA in a Nutshell                            18 (2)
      An Excursion into Versions                   20 (3)
    Chapter 2 Jumping Right In                     23 (12)
      First Things First                           23 (2)
        Excel 2010 Users                           24 (1)
        Excel 2007 Users                           24 (1)
      What You'll Be Doing                         25 (1)
      Taking the First Steps                       25 (1)
      Recording the Macro                          26 (1)
      Testing the Macro                            27 (1)
      Examining the Macro                          27 (3)
      Modifying the Macro                          30 (1)
      Saving Workbooks That Contain Macros         30 (1)
      Understanding Macro Security                 31 (2)
      More about the NameAndTime Macro             33 (2)
  Part II How VBA Works with Excel                 35 (58)
    Chapter 3 Working in the Visual Basic Editor   37 (32)
      What Is the Visual Basic Editor?             37 (3)
        Activating the VBE                         37 (1)
        Understanding VBE components               38 (2)
      Working with the Project Window              40 (2)
        Adding a new VBA module                    41 (1)
        Removing a VBA module                      41 (1)
        Exporting and importing objects            42 (1)
      Working with a Code Window                   42 (7)
        Minimizing and maximizing windows          42 (2)
        Creating a module                          44 (1)
        Getting VBA code into a module             44 (1)
        Entering code directly                     45 (2)
        Using the macro recorder                   47 (2)
        Copying VBA code                           49 (1)
      Customizing the VBA Environment              49 (6)
        Using the Editor tab                       50 (2)
        Using the Editor Format tab                52 (1)
        Using the General tab                      53 (1)
        Using the Docking tab                      54 (1)
      Chapter 4 Introducing the Excel Object       55 (3)
      Model
        Excel Is an Object?                        56 (1)
        Climbing the Object Hierarchy              56 (2)
        Wrapping Your Mind around Collections      58 (1)
      Referring to Objects                         58 (2)
        Navigating through the hierarchy           59 (1)
        Simplifying object references              60 (1)
      Diving into Object Properties and Methods    60 (4)
        Object properties                          62 (1)
        Object methods                             63 (1)
        Object events                              64 (1)
      Finding Out More                             64 (5)
        Using VBA's Help system                    65 (1)
        Using the Object Browser                   66 (1)
        Automatically listing properties and       66 (3)
        methods
    Chapter 5 VBA Sub and Function Procedures      69 (12)
      Subs versus Functions                        69 (2)
        Looking at Sub procedures                  70 (1)
        Looking at Function procedures             70 (1)
        Naming Subs and Functions                  71 (1)
      Executing Sub procedures                     71 (7)
        Executing the Sub procedure directly       73 (1)
        Executing the procedure from the Macro     74 (1)
        dialog box
        Executing a macro by using a shortcut      75 (1)
        key
        Executing the procedure from a button      76 (2)
        or shape
        Executing the procedure from another       78 (1)
        procedure
      Executing Function procedures                78 (3)
        Calling the function from a Sub            79 (1)
        procedure
        Calling a function from a worksheet        79 (2)
        formula
    Chapter 6 Using the Excel Macro Recorder       81 (12)
      Is it Live or is It VBA?                     81 (1)
      Recording Basics                             82 (2)
      Preparing to Record                          84 (1)
      Relative or Absolute?                        84 (3)
        Recording in absolute mode                 84 (1)
        Recording in relative mode                 85 (2)
      What Gets Recorded?                          87 (1)
      Recording Options                            88 (2)
        Macro name                                 88 (1)
        Shortcut key                               89 (1)
        Store Macro in                             89 (1)
        Description                                89 (1)
      Is This Thing Efficient?                     90 (3)
  Part III Programming Concepts                    93 (140)
    Chapter 7 Essential VBA Language Elements      95 (20)
      Using Comments in Your VBA Code              95 (2)
      Using Variables, Constants, and Data Types   97 (12)
        Understanding variables                    97 (1)
        What are VBA's data types?                 98 (1)
        Declaring and scoping variables            99 (6)
        Working with constants                     105(1)
        Pre-made constants                         106(1)
        Working with strings                       107(1)
        Working with dates                         108(1)
      Using Assignment Statements                  109(3)
        Assignment statement examples              109(1)
        About that equal sign                      109(1)
        Smooth operators                           110(2)
      Working with Arrays                          112(2)
        Declaring arrays                           112(1)
        Multidimensional arrays                    113(1)
        Dynamic arrays                             113(1)
      Using Labels                                 114(1)
    Chapter 8 Working with Range Objects           115(14)
      A Quick Review                               115(2)
      Other Ways to Refer to a Range               117(2)
        The Cells property                         117(1)
        The Offset property                        118(1)
        Referring to entire columns and rows       119(1)
      Some Useful Range Object Properties          119(7)
        The Value property                         120(1)
        The Text property                          121(1)
        The Count property                         121(1)
        The Column and Row properties              121(1)
        The Address property                       122(1)
        The HasFormula property                    122(1)
        The Font property                          123(1)
        The Interior property                      123(1)
        The Formula property                       124(2)
        The NumberFormat property                  126(1)
      Some Useful Range Object Methods             126(3)
        The Select method                          126(1)
        The Copy and Paste methods                 127(1)
        The Clear method                           128(1)
        The Delete method                          128(1)
    Chapter 9 Using VBA and Worksheet Functions    129(12)
      What is a Function?                          129(1)
      Using Built-In VBA Functions                 130(5)
        VBA function examples                      130(2)
        VBA functions that do more than return     132(1)
        a value
        Discovering VBA functions                  133(2)
      Using Worksheet Functions in VBA             135(4)
        Worksheet function examples                136(2)
        Entering worksheet functions               138(1)
      More about Using Worksheet Functions         139(1)
      Using Custom Functions                       139(2)
    Chapter 10 Controlling Program Flow and        141(20)
    Making Decisions
      Going with the Flow, Dude                    141(1)
      The Go to Statement                          142(9)
        Decisions, decisions                       143(1)
        The If-Then structure                      143(5)
        The Select Case structure                  148(3)
      Knocking Your Code for a Loop                151(7)
        For-Next loops                             152(4)
        Do-While loop                              156(1)
        Do-Until loop                              157(1)
      Looping through a Collection                 158(3)
    Chapter 11 Automatic Procedures and Events     161(22)
      Preparing for the Big Event                  161(3)
        Are events useful?                         163(1)
        Programming event-handler procedures       164(1)
      Where Does the VBA Code Go?                  164(1)
      Writing an Event-Handler Procedure           165(2)
      Introductory Examples                        167(3)
        The Open event for a workbook              167(2)
        The BeforeClose event for a workbook       169(1)
        The BeforeSave event for a workbook        169(1)
      Examples of Activation Events                170(4)
        Activate and deactivate events in a        170(1)
        sheet
        Activate and deactivate events in a        171(2)
        workbook
        Workbook activation events                 173(1)
      Other Worksheet-Related Events               174(3)
        The BeforeDoubleClick event                174(1)
        The BeforeRightClick event                 174(1)
        The Change event                           175(2)
      Events Not Associated with Objects           177(6)
        The OnTime event                           178(2)
        Keypress events                            180(3)
    Chapter 12 Error-Handling Techniques           183(14)
      Types of Errors                              183(1)
      An Erroneous Example                         184(4)
        The macro's not quite perfect              185(1)
        The macro is still not perfect             186(1)
        Is the macro perfect yet?                  186(1)
        Giving up on perfection                    187(1)
      Handling Errors Another Way                  188(2)
        Revisiting the EnterSquareRoot procedure   188(1)
        About the On Error statement               189(1)
      Handling Errors: The Details                 190(4)
        Resuming after an error                    190(2)
        Error handling in a nutshell               192(1)
        Knowing when to ignore errors              192(1)
        Identifying specific errors                193(1)
      An Intentional Error                         194(3)
    Chapter 13 Bug Extermination Techniques        197(14)
      Species of Bugs                              197(1)
      Identifying Bugs                             198(1)
      Debugging Techniques                         199(3)
        Examining your code                        199(1)
        Using the MsgBox function                  200(1)
        Inserting Debug Print statements           201(1)
        Using the VBA debugger                     202(1)
      About the Debugger                           202(6)
        Setting breakpoints in your code           202(4)
        Using the Watch window                     206(1)
        Using the Locals Window                    207(1)
      Bug Reduction Tips                           208(3)
    Chapter 14 VBA Programming Examples            211(22)
      Working with Ranges                          211(9)
        Copying a range                            212(1)
        Copying a variable-sized range             213(1)
        Selecting to the end of a row or column    214(1)
        Selecting a row or column                  215(1)
        Moving a range                             215(1)
        Looping through a range efficiently        216(1)
        Looping through a range efficiently        217(1)
        (Part II)
        Prompting for a cell value                 218(1)
        Determining the selection type             219(1)
        Identifying a multiple selection           219(1)
      Changing Excel Settings                      220(2)
        Changing Boolean settings                  221(1)
        Changing non-Boolean settings              221(1)
      Working with Charts                          222(5)
        Modifying the chart type                   224(1)
        Looping through the ChartObjects           224(1)
        collection
        Modifying chart properties                 225(1)
        Applying chart formatting                  225(2)
      VBA Speed Tips                               227(6)
        Turning off screen updating                227(1)
        Turning off automatic calculation          228(1)
        Eliminating those pesky alert messages     228(1)
        Simplifying object references              229(1)
        Declaring variable types                   230(1)
        Using the With-End With structure          231(2)
  Part IV Communicating with Your Users            233(98)
    Chapter 15 Simple Dialog Boxes                 235(18)
      UserForm Alternatives                        235(1)
      The MsgBox Function                          236(5)
        Getting a response from a message box      237(1)
        Customizing message boxes                  238(3)
      The InputBox Function                        241(3)
        InputBox syntax                            241(1)
        An InputBox example                        242(2)
      The GetOpenFilename Method                   244(4)
        The syntax for the GetOpenFilename         244(1)
        method
        A GetOpenFilename example                  245(2)
        Selecting multiple files                   247(1)
      The GetSaveAsFilename Method                 248(1)
      Getting a Folder Name                        249(1)
      Displaying Excel's Built-in Dialog Boxes     250(3)
    Chapter 16 UseForm Basics                      253(16)
      Knowing When to Use a UserForm               253(1)
      Creating UserForms: An Overview              254(1)
      Working with UserForms                       255(5)
        Inserting a new UserForm                   255(1)
        Adding controls to a UserForm              256(1)
        Changing properties for a UserForm         257(1)
        control
        Viewing the UserForm Code window           258(1)
        Displaying a UserForm                      259(1)
        Using information from a UserForm          259(1)
      A UserForm Example                           260(9)
        Creating the UserForm                      260(1)
        Adding the CommandButtons                  261(1)
        Adding the OptionButtons                   262(1)
        Adding event-handler procedures            263(2)
        Creating a macro to display the dialog     265(1)
        box
        Making the macro available                 266(1)
        Testing the macro                          267(2)
    Chapter 17 Using UserForm Controls             269(20)
      Getting Started with Dialog Box Controls     269(4)
        Adding controls                            269(1)
        Introducing control properties             270(3)
      Dialog Box Controls: The Details             273(11)
        CheckBox Control                           274(1)
        ComboBox Control                           274(1)
        CommandButton control                      275(1)
        Frame control                              276(1)
        Image control                              276(1)
        Label control                              277(1)
        ListBox control                            278(1)
        MultiPage control                          279(1)
        OptionButton control                       279(1)
        RefEdit control                            280(1)
        ScrollBar control                          281(1)
        SpinButton control                         282(1)
        TabStrip control                           282(1)
        TextBox control                            282(1)
        ToggleButton control                       283(1)
      Working with Dialog Box Controls             284(3)
        Moving and resizing controls               284(1)
        Aligning and spacing controls              284(1)
        Accommodating keyboard users               285(2)
        Testing a UserForm                         287(1)
      Dialog Box Aesthetics                        287(2)
    Chapter 18 UserForm Techniques and Tricks      289(24)
      Using Dialog Boxes                           289(1)
      A UserForm Example                           289(7)
        Creating the dialog box                    290(2)
        Writing code to display the dialog box     292(1)
        Making the macro available                 292(1)
        Trying out your dialog box                 293(1)
        Adding event-handler procedures            294(1)
        Validating the data                        295(1)
        Now the dialog box works                   296(1)
      More UserForm Examples                       296(15)
        A ListBox example                          296(5)
        Selecting a range                          301(1)
        Using multiple sets of OptionButtons       302(1)
        Using a SpinButton and a TextBox           303(2)
        Using a UserForm as a progress indicator   305(3)
        Creating a tabbed dialog box               308(2)
        Displaying a chart in a UserForm           310(1)
      A Dialog Box Checklist                       311(2)
    Chapter 19 Accessing Your Macros through       313(18)
    the User Interface
      What Happened to CommandBars?                313(1)
      Ribbon Customization                         314(7)
        Customizing the Ribbon manually            314(2)
        Customizing the Ribbon with XML            316(5)
      Customizing Shortcut Menus                   321(5)
        Commanding the CommandBars collection      321(1)
        Listing all shortcut menus                 321(1)
        Referring to CommandBars                   322(1)
        Referring to controls in a CommandBar      323(1)
        Properties of CommandBar controls          324(2)
      VBA Shortcut Menu Examples                   326(3)
        Adding a new item to the Cell shortcut     326(2)
        menu
        Disabling a shortcut menu                  328(1)
      Creating a Custom Toolbar                    329(2)
  Part V Putting It All Together                   331(30)
    Chapter 20 Creating Worksheet Functions ---    333(16)
    and Living to Tell about It
      Why Create Custom Functions?                 333(1)
      Understanding VBA Function Basics            334(1)
      Writing Functions                            335(1)
      Working with Function Arguments              335(1)
      Function Examples                            336(7)
        A function with no argument                336(1)
        A function with one argument               336(2)
        A function with two arguments              338(1)
        A function with a range argument           339(1)
        A function with an optional argument       340(2)
        A function with an indefinite number of    342(1)
        arguments
      Functions That Return an Array               343(2)
        Returning an array of month names          343(1)
        Returning a sorted list                    344(1)
      Using the Insert Function Dialog Box         345(4)
        Displaying the function's description      346(1)
        Argument descriptions                      347(2)
    Chapter 21 Creating Excel Add-Ins              349(12)
      Okay...So What's an Add-In?                  349(1)
      Why Create Add-Ins?                          350(1)
      Working with Add-Ins                         351(1)
      Add-In Basics                                352(1)
      An Add-In Example                            353(8)
        Setting up the workbook                    353(2)
        Testing the workbook                       355(1)
        Adding descriptive information             356(1)
        Protecting the VBA code                    357(1)
        Creating the add-in                        357(1)
        Opening the add-in                         357(1)
        Distributing the add-in                    358(1)
        Modifying the add-in                       359(2)
  Part VI The Part of Tens                         361(10)
    Chapter 22 Ten VBA Questions (And Answers)     363(8)
      Chapter 23 (Almost) Ten Excel Resources      367(1)
      The VBA Help System                          367(1)
      Microsoft Product Support                    367(1)
      Internet Newsgroups                          368(1)
      Internet Web Sites                           369(1)
      Excel Blogs                                  369(1)
      Google                                       369(1)
      Bing                                         369(1)
      Local User Groups                            370(1)
      My Other Books                               370(1)
  Bonus Chapters See companion Web site
  (www.dummies.com/go/excelubaprogramming/d2e)
    Bonus Chapter 1 Working with Colors
    Companion web site
    Bonus Chapter 2 Ten VBA Do's and Don'ts
    companion Web site
Index                                              371