"Trace" no (VBA/Excel), how do I get the name of a subroutine?

Question

How do I take the name of a subroutine (VBA/Excel) within itself to use in a code of type "Trace"? (see basic code in block UPDATE after the example code)

The idea is to use something like "ME.Name", which takes the name of the project.

For example, the ideal would be something like:

Private Sub Rotina()

    Trace(???.Name) 'Captura o nome da subrotina como argumento do Trace
    ...
    ...

End Sub


Public Sub Trace(ByVal NomeDaSubRotina As String)

     if TraceAtivo Then

      MsgBox "Estou em " & NomeDaSubRotina 

     End If

    'Pode ser feito também com ListBox (ver código na atualização abaixo)  

End Sub

SOLICITATION

Please, I have found several solutions to the question, but I do not have enough knowledge in VBA to test them. Can anyone point out one of these solutions? Follow the link:

https://stackoverflow.com/questions/23945321/how-to-get-the-procedure-or-function-name-at-runtime

Grateful

COMPLEMENT: Resultado da pesquisa!

Through this "link", I made some tests with the codes of the "site" and I did the same with other "sites" within this context.

The result that I arrived in several different attempts is that there is always a message more or less like this:

"It is not safe to use this function"

Therefore, "IT IS POSSIBLE TO CAPTURE THE NAME OF THE SUBROUTINE WITHIN ITSELF, BUT IT TAKES A CERTAIN TYPE OF PERMISSION!!!"

Researching about the message I found that there are explanations of how to do this, however, I did not understand how to do it right. I do not indicate the links at the time, because I did the research outside the home and I would need to make the survey where I found, BUT I imagined that I would have time to pick up the links and summarize what each one contained, and I did not give.

QUESTION:

What are the consequences and/or risks of changing the safety standard and, if it decides to change, how to proceed? I didn’t understand very well and I didn’t want to risk without knowing the cause. Someone knows about it?

Grateful

UPDATING

The code below acts as a "Trace" and dynamically, being very interesting to see Listbox present the sequence of the subroutines triggered at each action (in block, from the beginning to the end of the operations of that action).

It shows by Msgbox and/or adds lines to the Listbox with the "Name" of the subroutine where "Msg" is (put in the first line of each subroutine).

As option: 1, 2 or 3 in a cell in Excel some subroutines or all are shown, because when I give the other option of a positive integer (in another cell in Excel), only one or some subroutines indicated with this same number are shown, or all, if the option is -1.

Just need to get the name of each subroutine automatically inside itself so you don’t have to type your name inside each one.

Public Sub Msg(ByRef MsgTexto As String, ByRef MsgTipo As Integer)

' Msg - PARAMETROS: Digitar na planilha nas células:
' MsgOuListbox  e  MsgTipo os valores dos parâmetros.
'
‘ A célula MsgIndice inicia com 0 quando o formulário é acionado, 
‘ e guarda o índice para inserir cada nova linha ao Listbox
'
' MsgOuListbox: 0=Não mostra, 1=Mostra MsgBox  2=Preenche ListBox  3=Ambos
'
' MsgTipo: Indica se mostra MsgBox e/ou acrescenta linha ao ListBox de cada 
' subrotina conforme o número indicado na chamada dentro de cada rotina:
' 0 = não mostra
' 1 ... n = Faz só para o código indicado (um número apenas, pode ser
'        individual de uma subrotina ou um grupo delas com este mesmo número)
' -1 = faz para todas as subrotinas
'
' O listbox foi nomeado como ListBoxMsg1

' Se uma das células tem 0 não faz
If Range("MsgOuListbox").Value = 0 Or Range("MsgTipo").Value = 0 Then Exit Sub

' se tipo 2 ou 3 mostra MsgBox com o nome enviado da rotina
If Range("MsgOuListbox").Value = 2 Or Range("MsgOuListbox").Value = 3 Then

 ' se o número do tipo enviado é o mesmo do parâmetro ou se escolheu -1,    
 ' acrescenta linha ao Listbox
 If MsgTipo = Range("MsgTipo").Value Or Range("MsgTipo").Value = -1 Then

  ListBoxMsg1.AddItem MsgTexto, Range("MsgIndice").Value

  Range("MsgIndice").Value = Range("MsgIndice").Value + 1

 End If

End If

' se tipo 1 ou 3 pode mostrar o nome enviado com MsgBox  
If Range("MsgOuListbox").Value = 1 Or Range("MsgOuListbox").Value = 3 Then

 ' se o número do tipo enviado é o mesmo do parâmetro ou se escolheu -1,
 ' mostra MsgBox
 If MsgTipo = Range("MsgTipo").Value Or Range("MsgTipo").Value = -1 Then

  MsgBox MsgTexto

 End If

End If

End Sub

Within each subroutine I place at the beginning:

Msg "Nomedarotina", 1

in another:

Msg "Outrasubrotina", 12

If you put it in the worksheet cells Msgoulistbox = 2 and Msgtipo = 12 , only the name of the subroutines I indicated the number 12 dynamically appear in the Listbox (if triggered during execution), in this example "Othersaferin" will be shown and "Nomedarotina" will not be shown.

Answer 1

A few years ago I had the curiosity to make a mistake treatment like this as soon as the package came out . Net, until today I hope some function wants to allow this, the answer is that it is not possible to pick up, not simply the way you want.

I found that code, is worth a try:

ErrorHandler:
   Dim procName As String
   procName = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
   MyErrorHandler err, Me.Name, getUserID(), procName
   Resume Exithere

He says it works in version 2010...

What I suggest you do is a Tracer class, where you can build it at the beginning of the method by passing to it the name of the method.

This class would be responsible for logging in, it can write where you find it best, and still chat with other classes.

UPDATING

I’ll put an example of how you can create a Trace class.

This is a test project, I just did...

Form code:

Public Class Form1

    Private meuTrace As Trace

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        meuTrace = New Trace(ListBox1)
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        meuTrace.logar("Cliquei no botão 1")
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        meuTrace.logar("Cliquei no botão 2")
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        meuTrace.logar("Cliquei no botão 3")
    End Sub
End Class

This is the Trace class:

Public Class Trace

    Private _lista As ListBox
    Public Sub New(lista As ListBox)

        _lista = lista

    End Sub

    Public Sub logar(log As String)

        _lista.Items.Add(log)

    End Sub

End Class

By clicking on the buttons he writes to the list.

With this solution you can create an instance of Trace by passing your list to it and use that instance anywhere, without having the dependency of having to see the List.

I could see more or less how it can be done?