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

  • Thread starter Thread starter ErdincEKaracam
  • Start date Start date
E

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ç.
 
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/
Click to expand...

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ç.
 
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/
Click to expand...

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