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

[ErdincEKaracam]

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ç.

 

[Dave Peterson]

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/

 

[ErdincEKaracam]

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/

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 Explicit", vbTextCompare) = 0 Then
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ç.

 

[Bob Phillips]

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

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/

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 Explicit", vbTextCompare) = 0 Then
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çam]

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ç.