| Microsoft Office 2000/Visual Basic Programmer's Guide | |
Before you can do anything with an object, you must return a reference to it. To do this, you must build an expression that gains access to one object in the object model and then use properties or methods to move up or down 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. Accessors typically have the same name as the object they are used to access; for example, the Word Documents property is used to access the Documents collection. Accessors are typically properties, but in some object models accessors are methods.
A common place to gain access to the object model is the top-level object. In all Office applications and in most applications that support VBA, the top-level object is the Application object. However, some applications and components may have a different top-level object. For example, when you are programming the Visual Basic Editor (by using a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 library), the top-level object is the VBE object.
You use the Application property to return a reference to the Application object. The following code fragment returns a reference to the Application object and then sets properties to display scroll bars, ScreenTips, and the status bar:
Dim wdApp As Application
Set wdApp = Application
With wdApp
.DisplayScrollBars = True
.DisplayScreenTips = True
.DisplayStatusBar = True
End With
If you have established references to more than one type library that contains an Application object, the Application property will always return the Application object for the host application. In addition, for any other object that has the same name in two or more referenced type libraries, the accessor property or method will return the object from the first type library referenced in the Available References list of the References dialog box (Tools menu).
For example, the ActiveX Data Objects (ADO) and Data Access Objects (DAO) type libraries both have Recordset objects. If you have a reference to the ADO type library followed by the DAO type library, a declaration like the following will always return the ADO Recordset object:
Dim rstNew As Recordset
While you may be able to adjust the priority of references in the References dialog box to correct this, a better solution, which eliminates any ambiguity and prevents errors, is to declare an object variable by using the fully qualified class name, also called the programmatic identifier or ProgID, of the object. To do this, combine the name of the application or component that contains the object (as it appears in the Object Browser's Project/Library box) with the name of the object separated by a period.
For example, to declare an object variable that will be used to work with the Word Application object from another application, you must declare the object variable like this:
Dim wdApp As Word.Application
Similarly, if you have both the ADO and the DAO type libraries referenced in your project, you should declare object variables to work with Recordset objects like this:
Dim rstADO As ADODB.Recordset
Dim rstDAO As DAO.Recordset
Note You can view the ProgIDs of all installed applications and components on a computer by running the Registry Editor and looking under the \HKEY_CLASSES_ROOT\CLSID subkey.
For more information about using the References dialog box, see "Setting References and Working with Object Variables" later in this chapter. For more information about using the Object Browser, see "Using the Object Browser" later in this chapter.
To get to an object from the top-level object, you must step through all the objects above it in the hierarchy by using accessors to return one object from another. Many objects, such as workbooks, worksheets, documents, presentations, and slides, are members of collections. A collection 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. Because collections are always one level higher than individual objects in the hierarchy, you usually have to access a collection before you can access an object in that collection. The accessor that returns a collection object usually has the same name as the collection object itself. 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
You reference an item in a collection by using either a number that refers to its position in the collection or by using its name. For example, if a document named Report.doc is the first open document in the Documents collection, you can reference it in either of the following ways:
Application.Documents(1)
-or-
Application.Documents("Report.doc")
To get to an object further down the object hierarchy, simply add additional accessors and objects to your expression until you get to the object you want to work with. For example, the following expression returns a reference to the second paragraph in the Paragraphs collection of the first open document:
Application.Documents(1).Paragraphs(2)
For more information about referencing and working with objects in collections, see "Working with Collections" later in this chapter.
There are shortcut accessors you can use to gain direct access to objects in the model without having to navigate from the Application object. These shortcuts include accessors, such as the Documents, Workbooks, Items, and Presentations properties, that you can use by themselves to return a reference to the document collection for the corresponding 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"
-or-
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. Note that the Application object and the Documents collection object are not explicitly referenced.
ActiveDocument.Close
Tip When <globals> is selected in the Classes list in the Object Browser, you can use any accessor that appears in the Members of list 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 VBA can determine that information from the context in which your code is running.
When you are using the ActiveDocument and ActiveWorkbook accessor properties, it is important to remember that the reference returned is to the document or workbook that is currently in use (the topmost window of all open documents or workbooks). In many circumstances, you can reference an active object implicitly, that is, without including the entire hierarchy above the object you're referring to. For example, you can create a reference to the active workbook's Worksheets collection without preceding the collection with ActiveWorkbook. or an explicit reference to the workbook's name or number in the Workbooks collection:
Worksheets("MySheet")
However, using implicit references or references to the ActiveDocument or ActiveWorkbook accessor properties can create problems if you are developing a global template or add-in and need to make sure your code refers to the add-in or global template itself. Word and Excel provide two special accessor properties that return a reference to the document or workbook in which the VBA code is running: ThisDocument and ThisWorkbook. Use the ThisDocument or ThisWorkbook property whenever you need to make sure that your code refers to the document or workbook that contains the code that is running.
For example, both of the following Set statements reference the worksheet named Addin Definition. The first makes an explicit reference to the active workbook by using the ActiveWorkbook property. The second makes an implicit reference; because it doesn't explicitly refer to a specific workbook, the reference is assumed to be to the active workbook. In either case, the reference made in the Set statement will be to the worksheet in whatever workbook happens to be active when the code runs.
Set rngMenuDef = ActiveWorkbook.Worksheets("Addin Definition"). _
Range("MenuDefinition")
Set rngMenuDef = Worksheets("Addin Definition").Range("MenuDefinition")
References like these will work correctly while you are developing an add-in or template if you have no other documents or workbooks open while you are testing your code, or if the add-in or template is in the active window when the code is running. However, once your add-in or template is in use, these type of references can cause errors. To make sure that you are referencing the workbook in which code is running, use the ThisWorkbook property as shown in the following Set statement:
Set rngMenuDef = ThisWorkbook.Worksheets("Addin Definition"). _
Range("MenuDefinition")
To access an object higher up in the object hierarchy from the current object, you can often use the Parent property of the object. Using an object's Parent property allows you to reference the object that contains the object. For example, if you write a function to work with a control on a form (the function takes an argument of type Control), you can use the control's Parent property to reference the form that contains the control.
Note that the Parent property doesn't always return the object immediately above the current object in the hierarchy, it may return a higher object, especially if the object immediately above the current object is a collection. For example, the Parent property of a Word Document object returns the Application object, not the Documents collection. You can use the TypeName function to find out what kind of object the Parent property of an object refers to. For example, in Word, the following statement displays the type of object that the Parent property of the Document object refers to:
MsgBox TypeName(Documents("Document1").Parent)
Tip You can use the TypeName function to determine the type of object returned by any expression, not just expressions that use the Parent property. The TypeName function can also be used to determine the kind of data type returned by an expression, such as Byte, Integer, or Long.
To navigate from an embedded OLE object to the Application object of the program it was created with, you can often use the Application property of the object. For example, in PowerPoint, the following expression returns a reference to the Application object for the application used to create the embedded OLE object in the third shape on the first slide of the active presentation:
ActivePresentation.Slides(1).Shapes(3).Object.Application