Richard Mansfield is a widely recognized expert on computer programming. He's the author of more than 44 computer books that have sold over half a million copies worldwide. They include Visual Basic .NET Power Toolkit, Visual Basic .NET Database Programming For Dummies, and Programming: A Beginner's Guide, as well as several previous editions of Mastering VBA for Microsoft Office. Richard is also the former editor of Compute! magazine.

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-Box...