Active VBE Module ?

  • Thread starter Thread starter MWE
  • Start date Start date
M

MWE

I wish to execute a macro while in the VBE and the macro needs to kno
what VBE module is presently active / hi-lited. I know how to do mos
VBE programming, just not how to know which module is active. How do
do this?

Thank
 
It depends on what you actually need. If you need the window,
use

Application.VBE.ActiveWindow.

If you need the code module object, use

Application.VBE.ActiveCodePane.CodeModule


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Chip:

Thanks: what you provided was quite helpful. I was able to figure ou
how to get the VBComponent Name associated with the active code modul
(which is what I really wanted).
(Application.VBE.ActiveCodePane.CodeModule.Parent.Name)

I also need to go "up" another level (or two) and determine th
worksheet name for the VBProject that contains the VBComponent with th
active code module. Using something lik
Application.VBE.ActiveVBProject does not work because as soon as my VB
macro executes, the ActiveVBProject is reset from the active VBProjec
in the VBE to the VBproject that contains the macro code (it is simila
to the ActiveWorksheet vs ThisWorkSheet issue).

Thank
 
MWE,

If you want to get the VBProject of a CodePane, use

Set VBProj = VBCodePane.CodeModule.Parent.Collection.Parent

To get the Workbook associated with the CodePane, you have to
loop through the workbooks and compare file names. E.g.,

Dim WB As Workbook
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Set VBProj = VBCodePane.CodeModule.Parent.Collection.Parent
For Each WB In Application.Workbooks
If WB.FullName = VBProj.Filename Then
Exit For
End If
Next WB
Debug.Print WB.FullName

To get the worksheet associated with the CodePane, you have to
loop through the worksheets and compare code names. (The
following assumes that VBCodePane is set to the code pane of a
sheet module.)

Dim WS As Worksheet
For Each WS In WB.Worksheets
If WS.CodeName = VBCodePane.CodeModule.Parent.Name Then
Exit For
End If
Next WS
Debug.Print WS.Name


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top