Introduction 1 About This Book 1 Obligatory Typographical Conventions Section 2 Check Your Security Settings 3 Foolish Assumptions 4 Icons Used in This Book 5 Sample Files Online 5 Where to Go from Here 6 Part 1: Getting Started with Excel VBA Programming 7 Chapter 1: What Is VBA? 9 Okay, So What Is VBA? 9 What Can You Do with VBA? 10 Inserting a bunch of text 11 Automating a task you perform frequently 11 Automating repetitive operations 11 Creating a custom command 11 Creating a custom button 12 Developing new worksheet functions 12 Creating custom add-ins for Excel 12 Advantages and Disadvantages of VBA 12 VBA advantages 12 VBA disadvantages 13 VBA in a Nutshell 13 Excel Compatibility 16 Chapter 2: Jumping Right In 17 First Things First 17 What You''ll Be Doing 18 Taking the First Steps 18 Recording the Macro 19 Testing the Macro 21 Examining the Macro 21 Modifying the Macro 23 Saving Workbooks That Contain Macros 24 Understanding Macro Security 24 Revealing More about the NameAndTime Macro 26 Part 2: How VBA Works with Excel 29 Chapter 3: Working in the Visual Basic Editor 31 What Is the Visual Basic Editor? 31 Activating the VBE 32 Understanding VBE components 32 Working with the Project Window 34 Adding a new VBA module 35 Removing a VBA module 36 Exporting and importing objects 36 Working with a Code Window 37 Minimizing and maximizing windows 37 Creating a module 38 Getting VBA code into a module 39 Entering code directly 39 Using the macro recorder 42 Copying VBA code 45 Customizing the VBA Environment 45 Using the Editor tab 45 Using the Editor Format tab 48 Using the General tab 49 Using the Docking tab 50 Chapter 4: Introducing the Excel Object Model 51 Excel Is an Object? 52 Climbing Down the Object Hierarchy 52 Wrapping Your Mind around Collections 53 Referring to Objects 54 Navigating through the hierarchy 55 Simplifying object references 56 Diving into Object Properties and Methods 56 Object properties 58 Object methods 59 Object events 61 Finding Out More 61 Using VBA''s Help system 61 Using the Object Browser 62 Automatically listing properties and methods 63 Chapter 5: VBA Sub and Function Procedures 65 Understanding Subs versus Functions 65 Looking at Sub procedures 66 Looking at Function procedures 66 Naming Subs and Functions 67 Executing Sub procedures 68 Executing the Sub procedure directly 70 Executing the procedure from the Macro dialog box 70 Executing a macro by using a shortcut key 71 Executing the procedure from a button or shape 72 Executing the procedure from another procedure 74 Executing Function procedures 75 Calling the function from a Sub procedure 75 Calling a function from a worksheet formula 76 Chapter 6: Using the Excel Macro Recorder 79 Recording Basics 80 Preparing to Record 81 Relative or Absolute? 82 Recording in absolute mode 82 Recording in relative mode 83 What Gets Recorded? 84 Recording Options 86 Macro name 86 Shortcut key 87 Store Macro In option 87 Description 87 Is This Thing Efficient? 87 Part 3: Programming Concepts 91 Chapter 7: Essential VBA Language Elements 93 Using Comments in Your VBA Code 93 Using Variables, Constants, and Data Types 95 Understanding variables 95 What are VBA''s data types? 97 Declaring and scoping variables 98 Working with constants 104 Premade constants 105 Working with strings 106 Working with dates 107 Using Assignment Statements 108 Assignment statement examples 108 About that equal sign 108 Smooth operators 109 Working with Arrays 111 Declaring arrays 111 Multidimensional arrays 112 Dynamic arrays 112 Using Labels 113 Chapter 8: Working with Range Objects 115 A Quick Review 115 Other Ways to Refer to a Range 117 The Cells property 117 The Offset property 118 Some Useful Range Object Properties 119 The Value property 120 The Text property 121 The Count property 121 The Column and Row properties 121 The Address property 122 The HasFormula property 122 The Font property 123 The Interior property 124 The Formula property 125 The NumberFormat property 126 Some Useful Range Object Methods 127 The Select method 127 The Copy and Paste methods 127 The Clear method 128 The Delete method 128 Chapter 9: Using VBA and Worksheet Functions 131 What Is a Function? 131 Using Built-In VBA Functions 132 VBA function examples 132 VBA functions that do more than return a value 135 Discovering VBA functions 135 Using Worksheet Functions in VBA 138 Worksheet function examples 139 Entering worksheet functions 141 More about using worksheet functions 142 Using Custom Functions 142 Chapter 10: Controlling Program Flow and Making Decisions 145 Going with the Flow, Dude 145 The GoTo Statement 146 Decisions, Decisions 148 The If-Then structure 148 The Select Case structure 152 Knocking Your Code for a Loop 155 For-Next loops 156 Do-While loop 161 Do-Until loop 161 Using For Each-Next Loops with Collections 161 Chapter 11: Automatic Procedures and Events 165 Preparing for the Big Event 165 Are events useful? 167 Programming event-handler procedures 168 Where Does the VBA Code Go? 168 Writing an Event-Handler Procedure 169 Introductory Examples 171 The Open event for a workbook 171 The BeforeClose event for a workbook 173 The BeforeSave event for a workbook 174 Examples of Activation Events 175 Activate and deactivate events in a sheet 175 Activate and deactivate events in a workbook 176 Workbook activation events 178 Other Worksheet-Related Events 178 The BeforeDoubleClick event 178 The BeforeRightClick event 179 The Change event 179 Events Not Associated with Objects 181 The OnTime event 182 Keypress events 183 Chapter 12: Error-Handling Techniques 187 Types of Errors 187 An Erroneous Example 188 The macro''s not quite perfect 189 The macro is still not perfect 190 Is the macro perfect yet? 191 Giving up on perfection 192 Handling Errors Another Way 192 Revisiting the EnterSquareRoot procedure 192 About the On Error statement 194 Handling Errors: The Details 194 Resuming after an error 194 Error handling in a nutshell 196 Knowing when to ignore errors 197 Identifying specific errors 197 An Intentional Error 199 Chapter 13: Bug Extermination Techniques 201 Species of Bugs 201 Identifying Bugs 202 Debugging Techniques 203 Examining your code 204 Using the MsgBox function 204 Inserting Debug.Print statements 206 Using the VBA debugger 206 About the Debugger 207 Setting breakpoints in your code 207 Using the Watches window 210 Using the Locals window 211 Bug Reduction Tips 212 Chapter 14: VBA Programming Examples 213 Working with Ranges 214 Copying a range 214 Copying a variable-size range 215 Selecting to the end of a row or column 217 Selecting a row or column 218 Moving a range 218 Looping through a range efficiently 219 Looping through a range efficiently (Part II) 220 Prompting for a cell value 221 Determining the selection type 222 Identifying a multiple selection 223 Changing Excel Settings 223 Changing Boolean settings 224 Changing non-Boolean settings 225 Working with Charts 225 AddChart versus AddChart2 226 Modifying the chart type 228 Looping through the ChartObjects collection 228 Modifying chart properties 229 Applying chart formatting 229 VBA Speed Tips 231 Turning off screen updating 231 Turning off automatic calculation 232 Eliminating those pesky alert messages 233 Simplifying object references 233 Declaring variable types 234 Using the With-End With structure 235 Part 4: Communicating with Your Users 237 Chapter 15: Simple Dialog Boxes 239 UserForm Alternatives 239 The MsgBox Function 240 Displaying a simple message box 241 Getting a response from a message box 241 Customizing message boxes 243 The InputBox Function 246 InputBox syntax 246 An InputBox example 247 Another type of InputBox 248 The GetOpenFilename Method 249 The syntax for the GetOpenFilename method 250 A GetOpenFilename example 250 The GetSaveAsFilename Method 252 Getting a Folder Name 253 Displaying Excel''s Built-in Dialog Boxes 254 Chapter.