Introduction xxix Part 1 Recording Macros and Getting Started with VBA 1 Chapter 1 Recording and Running Macros in the Office Applications 3 What Is VBA and What Can You Do with It? 3 The Difference between Visual Basic and Visual Basic for Applications 4 What Are Visual Basic NET and Visual Basic Express? 5 Understanding Macro Basics 5 Recording a Macro 6 Displaying the Developer Tab on the Ribbon 7 Planning the Macro 8 Starting the Macro Recorder 10 Naming a Macro 13 Running a Macro 24 Recording a Sample Word Macro 25 Recording a Sample Excel Macro 27 Create a Personal Macro Workbook If You Don''t Have One Yet 28 Record the Macro 29 Specifying How to Trigger an Existing Macro 30 Assigning a Macro to a Quick Access Toolbar Button in Word 30 Assigning a Macro to a Shortcut Key Combination 31 Deleting a Macro 31 The Bottom Line 33 Chapter 2 Getting Started with the Visual Basic Editor 35 Opening the Visual Basic Editor 35 Opening the Visual Basic Editor with a Macro Selected 36 Opening the Visual Basic Editor Directly 37 Navigating to a Macro 37 Using the Visual Basic Editor''s Main Windows 38 The Project Explorer 39 The Object Browser 42 The Code Window 43 The Properties Window 48 The Immediate Window 50 Setting Properties for a Project 51 Customizing the Visual Basic Editor 53 Choosing Editor and View Preferences 54 Choosing and Laying Out the Editor Windows 61 Customizing the Toolbar and Menu Bar 61 Customizing the Toolbox 62 The Bottom Line 66 Chapter 3 Editing Recorded Macros 69 Testing a Macro in the Visual Basic Editor 70 Stepping Through a Macro 71 Setting Breakpoints 73 Commenting Out Lines 74 Stepping Out of a Macro 75 Editing a Word Macro 75 Stepping Through the Transpose_Word_Right Macro 77 Running the Transpose_Word_Right Macro 77 Creating a Transpose_Word_Left Macro 77 Saving Your Work 79 Editing an Excel Macro 79 Unhiding the Personal Macro Workbook 79 Opening a Macro for Editing 81 Editing a Macro 82 Editing a PowerPoint Macro 84 Reducing the Size of Your Macro 88 Saving Your Work 89 The Bottom Line 90 Chapter 4 Creating Code from Scratch in the Visual Basic Editor 93 Setting Up the Visual Basic Editor to Create Macros 93 Creating a Procedure for Word 95 Creating a Macro for Excel 101 Creating a Procedure for PowerPoint 106 Creating a Procedure for Access 112 The Bottom Line 113 Part 2 Learning How to Work with VBA 115 Chapter 5 Understanding the Essentials of VBA Syntax 117 Getting Ready 117 Procedures 118 Functions 119 Subprocedures 119 Statements 119 Keywords 123 Expressions 124 Operators 124 Variables 124 Constants 126 Arguments 126 Specifying Argument Names vs Omitting Argument Names 127 When to Include the Parentheses around an Argument List 128 Objects 129 Collections 129 Properties 130 Methods 130 Events 130 The Bottom Line 132 Chapter 6 Working with Variables, Constants, and Enumerations 135 Working with Variables 136 Choosing Names for Variables 136 Declaring a Variable 138 Choosing the Scope and Lifetime of a Variable 141 Specifying the Data Type for a Variable 148 Working with Constants 155 Declaring Your Own Constants 155 Choosing the Scope or Lifetime for Constants 156 Working with Enumerations 156 The Bottom Line 157 Chapter 7 Using Array Variables 159 What Is an Array? 159 Declaring an Array 161 Storing Values in an Array 163 Multidimensional Arrays 164 Declaring a Dynamic Array 165 Redimensioning an Array 165 Returning Information from an Array 166 Erasing an Array 166 Determining Whether a Variable Is an Array 166 Finding the Bounds of an Array 167 Sorting an Array 167 Searching an Array 171 Performing a Linear Search Through an Array 172 Binary Searching an Array 177 The Bottom Line 182 Chapter 8 Finding the Objects, Methods, and Properties You Need 185 What Is an Object? 185 The Benefits of OOP 185 Understanding Creatable Objects 187 Properties 187 Methods 188 Working with Collections 190 Working with an Object in a Collection 191 Adding an Object to a Collection 192 Finding the Objects You Need 192 Using the Macro Recorder to Add Code for the Objects You Need 192 Using the Object Browser 195 Using Help to Find the Object You Need 201 Using the Auto List Members Feature 205 Using Object Variables to Represent Objects 206 Team Programming and OOP 209 The Bottom Line 211 Part 3 Making Decisions and Using Loops and Functions 213 Chapter 9 Using Built-In Functions 215 What Is a Function? 215 Using Functions 217 Passing Arguments to a Function 219 Using Functions to Convert Data 220 Using the Asc Function to Return a Character Code 221 Using the Val Function to Extract a Number from the Start of a String 221 Using the Str Function to Convert a Number into a String 223 Using the Format Function to Format an Expression 224 Using Predefined Numeric Formats 225 Creating a Numeric Format 226 Creating a Date or Time Format 227 Creating a String Format 228 Using the Chr Function and Constants to Enter Special Characters in a String 229 Using Functions to Manipulate Strings 230 Using the Left, Right, and Mid Functions to Return Part of a String 230 Using the Left Function 232 Using the Right Function 232 Using the Mid Function 233 Using InStr and InStrRev to Find a String Within Another String 235 Using LTrim, RTrim, and Trim to Remove Spaces from a String 237 Using Len to Check the Length of a String 238 Using StrConv, LCase, and UCase to Change the Case of a String 240 Using the StrComp Function to Compare Apples to Apples 241 Using VBA''s Mathematical Functions 242 Using VBA''s Date and Time Functions 244 Using the DatePart t Function to Parse Dates 244 Calculating Time Intervals Using the DateDiff f Function 245 Using the DateAdd d Function to Add or Subtract Time from a Date 246 Using File-Management Functions 246 Checking Whether a File Exists Using the Dir Function 246 Returning the Current Path 248 The Bottom Line 248 Chapter 10 Creating Your Own Functions 251 Components of a Function 252 Creating a Function 254 Starting a Function Manually 254 Starting a Function by Using the Add Procedure Dialog Box 254 Passing Arguments to a Function 256 Declaring the Data Types of Arguments 257 Specifying an Optional Argument 257 Controlling the Scope of a Function 258 Examples of Functions for All VBA-Enabled Office Applications 258 How Functions Return Information 260 Returning Text Data from a Function 260 Creating a Function for Word 263 Creating a Function for Excel 265 Creating a Function for PowerPoint 267 Creating a Function for Access 269 The Bottom Line 271 Chapter 11 Making Decisions in Your Code 273 How Do You Compare Things in VBA? 274 Testing Multiple Conditions by Using Logical Operators 275 If Blocks 278 If Then 278 If Then Else Statements 280 If Then ElseIf Else Statements 282 Creating Loops with If and GoTo 287 Nesting If Blocks 289 Select Case Blocks 291 Syntax 291 Example 292 When Order Matters 294 The Bottom Line 294 Chapter 12 Using Loops to Repeat Actions 297 When Should You Use a Loop? 297 Understanding the Basics of Loops 298 Using For Loops for Fixed Repetitions 299 For Next Loops 299 For Each Next Loops 308 Using an Exit For Statement 308 Using Do Loops for Variable Numbers of Repetitions 309 Do While Loop Loops 310 Do Loop While Loops 314 Do Until Loop Loops 316 Do Loop Until Loops 318 Using an Exit Do Statement 319 Is the Exit Do Statement Bad Practice? 320 While Wend Loops 321 Nesting Loops 322 Avoiding Infinite Loops 325 The Bottom Line 326 Part 4 Using Message Boxes, Input Boxes, and Dialog Boxes 327 Chapter 13 Getting User Input with Message Boxes and Input Boxes 329 Opening a Macro 330 Displaying Status-Bar Messages in Word and Excel 331 Message Boxes 333 The Pros and Cons of Message Boxes 333 Message-Box Syntax 334 Displaying a Simple Message Box 335 Displaying a Multiline Message Box 336 Choosing Buttons for a Message Box 337 Choosing an Icon for a Message Box 338 Setting a Default Button for a Message Box 339 Controlling the Modality of a Message Box 340 Specifying a Title for a Message Box 341 Title Bars Can Provide Useful Information 342 Adding a Help Button to a Message Box 342 Specifying a Help File for a Message Box 343 Using Some Arguments Without Others 344 Retrieving a Value from a Message Box 344 Input Boxes 345 Input-Bo.