Mastering VBA for Microsoft Office 2016

Introduction xxxi 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 23 Recording a Sample Word Macro 24 Recording a Sample Excel Macro 27 Specifying How to Trigger an Existing Macro 29 Deleting a Macro 29 The Bottom Line 31 Chapter 2 Getting Started with the Visual Basic Editor .33 Opening the Visual Basic Editor 33 Using the Visual Basic Editor’s Main Windows 36 Setting Properties for a Project 48 Customizing the Visual Basic Editor 51 The Bottom Line 64 Chapter 3 Editing Recorded Macros .67 Testing a Macro in the Visual Basic Editor 68 Editing a Word Macro 73 Editing the Excel Macro 76 Editing a PowerPoint Macro 80 The Bottom Line 87 Chapter 4 Creating Code from Scratch in the Visual Basic Editor .89 Setting Up the Visual Basic Editor to Create Macros 89 Creating a Procedure for Word 91 Creating a Macro for Excel 96 Creating a Procedure for PowerPoint 101 Creating a Procedure for Access 106 The Bottom Line 107 Part 2 Learning How to Work with VBA 109 Chapter 5 Understanding the Essentials of VBA Syntax 111 Getting Ready 111 Procedures 112 Statements 114 Keywords 117 Expressions 118 Operators 118 Variables 118 Constants 120 Arguments 120 Objects 123 Collections 123 Properties 123 Methods 124 Events 124 The Bottom Line 126 Chapter 6 Working with Variables, Constants, and Enumerations 129 Working with Variables 130 Working with Constants 147 Working with Enumerations 149 The Bottom Line 150 Chapter 7 Using Array Variables 151 What Is an Array? 151 Declaring an Array 153 Storing Values in an Array 155 Multidimensional Arrays 156 Declaring a Dynamic Array 157 Redimensioning an Array 157 Returning Information from an Array 158 Erasing an Array 158 Determining Whether a Variable Is an Array 158 Finding the Bounds of an Array 158 Sorting an Array 159 Searching an Array 163 The Bottom Line 173 Chapter 8 Finding the Objects, Methods, and Properties You Need .175 What Is an Object? 175 Working with Collections 180 Finding the Objects You Need 182 Using Object Variables to Represent Objects 194 Team Programming and OOP 197 The Bottom Line 199 Part 3 Making Decisions and Using Loops and Functions 201 Chapter 9 Using Built-In Functions 203 What Is a Function? 203 Using Functions 205 Using Functions to Convert Data 208 Using the Asc Function to Return a Character Code 210 Using the Val Function to Extract a Number from the Start of a String 210 Using the Format Function to Format an Expression 213 Using the Chr Function and Constants to Enter Special Characters in a String 218 Using Functions to Manipulate Strings 219 Using the Left, Right, and Mid Functions to Return Part of a String 221 Using InStr and InStrRev to Find a String within another String 224 Using LTrim, RTrim, and Trim to Remove Spaces from a String 227 Using Len to Check the Length of a String 228 Using StrConv, LCase, and UCase to Change the Case of a String 229 Using the StrComp Function to Compare Apples to Apples 231 Using VBA’s Mathematical Functions 231 Using VBA’s Date and Time Functions 232 Using the DatePart Function to Parse Dates 234 Calculating Time Intervals Using the DateDiff Function 235 Using the DateAdd Function to Add or Subtract Time from a Date 236 Using File-Management Functions 236 Checking Whether a File Exists Using the Dir Function 236 The Bottom Line 238 Chapter 10 Creating Your Own Functions .241 Components of a Function 242 Creating a Function 244 Examples of Functions for Any VBA-Enabled Office Application 247 Creating a Function for Word 251 Creating a Function for Excel 253 Creating a Function for PowerPoint 255 Creating a Function for Access 257 The Bottom Line 258 Chapter 11 Making Decisions in Your Code 261 How Do You Compare Things in VBA? 262 Testing Multiple Conditions by Using Logical Operators 263 Select Case Blocks 278 The Bottom Line 282 Chapter 12 Using Loops to Repeat Actions 283 When Should You Use a Loop? 283 Understanding the Basics of Loops 284 Using For… Loops for Fixed Repetitions 285 Using Do… Loops for Variable Numbers of Repetitions 295 While…Wend Loops 306 Nesting Loops 307 Avoiding  Loops 310 The Bottom Line 310 Part 4 Using Message Boxes, Input Boxes, and Dialog Boxes 313 Chapter 13 Getting User Input with Message Boxes and Input Boxes 315 Opening a Macro 316 Displaying Status-Bar Messages in Word and Excel 317 Message Boxes 319 Input Boxes 331 Forms: When Message Boxes and Input Boxes The Bottom Line 334 Chapter 14 Creating Simple Custom Dialog Boxes 337 When Should You Use a Custom Dialog Box? 337 Creating a Custom Dialog Box 338 Working with Groups of Controls 372 Linking a Form to a Procedure 378 Retrieving the User’s Choices from a Dialog Box 380 Examples of Connecting Forms to Procedures 384 Using an Application’s Built-In Dialog Boxes from VBA 400 The Bottom Line 406 Chapter 15 Creating Complex Forms 409 Creating and Working with Complex Dialog Boxes 410 Using Events to Control Forms 430 The Bottom Line 457 Part 5 Creating Effective Code 459 Chapter 16 Building Modular Code and Using Classes 461 Creating Modular Code 461 Creating and Using Classes 478 The Bottom Line 489 Chapter 17 Debugging Your Code and Handling Errors 491 Principles of Debugging 491 The Different Types of Errors 493 VBA’s Debugging Tools 498 Dealing with  Loops 508 Dealing with Runtime Errors 509 Suppressing Alerts 516 Handling User Interrupts in Word, Excel, and Project 517 Documenting Your Code 518 The Bottom Line 520 Chapter 18 Building Well-Behaved Code 521 What Is a Well-Behaved Procedure? 521 Retaining or Restoring the User Environment 522 Leaving the User in the Best Position to Continue Working 523 Keeping the User Informed During the Procedure 524 Making Sure a Procedure Is Running Under Suitable Conditions 532 Cleaning Up After a Procedure 532 The Bottom Line 534 Chapter 19 Exploring VBA’s Security Features 537 Understanding How VBA Implements Security 537 Signing Your Macro Projects with Digital Signatures 541 Choosing a Suitable Level of Security 551 Locking Your Code 556 The Bottom Line 557 Part 6 Programming the Office Applications 559 Chapter 20 Understanding the Word Object Model and Key Objects 561 Examining the Word Object Model 561 Working with the Documents Collection and the Document Object 564 Printing a Document 576 Working with the ActiveDocument Object 578 Working with the Selection Object 579 Creating and Using Ranges 589 Manipulating Options 591 The Bottom Line 594 Chapter 21 Working with Widely Used Objects in Word 597 Using Find and Replace via VBA 597 Working with Headers, Footers, and Page Numbers 603 Working with Sections, Page Setup, Windows, and Views 610 Working with Tables 615 The Bottom Line 628 Chapter 22 Understanding the Excel Object Model and Key Objects 631 Getting an Overview of the Excel Object Model 631 Understanding Excel’s Creatable Objects 632 Managing Workbooks 633 Working with Worksheets 642 Working with the Active Cell or Selection 647 Working with Ranges 650 Setting Options 654 The Bottom Line 656 Chapter 23 Working with Widely Used Objects in Excel 659 Working with Charts 659 Working with Windows Objects 666 Working with Find and Replace 669 Adding Shapes 672 The Bottom Line 672 Chapter 24 Understanding the PowerPoint Object Model and Key Objects 673 Getting an Overview of the PowerPoint Object Model 673 Understanding PowerPoint’s Creatable Objects 674 Working with Presentations 675 Working with Windows and Views 683 Working with Slides 686 Working with Masters 693 The Bottom Line 695 Chapter 25 Working with Shapes and Running Slide Shows 697 Working with Shapes 697 Working with Headers and Footers 711 Setting Up and Running a Slide Show 713 The Bottom Line 717 Chapter 26 Understanding the Outlook Object Model and Key Objects 719 Getting an Overview of the Outlook Object Model 719 Working with the Application Object 721 Understanding General Methods for Working with Outlook Objects 727 Working with Messages 731 Working with Calendar Items 734 Working with Tasks and Task Requests 735 Searching for Items 737 The Bottom Line 738 Chapter 27 Working with Events in Outlook .741 Working with Application-Level Events 742 Working with Item-Level Events 748 Understanding Quick Steps 758 The Bottom Line 759 Chapter 28 Understanding the Access Object Model and Key Objects 761 Getting Started with VBA in Access 761 Getting an Overview of the Access Object Model 768 Understanding Creatable Objects in Access 769 Opening and Closing Databases 770 Working with the Screen Object 777 The Bottom Line 784 Chapter 29 Manipulating the Data in an Access Database via VBA 787 Understanding How to Proceed 787 Preparing to Manage the Data in a Database 788 Opening a Recordset 789 Accessing a Particular Record in a Recordset 800 Searching for a Record 802 Returning the Fields in a Record 805 Editing a Record 805 Inserting and Deleting Records 805 Closing a Recordset 806 Saving a Recordset to the Cloud 806 The Bottom Line 808 Chapter 30 Accessing One Application from another Application 809 Understanding the Tools Used to Communicate Between Applications 809 Using Automation to Transfer Information 810 Using the Shell Function to Run an Application 822 Using Data Objects to Store and Retrieve Information 824 Communicating via DDE 827 Communicating via SendKeys 831 Going Beyond VBA 835 The Bottom Line 836 Chapter 31 Programming the Office 2016 Ribbon 837 What Is XML? 838 Hiding the Clipboard Group on the Word Ribbon 839 Working with Excel and PowerPoint 846 Undoing Ribbon Modifications 847 Selecting the Scope of Your Ribbon Customization 847 Adding a New Group 848 Adding Callbacks 851 Adding Attributes 853 Using Menus and Lists 855 Toggling with a Toggle-Button Control 860 Modifying the Ribbon in Access 861 Adding a Callback in Access 865 What to Look For If Things Go Wrong 866 Where to Go from Here 869 The Bottom Line 870 Appendix The Bottom Line 871 Chapter 1: Recording and Running Macros in the Office Applications 871 Chapter 2: Getting Started with the Visual Basic Editor 872 Chapter 3: Editing Recorded Macros 873 Chapter 4: Creating Code from Scratch in the Visual Basic Editor 874 Chapter 5: Understanding the Essentials of VBA Syntax 876 Chapter 6: Working with Variables, Constants, and Enumerations 878 Chapter 7: Using Array Variables 880 Chapter 8: Finding the Objects, Methods, and Properties You Need 881 Chapter 9: Using Built-in Functions 882 Chapter 10: Creating Your Own Functions 883 Chapter 11: Making Decisions in Your Code 885 Chapter 12: Using Loops to Repeat Actions 886 Chapter 13: Getting User Input with Message Boxes and Input Boxes 887 Chapter 14: Creating Simple Custom Dialog Boxes 889 Chapter 15: Creating Complex Forms 892 Chapter 16: Building Modular Code and Using Classes 894 Chapter 17: Debugging Your Code and Handling Errors 895 Chapter 18: Building Well-Behaved Code 897 Chapter 19: Exploring VBA’s Security Features 898 Chapter 20: Understanding the Word Object Model and Key Objects 900 Chapter 21: Working with Widely Used Objects in Word 901 Chapter 22: Understanding the Excel Object Model and Key Objects 902 Chapter 23: Working with Widely Used Objects in Excel 903 Chapter 24: Understanding the PowerPoint Object Model and Key Objects 904 Chapter 25: Working with Shapes and Running Slide Shows 905 Chapter 26: Understanding the Outlook Object Model and Key Objects 906 Chapter 27: Working with Events in Outlook 906 Chapter 28: Understanding the Access Object Model and Key Objects 907 Chapter 29: Manipulating the Data in an Access Database via VBA 909 Chapter 30: Accessing One Application from another Application 910 Chapter 31: Programming the Office 2016 Ribbon 911 Index 913