Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
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
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.
| 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.