Mastering VBA for Microsoft Office 365, 2019 Edition by MS Office Expert

Mastering VBA for Microsoft® Office 365® 2019 Edition

Mastering VBA for Microsoft® Office 365® 2019 Edition Richard Mansfield

Copyright ÂŠ 2019 by John Wiley & Sons, Inc., Indianapolis, Indiana Published simultaneously in Canada ISBN: 978-1-119-57933-5 ISBN: 978-1-119-57941-0 (ebk.) ISBN: 978-1-119-57938-0 (ebk.)

We are making a service for people who want a MS office 365 student A1 account with lifetime licence. If you are interested on this service check the link: https://www.fiverr.com/share/emV5Ek If you want this ebook just contact us in the previous link and you will get it with your MS accounts for 5 dollars (both book+ MS account). Note: I canâ&#x20AC;&#x2122;t share all the ebook here on this website, because of Copyright so I will share with you just the summary of the content.

I dedicate this book to my good friend Richard Biggs.

Acknowledgments Iâ&#x20AC;&#x2122;d like to thank all the good people at Sybex who contributed to this book. I am indebted to development editor Tom Cirtin, whose valuable suggestions contributed to this bookâ&#x20AC;&#x2122;s tone and organization. Technical editor Russ Mullen carefully checked the book for accuracy and ensured that all the code examples work without any errors. Finally, thanks to Athiyappan Lalith Kumar, production editor, the book went smoothly through its final stages for author review, design, and assembly. My gratitude also goes to copyeditor Kim Cofer, who, via a very close read, polished this book in many ways. Evelyn Wellborn is also great at her job, and she flagged important issues during her proofreading.

About the Author Mastering VBA for Microsoft Office 2019 is Richard Mansfieldâ&#x20AC;&#x2122;s 45th book. His titles include CSS Web Design for Dummies (Wiley), Office Application Development All-in-One Desk Reference for Dummies (Wiley), How to Do Everything with Second Life (McGraw-Hill), and Programming: A Beginnerâ&#x20AC;&#x2122;s Guide (McGraw-Hill). Overall, his books have sold more than 500,000 copies worldwide and have been translated into 12 languages.

About the Technical Editor Russ Mullen is a veteran application developer and longtime tech editor (more than 125 titles). He has co-authored several software books. Written several large client server applications and proficient in several programming languages.

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

xii

| Contents 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

Contents

| xiii

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

xiV

| Contents 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 Function to Parse Dates ........................................................... 244 Calculating Time Intervals Using the DateDiff Function ....................................... 245 Using the DateAdd 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

Contents

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

xVi

| Contents 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

Contents

xVii

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

xViii

| Contents 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 How to Approach Creating Modular Code ............................................................... 477 Arranging Your Code in Modules .............................................................................. 477 Calling a Procedure..................................................................................................... 477 Making Logical Improvements to Your Code............................................................ 479 Making Visual Improvements to Your Code ............................................................. 486 Creating and Using Classes ............................................................................................. 492 What Can You Do with Class Modules? .................................................................... 492 A Brief Overview of Classes ....................................................................................... 493 Planning Your Class .................................................................................................... 493 Creating a Class Module............................................................................................. 494 Naming the Class ........................................................................................................ 494 Setting the Instancing Property .................................................................................. 495 Declaring Variables and Constants for the Class....................................................... 495 Adding Properties to the Class................................................................................... 495 Adding Methods to a Class ........................................................................................ 500 Using Your Class ......................................................................................................... 501 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 Runtime Errors ........................................................................................................... 510 Program Logic Errors ................................................................................................. 511 VBA’s Debugging Tools ............................................................................................................... 512 Break Mode ................................................................................................................. 513 The Step Over and Step Out Commands................................................................... 515 The Locals Window .................................................................................................... 515 The Watch Window..................................................................................................... 516 The Immediate Window ............................................................................................. 520 The Call Stack Dialog Box .......................................................................................... 522 Dealing with Infinite Loops............................................................................................. 523 Dealing with Runtime Errors .......................................................................................... 523 When Should You Write an Error Handler? .............................................................. 524 Trapping an Error ....................................................................................................... 524 Disabling an Error Trap ......................................................................................................... 526 Resuming After an Error ............................................................................................ 527 Getting a Description of an Error ............................................................................... 531 Raising Your Own Errors ............................................................................................ 531

Contents

| xix

Suppressing Alerts ........................................................................................................... 531 Handling User Interrupts in Word, and Excel ................................................................ 532 Disabling User Input While a Procedure Is Running ................................................ 532 Disabling User Input While Part of a Macro Is Running .......................................... 533 Documenting Your Code.................................................................................................. 533 The Bottom Line .............................................................................................................. 535

Chapter 18 • Building Well-Behaved Code .................................................... 537 What Is a Well-Behaved Macro? ..................................................................................... 537 Retaining or Restoring the User Environment ............................................................... 539 Leaving the User in the Best Position to Continue Working............................................... 540 Keeping the User Informed During the Procedure ........................................................ 541 Manipulating the Cursor ............................................................................................ 543 Displaying Information at the Beginning of a Macro................................................ 544 Communicating with the User via a Message Box or Dialog Box at the End of a Macro ................................................................................................... 544 Creating a Log File ...................................................................................................... 544 Making Sure a Macro Is Running Under Suitable Conditions ....................................................................................................... 548 Cleaning Up After a Procedure ....................................................................................... 549 Undoing Changes a Macro Has Made ....................................................................... 549 Removing Scratch Files and Folders .......................................................................... 549 The Bottom Line .............................................................................................................. 551

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 Getting a Digital Certificate........................................................................................ 558 Choosing a Suitable Level of Security ............................................................................. 568 Understanding the Security Threats Posed by VBA.................................................. 568 Protecting Against Macro Viruses .............................................................................. 568 Specifying a Suitable Security Setting........................................................................ 569 Additional Office Security Features ........................................................................... 570 File Block Settings ....................................................................................................... 571 Locking Your Code ........................................................................................................... 572 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 Creating a Template................................................................................................................ 582 Saving a Document .......................................................................................................... 582

| Contents Saving a File for the First Time or as a Different File ................................................582 Saving a Document That Has Already Been Saved ................................................... 587 Saving All Open Documents ....................................................................................... 587 Checking Whether a Document Contains Unsaved Changes ................................... 587 Opening a Document ...................................................................................................... 588 Closing a Document .........................................................................................................592 Changing a Documentâ&#x20AC;&#x2122;s Template ........................................................................................... 592 Printing a Document........................................................................................................592 Working with the ActiveDocument Object........................................................................594 Working with the Selection Object ...................................................................................596 Checking the Type of Selection ...................................................................................596 Checking the Story Type of the Selection.................................................................... 597 Getting Other Information About the Current Selection ..........................................599 Inserting Text at a Selection ...................................................................................... 603 Inserting a Paragraph in a Selection ......................................................................... 603 Applying a Style.......................................................................................................... 604 Extending an Existing Selection ................................................................................ 604 Collapsing an Existing Selection ............................................................................... 605 Creating and Using Ranges ............................................................................................ 605 Defining a Named Range ........................................................................................... 606 Redefining a Range .................................................................................................... 607 Using the Duplicate Property to Store or Copy Formatting...................................... 607 Manipulating Options ..................................................................................................... 607 Making Sure Hyperlinks Require Ctrl+Clicking....................................................... 607 Turning Off Overtype ................................................................................................. 608 Setting a Default File Path ......................................................................................... 608 Turning Off Track Changes ........................................................................................ 609 The Bottom Line ............................................................................................................. 609

Chapter 21 â&#x20AC;˘ 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 Understanding How VBA Implements Headers and Footers ................................... 617 Getting to a Header or Footer .................................................................................... 618 Checking to See If a Header or Footer Exists ............................................................ 618 Linking to the Header or Footer in the Previous Section.......................................... 619 Creating a Different First-Page Header ..................................................................... 619 Creating Different Odd- and Even-Page Headers ..................................................... 619 Adding Page Numbers to Your Headers and Footers ............................................... 620 Working with Sections, Page Setup, Windows, and Views.............................................623 Adding a Section to a Document ................................................................................623 Changing the Page Setup ............................................................................................624 Opening a New Window Containing an Open Document ........................................625 Closing All Windows Except the First for a Document .............................................625

Contents

| xxi

Splitting a Window ..................................................................................................... 626 Displaying the Document Map for a Window........................................................... 626 Scrolling a Window..................................................................................................... 626 Arranging Windows.................................................................................................... 627 Positioning and Sizing a Window .............................................................................. 627 Making Sure an Item Is Displayed in the Window ................................................... 628 Changing a Document’s View .............................................................................................. 628 Switching to Read Mode............................................................................................. 628 Zooming the View to Display Multiple Pages ........................................................... 629 Working with Tables .................................................................................................................... 629 Creating a Table....................................................................................................................... 630 Selecting a Table ...................................................................................................................... 630 Converting Text to a Table .................................................................................................... 631 Ensuring That a Selection Is Within a Table...................................................................... 633 Finding Out Where a Selection Is Within a Table ............................................................ 633 Sorting a Table ......................................................................................................................... 634 Adding a Column to a Table ................................................................................................ 635 Deleting a Column from a Table.......................................................................................... 636 Setting the Width of a Column ................................................................................... 636 Selecting a Column ..................................................................................................... 637 Adding a Row to a Table ....................................................................................................... 637 Deleting a Row from a Table ................................................................................................ 638 Setting the Height of One or More Rows................................................................... 638 Selecting a Row ........................................................................................................... 638 Inserting a Cell ............................................................................................................ 638 Returning the Text in a Cell ........................................................................................ 639 Entering Text in a Cell................................................................................................. 639 Deleting Cells .............................................................................................................. 639 Selecting a Range of Cells ........................................................................................... 640 Converting a Table or Rows to Text .................................................................................... 641 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 Saving a Workbook ................................................................................................................. 649 Accessing Cloud Storage ............................................................................................ 652 Opening a Workbook ............................................................................................................. 652 Closing a Workbook ............................................................................................................... 654 Sharing a Workbook ............................................................................................................... 655 Protecting a Workbook .......................................................................................................... 655 Working with the ActiveWorkbook Object .................................................................. 656 Working with Worksheets ............................................................................................... 657 Inserting a Worksheet................................................................................................. 657

xxii

| Contents Deleting a Worksheet..................................................................................................658 Copying or Moving a Worksheet ................................................................................658 Printing a Worksheet ..................................................................................................659 Protecting a Worksheet ............................................................................................. 660 Working with the ActiveSheet Object.........................................................................662 Working with the Active Cell or Selection .......................................................................662 Working with the Active Cell ......................................................................................662 Working with the Userâ&#x20AC;&#x2122;s Selection ............................................................................ 664 Working with Ranges ...................................................................................................... 664 Working with a Range of Cells ....................................................................................665 Creating a Named Range ............................................................................................665 Deleting a Named Range ........................................................................................... 666 Working with a Named Range ................................................................................... 666 Working with the Used Range ................................................................................... 666 Working with SpecialCells ......................................................................................... 666 Entering a Formula in a Cell ..................................................................................... 668 Setting Options ............................................................................................................... 668 Setting Options in the Application Object ................................................................ 668 Setting Options in a Workbook ........................................................................................... 669 The Bottom Line ..............................................................................................................670

Chapter 23 â&#x20AC;˘ Working with Widely Used Objects in Excel ...........................671 Working with Charts ........................................................................................................ 671 Creating a Chart .......................................................................................................... 671 Specifying the Source Data for the Chart................................................................... 673 Specifying a Chart Type ........................................................................................................ 673 Working with Series in the Chart ............................................................................... 673 Adding a Legend to the Chart..................................................................................... 676 Adding a Chart Title.................................................................................................... 677 Working with a Chart Axis .......................................................................................... 677 Formatting Headers and Footers ...............................................................................678 Working with Windows Objects ......................................................................................678 Opening a New Window on a Workbook .......................................................................... 678 Closing a Window ....................................................................................................... 679 Activating a Window................................................................................................... 679 Arranging and Resizing Windows .............................................................................. 679 Zooming a Window and Setting Display Options ..................................................... 681 Working with Find and Replace ...................................................................................... 681 Searching with the Find Method ................................................................................ 681 Continuing a Search with the FindNext and FindPrevious Methods ...................... 683 Replacing with the Replace Method ......................................................................... 683 Searching for and Replacing Formatting .................................................................. 684 Adding Shapes................................................................................................................. 684 The Bottom Line ..............................................................................................................685

Contents

| xxiii

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 Creating a New Presentation Based on the Default Template ...................................... 689 Opening an Existing Presentation.............................................................................. 690 Opening a Presentation from the Cloud .................................................................... 690 Saving a Presentation ................................................................................................. 691 Closing a Presentation ................................................................................................ 693 Exporting a Presentation or Some Slides to Graphics ............................................... 694 Printing a Presentation ............................................................................................... 694 Working with the Active Presentation ....................................................................... 695 Working with Windows and Views ................................................................................. 695 Working with the Active Window .............................................................................. 696 Opening a New Window on a Presentation .............................................................. 696 Closing a Window ....................................................................................................... 696 Activating a Window .................................................................................................. 697 Arranging and Resizing Windows ............................................................................. 697 Changing the View.................................................................................................................. 698 Working with Panes .................................................................................................... 698 Working with Slides ......................................................................................................... 699 Adding a Slide to a Presentation ................................................................................ 699 Inserting Slides from an Existing Presentation ......................................................... 700 Finding a Slide by Its ID Number .............................................................................. 700 Changing the Layout of an Existing Slide ................................................................. 701 Deleting an Existing Slide........................................................................................... 701 Copying and Pasting a Slide....................................................................................... 701 Duplicating a Slide...................................................................................................... 702 Moving a Slide ............................................................................................................ 702 Accessing a Slide by Name ......................................................................................... 702 Working with a Range of Slides ................................................................................. 702 Formatting a Slide....................................................................................................... 703 Setting a Transition for a Slide, a Range of Slides, or a Master ................................. 704 Working with Masters...................................................................................................... 705 Working with the Slide Master ................................................................................... 705 Working with the Title Master.................................................................................... 706 Working with the Handout Master ............................................................................ 706 Working with the Notes Master ................................................................................. 706 Deleting a Master ........................................................................................................ 706 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

xxiV

| Contents Selecting All Shapes .................................................................................................... 715 Repositioning and Resizing a Shape .......................................................................... 715 Copying Formatting from One Shape to Another ..................................................... 716 Working with Text in a Shape ..................................................................................... 716 Animating a Shape or a Range of Shapes ..................................................................720 Working with Headers and Footers................................................................................. 723 Returning the Header or Footer Object You Want...........................................................723 Displaying or Hiding a Header or Footer Object....................................................... 723 Setting the Text in a Header or Footer ....................................................................... 723 Setting the Format for Date and Time Headers and Footers .................................... 724 Setting Up and Running a Slide Show ............................................................................ 725 Controlling the Show Type....................................................................................................725 Creating a Custom Show............................................................................................. 726 Deleting a Custom Show ............................................................................................. 727 Starting a Slide Show .................................................................................................. 727 Changing the Size and Position of a Slide Show ........................................................728 Moving among Slides..................................................................................................728 Pausing the Show and Using White and Black Screens ............................................728 Starting and Stopping Custom Shows........................................................................ 729 Exiting a Slide Show ................................................................................................... 729 The Bottom Line .............................................................................................................. 729

Chapter 26 â&#x20AC;˘ 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â&#x20AC;&#x2122;s Most Common Creatable Objects .................................... 734 Working with the Application Object.............................................................................. 735 Introducing the NameSpace Object........................................................................... 735 Working with Namespaces ......................................................................................... 735 Understanding Inspectors and Explorers ..................................................................738 Creating Items............................................................................................................. 739 Quitting Outlook .........................................................................................................740 Understanding General Methods for Working with Outlook Objects ...........................740 Using the Display Method..........................................................................................740 Using the Close Method .............................................................................................. 741 Using the PrintOut Method ........................................................................................ 741 Using the Save Method............................................................................................... 742 Using the SaveAs Method .......................................................................................... 742 Working with Messages ................................................................................................... 744 Creating a New Message ............................................................................................. 744 Working with the Contents of a Message ................................................................... 744 Adding an Attachment to a Message .......................................................................... 745 Sending a Message ...................................................................................................... 746 Working with Calendar Items.......................................................................................... 747

Contents

| xxV

Creating a New Calendar Item................................................................................... 747 Working with the Contents of a Calendar Item ......................................................... 747 Working with Tasks and Task Requests .......................................................................... 749 Creating a Task ........................................................................................................................ 749 Working with the Contents of a Task Item................................................................. 749 Assigning a Task to a Colleague ................................................................................. 750 Searching for Items .......................................................................................................... 750 The Bottom Line .............................................................................................................. 752

Chapter 27 â&#x20AC;˘ 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 Using the Quit Event................................................................................................... 758 Using the ItemSend Event........................................................................................... 759 Using the NewMail Event ........................................................................................... 760 Using the AdvancedSearchComplete and the AdvancedSearchStopped Events............. 760 Using the MAPILogonComplete Event ....................................................................... 761 Using the Reminder Event .......................................................................................... 761 Using the OptionsPagesAdd Event ............................................................................. 762 Working with Item-Level Events..................................................................................... 762 Declaring an Object Variable and Initializing an Event ............................................ 763 Understanding the Events That Apply to All Message Items ................................... 764 Understanding the Events That Apply to Explorers, Inspectors, and Views ........... 766 Events That Apply to Folders ..................................................................................... 770 Understanding the Events That Apply to Items and Results Objects ...................... 771 Understanding the Events That Apply to Reminders ............................................... 771 Understanding the Events That Apply to Synchronization ...................................... 771 Understanding Quick Steps............................................................................................. 772 The Bottom Line .............................................................................................................. 773

Chapter 28 â&#x20AC;˘ 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 Using the Macro Designer .......................................................................................... 778 Creating an Access-Style Macro to Run a Function................................................... 778 Translating an Access-Style Macro into a VBA Macro .............................................. 781 Using an AutoExec Macro to Initialize an Access Session ......................................... 782 Running a Subprocedure ............................................................................................ 782 Understanding the Option Compare Database Statement ........................................... 783 Getting an Overview of the Access Object Model .......................................................... 784 Understanding Creatable Objects in Access ................................................................... 784 Opening and Closing Databases ..................................................................................... 785 Using the CurrentDb Method to Return the Current Database ................................ 785 Closing the Current Database and Opening a Different Database ........................... 786

xxVi

| Contents Communicating Between Office Applications .......................................................... 788 Opening Multiple Databases at Once.........................................................................790 Closing a Database ...................................................................................................... 792 Creating and Removing Workspaces ......................................................................... 792 Working with the Screen Object ...................................................................................... 793 Using the DoCmd Object to Run Access Commands ..................................................... 794 Using the OpenForm Method to Open a Form..........................................................798 Using the PrintOut Method to Print an Object..........................................................798 Using the RunMacro Method to Run an Access-Style Macro ................................... 799 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 Creating an Object with the CreateObject Function................................................. 804 Returning an Object with the GetObject Function ................................................... 805 Examples of Using Automation with the Office Applications .................................. 805 Using the Shell Function to Run an Application ............................................................ 815 Using Data Objects to Store and Retrieve Information ................................................. 818 Creating a Data Object ................................................................................................ 818 Storing Information in a Data Object ........................................................................ 819 Returning Information from a Data Object ............................................................... 819 Assigning Information to the Clipboard ................................................................... 820 Finding Out Whether a Data Object Contains a Given Format ................................ 821 Communicating via DDE ................................................................................................. 821 Using DDEInitiate to Start a DDE Connection ........................................................ 822 Using DDERequest to Return Text from Another Application................................. 822 Using DDEPoke to Send Text to Another Application .............................................. 823 Using DDEExecute to Have One Application Execute a Command in Another...... 824 Using DDETerminate to Close a DDE Channel........................................................ 824 Using DDETerminateAll to Close All Open DDE Channels......................................825 Communicating via SendKeys.........................................................................................825 Going Beyond VBA ......................................................................................................... 830 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

Contents

xxVii

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â&#x20AC;&#x2122;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