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

Question

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

Navigation

#1 by (3 votes)

2

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

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

For example, the ideal would look 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

REQUEST

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

link

Thankful

COMPLEMENT: Search Result!

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

The result that I've come up with in a number of different attempts is that a message always looks something like this:

"It is not safe to use this function"

So, "YOU CAN CAPTURE THE SUBJECT NAME WITHIN THE SAME, BUT YOU MUST HAVE A CERTAIN TYPE OF PERMISSION !!!"

Searching the message, I noticed that there are explanations of how to do this, however, I did not quite understand how to do it. I did not indicate the links at the moment, because I did the searches outside the house and I would have to do the survey where I found, BUT I imagined that I would have time at the moment to take the links and summarize what each one contained, and did not give.

QUESTION:

What are the consequences and / or risks of changing the default level of security and, if you decide to change, how to proceed? I did not understand very well and did not want to risk without knowing it. Does anyone know about this?

Thankful

UPDATE

The code below functions as a "Trace" and dynamically, and it is very interesting to see Listbox present the sequence of subroutines triggered by each action (in block, from the beginning to the end of that action's operations).

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

According to the option: 1, 2 or 3 in a cell in Excel some or all subroutines are shown, because when I give the other option 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.

You just need to get the name of each subroutine automatically inside itself so you do not 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 put in the beginning:

Msg "RoutineName", 1

in another:

Msg "AnotherSubRotina", 12

If you put cells in the MsgOuListbox = 2 and MsgType = 12 worksheets, only the name of the subroutines I gave the 12 number will appear dynamically in the Listbox (if triggered during execution), in this example "OtherRotline" will be shown and "RoutineName" will not be shown .

procedure excel vba

asked by anonymous 25.11.2015 / 23:56

1 answer

Secure Login with Remember [duplicate] Create a download link for path that is in mysql

score 3 · Accepted Answer

A few years ago I had the curiosity to make an error treatment of these as soon as the package came out .Net, until today I hope some function wants to allow it, the answer is that it is not possible to pick up, not simply the way that you either.

I found this code , 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

It says it works in the 2010 version ...

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

This class would be responsible for logging, it can write where you think best, and still talk to other classes.

UPDATE

I'll give you an example of how you can create a Trace class.

Thisisatestproject,Ididitnow...

FormCode:

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 the buttons it will write in the list.

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

Could you visualize more or less how it can be done?