Working with an Object Model

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.

Using References

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:

 Access.Form Excel.Application 

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:

• Select a library or object model to work with in the Project/Library drop-down list. You can also select <All Libraries> to have Object Browser look at everything that you have currently loaded.

• Locate a class by scrolling down the list on the left side. You can also enter part or all the name of the class in the Search Text box and click the Search button to quickly find the class.

• When you select a class, the right side of the Object Browser shows all the properties, methods, and events of the class. Collectively, properties, methods, and events are known as members. You learn about events later in this chapter.

• When you select a member of a class, the Definition window shows brief details on the member.

• Click the Help button to see detailed help on the currently selected member.

• Use the Go forward and Go back buttons to move back and forth in the list of members that you've viewed recently.

• Use the Copy to Clipboard button to copy the current member to the Clipboard.

TIP

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.