The IDE Object Model

Programming the IDE requires an understanding of its object model. The top object in the object hierarchy is the VBE (Visual Basic Environment). As with Excel's object model, the VBE contains other objects. A simplified version of the IDE object hierarchy is as follows :

 VBE     VBProject       VBComponent           CodeModule           Designer           Property       Reference     Window     CommandBar 

Note  

This chapter ignores the Extensibility Library's Windows collection and CommandBars collection, which aren't all that useful for Excel developers. Rather, the chapter focuses on the VBProject object, which can be very useful for developers - but make sure that you read the "An Important Security Note" sidebar.

The VBProjects collection

Every open workbook or add-in is represented by a VBProject object. To access the VBProject object for a workbook, make sure that you've established a reference to the Microsoft Visual Basic for Applications Extensibility Library (see "Introducing the IDE," earlier in this chapter).

The VBProject property of the Workbook object returns a VBProject object. The following instructions, for example, create an object variable that represents the VBProject object for the active workbook:

 Dim VBP As VBProject Set VBP = ActiveWorkbook.VBProject 

Note  

If you get an error message when VBA encounters the Dim statement, make sure that you've added a reference to Microsoft Visual Basic for Applications Extensibility Library.

Each VBProject object contains a collection of the VBA component objects in the project (UserForms, modules, class modules, and document modules). Not surprisingly, this collection is called VBComponents . A VBProject object also contains a References collection for the project, representing the libraries being referenced currently by the project.

You cannot add a new member to the VBProjects collection directly. Rather, you do so indirectly by opening or creating a new workbook in Excel. Doing so automatically adds a new member to the VBProjects collection. Similarly, you can't remove a VBProject object directly; closing a workbook removes the VBProject object from the collection.

THE VBCOMPONENTS COLLECTION

To access a member of the VBComponents collection, use the VBComponents property with an index number or name as its argument. The following instructions demonstrate the two ways to access a VBA component and create an object variable:

 Set VBC = ThisWorkbook.VBProject.VBComponents(1) Set VBC = ThisWorkbook.VBProject.VBComponents("Module1") 

THE REFERENCES COLLECTION

Every VBA project in Excel contains a number of references. You can view, add, or delete the references for a project by choosing the Tools References command (refer to Figure 28-1 to see the References dialog box). Every project contains some references (such as VBA itself, Excel, OLE Automation, and the Office object library), and you can add more references to a project as needed.

You can also manipulate the references for a project by using VBA. The References collection contains Reference objects, and these objects have properties and methods . The following procedure, for example, displays a message box that lists the Name , Description , and FullPath property for each Reference object in the active workbook's project:

 Sub ListReferences()     Dim Ref As Reference     Msg = ""     For Each Ref In ActiveWorkbook.VBProject.References         Msg = Msg & Ref.Name & vbNewLine         Msg = Msg & Ref.Description & vbNewLine         Msg = Msg & Ref.FullPath & vbNewLine & vbNewLine     Next Ref     MsgBox Msg End Sub 

Figure 28-2 shows the result of running this procedure when a workbook that contains six references is active.

Figure 28-2: This message box displays information about the references for a project.

Note  

Because it declares an object variable of type Reference , the ListReferences procedure requires a reference to the VBA Extensibility Library. If you declare Ref as a generic Object , the VBA Extensibility Library reference is not needed.

You can also add a reference programmatically by using either of two methods of the Reference class. The AddFromFile method adds a reference if you know its filename and path . AddFromGuid adds a reference if you know the reference's globally unique identifier, or GUID. Refer to the Help system for more information.