Get name of procedure

[pulsar777]

Hi, I'd like to return name of Sub or Function which executes its own lines of code.
Example:



Sub MyFalsyMacro()

Dim str as String
On Error GoTo Handler

'some code here


Handler:
str = <Name of this Sub - MyFalsyMacro>
End Sub


Anyone can help please?

[SamT]

I don't see the problem.

str = "MyFalsyMacro" is correct.

Perhaps if you explained your goal, instead of your imagined solution.

[pulsar777]

My aim is not to hard code name of routine but retrieve the calling Sub's name and store it:



Sub Main()

Dim str as String

Call MyTruthyMacro(str)
Call MyFalsyMacro(str)

MsgBox "Error happend in this procedure: " & str

End Sub

Sub MyTruthyMacro(str as String)

On Error Goto Handler
Debug.Print "All good here"

Exit Sub
Handler:
str = <name of this sub>

End Sub

Sub MyFalsyMacro(str as String)

On Error GoTo Handler
Cells(9999999999,1).Select

Exit Sub
Handler:
str = <name of this sub>
End Sub

[SamT]

LOL :joy:

Your code

Str = ReturnMyName("name of this sub")

Code for Function

Function ReturnMyName(SomeString As String) As String
ReturnMyName = SomeString
End Function

Also, ReturnMyName will return any string you wish to pass it and will work in any sub, Function, formula, or macro .

Why... you can even do something weird like

MsgBox ReturnMyName("this is your message") and it will work.



JUST COPYPASTE THE SUB NAME AFTER str= :banghead::banghead::banghead:

[Paul_Hossler]

When all else fails, turn to Chip:

http://www.cpearson.com/excel/InsertProcedureNames.aspx



In VBA, there is no way to determine programmatically the name of the currently running procedure. That is, there is no way for a procedure to get its own name. Such a feature would be very useful when generating debug and diagnostic reports. This page describes a VBA procedure that you can use to insert Const declarations in your procedures which have a value equal to the procedure name.




' modInsertProcNames
' By Chip Pearson, www.cpearson.com, chip@cpearson.com
'
' This module contains the InsertProcedureNameIntoProcedure and supporting
' procedures.
'
' InsertProcedureNameIntoProcedure will insert a CONST statement at the
' top of each procedure in the Application.VBE.ActiveCodePane.CodeModule.
' The user selects the name of the constant (e.g., "C_PROC_NAME") and the
' code insert statements like
' Const C_PROC_NAME = "InsertProcedureNameIntoProcedure"
' at the beginning of each procedure. It supports procedures whose Declaration
' spans more than one line, e.g.,
' Public Function Test( X As Integer, _
' Y As Integer, _
' Z As Integer)
' If comment lines appear DIRECTLY below the procedured declaration (no blank
' lines between the declaration and the start of the comments), the CONST
' statement is placed directly below the comment block. If a constant already
' exists with name user specified name, that constant declaration is deleted and
' replaced with the new CONST line.

[SamT]

Thankee, Paul, I downloaded that *.bas.zip to be imported into my Personal.xls

[Paul_Hossler]

Me too

Not exactly what the OP asked for, but can be made to work 'gud nuff'

Many times I've wanted a 'Me.' equivalent for inside a Sub or Function that would allow something like 'Me.Name' to be used

Don't forget to add "Microsoft Visual Basic for Applications Extensibility" to references

[snb]

If you use a unique Error Trapping Line in each procedure you can use e.g.


Sub M_snb()
On Error GoTo XL90

M_tst


If Err.Number = 0 Then Exit Sub

XL90:
With ThisWorkbook.VBProject.VBE.SelectedVBComponent.CodeModule
MsgBox .ProcOfLine(Application.Match("XL90:", Split(.Lines(1, .CountOfLines), vbCrLf), 0), 0)
End With
End Sub


Sub M_tst()
On Error GoTo XL91

x = 3 / 0

If Err.Number = 0 Then Exit Sub

XL91:
With ThisWorkbook.VBProject.VBE.SelectedVBComponent.CodeModule
MsgBox .ProcOfLine(Application.Match("XL91:", Split(.Lines(1, .CountOfLines), vbCrLf), 0), 0)
End With
End Sub

[SamT]

@ Pulsar777
As you can see from the above, all solutions require much more coding than

Sub MyFalsyMacro()
Const myName As String = "MyFalsyMacro" 'CopyPaste Declaration + "", Copy Sub Name Paste

On Error GoTo Handler

'some code here

'Common code follows: CopyPasta into any sub or function
Handler:
Dim str as String
str = myName
End Sub

[pulsar777]

Thank you guys !
@SamT: That HTML was just to make it understand in simple terms. I checked CPearson's reference before posting here, just couldn't get the line of code number that causes Error, but snb's logic seems legit, I'll try that out.