Excel - How to get the current module name and Sub in VBA?

Asked By ErdincEKaracam on 07-Nov-08 09:33 PM
Dear Sirs,

I want to learn that how to get the current module name and Sub in
VBA?

For example:

In a standard module named as "MPEP" ;

Sub XYZ()
Msgbox "It's an Example"
End Sub

The result should be:
For Module Name: MPEP
and
For Sub Name: XYZ

How we can do this with VBA?

Thanks inadvance.

Erdin=E7.




Dave Peterson replied on 07-Nov-08 12:56 PM
Nothing built into VBA allows you to do this.

You have to keep track of it yourself.

Chip Pearson has some code that may help you:
http://cpearson.com/excel/InsertProcedureNames.aspx

IIRC, MZTools has this ability, too:
http://www.mztools.com/


--

Dave Peterson
ErdincEKaracam replied on 07-Nov-08 09:34 PM
sertProcedureNames.aspx

Hi Dave thanks a lot your helps.

I have solved the thread a few seconds ago.

Option Explicit

Sub Bu_Modulun_Adi_Neymis()

Dim Kod_Modulu As CodeModule
Dim Option_Deklerasyon_Satir_Sayisi_Eger_Varsa As Long
Dim Kod_Satir_Sayisi As Long
Dim Ad As String

Set Kod_Modulu =3D Application.VBE.ActiveCodePane.CodeModule

For Kod_Satir_Sayisi =3D 1 To Kod_Modulu.CountOfDeclarationLines
If InStr(1, Kod_Modulu.Lines(Kod_Satir_Sayisi, 1), _
Option_Deklerasyon_Satir_Sayisi_Eger_Varsa =3D _
Option_Deklerasyon_Satir_Sayisi_Eger_Varsa + 1
End If
Next Kod_Satir_Sayisi

Option_Deklerasyon_Satir_Sayisi_Eger_Varsa =3D _
Kod_Modulu.CountOfDeclarationLines - _
Option_Deklerasyon_Satir_Sayisi_Eger_Varsa

For Kod_Satir_Sayisi =3D 1 To Kod_Modulu.CountOfLines
Ad =3D Kod_Modulu.Lines(Kod_Satir_Sayisi + _
Option_Deklerasyon_Satir_Sayisi_Eger_Varsa, 1)
If VBA.Left(Ad, 3) =3D "Sub" Then
Ad =3D Mid(Ad, 5, Len(Ad) - 6)
GoTo Bitti
End If

Next
Bitti:
MsgBox Ad
Set Kod_Modulu =3D Nothing

End Sub


and

Sub Bu_Subun_Bulundugu_Modulenin_Adi_Neymis()
MsgBox Application.VBE.ActiveCodePane.CodeModule
MsgBox Application.VBE.SelectedVBComponent.Name
MsgBox Application.VBE.ActiveCodePane.CodeModule.Name
MsgBox Application.VBE.SelectedVBComponent.CodeModule.Parent.Name
End sub


Loves and thanks again.

Erdin=E7.
Bob Phillips replied on 08-Nov-08 08:27 AM
Hey Erdinç,

Greetings to Bursa City, the home of the döner kebab.

That gets the code module name, but not the procedure name. But it is also a
problem with the codemodule as it just gives the active or selected code
pane, so if you call a procedure from another module you get the wrong
module name.

There is a way to get the procedure name, I forget it for now, but it
suffers the same limitations.

The only sure way is to hand-craft it as Dave suggests

--
__________________________________
HTH

Bob


Hi Dave thanks a lot your helps.

I have solved the thread a few seconds ago.

Option Explicit

Sub Bu_Modulun_Adi_Neymis()

Dim Kod_Modulu As CodeModule
Dim Option_Deklerasyon_Satir_Sayisi_Eger_Varsa As Long
Dim Kod_Satir_Sayisi As Long
Dim Ad As String

Set Kod_Modulu = Application.VBE.ActiveCodePane.CodeModule

For Kod_Satir_Sayisi = 1 To Kod_Modulu.CountOfDeclarationLines
If InStr(1, Kod_Modulu.Lines(Kod_Satir_Sayisi, 1), _
Option_Deklerasyon_Satir_Sayisi_Eger_Varsa = _
Option_Deklerasyon_Satir_Sayisi_Eger_Varsa + 1
End If
Next Kod_Satir_Sayisi

Option_Deklerasyon_Satir_Sayisi_Eger_Varsa = _
Kod_Modulu.CountOfDeclarationLines - _
Option_Deklerasyon_Satir_Sayisi_Eger_Varsa

For Kod_Satir_Sayisi = 1 To Kod_Modulu.CountOfLines
Ad = Kod_Modulu.Lines(Kod_Satir_Sayisi + _
Option_Deklerasyon_Satir_Sayisi_Eger_Varsa, 1)
If VBA.Left(Ad, 3) = "Sub" Then
Ad = Mid(Ad, 5, Len(Ad) - 6)
GoTo Bitti
End If

Next
Bitti:
MsgBox Ad
Set Kod_Modulu = Nothing

End Sub


and

Sub Bu_Subun_Bulundugu_Modulenin_Adi_Neymis()
MsgBox Application.VBE.ActiveCodePane.CodeModule
MsgBox Application.VBE.SelectedVBComponent.Name
MsgBox Application.VBE.ActiveCodePane.CodeModule.Name
MsgBox Application.VBE.SelectedVBComponent.CodeModule.Parent.Name
End sub


Loves and thanks again.

Erdinç.
Erdinç E. Karaça replied on 20-Dec-08 06:15 PM
Hi Bob and Dave,

Thanks a lot to help. I just saw your post now.  I solved my problem after i
have posted my message. But i thank you all again.

By the way;

I can order you the döner kebab if you visit out city. I promise. :)

Loves from Bursa City :)

Erdinç.