Introduction 1 About This Book 1 Typographical conventions 2 Macro security 3 Foolish Assumptions 5 Icons Used in This Book 5 Beyond the Book 6 Where to Go from Here 6 Part 1: Starting Excel VBA Programming 7 Chapter 1: Getting to Know VBA 9 Understanding VBA Basics 9 Knowing What VBA Can Do 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 Getting the Most from VBA 12 Knowing what VBA does best 12 Recognizing the disadvantages of using VBA 13 Understanding VBA Concepts 14 Ensuring Excel Compatibility 16 Chapter 2: Building Simple Macros 17 Displaying the Developer Tab 17 Creating a Macro 18 Preparing the Environment 18 Recording a Macro 19 Running the Macro 21 Viewing a Macro in the Visual Basic Editor 22 Modifying the Macro 24 Saving Workbooks That Contain Macros 25 Understanding Macro Security 25 Part 2: Employing VBA with Excel 29 Chapter 3: Working in the Visual Basic Editor 31 Getting to Know the Visual Basic Editor 31 Activating the VBE 32 Exploring VBE components 32 Working with the Project Explorer 34 Adding a new VBA module 35 Working with a Code Pane 36 Minimizing and maximizing windows 36 Looking at the parts of a module 37 Getting VBA code into a module 38 Entering code directly 38 Using the macro recorder 41 Copying VBA code 44 Customizing the VBE 44 Using the Editor tab 44 Using the Editor Format tab 47 Using the General tab 48 Using the Docking tab 48 Chapter 4: Introducing the Excel Object Model 51 Working with the Excel Object Model 51 Understanding the object hierarchy 52 Referring to objects 53 Diving into Object Properties and Methods 57 Setting object properties 58 Taking action with object methods 60 Triggering actions with object events 61 Finding Out More from VBA Resources 62 Using VBA''s Help system 62 Using the Object Browser 63 Automatically listing properties and methods 64 Chapter 5: VBA Sub and Function Procedures 67 Understanding Subs versus Functions 67 Looking at Sub procedures 68 Looking at Function procedures 68 Naming Subs and Functions 69 Executing Sub procedures 70 Executing the Sub procedure directly 72 Executing the procedure from the Macro dialog box 73 Executing a macro by using a shortcut key 74 Executing the procedure from a button or shape 75 Executing the procedure from another procedure 77 Executing the procedure from the Immediate window 77 Executing Function Procedures 78 Calling the function from a Sub procedure 78 Calling a function from the Immediate window 79 Calling a function from a worksheet formula 80 Chapter 6: Using the Excel Macro Recorder 83 Recording Basics 84 Preparing to Record 85 Choosing Between Relative and Absolute Mode 86 Recording in absolute mode 86 Recording in relative mode 87 Watching the Macro Recorder in Action 89 Specifying Recording Options for Your Macro 91 Streamlining Code Generated by the Macro Recorder 92 Part 3: Programming Concepts 97 Chapter 7: Essential VBA Language Elements 99 Using Comments in Your VBA Code 99 Using Variables, Constants, and Data Types 101 Understanding variables 101 What are VBA''s data types? 103 Declaring and scoping variables 103 Working with constants 110 Using premade constants 110 Working with strings 111 Working with dates 112 Using Assignment Statements 113 Assignment statement examples 113 About that equal sign 114 Smooth operators 114 Working with Arrays 116 Declaring arrays 116 Multidimensional arrays 117 Dynamic arrays 117 Using Labels 118 Chapter 8: Working with Range Objects 119 Referring to Range Objects 119 Referring to a Range Using Properties 121 The Cells property 122 The Offset property 122 The Resize property 123 Working with Range Object Properties 124 The Value property 124 The Text property 125 The Count property 126 The Column and Row properties 126 The Address property 126 The HasFormula property 127 The Font property 128 The Interior property 128 The Formula property 130 The NumberFormat property 131 Taking Action with Range Object Methods 131 The Select method 132 The Copy and Paste methods 132 The Clear method 133 The Delete method 133 Chapter 9: Using VBA and Worksheet Functions 135 Understanding Functions 135 Using Built-In VBA Functions 136 Working with dates and times 136 Manipulating strings 138 Identifying objects and data 139 VBA functions that do more than return a value 140 Discovering VBA functions 141 Using Worksheet Functions in VBA 144 Worksheet function examples 144 Entering worksheet functions 147 More about using worksheet functions 148 Using Custom Functions 148 Chapter 10: Controlling Program Flow and Making Decisions 151 Going with the Flow, Dude 151 The GoTo Statement 152 Decisions, Decisions 154 The If-Then structure 154 The Select Case structure 158 Knocking Your Code for a Loop 162 For-Next loops 162 Do-While loop 167 Do-Until loop 168 Using For Each-Next Loops with Collections 168 Chapter 11: Automatic Procedures and Events 171 Preparing for the Big Event 171 Learning when to use event procedures 173 Programming event-handler procedures 174 Knowing Where to Put the Event Code 174 Writing an Event-Handler Procedure 175 Triggering Workbook Events 177 The Open event for a workbook 177 The BeforeClose event for a workbook 179 The BeforeSave event for a workbook 180 Using Activation Events 181 Activate and deactivate events in a sheet 181 Activate and deactivate events in a workbook 182 Workbook activation events 184 Programming Worksheet-Related Events 185 The BeforeDoubleClick event 185 The BeforeRightClick event 185 The Change event 186 Understanding Events Not Associated with Objects 188 The OnTime event 188 Keypress events 190 Chapter 12: Error-Handling Techniques 193 Types of Errors 193 An Erroneous Macro Example 194 Not-quite-perfect macros 195 Perfecting the macro 196 Giving up on perfection 198 Alternate Ways of Handling Errors 198 Revisiting the EnterSquareRoot procedure 198 Trapping errors with the On Error statement 200 Handling Errors: The Details 200 Resuming after an error 200 Error handling in a nutshell 202 An Intentional Error 203 Chapter 13: Bug Extermination Techniques 205 Species of Bugs 205 Identifying Bugs 207 Debugging Techniques 208 Examining your code 208 Using the MsgBox function 208 Inserting Debug.Print statements 210 Using the VBA debugger 210 Using the Debugger''s Tools 211 Setting breakpoints in your code 211 Using the Watches window 214 Using the Locals window 216 Bug Reduction Tips 217 Chapter 14: VBA Programming Examples 219 Working with Ranges 220 Copying a range 220 Copying a variable-size range 221 Selecting to the end of a row or column 223 Selecting a row or column 224 Moving a range 224 Looping through a range efficiently 225 Looping through a range efficiently (Part II) 227 Prompting for a cell value 227 Determining the selection type 228 Identifying a multiple selection 229 Changing Excel Settings 230 Changing Boolean settings 230 Changing non-Boolean settings 231 Working with Charts 231 AddChart versus AddChart2 232 Modifying the chart type 234 Looping through the ChartObjects collection 234 Modifying chart properties 235 Applying chart formatting 235 VBA Speed Tips 237 Turning off screen updating 237 Turning off automatic calculation 238 Eliminating those pesky alert messages 239 Simplifying object references 240 Declaring variable types 241 Using the With-End With structure 242 Part 4: Communicating with Your Users 243 Chapter 15: Simple Dialog Boxes 245 Interacting with the User in VBA 246 Displaying Messages with the MsgBox Function 247 Displaying a simple message box 247 Getting a response from a message box 248 Customizing message boxes 250 Getting Data with an Input Box 252 Understanding the InputBox syntax 253 Using the InputBox function 253 Using the InputBox method 255 Allowing the User to Select a File or Folder 256 Constructing a GetOpenFilename statement 256 Selec.