Customize and ramp-up Office 365 applications
NOTE: Please click Downloads (located in the menu on the left) to download “Full Code Download.”
The revised 2019 edition of Mastering VBA Microsoft Office 365 offers an accessible guide that shows how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. To enhance understanding, the content is explored in real-world projects in Word, Excel, Outlook, and PowerPoint.
Since the technical programming methods in the Office applications continue to evolve, the updated 2019 edition reviews the changes to the program. Code libraries, the API, and the object model for each Office program have been modified during the three years since the last version of this book. Various elements within VBA have been deprecated or replaced, and the surface UI changed as well. The updated 2019 edition covers topics such as:
• Recording macros and getting started with VBA
• Learning how to work with VBA
• Using loops and functions
• Using message boxes, input boxes, and dialog boxes
• Creating effective code
• XML-based files, ActiveX, the developer tab, content controls, add-ins, embedded macros, and security
Written for all levels of Microsoft Office 365 users, Mastering VBA Microsoft Office 365: 2019 Edition explains how to customize and automate the Office suite of applications.
Table of Contents
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
