Introduction xxxi 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 Understanding Macro Basics 5 Recording a Macro 6 Displaying the Developer Tab on the Ribbon 7 Planning the Macro 7 Starting the Macro Recorder 8 Naming the Macro 11 Choosing How to Run a New Macro 16 Running a Macro 23 Recording a Sample Word Macro 24 Recording a Sample Excel Macro 27 Creating a Personal Macro Workbook 27 Recording the Macro 27 Specifying How to Trigger an Existing Macro 29 Assigning a Macro to a Quick Access Toolbar Button in Word 29 Assigning a Macro to a Shortcut Key Combination 29 Deleting a Macro 29 The Bottom Line 31 Chapter 2 * Getting Started with the Visual Basic Editor 33 Opening the Visual Basic Editor 33 Opening the Visual Basic Editor with a Macro Selected 34 Opening the Visual Basic Editor Directly 34 Navigating to a Macro 35 Using the Visual Basic Editor''s Main Windows 36 The Project Explorer 36 The Object Browser40 The Code Window 40 The Properties Window 45 The Immediate Window 48 Setting Properties for a Project 48 Customizing the Visual Basic Editor 51 Choosing Editor and View Preferences 51 Choosing and Laying Out the Editor Windows 59 Customizing the Toolbar and Menu Bar 59 Customizing the Toolbox 60 The Bottom Line 64 Chapter 3 * Editing Recorded Macros 67 Testing a Macro in the Visual Basic Editor 68 Stepping Through a Macro 69 Setting Breakpoints 71 Commenting Out Lines 71 Stepping Out of a Macro 72 Editing a Word Macro 73 Stepping Through the Transpose_Word_Right Macro 74 Running the Transpose_Word_Right Macro 75 Creating a Transpose_Word_Left Macro 75 Saving Your Work 76 Editing the Excel Macro 76 Unhiding the Personal Macro Workbook 77 Opening a Macro for Editing 77 Editing a Macro 78 Editing a PowerPoint Macro 80 Saving Your Work 85 The Bottom Line 87 Chapter 4 * Creating Code from Scratch in the Visual Basic Editor 89 Setting Up the Visual Basic Editor to Create Macros 89 Creating a Procedure for Word 91 Creating a Macro for Excel 96 Creating a Procedure for PowerPoint 101 Creating a Procedure for Access 106 The Bottom Line 107 Part 2 * Learning How to Work with VBA 109 Chapter 5 * Understanding the Essentials of VBA Syntax 111 Getting Ready 111 Procedures 112 Functions 113 Subprocedures 113 Statements 114 Keywords 117 Expressions 118 Operators 118 Variables 118 Constants 120 Arguments 120 Specifying Argument Names vsOmitting Argument Names 121 Including Parentheses Around the Argument List 122 Objects 123 Collections 123 Properties 123 Methods 124 Events 124 The Bottom Line 126 Chapter 6 * Working with Variables, Constants, and Enumerations 129 Working with Variables 130 Choosing Names for Variables 130 Declaring a Variable 131 Choosing the Scope and Lifetime of a Variable 134 Specifying the Data Type for a Variable 141 Working with Constants 147 Declaring Your Own Constants 148 Choosing the Scope or Lifetime for Your Constants 148 Working with Enumerations 149 The Bottom Line 150 Chapter 7 * Using Array Variables 151 What Is an Array? 151 Declaring an Array 153 Storing Values in an Array 155 Multidimensional Arrays 156 Declaring a Dynamic Array 157 Redimensioning an Array 157 Returning Information from an Array 158 Erasing an Array 158 Determining Whether a Variable Is an Array 158 Finding the Bounds of an Array 158 Sorting an Array 159 Searching an Array 163 Performing a Linear Search Through an Array 163 Binary Searching an Array 168 The Bottom Line 173 Chapter 8 * Finding the Objects, Methods, and Properties You Need 175 What Is an Object? 175 The Benefi ts of OOP 175 Understanding Creatable Objects 177 Properties 177 Methods 178 Working with Collections 180 Working with an Object in a Collection 181 Adding an Object to a Collection 182 Finding the Objects You Need 182 Using the Macro Recorder to Add Code for the Objects You Need 182 Using the Object Browser 185 Using Help to Find the Object You Need 191 Using the Auto List Members Feature 193 Using Object Variables to Represent Objects 194 Team Programming and OOP 197 The Bottom Line 199 Part 3 * Making Decisions and Using Loops and Functions 201 Chapter 9 * Using Built-In Functions 203 What Is a Function? 203 Using Functions 205 Passing Arguments to a Function 207 Using Functions to Convert Data 208 Using the Asc Function to Return a Character Code 210 Using the Val Function to Extract a Number from the Start of a String 210 Using the Str Function to Convert a Number into a String 212 Using the Format Function to Format an Expression 213 Using the Chr Function and Constants to Enter Special Characters in a String 218 Using Functions to Manipulate Strings 219 Using the Left, Right, and Mid Functions to Return Part of a String 221 Using InStr and InStrRev to Find a String Within Another String 224 Using LTrim, RTrim, and Trim to Remove Spaces from a String 227 Using Len to Check the Length of a String 228 Using StrConv, LCase, and UCase to Change the Case of a String 229 Using the StrComp Function to Compare Apples to Apples 231 Using VBA''s Mathematical Functions 231 Using VBA''s Date and Time Functions 232 Using the DatePart Function to Parse Dates 234 Calculating Time Intervals Using the Date Diff Function 235 Using the DateAdd Function to Add or Subtract Time from a Date 236 Using File-Management Functions 236 Checking Whether a File Exists Using the Dir Function 236 Returning the Current Path 238 The Bottom Line 238 Chapter 10 * Creating Your Own Functions 241 Components of a Function 242 Creating a Function 244 Starting a Function Manually 244 Starting a Function by Using the Add Procedure Dialog Box 244 Passing Arguments to a Function 245 Declaring the Data Types of Arguments 246 Specifying an Optional Argument 246 Controlling the Scope of a Function 247 Examples of Functions for Any VBA-Enabled Office Application 247 How Functions Return Information 248 Returning Text Data from a Function 249 Creating a Function for Word 251 Creating a Function for Excel 253 Creating a Function for PowerPoint 255 Creating a Function for Access 257 The Bottom Line 258 Chapter 11 * Making Decisions in Your Code 261 How Do You Compare Things in VBA? 262 Testing Multiple Conditions by Using Logical Operators 263 If Blocks 266 If.f Then 266 If.Then.Else Statements 268 If.f Then.ElseIf.f Else Statements 270 Creating Loops with If and GoTo 275 Nesting If Blocks 276 Select Case Blocks 278 Syntax 279 Example 279 When Order Matters 281 The Bottom Line 282 Chapter 12 * Using Loops to Repeat Actions 283 When Should You Use a Loop? 283 Understanding the Basics of Loops 284 Using For. Loops for Fixed Repetitions 285 For.

Next Loops 285 For Each.Next Loops 293 Using an Exit For Statement 294 Using Do. Loops for Variable Numbers of Repetitions 295 Do While.Loop Loops 295 Do.Loop While Loops 299 Do Until.Loop Loops301 Do.Loop Until Loops 303 Using an Exit Do Statement 305 Is the Exit Do Statement Bad Practice? 305 While.Wend Loops 306 Nesting Loops 307 Avoiding Infi nite Loops 310 The Bottom Line 310 Part 4 * Using Message Boxes, Input Boxes, and Dialog Boxes 313 Chapter 13 * Getting User Input with Message Boxes and Input Boxes 315 Opening a Macro 316 Displaying Status-Bar Messages in Word and Excel 317 Message Boxes 319 The Pros and Cons of Message Boxes 319 Message-Box Syntax 319 Displaying a Simple Message Box 320 Displaying a Multiline Message Box 321 Choosing Buttons for a Message Box 322 Choosing an Icon for a Message Box 323 Setting a Default Button for a Message Box 324 Controlling the Modality of a Message Box 326 Specifying a Title for a Message Box 327 Title Bars Can Provide Useful Information 327 Adding a Help Button to a Message Box 328 Specifying a Help File for a Message Box 328 Using Some Arguments Without Others 329 Retrieving a Value from a Message Box 330 Input Boxes 331 Input-Box Syntax 332 Retrieving Input from an Input Box 333 Forms: When Message Boxes and Input Boxes Won''t Suffice 334 The Bottom Line 334 Chapter 14 * Creating Simple Custom Dialog Boxes 337 When Should You Use a Custom Dialog Box? 337 Creati.