Mastering VBA for Office 2010

A comprehensive guide to the language used to customize Microsoft Office Visual Basic for Applications (VBA) is the language used for writing macros, automating Office applications, and creating custom applications in Word, Excel, PowerPoint, Outlook, and Access. This complete guide shows both IT professionals and novice developers how to master VBA in order to customize the entire Office suite for specific business needs. Office 2010 is the leading productivity suite, and the VBA language enables customizations of all the Office programs; this complete guide gives both novice and experienced programmers the knowledge they need to make maximum use of VBA for Office Supported with real-world examples in Word, Excel, PowerPoint, Outlook, and Access, this book offers clear, systematic tutorials with both intermediate and advanced content Covers learning how to work with VBA; recording macros; using loops and functions; using message boxes, input boxes, and dialog boxes; creating effective code; XML-based files; ActiveX; the developer tab; content controls; add-ins; embedded macros; and security Mastering VBA for Office 2010 prepares developers to customize all Microsoft Office 2010 applications for the unique needs of their employers.

Table of Contents:
Introduction xxvii Part 1 Recording Macros and Getting Started with VBA 1 Chapter 1 Recording and Running Macros in the Office Applications 3 What is VBA and What Can You Do with It? 3 Understanding Macro Basics 5 Recording a Macro 6 Running a Macro 20 Recording a Sample Word Macro 21 Recording a Sample Excel Macro 24 Assigning a Way of Running the Macro 25 Deleting a Macro 26 The Bottom Line 28 Chapter 2 Getting Started with the Visual Basic Editor 29 Opening the Visual Basic Editor 29 Using the Visual Basic Editor’s Main Windows 32 Setting Properties for a Project 43 Customizing the Visual Basic Editor 45 Closing the Visual Basic Editor and Returning to the Host Application 61 The Bottom Line 61 Chapter 3 Editing Recorded Macros 63 Testing a Macro in the Visual Basic Editor 63 Editing the Word Macro 68 Editing the Excel Macro 72 Editing a PowerPoint Macro 77 The Bottom Line 83 Chapter 4 Creating Code from Scratch in the Visual Basic Editor 85 Setting Up the Visual Basic Editor for Creating the Procedures 85 Creating a Procedure for Word 86 Creating a Procedure for Excel 92 Creating a Procedure for PowerPoint 96 Creating a Procedure for Access 101 The Bottom Line 102 Part 2 Learning How to Work with VBA 105 Chapter 5 Understanding the Essentials of VBA Syntax 107 Getting Ready 107 Procedures 108 Statements 109 Keywords 113 Expressions 113 Operators 113 Variables 114 Constants 114 Arguments 115 Objects 117 Collections 117 Properties 118 Methods 118 Events 118 The Bottom Line 120 Chapter 6 Working with Variables, Constants, and Enumerations 123 Working with Variables 123 Working with Constants 140 Working with Enumerations 142 The Bottom Line 142 Chapter 7 Using Array Variables 145 What is an Array? 145 Declaring an Array 146 Storing Values in an Array 148 Multidimensional Arrays 149 Declaring a Dynamic Array 150 Redimensioning an Array 150 Returning Information from an Array 151 Erasing an Array 151 Finding Out Whether a Variable is an Array 151 Finding the Bounds of an Array 152 Sorting an Array 152 Searching through an Array 156 The Bottom Line 165 Chapter 8 Finding the Objects, Methods, and Properties You Need 167 What is an Object? 167 Working with Collections 172 Finding the Objects You Need 174 Using Object Variables to Represent Objects 186 The Bottom Line 189 Part 3 Making Decisions and Using Loops and Functions 191 Chapter 9 Using Built-in Functions 193 What is a Function? 193 Using Functions 194 Using Functions to Convert Data from One Type to Another 198 Using Functions to Manipulate Strings 207 Using VBA’s Mathematical Functions 218 Using VBA’s Date and Time Functions 219 Using File-Management Functions 223 The Bottom Line 225 Chapter 10 Creating Your Own Functions 227 Components of a Function 228 Creating a Function 229 Examples of Functions for Any VBA-Enabled Application 233 Creating a Function for Word 237 Creating a Function for Excel 239 Creating a Function for PowerPoint 240 Creating a Function for Access 242 The Bottom Line 244 Chapter 11 Making Decisions in Your Code 245 How Do You Compare Things in VBA? 245 Testing Multiple Conditions by Using Logical Operators 247 Select Case Statements261 The Bottom Line 264 Chapter 12 Using Loops to Repeat Actions 265 When Should You Use a Loop? 265 Understanding the Basics of Loops 266 Using For… Loops for Fixed Repetitions 267 Using Do…Loops for Variable Numbers of Repetitions 276 While… Wend Loops 287 Nesting Loops 288 Avoiding Infinite Loops 290 The Bottom Line 291 Part 4 Using Message Boxes, Input Boxes, and Dialog Boxes 293 Chapter 13 Getting User Input with Message Boxes and Input Boxes 295 Opening a Procedure to Work On 296 Displaying Status Bar Messages in Word and Excel 297 Message Boxes 299 Input Boxes 311 Forms: When Message Boxes and Input Boxes Won’t Suffice 313 The Bottom Line 314 Chapter 14 Creating Simple Custom Dialog Boxes 315 When Should You Use a Custom Dialog Box? 315 Creating a Custom Dialog Box 316 Linking a Dialog Box to a Procedure 352 Retrieving the User’s Choices from a Dialog Box 354 Examples of Connecting Dialog Boxes to Procedures 358 Using an Application’s Built-in Dialog Boxes from VBA 374 The Bottom Line 380 Chapter 15 Creating Complex Dialog Boxes 381 Creating and Working with Complex Dialog Boxes 382 Using Events to Control Forms 400 The Bottom Line 426 Part 5 Creating Effective Code 429 Chapter 16: Building Modular Code and Using Classes 431 Creating Modular Code 431 Creating and Using Classes 446 The Bottom Line 456 Chapter 17: Debugging Your Code and Handling Errors 457 Principles of Debugging 457 The Different Types of Errors 458 VBA’s Debugging Tools 463 Dealing with Infinite Loops 473 Dealing with Runtime Errors 474 Suppressing Alerts 481 Handling User Interrupts in Word, Excel, and Project 481 Documenting Your Code 483 The Bottom Line 484 Chapter 18: Building Well-Behaved Code 487 What is a Well-Behaved Procedure? 487 Retaining or Restoring the User Environment 488 Leaving the User in the Best Position to Continue Working 489 Keeping the User Informed during the Procedure 489 Making Sure a Procedure is Running under Suitable Conditions 496 Cleaning Up after a Procedure 497 The Bottom Line 499 Chapter 19: Securing Your Code with VBA’s Security Features 501 Understanding How VBA Implements Security 501 Signing Your Macro Projects with Digital Signatures 504 Choosing a Suitable Level of Security 514 Locking Your Code 521 The Bottom Line 523 Part 6 Programming the Office Applications 525 Chapter 20 Understanding the Word Object Model and Key Objects 527 Examining the Word Object Model 527 Working with the Documents Collection and the Document Object 530 Working with the Selection Object 543 Creating and Using Ranges 553 Manipulating Options 555 The Bottom Line 557 Chapter 21 Working with Widely Used Objects in Word 559 Using Find and Replace via VBA 559 Working with Headers, Footers, and Page Numbers 566 Working with Sections, Page Setup, Windows, and Views 571 Working with Tables 576 The Bottom Line 588 Chapter 22 Understanding the Excel Object Model and Key Objects 591 Getting an Overview of the Excel Object Model 591 Understanding Excel’s Creatable Objects 593 Managing Workbooks 593 Working with Worksheets 603 Working with the Active Cell or Selection 608 Working with Ranges 610 Setting Options 613 The Bottom Line 615 Chapter 23 Working with Widely Used Objects in Excel 617 Working with Charts 617 Working with Windows 623 Working with Find and Replace 626 Adding Shapes 629 The Bottom Line 629 Chapter 24 Understanding the PowerPoint Object Model and Key Objects 631 Getting an Overview of the PowerPoint Object Model 631 Understanding PowerPoint’s Creatable Objects 632 Working with Presentations 633 Working with Windows and Views 640 Working with Slides 643 Working with Masters 649 The Bottom Line 651 Chapter 25 Working with Shapes and Running Slide Shows 653 Working with Shapes 653 Working with Headers and Footers 665 Setting Up and Running a Slide Show 667 The Bottom Line 671 Chapter 26 Understanding the Outlook Object Model and Key Objects 673 Getting an Overview of the Outlook Object Model 673 Working with the Application Object 675 Understanding General Methods for Working with Outlook Objects 681 Working with Messages 684 Working with Calendar Items 686 Working with Tasks and Task Requests 688 Searching for Items 689 The Bottom Line 692 Chapter 27 Working with Events in Outlook 693 Working with Application-Level Events 694 Working with Item-Level Events 700 Understanding Quick Steps 710 The Bottom Line 711 Chapter 28 Understanding the Access Object Model and Key Objects 713 Getting Started with VBA in Access 713 Getting an Overview of the Access Object Model 719 Understanding Creatable Objects in Access 720 Opening and Closing Databases 721 Working with the Screen Object 726 Using the DoCmd Object to Run Access Commands 727 The Bottom Line 733 Chapter 29 Manipulating the Data in an Access Database via VBA 735 Understanding How to Proceed 735 Preparing to Manage the Data in a Database 736 Opening a Recordset 737 Accessing a Particular Record in a Recordset 747 Searching for a Record 749 Returning the Fields in a Record 751 Editing a Record 752 Inserting and Deleting Records 752 Closing a Recordset 753 The Bottom Line 753 Chapter 30 Accessing One Application from Another Application.755 Understanding the Tools Used to Communicate between Applications 755 Using Automation to Transfer Information 756 Using the Shell Function to Run an Application 768 Using Data Objects to Store and Retrieve Information 770 Communicating via DDE 773 Communicating via SendKeys 776 The Bottom Line 781 Chapter 31 Programming the Office 2010 Ribbon 783 Hiding the Editing Group on the Word Ribbon 784 Working with Excel and PowerPoint 788 Undoing Ribbon Modifications 789 Selecting the Scope of Your Ribbon Customization 789 Adding a New Group 789 Two Ways to Find the Correct idMso 791 Adding Callbacks 792 Adding Attributes 794 Using Menus and Lists 795 Toggling with a Toggle Button Control 800 Modifying the Ribbon in Access 801 Adding a Callback in Access 805 What to Look For If Things Go Wrong 806 Where to Go from Here 809 The Bottom Line 810 Appendix The Bottom Line 811 Chapter 1: Recording and Running Macros in the Office Applications 811 Chapter 2: Getting Started with the Visual Basic Editor 812 Chapter 3: Editing Recorded Macros 813 Chapter 4: Creating Code from Scratch in the Visual Basic Editor 814 Chapter 5: Understanding the Essentials of VBA Syntax 817 Chapter 6: Working with Variables, Constants, and Enumerations 818 Chapter 7: Using Array Variables 820 Chapter 8: Finding the Objects, Methods, and Properties You Need 821 Chapter 9: Using Built-in Functions 823 Chapter 10: Creating Your Own Functions 824 Chapter 11: Making Decisions in Your Code 826 Chapter 12: Using Loops to Repeat Actions 827 Chapter 13: Getting User Input with Message Boxes and Input Boxes 828 Chapter 14: Creating Simple Custom Dialog Boxes 830 Chapter 15: Creating Complex Dialog Boxes 833 Chapter 16: Building Modular Code and Using Classes 835 Chapter 17: Debugging Your Code and Handling Errors 836 Chapter 18: Building Well-Behaved Code 837 Chapter 19: Securing Your Code with VBA’s Security Features 839 Chapter 20: Understanding the Word Object Model and Key Objects 841 Chapter 21: Working with Widely Used Objects in Word 842 Chapter 22: Understanding the Excel Object Model and Key Objects 844 Chapter 23: Working with Widely Used Objects in Excel 844 Chapter 24: Understanding the PowerPoint Object Model and Key Objects 845 Chapter 25: Working with Shapes and Running Slide Shows 846 Chapter 26: Understanding the Outlook Object Model and Key Objects 847 Chapter 27: Working with Events in Outlook 848 Chapter 28: Understanding the Access Object Model and Key Objects 849 Chapter 29: Manipulating the Data in an Access Database via VBA 850 Chapter 30: Accessing One Application from Another Application 851 Chapter 31: Programming the Office 2010 Ribbon 853 Index 855