|< Day Day Up >|
Collections and the items that they contain are often arranged into an object model. An object model is a diagram that shows the objects in an application and their relationships. For example, Figure 8.4 shows a portion of the object model supplied by Access.
Figure 8.4. A portion of the Access object model.
The diagram indicates that the Forms collection contains individual Form objects. Each Form object contains a Controls collection. The Controls collections each contain Control objects. As you undoubtedly guessed, the Control objects each represent a control on a form.
Using an Object Model
Here's some code that uses this portion of the object model:
Sub ListControls() ' List the controls on all open forms ' First, open three forms Dim frmClients As New Form_Clients Dim frmProjects As New Form_Projects Dim frmSwitchboard As New Form_Switchboard frmClients.Visible = True frmProjects.Visible = True frmSwitchboard.Visible = True ' Use collections to list their controls Dim frm As Form Dim cnt As Control For Each frm In Forms Debug.Print frm.Name For Each cnt In frm.Controls Debug.Print " " & cnt.Name Next cnt Next frm End Sub
Here, the nested For…Each loops go through both levels of the Access object model for forms and controls. The net effect is to list every control name on the three specified forms.
Many applications supply object models that VBA can use. For example, every application in the Microsoft Office suite has its own object model. To use the object model from an application other than the one where VBA is running, you must first set a reference to the object model. A reference tells VBA that you intend to use objects from a particular object model.
To set a new reference, select Tools, References in the VBA editor. This opens the References dialog box, shown in Figure 8.5. The references that are already set for the current VBA project are listed at the top of the dialog box, followed by all the available references on your computer, in alphabetical order.
Figure 8.5. The References dialog box.
To set a reference to an object model, scroll to the model in the list and select its check box. Then click OK. After you set a reference to an object model, you can use the objects in that model. For example, if you set a reference to the Microsoft Excel 11.0 Object Library, this code runs perfectly well in Access:
Sub TestExcel() ' Use the Excel object model Dim objExcel As New Excel.Application objExcel.Visible = True MsgBox "Excel should be visible" End Sub
Because Access and Excel both have objects named Application, it's necessary to specify that the code use the Application object from the Excel object model rather than the one from Access. This is done by specifying the name of the library followed by a dot and the name of the object, as in these examples:
In this book, you mainly use objects from two object models. The Access object model includes Forms, Controls, Reports, and other objects specific to Access. The ADO object model includes objects for working with data. You learn more about these object models in Parts II and III of this book.
The Object Browser
When you start working with objects, you quickly discover that there are hundreds of available objects, properties, and methods. The Object Browser tool makes it easier to navigate this often confusing forest.
To open the Object Browser, select View, Object Browser or press F2. This opens the Object Browser as a window in the VBA editor, as shown in Figure 8.6.
Figure 8.6. Using the Object Browser.
Here are some of the things that you can do with the Object Browser:
When you're writing code that uses objects, you'll probably find that the Object Browser is a faster way to get help than digging through the help files.
|< Day Day Up >|