VBComponents Collection Methods






Parameters: ComponentType. Adds a new, built-in VBComponent to the project. The ComponentType can be one ofvbext_ct_StdModule, vbext_ct_ClassModule, or vbext_ct_MSForm.



Parameters: ProgId. Adds a new, custom VBComponent to the project. The result is always of type vbext_ct_ ActiveXDesigner. It seems that custom VB components can only be added to ActiveX DLL projects and not to Excel workbook projects.



Parameters: FileName. Adds a new VBComponent to the project from a file (usually a previously exported VBComponent).


Parameters: VBComponent. Removes a VBComponent from a project.

Many of the examples in this section and in Chapter 26 use the VBComponent object and its properties and methods. The example that follows exports a UserForm from the workbook containing the code, imports it into a new workbook, and renames it. It then adds a standard module, fills in some code to show the form, then calls the routine to show the form in the new workbook:

Sub CopyAndShowUserForm()

Dim oNewBk As Workbook, oVBC As VBIDE.VBComponent

'Create a new workbook Set oNewBk = Workbooks.Add

'Export a UserForm from this workbook to disk

ThisWorkbook.VBProject.VBComponents("UserForml").Export "c:\temp.

. frm"

'Import the UserForm into the new workbook

Set oVBC = oNewBk.VBProject.VBComponents.Import("c:\temp.frm")

'Rename the UserForm oVBC.Name = "MyForm"

'Add a standard module to the new workbook

Set oVBC = oNewBk.VBProject.VBComponents.Add(vbext_ct_StdModule)

'Add some code to the standard module, to show the form oVBC.CodeModule.AddFromString _

"Sub ShowMyForm()" & vbCrLf & _ " MyForm.Show" & vbCrLf & _ "End Sub" & vbCrLf

'Close the code pane the Excel opened when you added code to the



'Delete the exported file Kill "c:\temp.frm"

'Run the new routine to show the imported UserForm Application.Run oNewBk.Name & "!ShowMyForm" End Sub

+3 -1

Post a comment