Mastering VBA for Microsoft® Office 2010

Mastering VBA for Microsoft® Office 2010

by Richard Mansfield
Released August 2010
Publisher(s): Sybex
ISBN: 9780470634004

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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

  1. Copyright
  2. Dear Reader,
  3. Acknowledgments
  4. About the Author
  5. Introduction
    1. Where to Get the Example Code
    2. What Can I Do with VBA?
    3. What's in This Book?
    4. How Should I Use This Book?
    5. Is This Book Suitable for Me?
    6. Conventions Used in This Book
  6. 1. Recording Macros and Getting Started with VBA
    1. 1. Recording and Running Macros in the Office Applications
      1. 1.1. What Is VBA and What Can You Do with It?
        1. 1.1.1. The Difference between Visual Basic and Visual Basic for Applications
      2. 1.2. Understanding Macro Basics
      3. 1.3. Recording a Macro
        1. 1.3.1. Planning the Macro
        2. 1.3.2. Starting the Macro Recorder
        3. 1.3.3. Naming the Macro
          1. 1.3.3.1. Invalid Macro Names
          2. 1.3.3.2. Describing Your Macros
          3. 1.3.3.3. Storing Your Macros
        4. 1.3.4. Assigning a Way to Run a Macro
          1. 1.3.4.1.
            1. 1.3.4.1.1. Running a Macro from the Quick Access Toolbar
            2. 1.3.4.1.2. Running a Macro Via a Shortcut Key Combination
          2. 1.3.4.2. Assigning a Way to Run a Macro in Excel
          3. 1.3.4.3. Assigning a Way to Run a Macro in PowerPoint
          4. 1.3.4.4. Assigning a Way to Run a Macro in Outlook
          5. 1.3.4.5. Recording the Actions in the Macro
      4. 1.4. Running a Macro
      5. 1.5. Recording a Sample Word Macro
      6. 1.6. Recording a Sample Excel Macro
        1. 1.6.1. Create a Personal Macro Workbook if You Don't Have One Yet
        2. 1.6.2. Record a Sample Excel Macro
      7. 1.7. Assigning a Way of Running the Macro
        1. 1.7.1. Assigning a Macro to a Quick Access Toolbar Button in Word
        2. 1.7.2. Assigning a Macro to a Shortcut Key Combination
      8. 1.8. Deleting a Macro
      9. 1.9. The Bottom Line
    2. 2. Getting Started with the Visual Basic Editor
      1. 2.1. Opening the Visual Basic Editor
        1. 2.1.1. Opening the Visual Basic Editor with a Macro Selected
        2. 2.1.2. Opening the Visual Basic Editor Directly
        3. 2.1.3. Navigating to a Macro
      2. 2.2. Using the Visual Basic Editor's Main Windows
        1. 2.2.1. The Project Explorer
        2. 2.2.2. The Object Browser
        3. 2.2.3. The Code Window
          1. 2.2.3.1. Complete Word
          2. 2.2.3.2. Quick Info
          3. 2.2.3.3. Auto List Members
          4. 2.2.3.4. List Constants
          5. 2.2.3.5. Data Tips
          6. 2.2.3.6. Margin Indicators
          7. 2.2.3.7. Other Editing Features
        4. 2.2.4. The Properties Window
        5. 2.2.5. The Immediate Window
      3. 2.3. Setting Properties for a Project
      4. 2.4. Customizing the Visual Basic Editor
        1. 2.4.1. Choosing Editor and View Preferences
          1. 2.4.1.1. Editor Page Options
          2. 2.4.1.2. Editor Format Page Options
          3. 2.4.1.3. General Page Options
            1. 2.4.1.3.1. Form Grid Settings Group Box
            2. 2.4.1.3.2. The Edit and Continue Group Box
            3. 2.4.1.3.3. Error Trapping Group Box
            4. 2.4.1.3.4. Compile Group Box
            5. 2.4.1.3.5. Show ToolTips and Collapse Proj. Hides Windows
          4. 2.4.1.4. Docking Page Options
        2. 2.4.2. Choosing and Laying Out the Editor Windows
        3. 2.4.3. Customizing the Toolbar and Menu Bar
        4. 2.4.4. Customizing the Toolbox
          1. 2.4.4.1. Adding Controls to the Toolbox
          2. 2.4.4.2. Renaming a Toolbox Control
          3. 2.4.4.3. Assigning a Picture to a Control's Toolbox Icon
          4. 2.4.4.4. Removing Controls from the Toolbox
          5. 2.4.4.5. Adding Pages to the Toolbox
          6. 2.4.4.6. Renaming Pages in the Toolbox
          7. 2.4.4.7. Removing Pages from the Toolbox
          8. 2.4.4.8. Importing and Exporting Toolbox Pages
          9. 2.4.4.9. Moving Pages in the Toolbox
      5. 2.5. Closing the Visual Basic Editor and Returning to the Host Application
      6. 2.6. The Bottom Line
    3. 3. Editing Recorded Macros
      1. 3.1. Testing a Macro in the Visual Basic Editor
        1. 3.1.1. Stepping through a Macro
        2. 3.1.2. Setting Breakpoints
        3. 3.1.3. Commenting Out Lines
        4. 3.1.4. Stepping Out of a Macro
      2. 3.2. Editing the Word Macro
        1. 3.2.1. Stepping Through the Transpose_Word_Right Macro
        2. 3.2.2. Running the Transpose_Word_Right Macro
        3. 3.2.3. Creating a Transpose_Word_Left Macro
        4. 3.2.4. Save Your Work
      3. 3.3. Editing the Excel Macro
        1. 3.3.1. Unhiding the Personal Macro Workbook
        2. 3.3.2. Opening the Macro for Editing
        3. 3.3.3. Editing the Macro
        4. 3.3.4. Saving Your Work
      4. 3.4. Editing a PowerPoint Macro
        1. 3.4.1. Save Your Work
      5. 3.5. The Bottom Line
    4. 4. Creating Code from Scratch in the Visual Basic Editor
      1. 4.1. Setting Up the Visual Basic Editor for Creating the Procedures
      2. 4.2. Creating a Procedure for Word
      3. 4.3. Creating a Procedure for Excel
      4. 4.4. Creating a Procedure for PowerPoint
      5. 4.5. Creating a Procedure for Access
      6. 4.6. The Bottom Line
  7. 2. Learning How to Work with VBA
    1. 5. Understanding the Essentials of VBA Syntax
      1. 5.1. Getting Ready
      2. 5.2. Procedures
        1. 5.2.1. Functions
        2. 5.2.2. Subprocedures
      3. 5.3. Statements
      4. 5.4. Keywords
      5. 5.5. Expressions
      6. 5.6. Operators
      7. 5.7. Variables
      8. 5.8. Constants
      9. 5.9. Arguments
        1. 5.9.1. Specifying Argument Names vs. Omitting Argument Names
        2. 5.9.2. When to Include the Parentheses around the Argument List
      10. 5.10. Objects
      11. 5.11. Collections
      12. 5.12. Properties
      13. 5.13. Methods
      14. 5.14. Events
      15. 5.15. The Bottom Line
    2. 6. Working with Variables, Constants, and Enumerations
      1. 6.1. Working with Variables
        1. 6.1.1. Choosing Names for Variables
        2. 6.1.2. Declaring a Variable
          1. 6.1.2.1. Declaring a Variable Implicitly
          2. 6.1.2.2. Declaring a Variable Explicitly
        3. 6.1.3. Choosing the Scope and Lifetime of a Variable
          1. 6.1.3.1. Procedure Scope
          2. 6.1.3.2. Private Scope
          3. 6.1.3.3. Public Scope
          4. 6.1.3.4. Using Static Variables
        4. 6.1.4. Specifying the Data Type for a Variable
          1. 6.1.4.1. Do You Need to Specify the Data Type?
          2. 6.1.4.2. Boolean
          3. 6.1.4.3. Byte
          4. 6.1.4.4. Currency
          5. 6.1.4.5. Date
          6. 6.1.4.6. Decimal
          7. 6.1.4.7. Double
          8. 6.1.4.8. Integer
          9. 6.1.4.9. Long
          10. 6.1.4.10. Object
          11. 6.1.4.11. Single
          12. 6.1.4.12. String
          13. 6.1.4.13. Variant
          14. 6.1.4.14. Deciding among Types for Variables
      2. 6.2. Working with Constants
        1. 6.2.1. Declaring Your Own Constants
          1. 6.2.1.1. Syntax
          2. 6.2.1.2. Example
        2. 6.2.2. Choosing the Scope and Lifetime for Your Constants
      3. 6.3. Working with Enumerations
      4. 6.4. The Bottom Line
    3. 7. Using Array Variables
      1. 7.1. What Is an Array?
      2. 7.2. Declaring an Array
      3. 7.3. Storing Values in an Array
      4. 7.4. Multidimensional Arrays
      5. 7.5. Declaring a Dynamic Array
      6. 7.6. Redimensioning an Array
      7. 7.7. Returning Information from an Array
      8. 7.8. Erasing an Array
      9. 7.9. Finding Out Whether a Variable Is an Array
      10. 7.10. Finding the Bounds of an Array
      11. 7.11. Sorting an Array
      12. 7.12. Searching through an Array
        1. 7.12.1. Performing a Linear Search through an Array
        2. 7.12.2. Performing a Binary Search Through an Array
      13. 7.13. The Bottom Line
    4. 8. Finding the Objects, Methods, and Properties You Need
      1. 8.1. What Is an Object?
        1. 8.1.1. Properties
        2. 8.1.2. Methods
      2. 8.2. Working with Collections
        1. 8.2.1. Working with an Object in a Collection
        2. 8.2.2. Adding an Object to a Collection
      3. 8.3. Finding the Objects You Need
        1. 8.3.1. Using the Macro Recorder to Record the Objects You Need
        2. 8.3.2. Using the Object Browser
          1. 8.3.2.1. Components of the Object Browser
          2. 8.3.2.2. Adding and Removing Object Libraries
          3. 8.3.2.3. Navigating with the Object Browser
        3. 8.3.3. Using Help to Find the Object You Need
        4. 8.3.4. Using the Auto List Members Feature
      4. 8.4. Using Object Variables to Represent Objects
      5. 8.5. The Bottom Line
  8. 3. Making Decisions and Using Loops and Functions
    1. 9. Using Built-in Functions
      1. 9.1. What Is a Function?
      2. 9.2. Using Functions
        1. 9.2.1. Passing Arguments to a Function
      3. 9.3. Using Functions to Convert Data from One Type to Another
        1. 9.3.1. Using the Asc Function to Return a Character Code
        2. 9.3.2. Using the Val Function to Extract a Number from the Start of a String
        3. 9.3.3. Using the Str Function to Convert a Value to a String
        4. 9.3.4. Using the Format Function to Format an Expression
          1. 9.3.4.1. Using Predefined Numeric Formats
          2. 9.3.4.2. Creating a Numeric Format
          3. 9.3.4.3. Creating a Date or Time Format
          4. 9.3.4.4. Creating a String Format
        5. 9.3.5. Using the Chr Function and Constants to Enter Special Characters in a String
      4. 9.4. Using Functions to Manipulate Strings
        1. 9.4.1. Using the Left, Right, and Mid Functions to Return Part of a String
          1. 9.4.1.1. Using the Left Function
          2. 9.4.1.2. Using the Right Function
          3. 9.4.1.3. Using the Mid Function
        2. 9.4.2. Using InStr and InStrRev to Find a String within Another String
        3. 9.4.3. Using LTrim, RTrim, and Trim to Trim Spaces from a String
        4. 9.4.4. Using Len to Check the Length of a String
        5. 9.4.5. Using StrConv, LCase, and UCase to Change the Case of a String
          1. 9.4.5.1. Using StrConv
          2. 9.4.5.2. Using LCase and UCase
        6. 9.4.6. Using the StrComp Function to Compare Apples to Apples
      5. 9.5. Using VBA's Mathematical Functions
      6. 9.6. Using VBA's Date and Time Functions
        1. 9.6.1. Using the DatePart Function to Parse Dates
        2. 9.6.2. Using the DateDiff Function to Return an Interval
        3. 9.6.3. Using the DateAdd Function to Add to a Date
      7. 9.7. Using File-Management Functions
        1. 9.7.1. Using the Dir Function to Check Whether a File Exists
        2. 9.7.2. Returning the Current Path
      8. 9.8. The Bottom Line
    2. 10. Creating Your Own Functions
      1. 10.1. Components of a Function
      2. 10.2. Creating a Function
        1. 10.2.1. Starting a Function Manually
        2. 10.2.2. Starting a Function by Using the Add Procedure Dialog Box
        3. 10.2.3. Passing Arguments to the Function
        4. 10.2.4. Declaring the Data Types of Arguments
        5. 10.2.5. Specifying an Optional Argument
        6. 10.2.6. Controlling the Scope of a Function
      3. 10.3. Examples of Functions for Any VBA-Enabled Application
      4. 10.4. Creating a Function for Word
      5. 10.5. Creating a Function for Excel
      6. 10.6. Creating a Function for PowerPoint
      7. 10.7. Creating a Function for Access
      8. 10.8. The Bottom Line
    3. 11. Making Decisions in Your Code
      1. 11.1. How Do You Compare Things in VBA?
      2. 11.2. Testing Multiple Conditions by Using Logical Operators
        1. 11.2.1. If Statements
        2. 11.2.2. If...Then
          1. 11.2.2.1. Syntax
          2. 11.2.2.2. Examples
            1. 11.2.2.2.1. One-Line If Statements
            2. 11.2.2.2.2. Block If Statements
        3. 11.2.3. If... Then... Else Statements
          1. 11.2.3.1. Syntax
          2. 11.2.3.2. Example
        4. 11.2.4. If... Then... ElseIf... Else Statements
          1. 11.2.4.1. Syntax
          2. 11.2.4.2. Examples
            1. 11.2.4.2.1. A Simple If... Then... ElseIf... Else Statement
            2. 11.2.4.2.2. An If... Then... ElseIf Statement without an Else Clause
        5. 11.2.5. Creating Loops with If and GoTo
          1. 11.2.5.1. Syntax
          2. 11.2.5.2. Example
        6. 11.2.6. Nesting If Statements
      3. 11.3. Select Case Statements
        1. 11.3.1. Syntax
        2. 11.3.2. Example
      4. 11.4. The Bottom Line
    4. 12. Using Loops to Repeat Actions
      1. 12.1. When Should You Use a Loop?
      2. 12.2. Understanding the Basics of Loops
      3. 12.3. Using For... Loops for Fixed Repetitions
        1. 12.3.1. For... Next Loops
          1. 12.3.1.1. Syntax
          2. 12.3.1.2. Straightforward For... Next Loops
          3. 12.3.1.3. For... Next Loops with Step Values
          4. 12.3.1.4. Using an Input Box to Drive a For... Next Loop
        2. 12.3.2. For Each... Next Loops
          1. 12.3.2.1. Syntax
        3. 12.3.3. Using an Exit For Statement
      4. 12.4. Using Do... Loops for Variable Numbers of Repetitions
        1. 12.4.1. Do While... Loop Loops
          1. 12.4.1.1. Syntax
        2. 12.4.2. Do... Loop While Loops
          1. 12.4.2.1. Syntax
        3. 12.4.3. Do Until... Loop Loops
          1. 12.4.3.1. Syntax
        4. 12.4.4. Do... Loop Until Loops
          1. 12.4.4.1. Syntax
        5. 12.4.5. Using an Exit Do Statement
      5. 12.5. While... Wend Loops
      6. 12.6. Nesting Loops
      7. 12.7. Avoiding Infinite Loops
      8. 12.8. The Bottom Line
  9. 4. Using Message Boxes, Input Boxes, and Dialog Boxes
    1. 13. Getting User Input with Message Boxes and Input Boxes
      1. 13.1. Opening a Procedure to Work On
      2. 13.2. Displaying Status Bar Messages in Word and Excel
      3. 13.3. Message Boxes
        1. 13.3.1. The Pros and Cons of Message Boxes
        2. 13.3.2. Message Box Syntax
        3. 13.3.3. Displaying a Simple Message Box
        4. 13.3.4. Displaying a Multiline Message Box
        5. 13.3.5. Choosing Buttons for a Message Box
        6. 13.3.6. Choosing an Icon for a Message Box
        7. 13.3.7. Setting a Default Button for a Message Box
        8. 13.3.8. Controlling the Modality of a Message Box
        9. 13.3.9. Specifying a Title for a Message Box
        10. 13.3.10. Title Bars Can Provide Useful Information
        11. 13.3.11. Adding a Help Button to a Message Box
        12. 13.3.12. Specifying a Help File for a Message Box
        13. 13.3.13. Using Some Arguments without Others
        14. 13.3.14. Retrieving a Value from a Message Box
      4. 13.4. Input Boxes
        1. 13.4.1. Input Box Syntax
        2. 13.4.2. Retrieving Input from an Input Box
      5. 13.5. Forms: When Message Boxes and Input Boxes Won't Suffice
      6. 13.6. The Bottom Line
    2. 14. Creating Simple Custom Dialog Boxes
      1. 14.1. When Should You Use a Custom Dialog Box?
      2. 14.2. Creating a Custom Dialog Box
        1. 14.2.1. Designing the Dialog Box
        2. 14.2.2. Inserting a User Form
          1. 14.2.2.1. Choosing User Form Grid Settings
        3. 14.2.3. Renaming a User Form
        4. 14.2.4. Adding Controls to the User Form
        5. 14.2.5. Renaming Controls
        6. 14.2.6. Moving a Control
        7. 14.2.7. Changing the Caption on a Control
        8. 14.2.8. Key Properties for the Toolbox Controls
          1. 14.2.8.1. Common Properties
          2. 14.2.8.2. Label
          3. 14.2.8.3. TextBox
          4. 14.2.8.4. ComboBox and ListBox
          5. 14.2.8.5. CheckBox
          6. 14.2.8.6. OptionButton
          7. 14.2.8.7. ToggleButton
          8. 14.2.8.8. Frame
          9. 14.2.8.9. CommandButton
          10. 14.2.8.10. TabStrip and MultiPage
          11. 14.2.8.11. ScrollBar and SpinButton
          12. 14.2.8.12. Image
          13. 14.2.8.13. Page
          14. 14.2.8.14. Tab
        9. 14.2.9. Working with Groups of Controls
          1. 14.2.9.1. Grouping Controls
          2. 14.2.9.2. Ungrouping Controls
          3. 14.2.9.3. Sizing Grouped Controls
          4. 14.2.9.4. Deleting Grouped Controls
          5. 14.2.9.5. Working with One Control in a Group
        10. 14.2.10. Aligning Controls
        11. 14.2.11. Placing Controls
        12. 14.2.12. Adjusting the Tab Order of the Dialog Box
      3. 14.3. Linking a Dialog Box to a Procedure
        1. 14.3.1. Loading and Unloading a Dialog Box
        2. 14.3.2. Displaying and Hiding a Dialog Box
        3. 14.3.3. Setting a Default Command Button
      4. 14.4. Retrieving the User's Choices from a Dialog Box
        1. 14.4.1. Returning a String from a Text Box
        2. 14.4.2. Returning a Value from an Option Button
        3. 14.4.3. Returning a Value from a Check Box
        4. 14.4.4. Returning a Value from a List Box
        5. 14.4.5. Returning a Value from a Combo Box
      5. 14.5. Examples of Connecting Dialog Boxes to Procedures
        1. 14.5.1. Word Example: The Move-Paragraph Procedure
          1. 14.5.1.1. Recording the Procedure
          2. 14.5.1.2. Creating the Dialog Box
        2. 14.5.2. General Example: Opening a File from a List Box
          1. 14.5.2.1. Building the User Form
        3. 14.5.3. Creating the Code for the User Form
      6. 14.6. Using an Application's Built-in Dialog Boxes from VBA
        1. 14.6.1. Displaying a Built-in Dialog Box
          1. 14.6.1.1. Finding the Dialog Box Name and Constant
          2. 14.6.1.2. Choosing between the Show Method and the Display Method
          3. 14.6.1.3. Using the Show Method to Display and Execute a Dialog Box
          4. 14.6.1.4. Using the Display Method to Display a Dialog Box
        2. 14.6.2. Setting and Restoring Options in a Built-in Dialog Box
        3. 14.6.3. Which Button Did the User Choose in a Dialog Box?
        4. 14.6.4. Specifying a Time-Out for a Dialog Box
      7. 14.7. The Bottom Line
    3. 15. Creating Complex Dialog Boxes
      1. 15.1. Creating and Working with Complex Dialog Boxes
        1. 15.1.1. Updating a Dialog Box to Reflect the User's Choices
        2. 15.1.2. Revealing a Hidden Part of a Dialog Box
        3. 15.1.3. Tracking a Procedure in a Dialog Box
        4. 15.1.4. Using Multipage Dialog Boxes and Tab Strip Controls
          1. 15.1.4.1. Multipage Dialog Boxes
          2. 15.1.4.2. Dialog Boxes That Use Tab Strips
          3. 15.1.4.3. Using a Picture in a Dialog Box
        5. 15.1.5. Creating a Modeless Dialog Box
        6. 15.1.6. Choosing a Position for the Dialog Box
      2. 15.2. Using Events to Control Forms
        1. 15.2.1. Events That Apply Only to the UserForm Object
          1. 15.2.1.1. Initialize Event
          2. 15.2.1.2. QueryClose Event
          3. 15.2.1.3. Activate Event
          4. 15.2.1.4. Deactivate Event
          5. 15.2.1.5. Resize Event
          6. 15.2.1.6. Terminate Event
        2. 15.2.2. Events That Apply to the UserForm Object and to Container Controls
          1. 15.2.2.1. Scroll Event
          2. 15.2.2.2. Zoom Event
          3. 15.2.2.3. Layout Event
          4. 15.2.2.4. AddControl Event
          5. 15.2.2.5. RemoveControl Event
        3. 15.2.3. Events That Apply to Many or Most Controls
          1. 15.2.3.1. Click Event
          2. 15.2.3.2. Change Event
          3. 15.2.3.3. Enter and Exit Events
          4. 15.2.3.4. BeforeUpdate Event
          5. 15.2.3.5. AfterUpdate Event
          6. 15.2.3.6. KeyDown and KeyUp Events
          7. 15.2.3.7. KeyPress Event
          8. 15.2.3.8. MouseDown Event and MouseUp Event
          9. 15.2.3.9. MouseMove Event
          10. 15.2.3.10. BeforeDragOver Event
          11. 15.2.3.11. BeforeDropOrPaste Event
          12. 15.2.3.12. DblClick Event
          13. 15.2.3.13. Error Event
        4. 15.2.4. Events That Apply Only to a Few Controls
          1. 15.2.4.1. DropButtonClick Event
          2. 15.2.4.2. SpinDown and SpinUp Events
      3. 15.3. The Bottom Line
  10. 5. Creating Effective Code
    1. 16. Building Modular Code and Using Classes
      1. 16.1. Creating Modular Code
        1. 16.1.1. What Is Modular Code?
        2. 16.1.2. Advantages of Using Modular Code
        3. 16.1.3. How to Approach Creating Modular Code
        4. 16.1.4. Arranging Your Code in Modules
        5. 16.1.5. Calling a Procedure
        6. 16.1.6. Making Logical Improvements to Your Code
          1. 16.1.6.1. Declaring Variables Explicitly instead of Implicitly
          2. 16.1.6.2. Using With Statements to Simplify Your Code
          3. 16.1.6.3. Don't Use With Statements Pointlessly
          4. 16.1.6.4. Optimizing Your Select Case Statements
          5. 16.1.6.5. Don't Check Things Pointlessly
          6. 16.1.6.6. Removing Unused Elements from Your Code
        7. 16.1.7. Making Visual Improvements to Your Code
          1. 16.1.7.1. Indenting the Different Levels of Code
          2. 16.1.7.2. Using Line-Continuation Characters to Break Long Lines
          3. 16.1.7.3. Using the Concatenation Character to Break Long Strings
          4. 16.1.7.4. Using Blank Lines to Break Up Your Code
          5. 16.1.7.5. Using Variables to Simplify Complex Syntax
          6. 16.1.7.6. Passing Information from One Procedure to Another Using Arguments
          7. 16.1.7.7. Passing Information from One Procedure to Another Using Private or Public Variables
      2. 16.2. Creating and Using Classes
        1. 16.2.1. What Can You Do with Class Modules?
        2. 16.2.2. A Brief Overview
        3. 16.2.3. Planning Your Class
        4. 16.2.4. Creating the Class Module
        5. 16.2.5. Naming the Class
        6. 16.2.6. Setting the Instancing Property
        7. 16.2.7. Declaring Variables and Constants for the Class
        8. 16.2.8. Adding Properties to the Class
          1. 16.2.8.1. Creating a Property by Using a Public Variable
          2. 16.2.8.2. Creating a Property by Using Property Procedures
            1. 16.2.8.2.1. Assigning a Value to a Property with a Property Let Procedure
            2. 16.2.8.2.2. Returning a Value from a Property with a Property Get Procedure
            3. 16.2.8.2.3. Assigning an Object to a Property with a Property Set Procedure
          3. 16.2.8.3. The Properties for the Book Class
        9. 16.2.9. Adding Methods to the Class
        10. 16.2.10. Using Your Class
      3. 16.3. The Bottom Line
    2. 17. Debugging Your Code and Handling Errors
      1. 17.1. Principles of Debugging
      2. 17.2. The Different Types of Errors
        1. 17.2.1. Language Errors
        2. 17.2.2. Compile Errors
        3. 17.2.3. Runtime Errors
        4. 17.2.4. Program Logic Errors
      3. 17.3. VBA's Debugging Tools
        1. 17.3.1. Break Mode
        2. 17.3.2. The Step Over and Step Out Commands
        3. 17.3.3. The Locals Window
        4. 17.3.4. The Watch Window
          1. 17.3.4.1. Setting Watch Expressions
          2. 17.3.4.2. Editing Watch Expressions
          3. 17.3.4.3. Deleting Watch Expressions
          4. 17.3.4.4. Using the Quick Watch Feature
        5. 17.3.5. The Immediate Window
          1. 17.3.5.1. What You Can't Do in the Immediate Window
          2. 17.3.5.2. Entering Code in the Immediate Window
          3. 17.3.5.3. Printing Information to the Immediate Window
        6. 17.3.6. The Call Stack Dialog Box
      4. 17.4. Dealing with Infinite Loops
      5. 17.5. Dealing with Runtime Errors
        1. 17.5.1. When Should You Write an Error Handler?
        2. 17.5.2. Trapping an Error
        3. 17.5.3. Disabling an Error Trap
        4. 17.5.4. Resuming after an Error
          1. 17.5.4.1. Using a Resume Statement
          2. 17.5.4.2. Using a Resume Next Statement
          3. 17.5.4.3. Using a Resume Line Statement
        5. 17.5.5. Getting a Description of an Error
        6. 17.5.6. Raising Your Own Errors
      6. 17.6. Suppressing Alerts
      7. 17.7. Handling User Interrupts in Word, Excel, and Project
        1. 17.7.1. Disabling User Input While a Procedure Is Running
        2. 17.7.2. Disabling User Input While Part of a Procedure Is Running
      8. 17.8. Documenting Your Code
      9. 17.9. The Bottom Line
    3. 18. Building Well-Behaved Code
      1. 18.1. What Is a Well-Behaved Procedure?
      2. 18.2. Retaining or Restoring the User Environment
      3. 18.3. Leaving the User in the Best Position to Continue Working
      4. 18.4. Keeping the User Informed during the Procedure
        1. 18.4.1. Manipulating the Cursor
          1. 18.4.1.1. Manipulating the Cursor in Word
          2. 18.4.1.2. Manipulating the Cursor in Excel
        2. 18.4.2. Displaying Information at the Beginning of a Procedure
        3. 18.4.3. Communicating with the User via a Message Box or Dialog Box at the End of a Procedure
        4. 18.4.4. Creating a Log File
      5. 18.5. Making Sure a Procedure Is Running under Suitable Conditions
      6. 18.6. Cleaning Up after a Procedure
        1. 18.6.1. Undoing Changes the Procedure Has Made
        2. 18.6.2. Removing Scratch Files and Folders
          1. 18.6.2.1. Building a Scratch Folder
          2. 18.6.2.2. Deleting the Scratch Folder
      7. 18.7. The Bottom Line
    4. 19. Securing Your Code with VBA's Security Features
      1. 19.1. Understanding How VBA Implements Security
      2. 19.2. Signing Your Macro Projects with Digital Signatures
        1. 19.2.1. What Is a Digital Certificate?
        2. 19.2.2. Getting a Digital Certificate
          1. 19.2.2.1. Creating a Digital Certificate of Your Own
          2. 19.2.2.2. Getting a Digital Certificate from Your Company
          3. 19.2.2.3. Getting a Digital Certificate from a Commercial Certification Authority
          4. 19.2.2.4. Installing a Digital Certificate
          5. 19.2.2.5. Exporting a Digital Certificate
          6. 19.2.2.6. Removing a Digital Certificate
          7. 19.2.2.7. Signing a Macro Project with a Digital Signature
          8. 19.2.2.8. Removing a Digital Signature from a Macro Project
          9. 19.2.2.9. Whose Certificate Is It, and What Does It Mean?
      3. 19.3. Choosing a Suitable Level of Security
        1. 19.3.1. Understanding the Security Threats Posed by VBA
        2. 19.3.2. Protecting against Macro Viruses
        3. 19.3.3. Specifying a Suitable Security Setting
        4. 19.3.4. New Security in Office 2010
        5. 19.3.5. Specifying Whom to Trust
          1. 19.3.5.1. Whom Does the Computer Trust Already?
          2. 19.3.5.2. Adding a Trusted Publisher
          3. 19.3.5.3. Removing a Previously Trusted Publisher
      4. 19.4. Locking Your Code
      5. 19.5. The Bottom Line
  11. 6. Programming the Office Applications
    1. 20. Understanding the Word Object Model and Key Objects
      1. 20.1. Examining the Word Object Model
      2. 20.2. Working with the Documents Collection and the Document Object
        1. 20.2.1. Creating a Document
        2. 20.2.2. Creating a Template
        3. 20.2.3. Saving a Document
          1. 20.2.3.1. Saving a File for the First Time or as a Different File
          2. 20.2.3.2. Saving a Document That Has Already Been Saved
          3. 20.2.3.3. Saving All Open Documents
          4. 20.2.3.4. Checking Whether a Document Contains Unsaved Changes
        4. 20.2.4. Opening a Document
        5. 20.2.5. Closing a Document
        6. 20.2.6. Changing a Document's Template
        7. 20.2.7. Printing a Document
        8. 20.2.8. Working with the ActiveDocument Object
      3. 20.3. Working with the Selection Object
        1. 20.3.1. Checking the Type of Selection
        2. 20.3.2. Checking the Story Type of the Selection
        3. 20.3.3. Getting Other Information about the Current Selection
        4. 20.3.4. Inserting Text at, after, or before the Selection
        5. 20.3.5. Inserting a Paragraph in a Selection
        6. 20.3.6. Applying a Style
        7. 20.3.7. Extending a Selection
        8. 20.3.8. Collapsing a Selection
      4. 20.4. Creating and Using Ranges
        1. 20.4.1. Defining a Named Range
        2. 20.4.2. Redefining a Range
        3. 20.4.3. Using the Duplicate Property to Store or Copy Formatting
      5. 20.5. Manipulating Options
        1. 20.5.1. Making Sure Hyperlinks Require Ctrl+Clicking
        2. 20.5.2. Turning Off Overtype
        3. 20.5.3. Setting a Default File Path
        4. 20.5.4. Turning Off Track Changes
      6. 20.6. The Bottom Line
    2. 21. Working with Widely Used Objects in Word
      1. 21.1. Using Find and Replace via VBA
        1. 21.1.1. Understanding the Syntax for the Execute Method
        2. 21.1.2. Putting Find and Replace to Work
      2. 21.2. Working with Headers, Footers, and Page Numbers
        1. 21.2.1. Understanding How VBA Implements Headers and Footers
        2. 21.2.2. Getting to a Header or Footer
        3. 21.2.3. Checking to See If a Header or Footer Exists
        4. 21.2.4. Linking to the Header or Footer in the Previous Section
        5. 21.2.5. Creating a Different First-Page Header
        6. 21.2.6. Creating Different Odd- and Even-Page Headers
        7. 21.2.7. Adding Page Numbers to Your Headers and Footers
          1. 21.2.7.1. Adding Page Numbers to One or More Sections of a Document
          2. 21.2.7.2. Removing Page Numbers from One or More Sections of a Document
          3. 21.2.7.3. Finding Out If a Section of a Document Has Page Numbers
          4. 21.2.7.4. Changing the Page Numbering for a Section
          5. 21.2.7.5. Suppressing the Page Number for the First Page
          6. 21.2.7.6. Formatting Page Numbers
          7. 21.2.7.7. Creating "Page X of Y" Type Page Numbers
      3. 21.3. Working with Sections, Page Setup, Windows, and Views
        1. 21.3.1. Adding a Section to a Document
        2. 21.3.2. Changing the Page Setup
        3. 21.3.3. Opening a New Window Containing an Open Document
        4. 21.3.4. Closing All Windows for a Document Except the First
        5. 21.3.5. Splitting a Window
        6. 21.3.6. Displaying the Document Map for a Window
        7. 21.3.7. Scrolling a Window
        8. 21.3.8. Arranging Windows
        9. 21.3.9. Positioning and Sizing a Window
        10. 21.3.10. Making Sure an Item Is Displayed in the Window
        11. 21.3.11. Changing a Document's View
        12. 21.3.12. Zooming the View to Display Multiple Pages
      4. 21.4. Working with Tables
        1. 21.4.1. Creating a Table
        2. 21.4.2. Selecting a Table
        3. 21.4.3. Converting Text to a Table
        4. 21.4.4. Making Sure the Selection Is within a Table
        5. 21.4.5. Finding Out Where a Selection Is within a Table
        6. 21.4.6. Sorting a Table
        7. 21.4.7. Adding a Column to a Table
        8. 21.4.8. Deleting a Column from a Table
        9. 21.4.9. Setting the Width of a Column
        10. 21.4.10. Selecting a Column
        11. 21.4.11. Adding a Row to a Table
        12. 21.4.12. Deleting a Row from a Table
        13. 21.4.13. Setting the Height of One or More Rows
        14. 21.4.14. Selecting a Row
        15. 21.4.15. Inserting a Cell
        16. 21.4.16. Returning the Text within a Cell
        17. 21.4.17. Entering Text in a Cell
        18. 21.4.18. Deleting Cells
        19. 21.4.19. Selecting a Range of Cells
        20. 21.4.20. Converting a Table or Rows to Text
      5. 21.5. The Bottom Line
    3. 22. Understanding the Excel Object Model and Key Objects
      1. 22.1. Getting an Overview of the Excel Object Model
      2. 22.2. Understanding Excel's Creatable Objects
      3. 22.3. Managing Workbooks
        1. 22.3.1. Creating a Workbook
          1. 22.3.1.1. Creating a New Blank Workbook
          2. 22.3.1.2. Creating a New Workbook Based on a Template
          3. 22.3.1.3. Creating a New Workbook Based on an Existing Workbook
          4. 22.3.1.4. Creating a Chart Workbook, a Macro Sheet, or a Worksheet
        2. 22.3.2. Saving a Workbook
          1. 22.3.2.1. Saving a Workbook for the First Time or as a Different File
          2. 22.3.2.2. Saving a Workbook That Has Already Been Saved
          3. 22.3.2.3. Saving All Open Workbooks
        3. 22.3.3. Opening a Workbook
        4. 22.3.4. Closing a Workbook
          1. 22.3.4.1. Closing All Open Workbooks
        5. 22.3.5. Sharing a Workbook
        6. 22.3.6. Protecting a Workbook
        7. 22.3.7. Working with the ActiveWorkbook Object
      4. 22.4. Working with Worksheets
        1. 22.4.1. Inserting a Worksheet
        2. 22.4.2. Deleting a Worksheet
        3. 22.4.3. Copying or Moving a Worksheet
        4. 22.4.4. Printing a Worksheet
        5. 22.4.5. Protecting a Worksheet
        6. 22.4.6. Working with the ActiveSheet Object
      5. 22.5. Working with the Active Cell or Selection
        1. 22.5.1. Working with the Active Cell
          1. 22.5.1.1. Getting and Setting the Value of the Active Cell
          2. 22.5.1.2. Moving the Active Cell to Another Address
          3. 22.5.1.3. Working with the Region around the Active Cell
        2. 22.5.2. Working with the User's Selection
      6. 22.6. Working with Ranges
        1. 22.6.1. Working with a Range of Cells
        2. 22.6.2. Creating a Named Range
        3. 22.6.3. Deleting a Named Range
        4. 22.6.4. Working with a Named Range
        5. 22.6.5. Working with the Used Range
        6. 22.6.6. Working with the Special Cells
        7. 22.6.7. Entering a Formula in a Cell
      7. 22.7. Setting Options
        1. 22.7.1. Setting Options in the Application Object
          1. 22.7.1.1. Controlling Excel's Calculation
          2. 22.7.1.2. Clearing the Recently Used Files List
          3. 22.7.1.3. Setting a Default File Location
        2. 22.7.2. Setting Options in a Workbook
          1. 22.7.2.1. Forcing Excel to Remove Personal Information from the File Properties When You Save
      8. 22.8. The Bottom Line
    4. 23. Working with Widely Used Objects in Excel
      1. 23.1. Working with Charts
        1. 23.1.1. Creating a Chart
          1. 23.1.1.1. Creating a Chart on a New Chart Sheet
          2. 23.1.1.2. Creating a Chart on an Existing Worksheet
        2. 23.1.2. Specifying the Source Data for the Chart
        3. 23.1.3. Specifying the Chart Type
        4. 23.1.4. Working with Series in the Chart
          1. 23.1.4.1. Adding a New Series
          2. 23.1.4.2. Extending an Existing Series
          3. 23.1.4.3. Creating a New Series
        5. 23.1.5. Adding a Legend to the Chart
        6. 23.1.6. Adding a Chart Title
        7. 23.1.7. Working with a Chart Axis
      2. 23.2. Working with Windows
        1. 23.2.1. Opening a New Window on a Workbook
        2. 23.2.2. Closing a Window
        3. 23.2.3. Activating a Window
        4. 23.2.4. Arranging and Resizing Windows
        5. 23.2.5. Zooming a Window and Setting Display Options
      3. 23.3. Working with Find and Replace
        1. 23.3.1. Searching with the Find Method
        2. 23.3.2. Continuing a Search with the FindNext and FindPrevious Methods
        3. 23.3.3. Replacing with the Replace Method
        4. 23.3.4. Searching for and Replacing Formatting
      4. 23.4. Adding Shapes
      5. 23.5. The Bottom Line
    5. 24. Understanding the PowerPoint Object Model and Key Objects
      1. 24.1. Getting an Overview of the PowerPoint Object Model
      2. 24.2. Understanding PowerPoint's Creatable Objects
      3. 24.3. Working with Presentations
        1. 24.3.1. Creating a New Presentation Based on the Default Template
        2. 24.3.2. Creating a New Presentation Based on a Template
        3. 24.3.3. Opening an Existing Presentation
        4. 24.3.4. Saving a Presentation
          1. 24.3.4.1. Saving a Presentation for the First Time or under a Different Name
          2. 24.3.4.2. Saving a Presentation under Its Existing Name
          3. 24.3.4.3. Saving a Copy of a Presentation
          4. 24.3.4.4. Saving All Open Presentations
        5. 24.3.5. Closing a Presentation
        6. 24.3.6. Exporting a Presentation or Some Slides to Graphics
        7. 24.3.7. Printing a Presentation
        8. 24.3.8. Applying a Template to a Presentation, to a Slide, or to a Range of Slides
        9. 24.3.9. Working with the Active Presentation
      4. 24.4. Working with Windows and Views
        1. 24.4.1. Working with the Active Window
        2. 24.4.2. Opening a New Window on a Presentation
        3. 24.4.3. Closing a Window
        4. 24.4.4. Activating a Window
        5. 24.4.5. Arranging and Resizing Windows
        6. 24.4.6. Changing the View
        7. 24.4.7. Working with Panes
      5. 24.5. Working with Slides
        1. 24.5.1. Adding a Slide to a Presentation
        2. 24.5.2. Inserting Slides from an Existing Presentation
        3. 24.5.3. Finding a Slide by Its ID Number
        4. 24.5.4. Changing the Layout of an Existing Slide
        5. 24.5.5. Deleting an Existing Slide
        6. 24.5.6. Copying and Pasting a Slide
        7. 24.5.7. Duplicating a Slide
        8. 24.5.8. Moving a Slide
        9. 24.5.9. Accessing a Slide by Name
        10. 24.5.10. Working with a Range of Slides
        11. 24.5.11. Formatting a Slide
          1. 24.5.11.1. Applying a Background to One or More Slides
          2. 24.5.11.2. Applying a Color Scheme to a Slide
        12. 24.5.12. Setting a Transition for a Slide, a Range of Slides, or a Master
      6. 24.6. Working with Masters
        1. 24.6.1. Working with the Slide Master
        2. 24.6.2. Working with the Title Master
        3. 24.6.3. Working with the Handout Master
        4. 24.6.4. Working with the Notes Master
        5. 24.6.5. Deleting a Master
      7. 24.7. The Bottom Line
    6. 25. Working with Shapes and Running Slide Shows
      1. 25.1. Working with Shapes
        1. 25.1.1. Adding Shapes to Slides
          1. 25.1.1.1. Shared Arguments for Adding Shapes
          2. 25.1.1.2. Type Argument for Adding Shapes
          3. 25.1.1.3. Arguments Specific to the AddTextEffect Method
          4. 25.1.1.4. Arguments Specific to the AddOLEObject Method
          5. 25.1.1.5. An Example of Using the AddShape Method
          6. 25.1.1.6. An Example of Using the AddTextEffect Method
          7. 25.1.1.7. An Example of Using the AddTextbox Method
        2. 25.1.2. Deleting a Shape
        3. 25.1.3. Selecting All Shapes
        4. 25.1.4. Repositioning and Resizing a Shape
        5. 25.1.5. Copying Formatting from One Shape to Another
        6. 25.1.6. Working with Text in a Shape
          1. 25.1.6.1. Finding Out Whether a Shape Has a Text Frame
          2. 25.1.6.2. Returning and Setting the Text in a Text Range
          3. 25.1.6.3. Formatting the Text in a Text Range
          4. 25.1.6.4. Formatting the Bullets for a Text Range
        7. 25.1.7. Setting an Animation for a Shape or a Range of Shapes
      2. 25.2. Working with Headers and Footers
        1. 25.2.1. Returning the Header or Footer Object You Want
        2. 25.2.2. Displaying or Hiding a Header or Footer Object
        3. 25.2.3. Setting the Text in a Header or Footer
        4. 25.2.4. Setting the Format for Date and Time Headers and Footers
      3. 25.3. Setting Up and Running a Slide Show
        1. 25.3.1. Controlling the Show Type
        2. 25.3.2. Creating a Custom Show
        3. 25.3.3. Deleting a Custom Show
        4. 25.3.4. Starting a Slide Show
        5. 25.3.5. Changing the Size and Position of the Slide Show
        6. 25.3.6. Moving from Slide to Slide
        7. 25.3.7. Pausing the Show and Using White and Black Screens
        8. 25.3.8. Starting and Stopping Custom Shows
        9. 25.3.9. Exiting the Slide Show
      4. 25.4. The Bottom Line
    7. 26. Understanding the Outlook Object Model and Key Objects
      1. 26.1. Getting an Overview of the Outlook Object Model
        1. 26.1.1. Understanding Where Outlook Stores VBA Items
        2. 26.1.2. Understanding Outlook's Creatable Objects and Main User Interface Items
      2. 26.2. Working with the Application Object
        1. 26.2.1. Working with the NameSpace Object
          1. 26.2.1.1. Accessing Default Folders within the NameSpace Object
          2. 26.2.1.2. Accessing Other Folders within the NameSpace Object
          3. 26.2.1.3. Creating a New Folder
          4. 26.2.1.4. Deleting a Folder
        2. 26.2.2. Working with Inspectors and Explorers
          1. 26.2.2.1. Opening an Inspector Window
          2. 26.2.2.2. Returning the Inspector Associated with an Item
          3. 26.2.2.3. Returning the Active Window, Inspector, or Explorer
          4. 26.2.2.4. Working with the Active Inspector
        3. 26.2.3. Creating Items
          1. 26.2.3.1. Using the CreateItem Method to Create Default Items
          2. 26.2.3.2. Using the CreateItemFromTemplate Method to Create Items Based on Templates
        4. 26.2.4. Quitting Outlook
      3. 26.3. Understanding General Methods for Working with Outlook Objects
        1. 26.3.1. Using the Display Method
        2. 26.3.2. Using the Close Method
        3. 26.3.3. Using the Delete Method
        4. 26.3.4. Using the PrintOut Method
        5. 26.3.5. Using the Save Method
        6. 26.3.6. Using the SaveAs Method
      4. 26.4. Working with Messages
        1. 26.4.1. Creating a New Message
        2. 26.4.2. Working with the Contents of a Message
        3. 26.4.3. Adding an Attachment to a Message
        4. 26.4.4. Sending a Message
      5. 26.5. Working with Calendar Items
        1. 26.5.1. Creating a New Calendar Item
        2. 26.5.2. Working with the Contents of a Calendar Item
      6. 26.6. Working with Tasks and Task Requests
        1. 26.6.1. Creating a Task
        2. 26.6.2. Working with the Contents of a Task Item
        3. 26.6.3. Assigning a Task to a Colleague
      7. 26.7. Searching for Items
      8. 26.8. The Bottom Line
    8. 27. Working with Events in Outlook
      1. 27.1. Working with Application-Level Events
        1. 27.1.1. Using the Startup Event
        2. 27.1.2. Using the Quit Event
        3. 27.1.3. Using the ItemSend Event
        4. 27.1.4. Using the NewMail and NewMailEx Events
        5. 27.1.5. Using the AdvancedSearchComplete and the AdvancedSearchStopped Events
        6. 27.1.6. Using the MAPILogonComplete Event
        7. 27.1.7. Using the Reminder Event
        8. 27.1.8. Using the OptionsPagesAdd Event
      2. 27.2. Working with Item-Level Events
        1. 27.2.1. Declaring an Object Variable and Initializing an Event
        2. 27.2.2. Understanding the Events That Apply to All Message Items
        3. 27.2.3. Understanding the Events That Apply to Explorers, Inspectors, and Views
        4. 27.2.4. Understanding the Events That Apply to Folders
        5. 27.2.5. Understanding the Events That Apply to Items and Results
        6. 27.2.6. Understanding the Events That Apply to the Outlook Bar
        7. 27.2.7. Understanding the Events That Apply to Reminders
        8. 27.2.8. Understanding the Events That Apply to Synchronization
      3. 27.3. Understanding Quick Steps
      4. 27.4. The Bottom Line
    9. 28. Understanding the Access Object Model and Key Objects
      1. 28.1. Getting Started with VBA in Access
        1. 28.1.1. Creating a Module in the VBA Editor
        2. 28.1.2. Creating a Function
        3. 28.1.3. The Revamped Macro Builder
        4. 28.1.4. Creating an Access-Style Macro to Run a Function
        5. 28.1.5. Using an AutoExec Macro to Initialize an Access Session
        6. 28.1.6. Running a Subprocedure
        7. 28.1.7. Understanding the Option Compare Database Statement
      2. 28.2. Getting an Overview of the Access Object Model
      3. 28.3. Understanding Creatable Objects in Access
      4. 28.4. Opening and Closing Databases
        1. 28.4.1. Using the CurrentDb Method to Return the Current Database
        2. 28.4.2. Opening a Different Database as the Current Database and Closing the Current Database
        3. 28.4.3. Opening Multiple Databases at Once
        4. 28.4.4. Closing a Database
        5. 28.4.5. Creating and Removing Workspaces
          1. 28.4.5.1. Creating a New Workspace
          2. 28.4.5.2. Removing a Workspace
      5. 28.5. Working with the Screen Object
      6. 28.6. Using the DoCmd Object to Run Access Commands
        1. 28.6.1. Using the OpenForm Method to Open a Form
        2. 28.6.2. Using the PrintOut Method to Print an Object
        3. 28.6.3. Using the RunMacro Method to Run a Macro
      7. 28.7. The Bottom Line
    10. 29. Manipulating the Data in an Access Database via VBA
      1. 29.1. Understanding How to Proceed
      2. 29.2. Preparing to Manage the Data in a Database
        1. 29.2.1. Adding a Reference to the Appropriate Object Library
        2. 29.2.2. Establishing a Connection to the Database
      3. 29.3. Opening a Recordset
        1. 29.3.1. Opening a Recordset Using ADO
        2. 29.3.2. Choosing How to Access the Data in an ADO Recordset
          1. 29.3.2.1. Using a Table to Access the Data in an ADO Recordset
          2. 29.3.2.2. Using an SQL SELECT Statement to Access a Subset of the Data in an ADO Recordset
        3. 29.3.3. Opening a Recordset Using DAO
          1. 29.3.3.1. Opening a DAO Recordset Using a Table
          2. 29.3.3.2. Opening a DAO Recordset Using an SQL SELECT Statement
      4. 29.4. Accessing a Particular Record in a Recordset
        1. 29.4.1. Using the MoveFirst, MoveNext, MovePrevious, and MoveLast Methods
        2. 29.4.2. Using the Move Method to Move by Multiple Records
      5. 29.5. Searching for a Record
        1. 29.5.1. Searching for a Record in an ADO Recordset
        2. 29.5.2. Searching for a Record in a DAO Recordset
      6. 29.6. Returning the Fields in a Record
      7. 29.7. Editing a Record
      8. 29.8. Inserting and Deleting Records
      9. 29.9. Closing a Recordset
      10. 29.10. The Bottom Line
    11. 30. Accessing One Application from Another Application
      1. 30.1. Understanding the Tools Used to Communicate Between Applications
      2. 30.2. Using Automation to Transfer Information
        1. 30.2.1. Understanding Early and Late Binding
        2. 30.2.2. Creating an Object with the CreateObject Function
        3. 30.2.3. Returning an Object with the GetObject Function
        4. 30.2.4. Examples of Using Automation with the Office Applications
          1. 30.2.4.1. Transferring Information from an Excel Spreadsheet to a Word Document
          2. 30.2.4.2. Transferring Information from Word Documents to an Excel Workbook
          3. 30.2.4.3. Placing a PowerPoint Slide in an Outlook Message
      3. 30.3. Using the Shell Function to Run an Application
        1. 30.3.1. Returning the Task ID of the Started Application
        2. 30.3.2. Activating an Application
      4. 30.4. Using Data Objects to Store and Retrieve Information
        1. 30.4.1. Creating a Data Object
        2. 30.4.2. Storing Information in a Data Object
        3. 30.4.3. Returning Information from a Data Object
        4. 30.4.4. Assigning Information to the Clipboard
        5. 30.4.5. Returning Information from the Clipboard to a Data Object
        6. 30.4.6. Finding Out Whether a Data Object Contains a Given Format
      5. 30.5. Communicating via DDE
        1. 30.5.1. Using DDEInitiate to Start a DDE Connection
        2. 30.5.2. Using DDERequest to Return Text from Another Application
        3. 30.5.3. Using DDEPoke to Send Text to Another Application
        4. 30.5.4. Using DDEExecute to Execute a Command in Another Application
        5. 30.5.5. Using DDETerminate to Close a DDE Channel
        6. 30.5.6. Using DDETerminateAll to Close All Open DDE Channels
      6. 30.6. Communicating via SendKeys
      7. 30.7. The Bottom Line
    12. 31. Programming the Office 2010 Ribbon
      1. 31.1. Hiding the Editing Group on the Word Ribbon
        1. 31.1.1. A Word of Warning
        2. 31.1.2. XML Terminology
        3. 31.1.3. Using Built-in Icons
      2. 31.2. Working with Excel and PowerPoint
      3. 31.3. Undoing Ribbon Modifications
      4. 31.4. Selecting the Scope of Your Ribbon Customization
      5. 31.5. Adding a New Group
        1. 31.5.1. Cautions about Customizing
        2. 31.5.2. Two Ways to Find the Correct idMso
      6. 31.6. Adding Callbacks
      7. 31.7. Adding Attributes
        1. 31.7.1. Using Built-in Icons and ScreenTips
        2. 31.7.2. Creating Your Own Icons
      8. 31.8. Using Menus and Lists
        1. 31.8.1. Adding Menus
        2. 31.8.2. Adding a DropDown List Control
        3. 31.8.3. Using a DialogBoxLauncher
      9. 31.9. Toggling with a Toggle Button Control
      10. 31.10. Modifying the Ribbon in Access
        1. 31.10.1. Testing Your New Ribbon
      11. 31.11. Adding a Callback in Access
      12. 31.12. What to Look For If Things Go Wrong
        1. 31.12.1. Employ Error Message Tools
        2. 31.12.2. Cure Common User Interface Programming Problems
          1. 31.12.2.1. Callback Problems: Bad Reference, Missing VBA Procedure, or Security Issues
          2. 31.12.2.2. Other Callback Problems
          3. 31.12.2.3. XML Mistakes
      13. 31.13. Where to Go from Here
      14. 31.14. The Bottom Line
  12. A. The Bottom Line
    1. A.1. Chapter 1: Recording and Running Macros in the Office Applications
    2. A.2. Chapter 2: Getting Started with the Visual Basic Editor
    3. A.3. Chapter 3: Editing Recorded Macros
    4. A.4. Chapter 4: Creating Code from Scratch in the Visual Basic Editor
    5. A.5. Chapter 5: Understanding the Essentials of VBA Syntax
    6. A.6. Chapter 6: Working with Variables, Constants, and Enumerations
    7. A.7. Chapter 7: Using Array Variables
    8. A.8. Chapter 8: Finding the Objects, Methods, and Properties You Need
    9. A.9. Chapter 9: Using Built-in Functions
    10. A.10. Chapter 10: Creating Your Own Functions
    11. A.11. Chapter 11: Making Decisions in Your Code
    12. A.12. Chapter 12: Using Loops to Repeat Actions
    13. A.13. Chapter 13: Getting User Input with Message Boxes and Input Boxes
    14. A.14. Chapter 14: Creating Simple Custom Dialog Boxes
    15. A.15. Chapter 15: Creating Complex Dialog Boxes
    16. A.16. Chapter 16: Building Modular Code and Using Classes
    17. A.17. Chapter 17: Debugging Your Code and Handling Errors
    18. A.18. Chapter 18: Building Well-Behaved Code
    19. A.19. Chapter 19: Securing Your Code with VBA's Security Features
    20. A.20. Chapter 20: Understanding the Word Object Model and Key Objects
    21. A.21. Chapter 21: Working with Widely Used Objects in Word
    22. A.22. Chapter 22: Understanding the Excel Object Model and Key Objects
    23. A.23. Chapter 23: Working with Widely Used Objects in Excel
    24. A.24. Chapter 24: Understanding the PowerPoint Object Model and Key Objects
    25. A.25. Chapter 25: Working with Shapes and Running Slide Shows
    26. A.26. Chapter 26: Understanding the Outlook Object Model and Key Objects
    27. A.27. Chapter 27: Working with Events in Outlook
    28. A.28. Chapter 28: Understanding the Access Object Model and Key Objects
    29. A.29. Chapter 29: Manipulating the Data in an Access Database via VBA
    30. A.30. Chapter 30: Accessing One Application from Another Application
    31. A.31. Chapter 31: Programming the Office 2010 Ribbon

Product information

  • Title: Mastering VBA for Microsoft® Office 2010
  • Author(s): Richard Mansfield
  • Release date: August 2010
  • Publisher(s): Sybex
  • ISBN: 9780470634004