There are three main VBE objects
VBProject - This is the entire set if VBA Modules and references associated with a workbook or document.
VBComponent - This is the individual component within a VBProject (ie a module or a userform)
CodeModule - This is the actual module "Module1" or "Module2" etc.
This is the top level object of the Visual Basic Editor
The VBE refers to the Visual Basic Editor, which includes all the windows and projects that make up the editor.
Dim obVBAEditor As VBIDE.VBE
Set obVBAEditor = Application.VBE
A VBProject corresponds to one of the top level items in the Project Explorer
A VBProject contains all the code modules and components of a single workbook.
One workbook has exactly one VBProject. The VBProject is made up of 1 or more VBComponent objects.
You can also iterate through the VBProjects collection
Dim obVBProject As VBIDE.VBProject
Set obVBProject = Application.VBE.VBProjects(2)
Set obVBProject = Workbooks(1).VBProject
When programming the VB Editor it is very useful to know which project is currently highlighted in the Project Explorer
This is given by the ActiveVBProject property
Dim obVBP AS VBIDE.VBProject
Set objVBP = Application.VBE.ActiveVBProject
Note that this project is the project the user is currently editing in the VB Editor and is not related to the active workbook or document
A VBComponent is one object within the VBProject.
A VBComponent is a code module, a UserForm, a class module, one of the Sheet modules, or the ThisWorkbook module.
A VBComponent is of one of the following types, identified by the Type property. The following constants are used to identify the Type.
The numeric value of each constant is shown in parentheses.
vbext_ct_ClassModule (2): A class module to create your own objects.
vbext_ct_Document (100): One of the Sheet modules or the ThisWorkbook module.
vbext_ct_MSForm (3): A UserForm. The visual component of a UserForm in the VBA Editor is called a designer.
vbext_ct_StdModule (1): A regular code module. Most of the procedures on this page will work with these types of components.
Dim objVBComponent As VBIDE.VBComponent
Set objVBComponent = ActiveWorkbook.VBProject.VBComponents("Module1")
Set objVBComponent = objVBProject.VBComponents("Module1")
The Userforms, standard modules, class modules and code modules behind the worksheets and workbook are all VBComponents 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 UserForm1 form in Book1.xls you should use:
Dim obVBC As VBIDE.VBComponent
Set obVBC = Workbooks(").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
Hence to find the VBComponent for the code behind the workbook you can use:
Set obVBC = Workbooks(1).VBProject.VBComponents(Workbooks(1).CodeName)
and for a specific worksheet you could use:
Set obVBC = Workbooks(1).VBProject.VBComponents(Workbooks(1).Worksheets(2).CodeName)
Note that the name of the workbook's VBComponent is usually "ThisWorkbook" in the Project Explorer
Do not rely on this name
If the user has chosen a different language for the Office User Interface, it will be different.
When developing Add-ins for the VBE you often need to know the VBComponent that the user is currently editing.
This is given by the SelectedVBComponent property of the VBE.
Dim obVBC As VBIDE.VBComponent
Set obVBC = Application.VBE.SelectedVBComponent
Each VBComponent has a Properties collection, corresponding approximately to the list shown in the Properties window of the VBE when the VBComponent is selected in the Project Explorer.
One of these is the Name property.
Debug.Print .Name & ": " & .Properties("Name")
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.
Properties("Name") gives the name of the Excel object (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.
All the VBA code for a VBComponent is contained in a CodeModule object
A CodeModule is the VBA source code of a VBComponent. You use the CodeModule object to access the code associated with a VBComponent. A VBComponent has exactly one CodeModule.
There is only one CodeModule for each VBComponent
Every type of VBComponent has a CodeModule - check !!!
Dim objCodeModule As VBIDE.CodeModule
Set objCodeModule = ActiveWorkbook.VBProject.VBComponents("Module1").CodeModule
Set objCodeModule = objVBComponent.CodeModule
This provides access to the user's view of a code module.
A CodePane is an open editing window of a CodeModule.
This allows you to identify things like the section of a code module that is currently visible on the screen or the text the user has selected.
You can identify which CodePane ios currently being edited by using the ActiveCodePane property
Dim obCP AS VBIDE.CodePane
Set obCP = Application.VBE.ActiveCodePane
Subroutines, Functions and Properties
In the code and descriptions on this page, the term Procedure means a Sub, Function, Property Get, Property Let, or Property Set procedure. The Extensibility library defines four procedures types, identified by the following constants.
The numeric value of each constant is shown within parentheses.
vbext_pk_Get (3). A Property Get procedure.
vbext_pk_Let (1). A Property Let procedure.
vbext_pk_Set (2). A Property Set procedure.
vbext_pk_Proc (0). A Sub or Function procedure.
Userforms have a graphical interface and a code module
The Designer object provides access to the graphical part
It is possible to modify the code in your code modules tdynamically via code. The main procedures / functions are:
InsertLines (line,code) - inserts a line or lines of code at a specific location in a block of code. (example)
Lines (startline,nooflines) - to obtain the code on a particular line
DeleteLines (line,code) - deletes a single line or specified range of lines (example)
ProcStartLine - returns the line at which the specified procedure begins
ProcBodyLine - returns the first line of the procedure
Lines - returns the specified line of code
Project Locked properties
|vbext_pp_locked||Project is locked|
|vbext_pp_none||Project is not protected|
|Menu Bar||The normal VBE menu bar|
|Standard||The normal VBE toolbar|
|Edit||The VBE Edit toolbar|
|Debug||The VBE Debug toolbar|
|UserForm||The VBE Userform toolbar|
|MSForms||The shortcut menu when you right click on the background of a Userform|
|MSForms Control||The shortcut menu when you right click on a normal control on a Userform|
|MSForms Control Group||The shortcut menu when you right click on a group of controls on a Userform|
|MSForms MPC||The shortcut menu for a MultiPage control|
|MSForms Palette||The shortcut menu when you right click a tool on the Control Toolbox|
|MSForms Toolbox||The shortcut menu when you right click on one of the tabs of the Control Toolbox.|
|MSForms DragDrop||The shortcut menu when you right click on a tool to drag it between tabs on the Control Toolbox|
|Code Window||The shortcut menu when you right click in the Code Window.|
|Code Window (Break)||The shortcut menu when you right click in the Code Window while debugging (or in Break mode).|
|Watch Window||The shortcut menu for the Watch Window|
|Immediate Window||The shortcut menu for the Immediate Window|
|Locals Window||The shortcut menu for the Locals Window|
|Project Window||The shortcut menu for the Project Explorer Window|
|Project Window (Break)||The shortcut menu for the Project Explorer Window while debugging (or in Break mode).|
|Object Browser||The shortcut menu for the Object Browser|
|Property Browser||The shortcut menu for the Properties Window|
|Docked Window||The shortcut menu that appears when you right click the title bar of a docked window.|
© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext