The VBComponent Object

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

The UserForms, standard modules, class modules, and code modules behind the worksheets and workbook are all VBComponent objects. Each VBComponent object corresponds to one of the lower-level items in the Project Explorer tree. A specific VBComponent can be located through the VBComponents collection of a VBProject. Hence, to find the VBComponent that represents the UserForml form in Bookl.xls, code like this can be used:

Dim oVBC As VBIDE.VBComponent

Set oVBC = Workbooks("Book1.xlsm").VBProject.VBComponents("UserForm1")

The name of the VBComponent that contains the code behind the workbook, worksheets, and charts is given by the CodeName property of the related Excel object (the workbook, worksheet, or chart object). Hence, to find the VBComponent for the code behind the workbook (where code can be written to hook into workbook events), this code can be used:

Dim oVBC As VBIDE.VBComponent

With Workbooks("Book1.xlsm")

Set oVBC = .VBProject.VBComponents(.CodeName) End With

And for a specific worksheet:

Dim oVBC As VBIDE.VBComponent

With Workbooks("Book1.xlsm")

Set oVBC = .VBProject.VBComponents(.Worksheets("Sheet1";

1.CodeName)

End With

Note that the name of the workbook's VBComponent is usually ThisWorkbook in the Project Explorer. Do not be tempted to rely on this name. If your user has chosen a different language for the Office User Interface, it will be different. The name can also be easily changed by the user in the VBE. For this reason, do not use code like this:

Dim oVBC As VBIDE.VBComponent

With Workbooks("Book1.xlsm")

Set oVBC = .VBProject.VBComponents("ThisWorkbook") End With

When developing Add-ins for the VBE, you often need to know the VBComponent that the user is editing (the one highlighted in the Project Explorer). This is given by the SelectedVBComponent property of the VBE:

Dim oVBC As VBIDE.VBComponent

Set oVBC = Application.VBE.SelectedVBComponent

Each VBComponent has a Properties collection, corresponding approximately to the list shown in the Properties window of the VBE when a VBComponent is selected in the Project Explorer. One of these is the Name property, shown in the following test routine:

Sub ShowNames()

With Application.VBE.

SelectedVBComponent

Debug.Print .Name

& ": " & .Properties("Name")

End With

End Sub

For most VBComponent objects, the text returned by .Name and .Properties("Name") is the same. However, for the VBComponent objects that contain the code behind workbooks, worksheets, and charts, the .Properties collection includes all the properties of the native Excel object, so .Properties("Name") gives the name of the workbook, worksheet, or chart. You can use this to find the Excel object that corresponds to the item that the user is working on in the VBE, or the Excel workbook that corresponds to the ActiveVBProject. The code for doing this is shown later in this chapter.

Was this article helpful?

+11 -7

Post a comment