To program and manipulate Visual Basic Editor (VBE) in code, you need to access objects contained in the Microsoft Visual Basic for Applications Extensibility 5.3 Library (VBIDE). Before you can use the objects from the VBIDE library, you must do two things:
In the Microsoft Excel application window, choose Tools | Macro | Security. Click the Trusted Sources tab, select the Trust Access to Visual Basic Project check box, and click OK.
Note: If access to VBProjects is not enabled, an attempt to run a VBA procedure that accesses objects from the VBIDE Library results in the following error message: "Programmatic access to Visual Basic Project is not trusted."
2. Create a reference to the VBIDE Library.
In the Visual Basic Editor window, choose Tools | References, check Microsoft Visual Basic for Applications Extensibility 5.3 Library, and click OK.
The top-level object in the VBA Extensibility Library object model is the VBE object, which represents the Visual Basic Editor itself. The VBE object contains the collection of projects. A VBA project that is open in the Visual Basic Editor is represented by the VBProject object. Each VBA project can reference one or more type libraries or projects. Use the Reference object to find out what references are currently selected in the References dialog box for the specific VBA Project.
A VBProject object has a collection of VBComponents consisting of VBComponent objects. A VBComponent object represents a component in the project, such as a standard module, class module, or user form. Each VBComponent has a CodeModule property, which you can use to access the underlying CodeModule object. A CodeModule object represents each component's code module.
The following procedure creates a new standard module in a workbook and prints to the Immediate window the names of all VBA projects, the names and full paths of selected references for each VBA project, and the names of each project's components. The procedure also demonstrates how you can programmatically export the contents of a module to a file.
Sub VB_Project() Dim objVBPrj As VBIDE.VBProject Dim objVBCom As VBIDE.VBComponent Dim vbrRef As VBIDE.Reference
' Create new workbook Application.Workbooks.Add ' Create a new module in a workbook Application.VBE.ActiveVBProject. _ VBComponents.Add (vbext_ct_StdModule)
' List VBA projects as well as references and ' component names they contain For Each objVBPrj In Application.VBE.VBProjects Debug.Print objVBPrj.Name For Each vbrRef In objVBPrj.References With vbrRef
Debug.Print .Name & "---" & .FullPath End With
For Each objVBCom In objVBPrj.VBComponents Debug.Print vbTab & objVBCom.Name
' Export the entire Modulel in the activeVB project to disk With ThisWorkbook.VBProject. _ VBComponents("Module1") If MsgBox("Module1 contains " & _ .CodeModule.CountOfLines & _" " lines." & vbCrLf & _ "Do you want to export it to a file?", _ vbYesNo) = vbYes Then
End If End With
Was this article helpful?