book

Mastering VBA for Microsoft® Office 2010

Name: Mastering VBA for Microsoft® Office 2010
Author: Richard Mansfield
ISBN: 9780470634004

by Richard Mansfield

August 2010

Beginner

912 pages

24h 48m

English

Sybex

Read now

Unlock full access

Copyright
Dear Reader,
Acknowledgments
About the Author
Introduction
Where to Get the Example CodeWhat Can I Do with VBA?What's in This Book?How Should I Use This Book?Is This Book Suitable for Me?Conventions Used in This Book
1. Recording Macros and Getting Started with VBA
1. Recording and Running Macros in the Office Applications
1.1. What Is VBA and What Can You Do with It?1.1.1. The Difference between Visual Basic and Visual Basic for Applications1.2. Understanding Macro Basics1.3. Recording a Macro1.3.1. Planning the Macro1.3.2. Starting the Macro Recorder1.3.3. Naming the Macro1.3.3.1. Invalid Macro Names1.3.3.2. Describing Your Macros1.3.3.3. Storing Your Macros1.3.4. Assigning a Way to Run a Macro1.3.4.1.1.3.4.1.1. Running a Macro from the Quick Access Toolbar1.3.4.1.2. Running a Macro Via a Shortcut Key Combination1.3.4.2. Assigning a Way to Run a Macro in Excel1.3.4.3. Assigning a Way to Run a Macro in PowerPoint1.3.4.4. Assigning a Way to Run a Macro in Outlook1.3.4.5. Recording the Actions in the Macro1.4. Running a Macro1.5. Recording a Sample Word Macro1.6. Recording a Sample Excel Macro1.6.1. Create a Personal Macro Workbook if You Don't Have One Yet1.6.2. Record a Sample Excel Macro1.7. Assigning a Way of Running the Macro1.7.1. Assigning a Macro to a Quick Access Toolbar Button in Word1.7.2. Assigning a Macro to a Shortcut Key Combination1.8. Deleting a Macro1.9. The Bottom Line
2. Getting Started with the Visual Basic Editor
2.1. Opening the Visual Basic Editor2.1.1. Opening the Visual Basic Editor with a Macro Selected2.1.2. Opening the Visual Basic Editor Directly2.1.3. Navigating to a Macro2.2. Using the Visual Basic Editor's Main Windows2.2.1. The Project Explorer2.2.2. The Object Browser2.2.3. The Code Window2.2.3.1. Complete Word2.2.3.2. Quick Info2.2.3.3. Auto List Members2.2.3.4. List Constants2.2.3.5. Data Tips2.2.3.6. Margin Indicators2.2.3.7. Other Editing Features2.2.4. The Properties Window2.2.5. The Immediate Window2.3. Setting Properties for a Project2.4. Customizing the Visual Basic Editor2.4.1. Choosing Editor and View Preferences2.4.1.1. Editor Page Options2.4.1.2. Editor Format Page Options2.4.1.3. General Page Options2.4.1.3.1. Form Grid Settings Group Box2.4.1.3.2. The Edit and Continue Group Box2.4.1.3.3. Error Trapping Group Box2.4.1.3.4. Compile Group Box2.4.1.3.5. Show ToolTips and Collapse Proj. Hides Windows2.4.1.4. Docking Page Options2.4.2. Choosing and Laying Out the Editor Windows2.4.3. Customizing the Toolbar and Menu Bar2.4.4. Customizing the Toolbox2.4.4.1. Adding Controls to the Toolbox2.4.4.2. Renaming a Toolbox Control2.4.4.3. Assigning a Picture to a Control's Toolbox Icon2.4.4.4. Removing Controls from the Toolbox2.4.4.5. Adding Pages to the Toolbox2.4.4.6. Renaming Pages in the Toolbox2.4.4.7. Removing Pages from the Toolbox2.4.4.8. Importing and Exporting Toolbox Pages2.4.4.9. Moving Pages in the Toolbox2.5. Closing the Visual Basic Editor and Returning to the Host Application2.6. The Bottom Line
3. Editing Recorded Macros
3.1. Testing a Macro in the Visual Basic Editor3.1.1. Stepping through a Macro3.1.2. Setting Breakpoints3.1.3. Commenting Out Lines3.1.4. Stepping Out of a Macro3.2. Editing the Word Macro3.2.1. Stepping Through the Transpose_Word_Right Macro3.2.2. Running the Transpose_Word_Right Macro3.2.3. Creating a Transpose_Word_Left Macro3.2.4. Save Your Work3.3. Editing the Excel Macro3.3.1. Unhiding the Personal Macro Workbook3.3.2. Opening the Macro for Editing3.3.3. Editing the Macro3.3.4. Saving Your Work3.4. Editing a PowerPoint Macro3.4.1. Save Your Work3.5. The Bottom Line
4. Creating Code from Scratch in the Visual Basic Editor
4.1. Setting Up the Visual Basic Editor for Creating the Procedures4.2. Creating a Procedure for Word4.3. Creating a Procedure for Excel4.4. Creating a Procedure for PowerPoint4.5. Creating a Procedure for Access4.6. The Bottom Line

2. Learning How to Work with VBA
5. Understanding the Essentials of VBA Syntax
5.1. Getting Ready5.2. Procedures5.2.1. Functions5.2.2. Subprocedures5.3. Statements5.4. Keywords5.5. Expressions5.6. Operators5.7. Variables5.8. Constants5.9. Arguments5.9.1. Specifying Argument Names vs. Omitting Argument Names5.9.2. When to Include the Parentheses around the Argument List5.10. Objects5.11. Collections5.12. Properties5.13. Methods5.14. Events5.15. The Bottom Line
6. Working with Variables, Constants, and Enumerations
6.1. Working with Variables6.1.1. Choosing Names for Variables6.1.2. Declaring a Variable6.1.2.1. Declaring a Variable Implicitly6.1.2.2. Declaring a Variable Explicitly6.1.3. Choosing the Scope and Lifetime of a Variable6.1.3.1. Procedure Scope6.1.3.2. Private Scope6.1.3.3. Public Scope6.1.3.4. Using Static Variables6.1.4. Specifying the Data Type for a Variable6.1.4.1. Do You Need to Specify the Data Type?6.1.4.2. Boolean6.1.4.3. Byte6.1.4.4. Currency6.1.4.5. Date6.1.4.6. Decimal6.1.4.7. Double6.1.4.8. Integer6.1.4.9. Long6.1.4.10. Object6.1.4.11. Single6.1.4.12. String6.1.4.13. Variant6.1.4.14. Deciding among Types for Variables6.2. Working with Constants6.2.1. Declaring Your Own Constants6.2.1.1. Syntax6.2.1.2. Example6.2.2. Choosing the Scope and Lifetime for Your Constants6.3. Working with Enumerations6.4. The Bottom Line
7. Using Array Variables
7.1. What Is an Array?7.2. Declaring an Array7.3. Storing Values in an Array7.4. Multidimensional Arrays7.5. Declaring a Dynamic Array7.6. Redimensioning an Array7.7. Returning Information from an Array7.8. Erasing an Array7.9. Finding Out Whether a Variable Is an Array7.10. Finding the Bounds of an Array7.11. Sorting an Array7.12. Searching through an Array7.12.1. Performing a Linear Search through an Array7.12.2. Performing a Binary Search Through an Array7.13. The Bottom Line
8. Finding the Objects, Methods, and Properties You Need
8.1. What Is an Object?8.1.1. Properties8.1.2. Methods8.2. Working with Collections8.2.1. Working with an Object in a Collection8.2.2. Adding an Object to a Collection8.3. Finding the Objects You Need8.3.1. Using the Macro Recorder to Record the Objects You Need8.3.2. Using the Object Browser8.3.2.1. Components of the Object Browser8.3.2.2. Adding and Removing Object Libraries8.3.2.3. Navigating with the Object Browser8.3.3. Using Help to Find the Object You Need8.3.4. Using the Auto List Members Feature8.4. Using Object Variables to Represent Objects8.5. The Bottom Line
3. Making Decisions and Using Loops and Functions
9. Using Built-in Functions
9.1. What Is a Function?9.2. Using Functions9.2.1. Passing Arguments to a Function9.3. Using Functions to Convert Data from One Type to Another9.3.1. Using the Asc Function to Return a Character Code9.3.2. Using the Val Function to Extract a Number from the Start of a String9.3.3. Using the Str Function to Convert a Value to a String9.3.4. Using the Format Function to Format an Expression9.3.4.1. Using Predefined Numeric Formats9.3.4.2. Creating a Numeric Format9.3.4.3. Creating a Date or Time Format9.3.4.4. Creating a String Format9.3.5. Using the Chr Function and Constants to Enter Special Characters in a String9.4. Using Functions to Manipulate Strings9.4.1. Using the Left, Right, and Mid Functions to Return Part of a String9.4.1.1. Using the Left Function9.4.1.2. Using the Right Function9.4.1.3. Using the Mid Function9.4.2. Using InStr and InStrRev to Find a String within Another String9.4.3. Using LTrim, RTrim, and Trim to Trim Spaces from a String9.4.4. Using Len to Check the Length of a String9.4.5. Using StrConv, LCase, and UCase to Change the Case of a String9.4.5.1. Using StrConv9.4.5.2. Using LCase and UCase9.4.6. Using the StrComp Function to Compare Apples to Apples9.5. Using VBA's Mathematical Functions9.6. Using VBA's Date and Time Functions9.6.1. Using the DatePart Function to Parse Dates9.6.2. Using the DateDiff Function to Return an Interval9.6.3. Using the DateAdd Function to Add to a Date9.7. Using File-Management Functions9.7.1. Using the Dir Function to Check Whether a File Exists9.7.2. Returning the Current Path9.8. The Bottom Line
10. Creating Your Own Functions
10.1. Components of a Function10.2. Creating a Function10.2.1. Starting a Function Manually10.2.2. Starting a Function by Using the Add Procedure Dialog Box10.2.3. Passing Arguments to the Function10.2.4. Declaring the Data Types of Arguments10.2.5. Specifying an Optional Argument10.2.6. Controlling the Scope of a Function10.3. Examples of Functions for Any VBA-Enabled Application10.4. Creating a Function for Word10.5. Creating a Function for Excel10.6. Creating a Function for PowerPoint10.7. Creating a Function for Access10.8. The Bottom Line
11. Making Decisions in Your Code
11.1. How Do You Compare Things in VBA?11.2. Testing Multiple Conditions by Using Logical Operators11.2.1. If Statements11.2.2. If...Then11.2.2.1. Syntax11.2.2.2. Examples11.2.2.2.1. One-Line If Statements11.2.2.2.2. Block If Statements11.2.3. If... Then... Else Statements11.2.3.1. Syntax11.2.3.2. Example11.2.4. If... Then... ElseIf... Else Statements11.2.4.1. Syntax11.2.4.2. Examples11.2.4.2.1. A Simple If... Then... ElseIf... Else Statement11.2.4.2.2. An If... Then... ElseIf Statement without an Else Clause11.2.5. Creating Loops with If and GoTo11.2.5.1. Syntax11.2.5.2. Example11.2.6. Nesting If Statements11.3. Select Case Statements11.3.1. Syntax11.3.2. Example11.4. The Bottom Line
12. Using Loops to Repeat Actions
12.1. When Should You Use a Loop?12.2. Understanding the Basics of Loops12.3. Using For... Loops for Fixed Repetitions12.3.1. For... Next Loops12.3.1.1. Syntax12.3.1.2. Straightforward For... Next Loops12.3.1.3. For... Next Loops with Step Values12.3.1.4. Using an Input Box to Drive a For... Next Loop12.3.2. For Each... Next Loops12.3.2.1. Syntax12.3.3. Using an Exit For Statement12.4. Using Do... Loops for Variable Numbers of Repetitions12.4.1. Do While... Loop Loops12.4.1.1. Syntax12.4.2. Do... Loop While Loops12.4.2.1. Syntax12.4.3. Do Until... Loop Loops12.4.3.1. Syntax12.4.4. Do... Loop Until Loops12.4.4.1. Syntax12.4.5. Using an Exit Do Statement12.5. While... Wend Loops12.6. Nesting Loops12.7. Avoiding Infinite Loops12.8. The Bottom Line
4. Using Message Boxes, Input Boxes, and Dialog Boxes
13. Getting User Input with Message Boxes and Input Boxes
13.1. Opening a Procedure to Work On13.2. Displaying Status Bar Messages in Word and Excel13.3. Message Boxes13.3.1. The Pros and Cons of Message Boxes13.3.2. Message Box Syntax13.3.3. Displaying a Simple Message Box13.3.4. Displaying a Multiline Message Box13.3.5. Choosing Buttons for a Message Box13.3.6. Choosing an Icon for a Message Box13.3.7. Setting a Default Button for a Message Box13.3.8. Controlling the Modality of a Message Box13.3.9. Specifying a Title for a Message Box13.3.10. Title Bars Can Provide Useful Information13.3.11. Adding a Help Button to a Message Box13.3.12. Specifying a Help File for a Message Box13.3.13. Using Some Arguments without Others13.3.14. Retrieving a Value from a Message Box13.4. Input Boxes13.4.1. Input Box Syntax13.4.2. Retrieving Input from an Input Box13.5. Forms: When Message Boxes and Input Boxes Won't Suffice13.6. The Bottom Line
14. Creating Simple Custom Dialog Boxes
14.1. When Should You Use a Custom Dialog Box?14.2. Creating a Custom Dialog Box14.2.1. Designing the Dialog Box14.2.2. Inserting a User Form14.2.2.1. Choosing User Form Grid Settings14.2.3. Renaming a User Form14.2.4. Adding Controls to the User Form14.2.5. Renaming Controls14.2.6. Moving a Control14.2.7. Changing the Caption on a Control14.2.8. Key Properties for the Toolbox Controls14.2.8.1. Common Properties14.2.8.2. Label14.2.8.3. TextBox14.2.8.4. ComboBox and ListBox14.2.8.5. CheckBox14.2.8.6. OptionButton14.2.8.7. ToggleButton14.2.8.8. Frame14.2.8.9. CommandButton14.2.8.10. TabStrip and MultiPage14.2.8.11. ScrollBar and SpinButton14.2.8.12. Image14.2.8.13. Page14.2.8.14. Tab14.2.9. Working with Groups of Controls14.2.9.1. Grouping Controls14.2.9.2. Ungrouping Controls14.2.9.3. Sizing Grouped Controls14.2.9.4. Deleting Grouped Controls14.2.9.5. Working with One Control in a Group14.2.10. Aligning Controls14.2.11. Placing Controls14.2.12. Adjusting the Tab Order of the Dialog Box14.3. Linking a Dialog Box to a Procedure14.3.1. Loading and Unloading a Dialog Box14.3.2. Displaying and Hiding a Dialog Box14.3.3. Setting a Default Command Button14.4. Retrieving the User's Choices from a Dialog Box14.4.1. Returning a String from a Text Box14.4.2. Returning a Value from an Option Button14.4.3. Returning a Value from a Check Box14.4.4. Returning a Value from a List Box14.4.5. Returning a Value from a Combo Box14.5. Examples of Connecting Dialog Boxes to Procedures14.5.1. Word Example: The Move-Paragraph Procedure14.5.1.1. Recording the Procedure14.5.1.2. Creating the Dialog Box14.5.2. General Example: Opening a File from a List Box14.5.2.1. Building the User Form14.5.3. Creating the Code for the User Form14.6. Using an Application's Built-in Dialog Boxes from VBA14.6.1. Displaying a Built-in Dialog Box14.6.1.1. Finding the Dialog Box Name and Constant14.6.1.2. Choosing between the Show Method and the Display Method14.6.1.3. Using the Show Method to Display and Execute a Dialog Box14.6.1.4. Using the Display Method to Display a Dialog Box14.6.2. Setting and Restoring Options in a Built-in Dialog Box14.6.3. Which Button Did the User Choose in a Dialog Box?14.6.4. Specifying a Time-Out for a Dialog Box14.7. The Bottom Line
15. Creating Complex Dialog Boxes
15.1. Creating and Working with Complex Dialog Boxes15.1.1. Updating a Dialog Box to Reflect the User's Choices15.1.2. Revealing a Hidden Part of a Dialog Box15.1.3. Tracking a Procedure in a Dialog Box15.1.4. Using Multipage Dialog Boxes and Tab Strip Controls15.1.4.1. Multipage Dialog Boxes15.1.4.2. Dialog Boxes That Use Tab Strips15.1.4.3. Using a Picture in a Dialog Box15.1.5. Creating a Modeless Dialog Box15.1.6. Choosing a Position for the Dialog Box15.2. Using Events to Control Forms15.2.1. Events That Apply Only to the UserForm Object15.2.1.1. Initialize Event15.2.1.2. QueryClose Event15.2.1.3. Activate Event15.2.1.4. Deactivate Event15.2.1.5. Resize Event15.2.1.6. Terminate Event15.2.2. Events That Apply to the UserForm Object and to Container Controls15.2.2.1. Scroll Event15.2.2.2. Zoom Event15.2.2.3. Layout Event15.2.2.4. AddControl Event15.2.2.5. RemoveControl Event15.2.3. Events That Apply to Many or Most Controls15.2.3.1. Click Event15.2.3.2. Change Event15.2.3.3. Enter and Exit Events15.2.3.4. BeforeUpdate Event15.2.3.5. AfterUpdate Event15.2.3.6. KeyDown and KeyUp Events15.2.3.7. KeyPress Event15.2.3.8. MouseDown Event and MouseUp Event15.2.3.9. MouseMove Event15.2.3.10. BeforeDragOver Event15.2.3.11. BeforeDropOrPaste Event15.2.3.12. DblClick Event15.2.3.13. Error Event15.2.4. Events That Apply Only to a Few Controls15.2.4.1. DropButtonClick Event15.2.4.2. SpinDown and SpinUp Events15.3. The Bottom Line
5. Creating Effective Code
16. Building Modular Code and Using Classes
16.1. Creating Modular Code16.1.1. What Is Modular Code?16.1.2. Advantages of Using Modular Code16.1.3. How to Approach Creating Modular Code16.1.4. Arranging Your Code in Modules16.1.5. Calling a Procedure16.1.6. Making Logical Improvements to Your Code16.1.6.1. Declaring Variables Explicitly instead of Implicitly16.1.6.2. Using With Statements to Simplify Your Code16.1.6.3. Don't Use With Statements Pointlessly16.1.6.4. Optimizing Your Select Case Statements16.1.6.5. Don't Check Things Pointlessly16.1.6.6. Removing Unused Elements from Your Code16.1.7. Making Visual Improvements to Your Code16.1.7.1. Indenting the Different Levels of Code16.1.7.2. Using Line-Continuation Characters to Break Long Lines16.1.7.3. Using the Concatenation Character to Break Long Strings16.1.7.4. Using Blank Lines to Break Up Your Code16.1.7.5. Using Variables to Simplify Complex Syntax16.1.7.6. Passing Information from One Procedure to Another Using Arguments16.1.7.7. Passing Information from One Procedure to Another Using Private or Public Variables16.2. Creating and Using Classes16.2.1. What Can You Do with Class Modules?16.2.2. A Brief Overview16.2.3. Planning Your Class16.2.4. Creating the Class Module16.2.5. Naming the Class16.2.6. Setting the Instancing Property16.2.7. Declaring Variables and Constants for the Class16.2.8. Adding Properties to the Class16.2.8.1. Creating a Property by Using a Public Variable16.2.8.2. Creating a Property by Using Property Procedures16.2.8.2.1. Assigning a Value to a Property with a Property Let Procedure16.2.8.2.2. Returning a Value from a Property with a Property Get Procedure16.2.8.2.3. Assigning an Object to a Property with a Property Set Procedure16.2.8.3. The Properties for the Book Class16.2.9. Adding Methods to the Class16.2.10. Using Your Class16.3. The Bottom Line
17. Debugging Your Code and Handling Errors
17.1. Principles of Debugging17.2. The Different Types of Errors17.2.1. Language Errors17.2.2. Compile Errors17.2.3. Runtime Errors17.2.4. Program Logic Errors17.3. VBA's Debugging Tools17.3.1. Break Mode17.3.2. The Step Over and Step Out Commands17.3.3. The Locals Window17.3.4. The Watch Window17.3.4.1. Setting Watch Expressions17.3.4.2. Editing Watch Expressions17.3.4.3. Deleting Watch Expressions17.3.4.4. Using the Quick Watch Feature17.3.5. The Immediate Window17.3.5.1. What You Can't Do in the Immediate Window17.3.5.2. Entering Code in the Immediate Window17.3.5.3. Printing Information to the Immediate Window17.3.6. The Call Stack Dialog Box17.4. Dealing with Infinite Loops17.5. Dealing with Runtime Errors17.5.1. When Should You Write an Error Handler?17.5.2. Trapping an Error17.5.3. Disabling an Error Trap17.5.4. Resuming after an Error17.5.4.1. Using a Resume Statement17.5.4.2. Using a Resume Next Statement17.5.4.3. Using a Resume Line Statement17.5.5. Getting a Description of an Error17.5.6. Raising Your Own Errors17.6. Suppressing Alerts17.7. Handling User Interrupts in Word, Excel, and Project17.7.1. Disabling User Input While a Procedure Is Running17.7.2. Disabling User Input While Part of a Procedure Is Running17.8. Documenting Your Code17.9. The Bottom Line
18. Building Well-Behaved Code
18.1. What Is a Well-Behaved Procedure?18.2. Retaining or Restoring the User Environment18.3. Leaving the User in the Best Position to Continue Working18.4. Keeping the User Informed during the Procedure18.4.1. Manipulating the Cursor18.4.1.1. Manipulating the Cursor in Word18.4.1.2. Manipulating the Cursor in Excel18.4.2. Displaying Information at the Beginning of a Procedure18.4.3. Communicating with the User via a Message Box or Dialog Box at the End of a Procedure18.4.4. Creating a Log File18.5. Making Sure a Procedure Is Running under Suitable Conditions18.6. Cleaning Up after a Procedure18.6.1. Undoing Changes the Procedure Has Made18.6.2. Removing Scratch Files and Folders18.6.2.1. Building a Scratch Folder18.6.2.2. Deleting the Scratch Folder18.7. The Bottom Line
19. Securing Your Code with VBA's Security Features
19.1. Understanding How VBA Implements Security19.2. Signing Your Macro Projects with Digital Signatures19.2.1. What Is a Digital Certificate?19.2.2. Getting a Digital Certificate19.2.2.1. Creating a Digital Certificate of Your Own19.2.2.2. Getting a Digital Certificate from Your Company19.2.2.3. Getting a Digital Certificate from a Commercial Certification Authority19.2.2.4. Installing a Digital Certificate19.2.2.5. Exporting a Digital Certificate19.2.2.6. Removing a Digital Certificate19.2.2.7. Signing a Macro Project with a Digital Signature19.2.2.8. Removing a Digital Signature from a Macro Project19.2.2.9. Whose Certificate Is It, and What Does It Mean?19.3. Choosing a Suitable Level of Security19.3.1. Understanding the Security Threats Posed by VBA19.3.2. Protecting against Macro Viruses19.3.3. Specifying a Suitable Security Setting19.3.4. New Security in Office 201019.3.5. Specifying Whom to Trust19.3.5.1. Whom Does the Computer Trust Already?19.3.5.2. Adding a Trusted Publisher19.3.5.3. Removing a Previously Trusted Publisher19.4. Locking Your Code19.5. The Bottom Line
6. Programming the Office Applications
20. Understanding the Word Object Model and Key Objects
20.1. Examining the Word Object Model20.2. Working with the Documents Collection and the Document Object20.2.1. Creating a Document20.2.2. Creating a Template20.2.3. Saving a Document20.2.3.1. Saving a File for the First Time or as a Different File20.2.3.2. Saving a Document That Has Already Been Saved20.2.3.3. Saving All Open Documents20.2.3.4. Checking Whether a Document Contains Unsaved Changes20.2.4. Opening a Document20.2.5. Closing a Document20.2.6. Changing a Document's Template20.2.7. Printing a Document20.2.8. Working with the ActiveDocument Object20.3. Working with the Selection Object20.3.1. Checking the Type of Selection20.3.2. Checking the Story Type of the Selection20.3.3. Getting Other Information about the Current Selection20.3.4. Inserting Text at, after, or before the Selection20.3.5. Inserting a Paragraph in a Selection20.3.6. Applying a Style20.3.7. Extending a Selection20.3.8. Collapsing a Selection20.4. Creating and Using Ranges20.4.1. Defining a Named Range20.4.2. Redefining a Range20.4.3. Using the Duplicate Property to Store or Copy Formatting20.5. Manipulating Options20.5.1. Making Sure Hyperlinks Require Ctrl+Clicking20.5.2. Turning Off Overtype20.5.3. Setting a Default File Path20.5.4. Turning Off Track Changes20.6. The Bottom Line
21. Working with Widely Used Objects in Word
21.1. Using Find and Replace via VBA21.1.1. Understanding the Syntax for the Execute Method21.1.2. Putting Find and Replace to Work21.2. Working with Headers, Footers, and Page Numbers21.2.1. Understanding How VBA Implements Headers and Footers21.2.2. Getting to a Header or Footer21.2.3. Checking to See If a Header or Footer Exists21.2.4. Linking to the Header or Footer in the Previous Section21.2.5. Creating a Different First-Page Header21.2.6. Creating Different Odd- and Even-Page Headers21.2.7. Adding Page Numbers to Your Headers and Footers21.2.7.1. Adding Page Numbers to One or More Sections of a Document21.2.7.2. Removing Page Numbers from One or More Sections of a Document21.2.7.3. Finding Out If a Section of a Document Has Page Numbers21.2.7.4. Changing the Page Numbering for a Section21.2.7.5. Suppressing the Page Number for the First Page21.2.7.6. Formatting Page Numbers21.2.7.7. Creating "Page X of Y" Type Page Numbers21.3. Working with Sections, Page Setup, Windows, and Views21.3.1. Adding a Section to a Document21.3.2. Changing the Page Setup21.3.3. Opening a New Window Containing an Open Document21.3.4. Closing All Windows for a Document Except the First21.3.5. Splitting a Window21.3.6. Displaying the Document Map for a Window21.3.7. Scrolling a Window21.3.8. Arranging Windows21.3.9. Positioning and Sizing a Window21.3.10. Making Sure an Item Is Displayed in the Window21.3.11. Changing a Document's View21.3.12. Zooming the View to Display Multiple Pages21.4. Working with Tables21.4.1. Creating a Table21.4.2. Selecting a Table21.4.3. Converting Text to a Table21.4.4. Making Sure the Selection Is within a Table21.4.5. Finding Out Where a Selection Is within a Table21.4.6. Sorting a Table21.4.7. Adding a Column to a Table21.4.8. Deleting a Column from a Table21.4.9. Setting the Width of a Column21.4.10. Selecting a Column21.4.11. Adding a Row to a Table21.4.12. Deleting a Row from a Table21.4.13. Setting the Height of One or More Rows21.4.14. Selecting a Row21.4.15. Inserting a Cell21.4.16. Returning the Text within a Cell21.4.17. Entering Text in a Cell21.4.18. Deleting Cells21.4.19. Selecting a Range of Cells21.4.20. Converting a Table or Rows to Text21.5. The Bottom Line
22. Understanding the Excel Object Model and Key Objects
22.1. Getting an Overview of the Excel Object Model22.2. Understanding Excel's Creatable Objects22.3. Managing Workbooks22.3.1. Creating a Workbook22.3.1.1. Creating a New Blank Workbook22.3.1.2. Creating a New Workbook Based on a Template22.3.1.3. Creating a New Workbook Based on an Existing Workbook22.3.1.4. Creating a Chart Workbook, a Macro Sheet, or a Worksheet22.3.2. Saving a Workbook22.3.2.1. Saving a Workbook for the First Time or as a Different File22.3.2.2. Saving a Workbook That Has Already Been Saved22.3.2.3. Saving All Open Workbooks22.3.3. Opening a Workbook22.3.4. Closing a Workbook22.3.4.1. Closing All Open Workbooks22.3.5. Sharing a Workbook22.3.6. Protecting a Workbook22.3.7. Working with the ActiveWorkbook Object22.4. Working with Worksheets22.4.1. Inserting a Worksheet22.4.2. Deleting a Worksheet22.4.3. Copying or Moving a Worksheet22.4.4. Printing a Worksheet22.4.5. Protecting a Worksheet22.4.6. Working with the ActiveSheet Object22.5. Working with the Active Cell or Selection22.5.1. Working with the Active Cell22.5.1.1. Getting and Setting the Value of the Active Cell22.5.1.2. Moving the Active Cell to Another Address22.5.1.3. Working with the Region around the Active Cell22.5.2. Working with the User's Selection22.6. Working with Ranges22.6.1. Working with a Range of Cells22.6.2. Creating a Named Range22.6.3. Deleting a Named Range22.6.4. Working with a Named Range22.6.5. Working with the Used Range22.6.6. Working with the Special Cells22.6.7. Entering a Formula in a Cell22.7. Setting Options22.7.1. Setting Options in the Application Object22.7.1.1. Controlling Excel's Calculation22.7.1.2. Clearing the Recently Used Files List22.7.1.3. Setting a Default File Location22.7.2. Setting Options in a Workbook22.7.2.1. Forcing Excel to Remove Personal Information from the File Properties When You Save22.8. The Bottom Line
23. Working with Widely Used Objects in Excel
23.1. Working with Charts23.1.1. Creating a Chart23.1.1.1. Creating a Chart on a New Chart Sheet23.1.1.2. Creating a Chart on an Existing Worksheet23.1.2. Specifying the Source Data for the Chart23.1.3. Specifying the Chart Type23.1.4. Working with Series in the Chart23.1.4.1. Adding a New Series23.1.4.2. Extending an Existing Series23.1.4.3. Creating a New Series23.1.5. Adding a Legend to the Chart23.1.6. Adding a Chart Title23.1.7. Working with a Chart Axis23.2. Working with Windows23.2.1. Opening a New Window on a Workbook23.2.2. Closing a Window23.2.3. Activating a Window23.2.4. Arranging and Resizing Windows23.2.5. Zooming a Window and Setting Display Options23.3. Working with Find and Replace23.3.1. Searching with the Find Method23.3.2. Continuing a Search with the FindNext and FindPrevious Methods23.3.3. Replacing with the Replace Method23.3.4. Searching for and Replacing Formatting23.4. Adding Shapes23.5. The Bottom Line
24. Understanding the PowerPoint Object Model and Key Objects
24.1. Getting an Overview of the PowerPoint Object Model24.2. Understanding PowerPoint's Creatable Objects24.3. Working with Presentations24.3.1. Creating a New Presentation Based on the Default Template24.3.2. Creating a New Presentation Based on a Template24.3.3. Opening an Existing Presentation24.3.4. Saving a Presentation24.3.4.1. Saving a Presentation for the First Time or under a Different Name24.3.4.2. Saving a Presentation under Its Existing Name24.3.4.3. Saving a Copy of a Presentation24.3.4.4. Saving All Open Presentations24.3.5. Closing a Presentation24.3.6. Exporting a Presentation or Some Slides to Graphics24.3.7. Printing a Presentation24.3.8. Applying a Template to a Presentation, to a Slide, or to a Range of Slides24.3.9. Working with the Active Presentation24.4. Working with Windows and Views24.4.1. Working with the Active Window24.4.2. Opening a New Window on a Presentation24.4.3. Closing a Window24.4.4. Activating a Window24.4.5. Arranging and Resizing Windows24.4.6. Changing the View24.4.7. Working with Panes24.5. Working with Slides24.5.1. Adding a Slide to a Presentation24.5.2. Inserting Slides from an Existing Presentation24.5.3. Finding a Slide by Its ID Number24.5.4. Changing the Layout of an Existing Slide24.5.5. Deleting an Existing Slide24.5.6. Copying and Pasting a Slide24.5.7. Duplicating a Slide24.5.8. Moving a Slide24.5.9. Accessing a Slide by Name24.5.10. Working with a Range of Slides24.5.11. Formatting a Slide24.5.11.1. Applying a Background to One or More Slides24.5.11.2. Applying a Color Scheme to a Slide24.5.12. Setting a Transition for a Slide, a Range of Slides, or a Master24.6. Working with Masters24.6.1. Working with the Slide Master24.6.2. Working with the Title Master24.6.3. Working with the Handout Master24.6.4. Working with the Notes Master24.6.5. Deleting a Master24.7. The Bottom Line
25. Working with Shapes and Running Slide Shows
25.1. Working with Shapes25.1.1. Adding Shapes to Slides25.1.1.1. Shared Arguments for Adding Shapes25.1.1.2. Type Argument for Adding Shapes25.1.1.3. Arguments Specific to the AddTextEffect Method25.1.1.4. Arguments Specific to the AddOLEObject Method25.1.1.5. An Example of Using the AddShape Method25.1.1.6. An Example of Using the AddTextEffect Method25.1.1.7. An Example of Using the AddTextbox Method25.1.2. Deleting a Shape25.1.3. Selecting All Shapes25.1.4. Repositioning and Resizing a Shape25.1.5. Copying Formatting from One Shape to Another25.1.6. Working with Text in a Shape25.1.6.1. Finding Out Whether a Shape Has a Text Frame25.1.6.2. Returning and Setting the Text in a Text Range25.1.6.3. Formatting the Text in a Text Range25.1.6.4. Formatting the Bullets for a Text Range25.1.7. Setting an Animation for a Shape or a Range of Shapes25.2. Working with Headers and Footers25.2.1. Returning the Header or Footer Object You Want25.2.2. Displaying or Hiding a Header or Footer Object25.2.3. Setting the Text in a Header or Footer25.2.4. Setting the Format for Date and Time Headers and Footers25.3. Setting Up and Running a Slide Show25.3.1. Controlling the Show Type25.3.2. Creating a Custom Show25.3.3. Deleting a Custom Show25.3.4. Starting a Slide Show25.3.5. Changing the Size and Position of the Slide Show25.3.6. Moving from Slide to Slide25.3.7. Pausing the Show and Using White and Black Screens25.3.8. Starting and Stopping Custom Shows25.3.9. Exiting the Slide Show25.4. The Bottom Line
26. Understanding the Outlook Object Model and Key Objects
26.1. Getting an Overview of the Outlook Object Model26.1.1. Understanding Where Outlook Stores VBA Items26.1.2. Understanding Outlook's Creatable Objects and Main User Interface Items26.2. Working with the Application Object26.2.1. Working with the NameSpace Object26.2.1.1. Accessing Default Folders within the NameSpace Object26.2.1.2. Accessing Other Folders within the NameSpace Object26.2.1.3. Creating a New Folder26.2.1.4. Deleting a Folder26.2.2. Working with Inspectors and Explorers26.2.2.1. Opening an Inspector Window26.2.2.2. Returning the Inspector Associated with an Item26.2.2.3. Returning the Active Window, Inspector, or Explorer26.2.2.4. Working with the Active Inspector26.2.3. Creating Items26.2.3.1. Using the CreateItem Method to Create Default Items26.2.3.2. Using the CreateItemFromTemplate Method to Create Items Based on Templates26.2.4. Quitting Outlook26.3. Understanding General Methods for Working with Outlook Objects26.3.1. Using the Display Method26.3.2. Using the Close Method26.3.3. Using the Delete Method26.3.4. Using the PrintOut Method26.3.5. Using the Save Method26.3.6. Using the SaveAs Method26.4. Working with Messages26.4.1. Creating a New Message26.4.2. Working with the Contents of a Message26.4.3. Adding an Attachment to a Message26.4.4. Sending a Message26.5. Working with Calendar Items26.5.1. Creating a New Calendar Item26.5.2. Working with the Contents of a Calendar Item26.6. Working with Tasks and Task Requests26.6.1. Creating a Task26.6.2. Working with the Contents of a Task Item26.6.3. Assigning a Task to a Colleague26.7. Searching for Items26.8. The Bottom Line
27. Working with Events in Outlook
27.1. Working with Application-Level Events27.1.1. Using the Startup Event27.1.2. Using the Quit Event27.1.3. Using the ItemSend Event27.1.4. Using the NewMail and NewMailEx Events27.1.5. Using the AdvancedSearchComplete and the AdvancedSearchStopped Events27.1.6. Using the MAPILogonComplete Event27.1.7. Using the Reminder Event27.1.8. Using the OptionsPagesAdd Event27.2. Working with Item-Level Events27.2.1. Declaring an Object Variable and Initializing an Event27.2.2. Understanding the Events That Apply to All Message Items27.2.3. Understanding the Events That Apply to Explorers, Inspectors, and Views27.2.4. Understanding the Events That Apply to Folders27.2.5. Understanding the Events That Apply to Items and Results27.2.6. Understanding the Events That Apply to the Outlook Bar27.2.7. Understanding the Events That Apply to Reminders27.2.8. Understanding the Events That Apply to Synchronization27.3. Understanding Quick Steps27.4. The Bottom Line
28. Understanding the Access Object Model and Key Objects
28.1. Getting Started with VBA in Access28.1.1. Creating a Module in the VBA Editor28.1.2. Creating a Function28.1.3. The Revamped Macro Builder28.1.4. Creating an Access-Style Macro to Run a Function28.1.5. Using an AutoExec Macro to Initialize an Access Session28.1.6. Running a Subprocedure28.1.7. Understanding the Option Compare Database Statement28.2. Getting an Overview of the Access Object Model28.3. Understanding Creatable Objects in Access28.4. Opening and Closing Databases28.4.1. Using the CurrentDb Method to Return the Current Database28.4.2. Opening a Different Database as the Current Database and Closing the Current Database28.4.3. Opening Multiple Databases at Once28.4.4. Closing a Database28.4.5. Creating and Removing Workspaces28.4.5.1. Creating a New Workspace28.4.5.2. Removing a Workspace28.5. Working with the Screen Object28.6. Using the DoCmd Object to Run Access Commands28.6.1. Using the OpenForm Method to Open a Form28.6.2. Using the PrintOut Method to Print an Object28.6.3. Using the RunMacro Method to Run a Macro28.7. The Bottom Line
29. Manipulating the Data in an Access Database via VBA
29.1. Understanding How to Proceed29.2. Preparing to Manage the Data in a Database29.2.1. Adding a Reference to the Appropriate Object Library29.2.2. Establishing a Connection to the Database29.3. Opening a Recordset29.3.1. Opening a Recordset Using ADO29.3.2. Choosing How to Access the Data in an ADO Recordset29.3.2.1. Using a Table to Access the Data in an ADO Recordset29.3.2.2. Using an SQL SELECT Statement to Access a Subset of the Data in an ADO Recordset29.3.3. Opening a Recordset Using DAO29.3.3.1. Opening a DAO Recordset Using a Table29.3.3.2. Opening a DAO Recordset Using an SQL SELECT Statement29.4. Accessing a Particular Record in a Recordset29.4.1. Using the MoveFirst, MoveNext, MovePrevious, and MoveLast Methods29.4.2. Using the Move Method to Move by Multiple Records29.5. Searching for a Record29.5.1. Searching for a Record in an ADO Recordset29.5.2. Searching for a Record in a DAO Recordset29.6. Returning the Fields in a Record29.7. Editing a Record29.8. Inserting and Deleting Records29.9. Closing a Recordset29.10. The Bottom Line
30. Accessing One Application from Another Application
30.1. Understanding the Tools Used to Communicate Between Applications30.2. Using Automation to Transfer Information30.2.1. Understanding Early and Late Binding30.2.2. Creating an Object with the CreateObject Function30.2.3. Returning an Object with the GetObject Function30.2.4. Examples of Using Automation with the Office Applications30.2.4.1. Transferring Information from an Excel Spreadsheet to a Word Document30.2.4.2. Transferring Information from Word Documents to an Excel Workbook30.2.4.3. Placing a PowerPoint Slide in an Outlook Message30.3. Using the Shell Function to Run an Application30.3.1. Returning the Task ID of the Started Application30.3.2. Activating an Application30.4. Using Data Objects to Store and Retrieve Information30.4.1. Creating a Data Object30.4.2. Storing Information in a Data Object30.4.3. Returning Information from a Data Object30.4.4. Assigning Information to the Clipboard30.4.5. Returning Information from the Clipboard to a Data Object30.4.6. Finding Out Whether a Data Object Contains a Given Format30.5. Communicating via DDE30.5.1. Using DDEInitiate to Start a DDE Connection30.5.2. Using DDERequest to Return Text from Another Application30.5.3. Using DDEPoke to Send Text to Another Application30.5.4. Using DDEExecute to Execute a Command in Another Application30.5.5. Using DDETerminate to Close a DDE Channel30.5.6. Using DDETerminateAll to Close All Open DDE Channels30.6. Communicating via SendKeys30.7. The Bottom Line
31. Programming the Office 2010 Ribbon
31.1. Hiding the Editing Group on the Word Ribbon31.1.1. A Word of Warning31.1.2. XML Terminology31.1.3. Using Built-in Icons31.2. Working with Excel and PowerPoint31.3. Undoing Ribbon Modifications31.4. Selecting the Scope of Your Ribbon Customization31.5. Adding a New Group31.5.1. Cautions about Customizing31.5.2. Two Ways to Find the Correct idMso31.6. Adding Callbacks31.7. Adding Attributes31.7.1. Using Built-in Icons and ScreenTips31.7.2. Creating Your Own Icons31.8. Using Menus and Lists31.8.1. Adding Menus31.8.2. Adding a DropDown List Control31.8.3. Using a DialogBoxLauncher31.9. Toggling with a Toggle Button Control31.10. Modifying the Ribbon in Access31.10.1. Testing Your New Ribbon31.11. Adding a Callback in Access31.12. What to Look For If Things Go Wrong31.12.1. Employ Error Message Tools31.12.2. Cure Common User Interface Programming Problems31.12.2.1. Callback Problems: Bad Reference, Missing VBA Procedure, or Security Issues31.12.2.2. Other Callback Problems31.12.2.3. XML Mistakes31.13. Where to Go from Here31.14. The Bottom Line
A. The Bottom Line
A.1. Chapter 1: Recording and Running Macros in the Office ApplicationsA.2. Chapter 2: Getting Started with the Visual Basic EditorA.3. Chapter 3: Editing Recorded MacrosA.4. Chapter 4: Creating Code from Scratch in the Visual Basic EditorA.5. Chapter 5: Understanding the Essentials of VBA SyntaxA.6. Chapter 6: Working with Variables, Constants, and EnumerationsA.7. Chapter 7: Using Array VariablesA.8. Chapter 8: Finding the Objects, Methods, and Properties You NeedA.9. Chapter 9: Using Built-in FunctionsA.10. Chapter 10: Creating Your Own FunctionsA.11. Chapter 11: Making Decisions in Your CodeA.12. Chapter 12: Using Loops to Repeat ActionsA.13. Chapter 13: Getting User Input with Message Boxes and Input BoxesA.14. Chapter 14: Creating Simple Custom Dialog BoxesA.15. Chapter 15: Creating Complex Dialog BoxesA.16. Chapter 16: Building Modular Code and Using ClassesA.17. Chapter 17: Debugging Your Code and Handling ErrorsA.18. Chapter 18: Building Well-Behaved CodeA.19. Chapter 19: Securing Your Code with VBA's Security FeaturesA.20. Chapter 20: Understanding the Word Object Model and Key ObjectsA.21. Chapter 21: Working with Widely Used Objects in WordA.22. Chapter 22: Understanding the Excel Object Model and Key ObjectsA.23. Chapter 23: Working with Widely Used Objects in ExcelA.24. Chapter 24: Understanding the PowerPoint Object Model and Key ObjectsA.25. Chapter 25: Working with Shapes and Running Slide ShowsA.26. Chapter 26: Understanding the Outlook Object Model and Key ObjectsA.27. Chapter 27: Working with Events in OutlookA.28. Chapter 28: Understanding the Access Object Model and Key ObjectsA.29. Chapter 29: Manipulating the Data in an Access Database via VBAA.30. Chapter 30: Accessing One Application from Another ApplicationA.31. Chapter 31: Programming the Office 2010 Ribbon

Overview

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.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780470634004Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills