C H A P T E R    2 Microsoft Office 97/Visual Basic Programmer's Guide

Understanding Object Models

Contents

Objects are the fundamental building blocks of the Microsoft Office 97 applications; nearly everything you do in Visual Basic involves manipulating objects. Every unit of content and functionality in Office — each workbook, worksheet, document, range of text, slide, and so on — is an object that you can control programmatically in Visual Basic. When you understand how to work with objects, you're ready to automate tasks in Office.

This chapter gives you a conceptual overview of objects and object models and the tools and techniques you use to explore and use them. For more information about using the object model for a particular application, see the chapter in this book that's devoted to working with that object model.

Overview of Object Models

Before you can programmatically gain access to an application's content and functionality, it's important to understand how the content and functionality of the application is partitioned into discrete objects and how these objects are arranged in a hierarchical model.

What Are Objects and Object Models?

An application consists of two things: content and functionality. Content refers to the documents the application contains and the words, numbers, or graphics included in the documents; it also refers to information about attributes of individual elements in the application, such as the size of a window, the color of a graphic, or the font size of a word. Functionality refers to all the ways you can work with the content in the application — for example, opening, closing, adding, deleting, copying, pasting, editing, or formatting elements in the application.

The content and functionality in an application are broken down into discrete units of related content and functionality called objects. You're already familiar with some of these objects, as elements of the user interface: Microsoft Excel workbooks, work-sheets, and cell ranges; Word documents and sections; and PowerPoint presentations and slides.

The top­level object in an application is usually the Application object, which is the application itself. For instance, Microsoft Excel itself is the Application object in the Microsoft Excel object model. The Application object contains other objects that you have access to only when the Application object exists (that is, when the application is running). For example, the Microsoft Excel Application object contains Workbook objects, and the Word Application object contains Document objects. Because the Document object depends on the existence of the Word Application object for its own existence, the Document object is said to be the child of the Application object; conversely, the Application object is said to be the parent of the Document object.

Many objects that are children have children of their own. For example, the Microsoft Excel Workbook object contains, or is parent to, the collection of Worksheet objects that represent all the worksheets in the workbook. A parent object can have multiple children; for instance, the Word Window object has as children the Panes, Selection, and View objects. Likewise, a child object can have multiple parents; for instance, the Word Windows collection object is the child of both the Application object and the Document object.

The way the objects that make up an application are arranged relative to each other, together with the way the content and functionality are divided among the objects, is called the object hierarchy or the object model. To see a graphical representation of the object model for a particular application, see "Microsoft Access Objects," "Microsoft Excel Objects," "Microsoft Word Objects," or "Microsoft PowerPoint Objects" in Visual Basic Help for that application. For information about using Help and the Object Browser to explore an object model, see "Getting Help Writing Code" later in this chapter.

Note   If you clicked Typical when you installed Microsoft Office, you'll need to run Setup again to install Visual Basic Help for the application you want to program in.

In addition to containing lower­level objects, each object in the hierarchy contains content and functionality that apply both to the object itself and to all objects below it in the hierarchy. The higher an object is in the hierarchy, the wider the scope of its content and functionality. For example, in Microsoft Excel, the Application object contains the size of the application window and the ability to quit the application; the Workbook object contains the file name and format of the workbook and the ability to save the workbook; and the Worksheet object contains the worksheet name and the ability to delete the worksheet.

You often don't get to what you think of as the contents of a file (such as the values on a Microsoft Excel worksheet or the text in a Word document) until you've navigated through quite a few levels in the object hierarchy, because this specific information belongs to a very specific part of the application. In other words, the value in a cell on a worksheet applies only to that cell, not to all cells on the worksheet, so you cannot store it directly in the Worksheet object. The content and functionality stored in an object are thus intrinsically appropriate to the scope of the object.

In summary, the content and functionality in an application are divided among the objects in the application's object model. Together, the objects in the hierarchy contain all the content and functionality in the application. Separately, the objects provide access to very specific areas of content and functionality.

What Are Properties and Methods?

To get to the content and functionality contained in an object, you use properties and methods of that object. The following Microsoft Excel example uses the Value property of the Range object to set the contents of cell B3 on the worksheet named "Sales" in the workbook named "Current.xls." 

Workbooks("Current.xls").Worksheets("Sales").Range("B3").Value = 3

The following example uses the Bold property of the Font object to apply bold formatting to cell B3 on the Sales worksheet. 

Workbooks("Current.xls").Worksheets("Sales").Range("B3").Font.Bold = True

The following Word example uses the Close method of the Document object to close the file named "Draft 3.doc." 

Documents("Draft 3.doc").Close

In general, you use properties to get to content, which can include the text contained in an object or the attribute settings for the object; and you use methods to get to functionality, which entails everything you can do to the content. Be aware, however, that this distinction doesn't always hold true; there are a number of properties and methods in every object model that constitute exceptions to this rule.

How Is the Object Model Related to the User Interface?

There are two ways to interact with an application's objects: manually (using the user interface) or programmatically (using a programming language). In the user interface, you use the keyboard or the mouse, or both, to navigate to the part of the application that controls the data you want to change or the commands you want to use. For example, in Microsoft Excel, to enter a value into cell B3 on the worksheet named "Sales" in the workbook named "Current.xls," you open the Current.xls workbook, you click the tab for the Sales worksheet, you click in cell B3, and then you type a value.

In Visual Basic statements, you navigate through the object model from the top­level object to the object that contains the content and functionality you want to work with, and you use properties and methods of that object to get to the content and functionality. For example, the following Microsoft Excel example navigates to cell B3 on the Sales worksheet in the Current.xls workbook and sets the contents of the cell. 

Workbooks("Current.xls").Worksheets("Sales").Range("B3").Value = 3

Because the user interface and Visual Basic are two ways of gaining access to the exact same content and functionality, many objects, properties, and methods share names with elements in the user interface, and the overall structure of the object model resembles the structure of the user interface. This also means that for every action you can take in the user interface, there's a Visual Basic code equivalent. For information about using the macro recorder to translate user interface actions into their Visual Basic code equivalents, see "Using the Macro Recorder" later in this chapter.

Why Does It Matter Where an Object Is in the Object Model?

It's important to understand an object's place in the object model, because before you can work with an object, you have to navigate through the object model to get to it. This usually means that you have to step down through all the objects above it in the object hierarchy to get to it. For example, in Microsoft Excel, you cannot get to a particular cell on a worksheet without first going through the application , which contains the workbook that contains the worksheet that contains the cell. The following example inserts the value 3 in cell B3 on the worksheet named "Second Quarter" in the workbook named "Annual Sales.xls." 

Application.Workbooks("Annual Sales.xls").WorkSheets("Second Quarter").Range("B3").Value = 3

Similarly, the following Word example applies bold formatting to the second word in the third paragraph in the first open document. 

Application.Documents(1).Paragraphs(3).Range.Words(2).Bold = True

What Are Collection Objects?

When using Visual Basic Help graphics to explore the object model for the application in which you want to program, you may notice that there are many boxes in the graphics that contain two words — usually the singular and plural forms of the same object name, such as "Documents (Document)" or "Workbooks (Workbook)." In these cases, the first name (usually the plural form) is the name of a collection object. A collection object is an object that contains a set of related objects. You can work with the objects in a collection as a single group rather than as separate entities. The second name (usually the singular form), enclosed in parentheses, is the name of an individual object in the collection. For example, in Word, you can use the Documents collection to work with all the Document objects as a group.

Although the Documents collection object and the Document object are both objects in their own right, each with its own properties and methods, they're grouped as one unit in most object model graphics to reduce complexity. You can use a collection object to get to an individual object in that collection, usually with the Item method or property. The following PowerPoint example uses the Item property of the Presentations collection object to activate the presentation named "Trade Show" and then close it. All other open presentations are left open. 

Presentations.Item("Trade Show").Close

Note   The Item property or method is the default method for most collections. Therefore, Presentations("Trade Show").Close is equivalent to the preceding example.

You can also create new objects and add them to a collection, usually by using the Add method of that collection. The following Word example creates a new document based on the Normal template. 

Documents.Add

You can find out how many objects there are in the collection by using the Count property. The following Microsoft Excel example displays the number of open workbooks in a message box if more than three workbooks are open. 

If Workbooks.Count > 3 Then MsgBox "More than 3 workbooks are open"

Collections are useful in other ways as well. For instance, you can perform an operation on all the objects in a given collection, or you can set or test a value for all the objects in the collection. To do this, you use a For Each…Next or For…Next structure to loop through all the objects in the collection. For more information about looping through a collection, see Chapter 1, "Programming Basics."

Automating a Task by Using Objects

To automate a task in Microsoft Office, you first return a reference to the object that contains the content and functionality you want to get to, and then you apply properties and methods to that object. If you don't know which properties and methods you need to apply to what object to accomplish the task, or how to navigate through the object model to get to that object, see "Getting Help Writing Code" later in this chapter.

Returning a Reference to an Object

Before you can do anything with an object, you must return a reference to the object. To do this, you must build an expression that gains access to one object in the object model and then uses properties or methods to move up or down through the object hierarchy until you get to the object you want to work with. The properties and methods you use to return the object you start from and to move from one object to another are called object accessors, or just accessors. As you build an expression with accessors to return a reference to an object, keep the following guidelines in mind.

  • A common place to gain access to the object model is the top­level object, which is usually the Application object. Use the Application property to return a reference to the Application object. The following expression returns a reference to the Application object (for any object library that contains an Application object). 
    Application

  • To drill down to an object from the top­level object in a hierarchy, you must step down through all the objects above it in the hierarchy, using accessors to return one object from another. For example, the Documents property of the Word Application object returns the Documents collection object, which represents all open documents. The following expression returns a reference to the Word Documents collection object. 
    Application.Documents

  • There are shortcut accessors you can use to gain direct access to objects in the model without having to drill down from the Application object. These shortcuts include accessors — such as the Documents, Workbooks, and Presentations properties — that you can use by themselves to return a reference to the document collection for a particular application. For example, in Word, you can use either of the following statements to open MyDoc.doc 
    Application.Documents.Open FileName:="C:\DOCS\MYDOC.DOC"
Documents.Open FileName:="C:\DOCS\MYDOC.DOC"

    There are other shortcut accessors — such as the ActiveWindow, ActiveDocument, ActiveWorksheet, or ActiveCell properties — that return a direct reference to an active part of an application. The following statement closes the active Word document. Notice that the Application object and the Documents collection object are never mentioned. 

    ActiveDocument.Close

    Tip   You can use any accessor that appears in the Members of pane of the Object Browser when <globals> is selected in the Classes pane as a shortcut; that is, you don't have to return the object that the property or method applies to before you use the property or method, because Visual Basic can determine from the context in which your code runs which object a global property or method applies to. For more information about the Object Browser, see "Getting Help Writing Code" later in this chapter. 

    Workbooks

  • To return a single member of a collection, you usually use the Item property or method with the name or index number of the member. For example, in Microsoft Excel, the following expression returns a reference to an open workbook named "Sales." 
    Workbooks.Item("Sales")

    The Item property or method is the default method for most collections. Therefore, the following two expressions are equivalent. 

    Workbooks.Item("Sales")
Workbooks.("Sales")

  • To navigate from an object higher up in the object hierarchy, you can often use the Parent property of the object. Note that the Parent property doesn't always return the immediate parent of an object — it may return the object's "grandparent," especially if the object is a member of a collection. That is, the Parent property of an object in a collection may return the collection's parent instead of the collection itself. For example, the Parent property of a Word Document object returns the Application object, not the Documents collection. Use the TypeName function to find out what kind of object the Parent property of an object returns a reference to. For example, in Microsoft Excel, the following statement displays the type of object that the Parent property of the Worksheet object refers to. 
    MsgBox TypeName(Workbooks(1).Worksheets(1).Parent)

    Tip   You can use the TypeName function to determine the type of object returned by any expression, not just expressions containing the Parent property. 

    ActivePresentation.Slides(1).Shapes(3).Object.Application

Applying Properties and Methods to an Object

After you've returned a reference to the object you want to work with it, you can apply properties and methods to the object to set an attribute for it or perform an action on it. You use the "dot" operator (.) to separate the expression that returns a reference to an object from the property or method you apply to the object. The following example, which can be run from Microsoft Excel, Word, or PowerPoint, sets the left position of the active window by using the Left property of the Window object that the ActiveWindow property returns a reference to. 

ActiveWindow.Left = 200

The following Word example closes the active document by using the Close method of the Document object that the ActiveDocument property returns a reference to. 

ActiveDocument.Close

Properties and methods can take arguments that qualify how they perform. In the following Word example, the PrintOut method of the Document object that the ActiveDocument property returns a reference to takes arguments that specify the range of pages it should print. 

ActiveDocument.PrintOut From:="3", To:="7"

You may have to navigate through several layers in an object model to get to what you consider the real data in the application, such as the values in cells on a Microsoft Excel worksheet or the text in a Word document. The following Word example uses the following properties and methods to navigate from the top of the object model to the text of a document:

  • The Application property returns a reference to the Application object.

  • The Documents property of the Application object returns a reference to the Documents collection.

  • The Item method of the Documents collection returns a reference to a single Document object.

  • The Words property of the Document object returns a reference to the Words collection.

  • The Item method of the Words collection returns a reference to a single Range object.

  • The Text property of the Range object sets the text for the first word of the document. 
    Application.Documents.Item(1).Words.Item(1).Text = "The "

Because the Documents property is a global property, it can be used without the Application qualifier, and because Item is the default property or method for collection objects, you don't need to explicitly mention it in your code. You can therefore shorten the preceding statement to the statement shown in the following example. This example implicitly drills down through the same levels as the previous example does explicitly. 

Documents(1).Words(1).Text = "The "

Similarly, the following Microsoft Excel example drills all the way down to the Range object that represents cell B3 on the worksheet named "New" in the workbook named "Sales.xls." 

Workbooks("Sales.xls").Worksheets("New").Range("B3").Value = 7

Getting Help Writing Code

Sometimes you can guess what object you need to return a reference to, how to build the expression to return it, and what property or method you need to apply to it to accomplish a task. For instance, if you want to close the active Word document, you might guess that the functionality of closing a document would be controlled by a Close method that applied to the Document object that was returned by the ActiveDocument property — and you'd be right. Most of the time, however, figuring out which object, property, and method you want to use isn't that simple. Fortunately, the Office applications include a host of tools that help you write the code to perform your tasks.

Using the Macro Recorder

If you don't know which properties and methods you need to use to accomplish a task but you know how to perform the task (or something very similar to it) with the user interface, you can use the macro recorder to translate that series of user­interface actions into a series of Visual Basic instructions. For example, if you don't know which property or method to use to indent a paragraph in Word, record the actions you take to indent a paragraph.

To record user­interface actions in Microsoft Excel, Word, or PowerPoint

  1. On the Tools menu, point to Macro, and then click Record New Macro.

  2. Change the default macro name and location if you want, and then click OK to start the macro recorder.

  3. In the user interface, perform the tasks you want to accomplish.

  4. When you finish your tasks, click the Stop Recording button on the Stop Recording toolbar.

  5. On the Tools menu, point to Macro, and then click Macros.

  6. Select the macro name from step 2, and then click Edit.

Examine the Visual Basic code, and try to correlate specific properties and methods to specific actions you took in the user interface.

Although this code can give you a good idea of what properties and methods to get more information about, you probably won't want to use the code without editing it, because the code the macro recorder generates is usually not very efficient or robust. For example, recorded code generally starts with an object that's selected or activated when you begin recording and navigates through the rest of the object model from that object, as shown in the following Word example. 

Selection.ParagraphFormat.LeftIndent = InchesToPoints(0.5)

The following is another example of selection­based code in PowerPoint: 

ActiveWindow.Selection.ShapeRange.Delete

The problem with code like that in the preceding examples, besides being inefficient, is that it relies on a particular element being selected or activated when you run the code for it to work properly. Your code will be much more robust and flexible if it contains expressions to navigate through the object model that don't begin with the selected or activated object. For example, in Word, if instead of applying the ParagraphFormat property to the Selection object that's returned by the Selection property, you apply the Format property to the Paragraph object that represents a specific paragraph (as shown in the following example), your code will run correctly no matter what's selected when you run it. 

Documents("Test Document.doc").Paragraphs(1).Format.LeftIndent = InchesToPoints(0.5)

For ideas on how to improve your recorded code, position the insertion point within a property or method in your code, and then press F1 to see a Help topic with example code for that property or method. For more information about using Visual Basic Help to write code, see the following section. For more information about editing recorded code to make it more efficient, see Chapter 13, "Optimizing for Size and Speed."

Help Files and Graphics

Visual Basic Help for any given Office application contains a topic on each object, property, method, and event in the object model. To see a graphical depiction of an application's entire object model, see "Microsoft Access Objects," "Microsoft Excel Objects," "Microsoft Word Objects," or "Microsoft PowerPoint Objects" in Visual Basic Help for that application.

How Do I Display Visual Basic Help for Microsoft Excel, Word, and PowerPoint?

To use Visual Basic Help for Microsoft Excel, Word, or PowerPoint, you must click Custom during Setup and select the Online Help for Visual Basic check box for that application. Otherwise, Visual Basic Help won't be installed. If you've already installed your application, you can run Setup again to install Visual Basic Help.

To see the contents and index of Visual Basic Help for Microsoft Excel, Word, or PowerPoint, click Contents and Index on the Help menu in the Visual Basic Editor. On the Contents tab in the Help Topics dialog box, double­click the book title that includes the name of the application you're working in (for example, "Microsoft Word Visual Basic Reference"), and then double­click the shortcut in that book (for example, "Shortcut to Microsoft Word Visual Basic Reference"). The Help Topics dialog box should reappear, displaying the contents and index for Visual Basic Help for your application.

If you cannot tell by looking at an object's name what content and functionality the object encompasses, you can click that object in the graphic to open its Help topic and learn more about it. The Help topic for an individual object contains the following information:

  • A graphic at the top of the topic that shows significant objects immediately above and below the object in the hierarchy (object model). You can click any object in the graphic to read more about it.

  • An explanation of the content and functionality that the object encompasses.

  • Instructions and examples that explain how to navigate through the object model to get to the object and how to then apply properties and methods to it. Note that you can copy code from Help topics to use in your own code.

  • Jumps at the top of the topic that display lists of the properties and methods that apply to the object. You can click the name of a property or method to open its Help topic.

The Help topic for an individual property or method contains both a description of the content or functionality that the property or method gives you access to and a jump to an example that uses the property or method. You can copy code from Help topics to the Clipboard and then paste this code into your own module.

Object Browser

Each Office application provides a file called an object library, or type library, that contains information about the objects, properties, methods, events, and built­in constants that the application exposes. You can use a tool called the Object Browser to look at the information in this file and to browse the object model it describes.

To open the Object Browser from the Visual Basic Editor (Microsoft Excel, Word, and PowerPoint) or from a module (Microsoft Access), click Object Browser on the View menu. In the Project/Library box, click the name of the object library whose objects you want to see, or click <All Libraries> to view a master list of all the objects in all the referenced object libraries. If the object library whose objects you want to view doesn't appear in the Project/Library box, you must create a reference to that object library by using the References dialog box (Tools menu).

The Classes box in the Object Browser displays the names of all the objects and enumerated types in all the referenced object libraries.

Note   A class is a type, or description, of object. An object is an actual instance of a class. For example, the Workbook class contains all the information you need to create a workbook. A Workbook object only comes into existence when you use the information in the Workbook class to create an actual workbook (an instance of the Workbook class). Despite this technical distinction, these terms are often used interchangeably. The term "object" is used generically for both "class" and "object" in this chapter.

When you click the name of an object in the Classes box in the Object Browser, you see all the properties, methods, and events associated with that object in the Members of box.

Tip   An event is an action recognized by an object, such as clicking the mouse or pressing a key. You can write code to respond to such actions. For general information about events, see Chapter 1, "Programming Basics." For information about events for a specific application, see the chapter on that application's object model, or see the topic for a specific event in Help.

Click a property or method in the Members of box. You can press F1 to see the Help topic for the selected keyword, or you can look in the Details pane at the bottom of the Object Browser window to see the following: syntax information, a property's read­only or read/write status, the object library that the object belongs to, and the type of data or object that the property or method returns. If a word in the Details pane is a jump, you can click it to get more information. This is useful if you want to figure out how to drill down to an object. For example, in Word, if you click the Application object in the Classes box and then click the ActiveDocument property in the Members of box, you see the following phrase in the Details pane:

Property ActiveDocument As Document

This tells you that the ActiveDocument property returns a reference to a Document object. If you click the return type (the object type or data type after the keyword As), which in this case is Document, the Object Browser will display the properties and methods of the Document object.

The Details pane can also be helpful if you cannot remember the exact syntax — the names and order of arguments that a given property or method takes, and which arguments are required or optional. For instance, in Word, if you click the ComputeStatistics method of the Document object that you've just navigated to, you'll see the following phrase in the Details pane:

Function ComputeStatistics(Statistic As WdStatistic, [IncludeFootnotesAndEndnotes]) As Long

This tells you that you can apply the ComputeStatistics method to the Document object and get back a value of type Long, but that you have to supply some additional information in the form of arguments for the method to work. Because the argument Statistic isn't in brackets, it's a required argument — that is, you must supply a value for it for the method to work. IncludeFootnotesAndEndnotes, which is in brackets, is an optional argument. If you don't supply a value for it, Visual Basic will use the default value.

If you're already familiar with the ComputeStatistics method, the information in the Details pane alone may jog your memory enough that you can use this method in code such as the following example. 

MsgBox ActiveDocument.ComputeStatistics(Statistic:=wdStatisticWords, _
    IncludeFootnotesAndEndnotes:=True) & " words"

You can copy text from the Details pane and then either paste it into a module or just drag it and drop it into a module to save yourself some typing. If you cannot remember what the possible values for the Statistic argument are, click WdStatistic to see a list of valid constants. If you still don't have enough information to use the ComputeStatistics method in code, click F1 to get Help.

Note that if you have references to object libraries that contain objects of the same name and you have <All Libraries> selected in the Project/Library box in the Object Browser, you'll see duplicate names in the Object Browser. For example, if you have a reference to the Microsoft Excel and Word object libraries, you'll see duplicates of the AddIn object, the AddIns object, the Adjustments object, the Application object, and so on. You can tell these duplicate objects apart by clicking one of them and looking in the Details pane. The Details pane shows you which object library the selected keyword is a member of.

For more information about the Object Browser, see "Object Browser" in Help.

Statement­Building Tools

There are a number of tools built in to the development environment that help you build expressions and statements in Visual Basic. To turn these tools on or off in the Visual Basic Editor (Microsoft Excel, Word, or PowerPoint), select one or more of the following check boxes under Code Settings on the Editor tab in the Options dialog box (Tools menu). In Microsoft Access, select one or more of the following check boxes under Coding Options on the Module tab in the Options dialog box (Tools menu).

OptionEffect
Auto Syntax Check Determines whether Visual Basic should automatically verify correct syntax after you enter a line of code.
Require Variable Declaration Determines whether explicit variable declarations are required in modules. Selecting this check box adds the Option Explicit statement to general declarations in any new module.
Auto List Member Displays a list that contains information that would logically complete the statement at the current location of the insertion point.
Auto Quick Info Displays information about functions and their parameters as you type.
Auto Data Tips Displays the value of the variable that the pointer is positioned over. Available only in break mode.
Auto Indent Repeats the indent of the preceding line when you press ENTER. That is, all subsequent lines will start at that indent. You can press BACKSPACE to remove automatic indents.
Tab Width Sets the tab width, which can range from 1 to 32 spaces (the default is 4 spaces).

These tools automatically display information and give you appropriate options to choose from at each stage of building your expression or statement. For example, with the Auto List Member option selected, type the keyword Application followed by the dot operator. You should see a box that lists the properties and methods that apply to the Application object in the first object library you have referenced. (If you have several object libraries referenced, you may want to qualify your statements with the library name to make sure you are returning a reference to the right object. For instance, you may want to use Excel.Application or Word.Application instead of just Application). You can select an item from the list and continue typing.

You can get also get help building expressions at any time by clicking List Properties/Methods, List Constants, Quick Info, Parameter Info, or Complete Word on the shortcut menu in a module. For more information about these commands in Microsoft Excel, Word, and PowerPoint, search for the command names in Visual Basic Help.

Early Binding and the Statement­Building Tools

When you create an object variable in one application that refers to an object supplied by another application, Visual Basic must verify that the object exists and that any properties or methods used with the object are specified correctly. This verification process is known as binding. Binding can occur at run time (late binding) or at compile time (early binding). Late­bound code is slower than early­bound code. In addition, many of the coding aids that are built into the development environment work only on early­bound code.

To make your code early bound

  1. Set a reference to the type library that contains the objects you want to refer; do this in the References dialog box (Tools menu).

  2. Declare your object variables as specific types. For example, if an object variable is going to contain a reference to a Document object, declare the variable as follows. 
    Dim wdObject As Document

    Don't declare the variable as the generic Object type, as shown in the following declaration. 

    Dim wdObject As Object

  3. If you'll be writing code that uses objects from more than one library, specify the name of the application when declaring object variables, especially if the different libraries contain objects with the same name, as shown in the following two declarations. 
    Dim wndXL As Excel.Window
Dim wndWD As Word.Window

If a property or method that you use in your code to return a reference to an object has the generic return type Object instead of a specific object type, you must take additional steps to ensure that your code is early bound and that the statement­building tools will work.

For example, in Microsoft Excel, the Item method of the Worksheets object returns the type Object, instead of Worksheet, so you won't get any more help from the statement­building tools after you reach the following point in your statement. 

Workbooks(1).Worksheets(1).

Because the returned object type is Object, which is the generic type for all objects, the statement­building tools don't know what the available properties and methods are. To get around this, you must explicitly declare an object variable that has the specific type Worksheet, and you must set that object variable to the expression that returns a reference to the Worksheet object, as shown in the following example. 

Dim testWS As Worksheet
Set testWs = Workbooks(1).Worksheets(1)

From this point on, when you type the name of the object variable followed by a period, the List Properties/Methods command will suggest properties and methods for the Workbook object that the variable refers to.

Programming Another Application's Objects

You can run code in one Microsoft Office application that works with the objects in another application.

To program another application's objects

  1. Set a reference to the other application's type library in the References dialog box (Tools menu). After you've done this, the objects, properties, and methods will show up in the Object Browser and the syntax will be checked at compile time. You can also get context­sensitive Help on them.

  2. Declare object variables that will refer to the objects in the other application as specific types. Make sure that you qualify each type with by the name of the application exposes the object. The following example declares a variable that will point to a Word document and another variable that refers to a Microsoft Excel workbook. 
    Dim appWD As Word.Application, wbXL As Excel.Workbook

  3. Use the CreateObject function with the OLE programmatic identifier of the object you want to work with in the other application, as shown in the following example. If you want to see the session of the other application, set the Visible property to True. 
    Dim appWD As Word.Application



    Set appWD = CreateObject("Word.Application.8")
appWd.Visible = True

    For specific information about the programmatic identifiers exposed by each Office application, see "OLE Programmatic Identifiers" in Help.

  4. Apply properties and methods to the object contained in the variable. The following example creates a new Word document. 
    Dim appWD As Word.Application

Set appWD = CreateObject("Word.Application.8")
appWD.Documents.Add

  5. When you finish working with the other application, use the Quit method to close it, as shown in the following example. 
    appWd.Quit