Mastering VBA for Office 2010

Introduction xxvii

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

Understanding Macro Basics 5

Recording a Macro 6

Running a Macro 20

Recording a Sample Word Macro 21

Recording a Sample Excel Macro 24

Assigning a Way of Running the Macro 25

Deleting a Macro 26

The Bottom Line 28

Chapter 2 Getting Started with the Visual Basic Editor 29

Opening the Visual Basic Editor 29

Using the Visual Basic Editor’s Main Windows 32

Setting Properties for a Project 43

Customizing the Visual Basic Editor 45

Closing the Visual Basic Editor and Returning to the Host Application 61

The Bottom Line 61

Chapter 3 Editing Recorded Macros 63

Testing a Macro in the Visual Basic Editor 63

Editing the Word Macro 68

Editing the Excel Macro 72

Editing a PowerPoint Macro 77

The Bottom Line 83

Chapter 4 Creating Code from Scratch in the Visual Basic Editor 85

Setting Up the Visual Basic Editor for Creating the Procedures 85

Creating a Procedure for Word 86

Creating a Procedure for Excel 92

Creating a Procedure for PowerPoint 96

Creating a Procedure for Access 101

The Bottom Line 102

Part 2 Learning How to Work with VBA 105

Chapter 5 Understanding the Essentials of VBA Syntax 107

Getting Ready 107

Procedures 108

Statements 109

Keywords 113

Expressions 113

Operators 113

Variables 114

Constants 114

Arguments 115

Objects 117

Collections 117

Properties 118

Methods 118

Events 118

The Bottom Line 120

Chapter 6 Working with Variables, Constants, and Enumerations 123

Working with Variables 123

Working with Constants 140

Working with Enumerations 142

The Bottom Line 142

Chapter 7 Using Array Variables 145

What is an Array? 145

Declaring an Array 146

Storing Values in an Array 148

Multidimensional Arrays 149

Declaring a Dynamic Array 150

Redimensioning an Array 150

Returning Information from an Array 151

Erasing an Array 151

Finding Out Whether a Variable is an Array 151

Finding the Bounds of an Array 152

Sorting an Array 152

Searching through an Array 156

The Bottom Line 165

Chapter 8 Finding the Objects, Methods, and Properties You Need 167

What is an Object? 167

Working with Collections 172

Finding the Objects You Need 174

Using Object Variables to Represent Objects 186

The Bottom Line 189

Part 3 Making Decisions and Using Loops and Functions 191

Chapter 9 Using Built-in Functions 193

What is a Function? 193

Using Functions 194

Using Functions to Convert Data from One Type to Another 198

Using Functions to Manipulate Strings 207

Using VBA’s Mathematical Functions 218

Using VBA’s Date and Time Functions 219

Using File-Management Functions 223

The Bottom Line 225

Chapter 10 Creating Your Own Functions 227

Components of a Function 228

Creating a Function 229

Examples of Functions for Any VBA-Enabled Application 233

Creating a Function for Word 237

Creating a Function for Excel 239

Creating a Function for PowerPoint 240

Creating a Function for Access 242

The Bottom Line 244

Chapter 11 Making Decisions in Your Code 245

How Do You Compare Things in VBA? 245

Testing Multiple Conditions by Using Logical Operators 247

Select Case Statements261

The Bottom Line 264

Chapter 12 Using Loops to Repeat Actions 265

When Should You Use a Loop? 265

Understanding the Basics of Loops 266

Using For… Loops for Fixed Repetitions 267

Using Do…Loops for Variable Numbers of Repetitions 276

While… Wend Loops 287

Nesting Loops 288

Avoiding Infinite Loops 290

The Bottom Line 291

Part 4 Using Message Boxes, Input Boxes, and Dialog Boxes 293

Chapter 13 Getting User Input with Message Boxes and Input Boxes 295

Opening a Procedure to Work On 296

Displaying Status Bar Messages in Word and Excel 297

Message Boxes 299

Input Boxes 311

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

The Bottom Line 314

Chapter 14 Creating Simple Custom Dialog Boxes 315

When Should You Use a Custom Dialog Box? 315

Creating a Custom Dialog Box 316

Linking a Dialog Box to a Procedure 352

Retrieving the User’s Choices from a Dialog Box 354

Examples of Connecting Dialog Boxes to Procedures 358

Using an Application’s Built-in Dialog Boxes from VBA 374

The Bottom Line 380

Chapter 15 Creating Complex Dialog Boxes 381

Creating and Working with Complex Dialog Boxes 382

Using Events to Control Forms 400

The Bottom Line 426

Part 5 Creating Effective Code 429

Chapter 16: Building Modular Code and Using Classes 431

Creating Modular Code 431

Creating and Using Classes 446

The Bottom Line 456

Chapter 17: Debugging Your Code and Handling Errors 457

Principles of Debugging 457

The Different Types of Errors 458

VBA’s Debugging Tools 463

Dealing with Infinite Loops 473

Dealing with Runtime Errors 474

Suppressing Alerts 481

Handling User Interrupts in Word, Excel, and Project 481

Documenting Your Code 483

The Bottom Line 484

Chapter 18: Building Well-Behaved Code 487

What is a Well-Behaved Procedure? 487

Retaining or Restoring the User Environment 488

Leaving the User in the Best Position to Continue Working 489

Keeping the User Informed during the Procedure 489

Making Sure a Procedure is Running under Suitable Conditions 496

Cleaning Up after a Procedure 497

The Bottom Line 499

Chapter 19: Securing Your Code with VBA’s Security Features 501

Understanding How VBA Implements Security 501

Signing Your Macro Projects with Digital Signatures 504

Choosing a Suitable Level of Security 514

Locking Your Code 521

The Bottom Line 523

Part 6 Programming the Office Applications 525

Chapter 20 Understanding the Word Object Model and Key Objects 527

Examining the Word Object Model 527

Working with the Documents Collection and the Document Object 530

Working with the Selection Object 543

Creating and Using Ranges 553

Manipulating Options 555

The Bottom Line 557

Chapter 21 Working with Widely Used Objects in Word 559

Using Find and Replace via VBA 559

Working with Headers, Footers, and Page Numbers 566

Working with Sections, Page Setup, Windows, and Views 571

Working with Tables 576

The Bottom Line 588

Chapter 22 Understanding the Excel Object Model and Key Objects 591

Getting an Overview of the Excel Object Model 591

Understanding Excel’s Creatable Objects 593

Managing Workbooks 593

Working with Worksheets 603

Working with the Active Cell or Selection 608

Working with Ranges 610

Setting Options 613

The Bottom Line 615

Chapter 23 Working with Widely Used Objects in Excel 617

Working with Charts 617

Working with Windows 623

Working with Find and Replace 626

Adding Shapes 629

The Bottom Line 629

Chapter 24 Understanding the PowerPoint Object Model and Key Objects 631

Getting an Overview of the PowerPoint Object Model 631

Understanding PowerPoint’s Creatable Objects 632

Working with Presentations 633

Working with Windows and Views 640

Working with Slides 643

Working with Masters 649

The Bottom Line 651

Chapter 25 Working with Shapes and Running Slide Shows 653

Working with Shapes 653

Working with Headers and Footers 665

Setting Up and Running a Slide Show 667

The Bottom Line 671

Chapter 26 Understanding the Outlook Object Model and Key Objects 673

Getting an Overview of the Outlook Object Model 673

Working with the Application Object 675

Understanding General Methods for Working with Outlook Objects 681

Working with Messages 684

Working with Calendar Items 686

Working with Tasks and Task Requests 688

Searching for Items 689

The Bottom Line 692

Chapter 27 Working with Events in Outlook 693

Working with Application-Level Events 694

Working with Item-Level Events 700

Understanding Quick Steps 710

The Bottom Line 711

Chapter 28 Understanding the Access Object Model and Key Objects 713

Getting Started with VBA in Access 713

Getting an Overview of the Access Object Model 719

Understanding Creatable Objects in Access 720

Opening and Closing Databases 721

Working with the Screen Object 726

Using the DoCmd Object to Run Access Commands 727

The Bottom Line 733

Chapter 29 Manipulating the Data in an Access Database via VBA 735

Understanding How to Proceed 735

Preparing to Manage the Data in a Database 736

Opening a Recordset 737

Accessing a Particular Record in a Recordset 747

Searching for a Record 749

Returning the Fields in a Record 751

Editing a Record 752

Inserting and Deleting Records 752

Closing a Recordset 753

The Bottom Line 753

Chapter 30 Accessing One Application from Another Application.755

Understanding the Tools Used to Communicate between Applications 755

Using Automation to Transfer Information 756

Using the Shell Function to Run an Application 768

Using Data Objects to Store and Retrieve Information 770

Communicating via DDE 773

Communicating via SendKeys 776

The Bottom Line 781

Chapter 31 Programming the Office 2010 Ribbon 783

Hiding the Editing Group on the Word Ribbon 784

Working with Excel and PowerPoint 788

Undoing Ribbon Modifications 789

Selecting the Scope of Your Ribbon Customization 789

Adding a New Group 789

Two Ways to Find the Correct idMso 791

Adding Callbacks 792

Adding Attributes 794

Using Menus and Lists 795

Toggling with a Toggle Button Control 800

Modifying the Ribbon in Access 801

Adding a Callback in Access 805

What to Look For If Things Go Wrong 806

Where to Go from Here 809

The Bottom Line 810

Appendix The Bottom Line 811

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

Chapter 2: Getting Started with the Visual Basic Editor 812

Chapter 3: Editing Recorded Macros 813

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

Chapter 5: Understanding the Essentials of VBA Syntax 817

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

Chapter 7: Using Array Variables 820

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

Chapter 9: Using Built-in Functions 823

Chapter 10: Creating Your Own Functions 824

Chapter 11: Making Decisions in Your Code 826

Chapter 12: Using Loops to Repeat Actions 827

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

Chapter 14: Creating Simple Custom Dialog Boxes 830

Chapter 15: Creating Complex Dialog Boxes 833

Chapter 16: Building Modular Code and Using Classes 835

Chapter 17: Debugging Your Code and Handling Errors 836

Chapter 18: Building Well-Behaved Code 837

Chapter 19: Securing Your Code with VBA’s Security Features 839

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

Chapter 21: Working with Widely Used Objects in Word 842

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

Chapter 23: Working with Widely Used Objects in Excel 844

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

Chapter 25: Working with Shapes and Running Slide Shows 846

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

Chapter 27: Working with Events in Outlook 848

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

Chapter 29: Manipulating the Data in an Access Database via VBA 850

Chapter 30: Accessing One Application from Another Application 851

Chapter 31: Programming the Office 2010 Ribbon 853

Index 855