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 xxixPart 1 Recording Macros and Getting Started with VBA 1Chapter 1 Recording and Running Macros in the Office Applications 3What Is VBA and What Can You Do with It? 3The Difference between Visual Basic and Visual Basic for Applications 4What Are Visual Basic NET and Visual Basic Express? 5Understanding Macro Basics 5Recording a Macro 6Displaying the Developer Tab on the Ribbon 7Planning the Macro 8Starting the Macro Recorder 10Naming a Macro 13Running a Macro 24Recording a Sample Word Macro 25Recording a Sample Excel Macro 27Create a Personal Macro Workbook If You Don’t Have One Yet 28Record the Macro 29Specifying How to Trigger an Existing Macro 30Assigning a Macro to a Quick Access Toolbar Button in Word 30Assigning a Macro to a Shortcut Key Combination 31Deleting a Macro 31The Bottom Line 33Chapter 2 Getting Started with the Visual Basic Editor 35Opening the Visual Basic Editor 35Opening the Visual Basic Editor with a Macro Selected 36Opening the Visual Basic Editor Directly 37Navigating to a Macro 37Using the Visual Basic Editor’s Main Windows 38The Project Explorer 39The Object Browser 42The Code Window 43The Properties Window 48The Immediate Window 50Setting Properties for a Project 51Customizing the Visual Basic Editor 53Choosing Editor and View Preferences 54Choosing and Laying Out the Editor Windows 61Customizing the Toolbar and Menu Bar 61Customizing the Toolbox 62The Bottom Line 66Chapter 3 Editing Recorded Macros 69Testing a Macro in the Visual Basic Editor 70Stepping Through a Macro 71Setting Breakpoints 73Commenting Out Lines 74Stepping Out of a Macro 75Editing a Word Macro 75Stepping Through the Transpose_Word_Right Macro 77Running the Transpose_Word_Right Macro 77Creating a Transpose_Word_Left Macro 77Saving Your Work 79Editing an Excel Macro 79Unhiding the Personal Macro Workbook 79Opening a Macro for Editing 81Editing a Macro 82Editing a PowerPoint Macro 84Reducing the Size of Your Macro 88Saving Your Work 89The Bottom Line 90Chapter 4 Creating Code from Scratch in the Visual Basic Editor 93Setting Up the Visual Basic Editor to Create Macros 93Creating a Procedure for Word 95Creating a Macro for Excel 101Creating a Procedure for PowerPoint 106Creating a Procedure for Access 112The Bottom Line 113Part 2 Learning How to Work with VBA 115Chapter 5 Understanding the Essentials of VBA Syntax 117Getting Ready 117Procedures 118Functions 119Subprocedures 119Statements 119Keywords 123Expressions 124Operators 124Variables 124Constants 126Arguments 126Specifying Argument Names vs Omitting Argument Names 127When to Include the Parentheses around an Argument List 128Objects 129Collections 129Properties 130Methods 130Events 130The Bottom Line 132Chapter 6 Working with Variables, Constants, and Enumerations 135Working with Variables 136Choosing Names for Variables 136Declaring a Variable 138Choosing the Scope and Lifetime of a Variable 141Specifying the Data Type for a Variable 148Working with Constants 155Declaring Your Own Constants 155Choosing the Scope or Lifetime for Constants 156Working with Enumerations 156The Bottom Line 157Chapter 7 Using Array Variables 159What Is an Array? 159Declaring an Array 161Storing Values in an Array 163Multidimensional Arrays 164Declaring a Dynamic Array 165Redimensioning an Array 165Returning Information from an Array 166Erasing an Array 166Determining Whether a Variable Is an Array 166Finding the Bounds of an Array 167Sorting an Array 167Searching an Array 171Performing a Linear Search Through an Array 172Binary Searching an Array 177The Bottom Line 182Chapter 8 Finding the Objects, Methods, and Properties You Need 185What Is an Object? 185The Benefits of OOP 185Understanding Creatable Objects 187Properties 187Methods 188Working with Collections 190Working with an Object in a Collection 191Adding an Object to a Collection 192Finding the Objects You Need 192Using the Macro Recorder to Add Code for the Objects You Need 192Using the Object Browser 195Using Help to Find the Object You Need 201Using the Auto List Members Feature 205Using Object Variables to Represent Objects 206Team Programming and OOP 209The Bottom Line 211Part 3 Making Decisions and Using Loops and Functions 213Chapter 9 Using Built-In Functions 215What Is a Function? 215Using Functions 217Passing Arguments to a Function 219Using Functions to Convert Data 220Using the Asc Function to Return a Character Code 221Using the Val Function to Extract a Number from the Start of a String 221Using the Str Function to Convert a Number into a String 223Using the Format Function to Format an Expression 224Using Predefined Numeric Formats 225Creating a Numeric Format 226Creating a Date or Time Format 227Creating a String Format 228Using the Chr Function and Constants to Enter Special Characters in a String 229Using Functions to Manipulate Strings 230Using the Left, Right, and Mid Functions to Return Part of a String 230Using the Left Function 232Using the Right Function 232Using the Mid Function 233Using InStr and InStrRev to Find a String Within Another String 235Using LTrim, RTrim, and Trim to Remove Spaces from a String 237Using Len to Check the Length of a String 238Using StrConv, LCase, and UCase to Change the Case of a String 240Using the StrComp Function to Compare Apples to Apples 241Using VBA’s Mathematical Functions 242Using VBA’s Date and Time Functions 244Using the DatePart t Function to Parse Dates 244Calculating Time Intervals Using the DateDiff f Function 245Using the DateAdd d Function to Add or Subtract Time from a Date 246Using File-Management Functions 246Checking Whether a File Exists Using the Dir Function 246Returning the Current Path 248The Bottom Line 248Chapter 10 Creating Your Own Functions 251Components of a Function 252Creating a Function 254Starting a Function Manually 254Starting a Function by Using the Add Procedure Dialog Box 254Passing Arguments to a Function 256Declaring the Data Types of Arguments 257Specifying an Optional Argument 257Controlling the Scope of a Function 258Examples of Functions for All VBA-Enabled Office Applications 258How Functions Return Information 260Returning Text Data from a Function 260Creating a Function for Word 263Creating a Function for Excel 265Creating a Function for PowerPoint 267Creating a Function for Access 269The Bottom Line 271Chapter 11 Making Decisions in Your Code 273How Do You Compare Things in VBA? 274Testing Multiple Conditions by Using Logical Operators 275If Blocks 278If Then 278If Then Else Statements 280If Then ElseIf Else Statements 282Creating Loops with If and GoTo 287Nesting If Blocks 289Select Case Blocks 291Syntax 291Example 292When Order Matters 294The Bottom Line 294Chapter 12 Using Loops to Repeat Actions 297When Should You Use a Loop? 297Understanding the Basics of Loops 298Using For Loops for Fixed Repetitions 299For Next Loops 299For Each Next Loops 308Using an Exit For Statement 308Using Do Loops for Variable Numbers of Repetitions 309Do While Loop Loops 310Do Loop While Loops 314Do Until Loop Loops 316Do Loop Until Loops 318Using an Exit Do Statement 319Is the Exit Do Statement Bad Practice? 320While Wend Loops 321Nesting Loops 322Avoiding Infinite Loops 325The Bottom Line 326Part 4 Using Message Boxes, Input Boxes, and Dialog Boxes 327Chapter 13 Getting User Input with Message Boxes and Input Boxes 329Opening a Macro 330Displaying Status-Bar Messages in Word and Excel 331Message Boxes 333The Pros and Cons of Message Boxes 333Message-Box Syntax 334Displaying a Simple Message Box 335Displaying a Multiline Message Box 336Choosing Buttons for a Message Box 337Choosing an Icon for a Message Box 338Setting a Default Button for a Message Box 339Controlling the Modality of a Message Box 340Specifying a Title for a Message Box 341Title Bars Can Provide Useful Information 342Adding a Help Button to a Message Box 342Specifying a Help File for a Message Box 343Using Some Arguments Without Others 344Retrieving a Value from a Message Box 344Input Boxes 345Input-Box Syntax 346Retrieving Input from an Input Box 348Forms: When Message Boxes and Input Boxes Won’t Suffice 348The Bottom Line 349Chapter 14 Creating Simple Custom Dialog Boxes 351When Should You Use a Custom Dialog Box? 351Creating a Custom Dialog Box 352Designing a Dialog Box 354Inserting a User Form 354Choosing User-Form Grid Settings 355Renaming a User Form 358Adding Controls to the User Form 360Grouping Controls 363Renaming Controls 364Moving a Control 365Changing the Caption on a Control 367Key Properties of the Toolbox Controls 368Working with Groups of Controls 386How to Group Controls 386Ungrouping Controls 386Sizing Grouped Controls 387Deleting Grouped Controls 387Working with One Control in a Group 387Aligning Controls 388Placing Controls 389Adjusting the Tab Order of a Form 389Linking a Form to a Procedure 391Loading and Unloading a Form 392Displaying and Hiding a Form 392Setting a Default Command Button 393Retrieving the User’s Choices from a Dialog Box 393Returning a String from a Text Box 393Returning a Value from an Option Button 394Returning a Value from a Check Box 395Returning a Value from a List Box 395Returning a Value from a Combo Box 396Examples Showing How to Connect Forms to Procedures 397Word Example: The Move-Paragraph Procedure 397General Example: Opening a File from a List Box 409Building the User Form 409Creating the Code for the User Form 411Using an Application’s Built-In Dialog Boxes 415Displaying a Built-In Dialog Box 415Setting and Restoring Options in a Built-In Dialog Box 419Which Button Did the User Choose in a Dialog Box? 419Specifying a Time-Out for a Dialog Box 420The Bottom Line 421Chapter 15 Creating Complex Forms 423Creating and Working with Complex Dialog Boxes 424Updating a Dialog Box to Reflect the User’s Choices 424Revealing a Hidden Part of a Form 424Tracking a Procedure in a Form 429Using Multipage Dialog Boxes and TabStrip Controls 432Creating a Modeless Dialog Box 443Specifying a Form’s Location Onscreen 444Using Events to Control Forms 445Events Unique to the UserForm Object 448Events That Apply to Both UserForms and Container Controls 453Events That Apply to Many or Most Controls 458Events That Apply Only to a Few Controls 471The Bottom Line 472Part 5 Creating Effective Code 473Chapter 16 Building Modular Code and Using Classes 475Creating Modular Code 475What Is Modular Code? 476Advantages of Using Modular Code 476The Bottom Line 503Chapter 17 Debugging Your Code and Handling Errors 505Principles of Debugging 505The Different Types of Errors 507Language Errors 507Compile Errors 507The Bottom Line 551Chapter 18 Building Well-Behaved Code 537What Is a Well-Behaved Macro? 537Retaining or Restoring the User Environment 539Chapter 19 Exploring VBA’s Security Features 553Understanding How VBA Implements Security 553Signing Your Macro Projects with Digital Signatures 557What Is a Digital Certificate? 557The Bottom Line 574Part 6 Programming the Office Applications 575Chapter 20 Understanding the Word Object Model and Key Objects 577Examining the Word Object Model 577Working with the Documents Collection and the Document Object 581Creating a Document 581The Bottom Line 609Chapter 21 Working with Widely Used Objects in Word 611Using Find and Replace via VBA 611Understanding the Syntax of the Execute Method 613Putting Find and Replace to Work 616Working with Headers, Footers, and Page Numbers 617The Bottom Line 642Chapter 22 Understanding the Excel Object Model and Key Objects 645Getting an Overview of the Excel Object Model 645Understanding Excel’s Creatable Objects 646Managing Workbooks 647Creating a Workbook 647The Bottom Line 670Chapter 23 Working with Widely Used Objects in Excel 671Working with Charts 671Creating a Chart 671Specifying the Source Data for the Chart 673The Bottom Line 685Chapter 24 Understanding the PowerPoint Object Model and Key Objects 687Getting an Overview of the PowerPoint Object Model 687Understanding PowerPoint’s Creatable Objects 688Working with Presentations 689The Bottom Line 707Chapter 25 Working with Shapes and Running Slide Shows 709Working with Shapes 709Adding Shapes to Slides 709Deleting a Shape 715The Bottom Line 729Chapter 26 Understanding the Outlook Object Model and Key Objects 731Getting an Overview of the Outlook Object Model 731The Outlook Object Model 732Understanding Where Outlook Stores VBA Macros 733Understanding Outlook’s Most Common Creatable Objects 734The Bottom Line 752Chapter 27 Working with Events in Outlook 755How Event-Handler Procedures Differ from Ordinary Macros 755Working with Application-Level Events 756Using the Startup Event 758The Bottom Line 773Chapter 28 Understanding the Access Object Model and Key Objects 775Getting Started with VBA in Access 775Creating a Module in the VBA Editor 778Creating a Function 778The Bottom Line 800Chapter 29 Accessing One Application from Another Application 801Understanding the Tools Used to Communicate Between Applications 801Using Automation to Transfer Information 802Understanding Early and Late Binding 803The Bottom Line 830Appendix A The Bottom Line 833Chapter 1: Recording and Running Macros in the Office Applications 833Chapter 2: Getting Started with the Visual Basic Editor 835Chapter 3: Editing Recorded Macros 836Chapter 4: Creating Code from Scratch in the Visual Basic Editor 836Chapter 5: Understanding the Essentials of VBA Syntax 839Chapter 6: Working with Variables, Constants, and Enumerations 840Chapter 7: Using Array Variables 842Chapter 8: Finding the Objects, Methods, and Properties You Need 843Chapter 9: Using Built-in Functions 844Chapter 10: Creating Your Own Functions 846Chapter 11: Making Decisions in Your Code 847Chapter 12: Using Loops to Repeat Actions 849Chapter 13: Getting User Input with Message Boxes and Input Boxes 850Chapter 14: Creating Simple Custom Dialog Boxes 851Chapter 15: Creating Complex Forms 855Chapter 16: Building Modular Code and Using Classes 856Chapter 17: Debugging Your Code and Handling Errors 858Chapter 18: Building Well-Behaved Code 859Chapter 19: Exploring VBA’s Security Features 860Chapter 20: Understanding the Word Object Model and Key Objects 862Chapter 21: Working with Widely Used Objects in Word 863Chapter 22: Understanding the Excel Object Model and Key Objects 864Chapter 23: Working with Widely Used Objects in Excel 865Chapter 24: Understanding the PowerPoint Object Model and Key Objects 866Chapter 25: Working with Shapes and Running Slide Shows 867Chapter 26: Understanding the Outlook Object Model and Key Objects 868Chapter 27: Working with Events in Outlook 869Chapter 28: Understanding the Access Object Model and Key Objects 870Chapter 29: Accessing One Application from Another Application 871Index 873