Mastering VBA for Microsoft Office 365

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 Syntax 346

Retrieving Input from an Input Box 348

Forms: When Message Boxes and Input Boxes Won’t Suffice 348

The Bottom Line 349

Chapter 14 Creating Simple Custom Dialog Boxes 351

When Should You Use a Custom Dialog Box? 351

Creating a Custom Dialog Box 352

Designing a Dialog Box 354

Inserting a User Form 354

Choosing User-Form Grid Settings 355

Renaming a User Form 358

Adding Controls to the User Form 360

Grouping Controls 363

Renaming Controls 364

Moving a Control 365

Changing the Caption on a Control 367

Key Properties of the Toolbox Controls 368

Working with Groups of Controls 386

How to Group Controls 386

Ungrouping Controls 386

Sizing Grouped Controls 387

Deleting Grouped Controls 387

Working with One Control in a Group 387

Aligning Controls 388

Placing Controls 389

Adjusting the Tab Order of a Form 389

Linking a Form to a Procedure 391

Loading and Unloading a Form 392

Displaying and Hiding a Form 392

Setting a Default Command Button 393

Retrieving the User’s Choices from a Dialog Box 393

Returning a String from a Text Box 393

Returning a Value from an Option Button 394

Returning a Value from a Check Box 395

Returning a Value from a List Box 395

Returning a Value from a Combo Box 396

Examples Showing How to Connect Forms to Procedures 397

Word Example: The Move-Paragraph Procedure 397

General Example: Opening a File from a List Box 409

Building the User Form 409

Creating the Code for the User Form 411

Using an Application’s Built-In Dialog Boxes 415

Displaying a Built-In Dialog Box 415

Setting and Restoring Options in a Built-In Dialog Box 419

Which Button Did the User Choose in a Dialog Box? 419

Specifying a Time-Out for a Dialog Box 420

The Bottom Line 421

Chapter 15 Creating Complex Forms 423

Creating and Working with Complex Dialog Boxes 424

Updating a Dialog Box to Reflect the User’s Choices 424

Revealing a Hidden Part of a Form 424

Tracking a Procedure in a Form 429

Using Multipage Dialog Boxes and TabStrip Controls 432

Creating a Modeless Dialog Box 443

Specifying a Form’s Location Onscreen 444

Using Events to Control Forms 445

Events Unique to the UserForm Object 448

Events That Apply to Both UserForms and Container Controls 453

Events That Apply to Many or Most Controls 458

Events That Apply Only to a Few Controls 471

The Bottom Line 472

Part 5 Creating Effective Code 473

Chapter 16 Building Modular Code and Using Classes 475

Creating Modular Code 475

What Is Modular Code? 476

Advantages of Using Modular Code 476

The Bottom Line 503

Chapter 17 Debugging Your Code and Handling Errors 505

Principles of Debugging 505

The Different Types of Errors 507

Language Errors 507

Compile Errors 507

The Bottom Line 551

Chapter 18 Building Well-Behaved Code 537

What Is a Well-Behaved Macro? 537

Retaining or Restoring the User Environment 539

Chapter 19 Exploring VBA’s Security Features 553

Understanding How VBA Implements Security 553

Signing Your Macro Projects with Digital Signatures 557

What Is a Digital Certificate? 557

The Bottom Line 574

Part 6 Programming the Office Applications 575

Chapter 20 Understanding the Word Object Model and Key Objects 577

Examining the Word Object Model 577

Working with the Documents Collection and the Document Object 581

Creating a Document 581

The Bottom Line 609

Chapter 21 Working with Widely Used Objects in Word 611

Using Find and Replace via VBA 611

Understanding the Syntax of the Execute Method 613

Putting Find and Replace to Work 616

Working with Headers, Footers, and Page Numbers 617

The Bottom Line 642

Chapter 22 Understanding the Excel Object Model and Key Objects 645

Getting an Overview of the Excel Object Model 645

Understanding Excel’s Creatable Objects 646

Managing Workbooks 647

Creating a Workbook 647

The Bottom Line 670

Chapter 23 Working with Widely Used Objects in Excel 671

Working with Charts 671

Creating a Chart 671

Specifying the Source Data for the Chart 673

The Bottom Line 685

Chapter 24 Understanding the PowerPoint Object Model and Key Objects 687

Getting an Overview of the PowerPoint Object Model 687

Understanding PowerPoint’s Creatable Objects 688

Working with Presentations 689

The Bottom Line 707

Chapter 25 Working with Shapes and Running Slide Shows 709

Working with Shapes 709

Adding Shapes to Slides 709

Deleting a Shape 715

The Bottom Line 729

Chapter 26 Understanding the Outlook Object Model and Key Objects 731

Getting an Overview of the Outlook Object Model 731

The Outlook Object Model 732

Understanding Where Outlook Stores VBA Macros 733

Understanding Outlook’s Most Common Creatable Objects 734

The Bottom Line 752

Chapter 27 Working with Events in Outlook 755

How Event-Handler Procedures Differ from Ordinary Macros 755

Working with Application-Level Events 756

Using the Startup Event 758

The Bottom Line 773

Chapter 28 Understanding the Access Object Model and Key Objects 775

Getting Started with VBA in Access 775

Creating a Module in the VBA Editor 778

Creating a Function 778

The Bottom Line 800

Chapter 29 Accessing One Application from Another Application 801

Understanding the Tools Used to Communicate Between Applications 801

Using Automation to Transfer Information 802

Understanding Early and Late Binding 803

The Bottom Line 830

Appendix A The Bottom Line 833

Chapter 1: Recording and Running Macros in the Office Applications 833

Chapter 2: Getting Started with the Visual Basic Editor 835

Chapter 3: Editing Recorded Macros 836

Chapter 4: Creating Code from Scratch in the Visual Basic Editor 836

Chapter 5: Understanding the Essentials of VBA Syntax 839

Chapter 6: Working with Variables, Constants, and Enumerations 840

Chapter 7: Using Array Variables 842

Chapter 8: Finding the Objects, Methods, and Properties You Need 843

Chapter 9: Using Built-in Functions 844

Chapter 10: Creating Your Own Functions 846

Chapter 11: Making Decisions in Your Code 847

Chapter 12: Using Loops to Repeat Actions 849

Chapter 13: Getting User Input with Message Boxes and Input Boxes 850

Chapter 14: Creating Simple Custom Dialog Boxes 851

Chapter 15: Creating Complex Forms 855

Chapter 16: Building Modular Code and Using Classes 856

Chapter 17: Debugging Your Code and Handling Errors 858

Chapter 18: Building Well-Behaved Code 859

Chapter 19: Exploring VBA’s Security Features 860

Chapter 20: Understanding the Word Object Model and Key Objects 862

Chapter 21: Working with Widely Used Objects in Word 863

Chapter 22: Understanding the Excel Object Model and Key Objects 864

Chapter 23: Working with Widely Used Objects in Excel 865

Chapter 24: Understanding the PowerPoint Object Model and Key Objects 866

Chapter 25: Working with Shapes and Running Slide Shows 867

Chapter 26: Understanding the Outlook Object Model and Key Objects 868

Chapter 27: Working with Events in Outlook 869

Chapter 28: Understanding the Access Object Model and Key Objects 870

Chapter 29: Accessing One Application from Another Application 871

Index 873