Knowing the name of the current sub in code

[747skipper]

OK a bit of a weird request, but does anyone know if I can obtain the name of the current sub or function at run time, in code?

I have a top level error handler to close a form and return to menu, acting as a back stop if an error I haven't allowed for occurs.
This covers several subs and functions and it would be really useful to know which one triggered it. (think line numbers in pre history)

I could declare a global variable and set it in each sub but I wondered if there is an easier way?

Thanks

 

[jdraw]

Colin,

Do you keep or have you tried a central logger file/table (centralized logging of errors.) that Pat mentioned in #18?

 

[jdraw]

For anyone interested, here is a youtube video Presentation at AccessUsergroup vbWatchdog by Peter Cole.

 

[isladogs]

Colin,

Do you keep or have you tried a central logger file/table (centralized logging of errors.) that Pat mentioned in #18?

Yes I have.
In fact, I first used the code in post #21 when I was setting up centralised error logging to a table.
The code also created an automatic email which was sent to me with full details of the error who/what/where/when etc together with Access & Windows versions in use
NOTE: The automated emails were sent 'silently' using CDO so end users weren't aware of it happening. However I made sure I had the agreement of my clients. They were able to opt out but none chose to do so

 

[747skipper]

Wow what a lot of replies. Thank you all

@MajP
That's the sort of thing I wanted BUT when I call a sub and pass me.name it passes the form name not the sub name.

@KitaYama
Mz-Tools sounds good but I'm only doing odd jobs for a charity, I am no longer a professional programmer.

@MarkK
Thanks, Yes I follow your reasoning but I was trying to avoid having to code the name of the sub into each error handler.
I could just as well declare a global variable and set it in each sub.

@The_Doc_Man
Thank you for your full explanation. It makes great sense and explains why I can't take the lazy way out but need to code the name manually in each sub. I had hoped there was and object, like me.name in MajP's answer, which made the name of the sub available in code but it seems there isn't.

Thank you all

 

[isladogs]

@747skipper
Did you try the code I supplied in post #21? If not, do see whether it works for you

 

[Gasman]

There is another way of doing this which I've used in several apps for some time
Its not 100% reliable. For example, it doesn't work in Form_Open events

1. Add the reference Microsoft Visual Basic for Applications Extensibility 5.3

2. In your error handler, add code like this:

strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
MsgBox "Error " & Err & " in procedure " & strProc & " :  " & vbCrLf & Err.Description

For example:

Option Compare Database
Option Explicit

Dim strProc As String

Private Sub cmd1_Click()

On Error GoTo Err_Handler
   
    'raise error
    Err.Raise 91
   
Exit_Handler:
    Exit Sub
   
Err_Handler:
    strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
    MsgBox "Error " & Err & " in procedure " & strProc & " :  " & vbCrLf & Err.Description
    Resume Exit_Handler
End Sub

Private Sub cmd2_Click()
 
    On Error GoTo Err_Handler
             
    'raise error
    Dim N As Integer
    N = 3500 / 0   'error 11

   '  Err.Raise 11
   
Exit_Handler:
    Exit Sub
   
Err_Handler:
    strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
    MsgBox "Error " & Err & " in procedure " & strProc & " :  " & vbCrLf & Err.Description
    Resume Exit_Handler
End Sub

I've also experimented putting the strProc line immediately after the On Error line as well as in the error handler

@isladogs
Colin, that works for me without that reference?

? Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
cmdAdd_Click

 

[MajP]

@MajP
That's the sort of thing I wanted BUT when I call a sub and pass me.name it passes the form name not the sub name.

NO that is not correct. It will pass whatever string you send it. I said to pass the form name and the name of the procedure. You must have done something wrong.

Call ErrorLog(Err.Description, Err.Number, Me.Name & " cmdClickMe_Click")

 

[isladogs]

@isladogs
Colin, that works for me without that reference?
View attachment 100533

? Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
cmdAdd_Click

Hi Paul
Thanks for confirming the code worked for you
I know it works in most cases despite many experienced developers stating here (and elsewhere) that it is impossible to get the name of the current procedure in code.

I'm surprised that worked without the reference but doing that in the Immediate window isn't the same as using it in error handling code.
Perhaps you could try again with error handling code similar to that I provided.
It may not be quite so reliable in that situation

 

[The_Doc_Man]

@isladogs - Colin, I have a question. The original goal of this thread was to find out which line of code faulted using a generic routine. The example you showed appeared to be within an event routine. I accept that from that event routine you would be able to look for the line of code using the VBE references as you stated. However, if that event routine happens to not contain that handler, isn't it the case that an external handler won't see the location of the error because it is now out of scope?

I'm not questioning that it is possible for a local error routine to determine a specific line. I'm questioning the ability of a non-local routine to do it.

You could still do that lookup locally from the error handler and then call a generic logging/annunciation handler - but doesn't SOMETHING have to still be local at the time of the error in order to get that information?

 

[Gasman]

Hi Paul
Thanks for confirming the code worked for you
I know it works in most cases despite many experienced developers stating here (and elsewhere) that it is impossible to get the name of the current procedure in code.

I'm surprised that worked without the reference but doing that in the Immediate window isn't the same as using it in error handling code.
Perhaps you could try again with error handling code similar to that I provided.
It may not be quite so reliable in that situation

No, it still does?

 

[isladogs]

@The_Doc_Man
My interpretation of this thread was that the main goal was to find out the name of the current procedure using code.
That procedure name was then to be passed to a central error logging routine.
I did both of those many years ago

AFAIK, the procedure name can only be determined in the procedure itself
I tried to determine it as part of the central error logging routine to save a lot of repeated code . . . but was unsuccessful

So my generic error handling code in forms/reports consisted of:

Private Sub MyProcedureName()

On Error GoTo Err_Handler

    'code here
   
Exit_Handler:
    Exit Sub

Err_Handler:
    'create error message & log
    strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
    PopulateErrorLog
    Resume Exit_Handler
   
End Sub

The PopulateErrorLog procedure is in a standard module and is used to log details of all errors to a table and send a 'silent' email to me for info/action

However. from experience I found that getting the procedure name in Form_Open events didn't work reliably so for those I used:

Private Sub Form_Open(Cancel As Integer)

On Error GoTo Err_Handler

'set information for error log
    strItemName = Me.Name
    SetNewObjectLogItems 'clear previously saved values from other forms/reports & set new
   
'other form open code here e.g. setup automatic form resizing

Exit_Handler:
    Exit Sub

Err_Handler:
    'create error message & log
    strProc = "Form_Open"
    PopulateErrorLog
    Resume Exit_Handler
   
End Sub

The process was a lot of work to setup a few years ago using A2010 BUT was VERY reliable for the intended purpose.
However, I've been unable to create a reliable example app in A365 which is why I haven't posted it.
Usually it works .... but not always.... and I haven't had time to determine why not.

 

[The_Doc_Man]

That answers my question. Thanks, Colin. (We actually agree - gotta be there to see what's there.)

 

[KitaYama]

@The_Doc_Man
My interpretation of this thread was that the main goal was to find out the name of the current procedure using code.
That procedure name was then to be passed to a central error logging routine.
I did both of those many years ago

AFAIK, the procedure name can only be determined in the procedure itself
I tried to determine it as part of the central error logging routine to save a lot of repeated code . . . but was unsuccessful

So my generic error handling code in forms/reports consisted of:

Private Sub MyProcedureName()

On Error GoTo Err_Handler

    'code here
  
Exit_Handler:
    Exit Sub

Err_Handler:
    'create error message & log
    strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
    PopulateErrorLog
    Resume Exit_Handler
  
End Sub

The PopulateErrorLog procedure is in a standard module and is used to log details of all errors to a table and send a 'silent' email to me for info/action

However. from experience I found that getting the procedure name in Form_Open events didn't work reliably so for those I used:

Private Sub Form_Open(Cancel As Integer)

On Error GoTo Err_Handler

'set information for error log
    strItemName = Me.Name
    SetNewObjectLogItems 'clear previously saved values from other forms/reports & set new
  
'other form open code here e.g. setup automatic form resizing

Exit_Handler:
    Exit Sub

Err_Handler:
    'create error message & log
    strProc = "Form_Open"
    PopulateErrorLog
    Resume Exit_Handler
  
End Sub

The process was a lot of work to setup a few years ago using A2010 BUT was VERY reliable for the intended purpose.
However, I've been unable to create a reliable example app in A365 which is why I haven't posted it.
Usually it works .... but not always.... and I haven't had time to determine why not.

@isladogs You're a star. I've been trying to do this for years, and everyone told me it's impossible. You made the impossible, possible.
Million thanks.

Anyhow, as a confirmation, your code works without the mentioned reference. Even from a sub and not immediate window.

Thanks again.

 

[747skipper]

Thanks again Chaps,
@MajP reading your code more carefully I see the "& " cmdClickMe_Click"" which is just what I'm trying to avoid, since I will have to add a different version of this in every sub or function.

@isladogs (love the Lab)
I haven't tried it with the reference yet (because I have to look up how to get to the references option.
Without the reference it returns the name of the form.
I will try it later.

What I have found is this:
I defined an alternative function to replace "Call" passing it the name of the sub or function I wish to call and use CallByName to do the actual call. It doesn't give me a line number but it does tell me the name of the sub which crashed without my having to hard code it in every sub.
Of course it doesn't work for events but it should be fairly easy to work out that if I click something and it crashes that was the problem.

Public Function CallThis(FRM, This As String, Optional param As Variant, Optional RTN As Variant) On Error GoTo ErrorHandler If IsMissing(param) Then result = CallByName(FRM, This, VbMethod) Else result = CallByName(FRM, This, VbMethod, param) End If CallThis = result Exit Function ErrorHandler: MsgBox ("Error " & Err.Number & " " & Err.Description & " happened in sub " & This) End Function

Please don't flame me if the layout doesn't match what you prefer, this isn't a real program just an illustration of a method.
It does handle returning the value from functions and could easily be extended if you call subs with more than one parameter.

 

[sonic8]

There is another way of doing this which I've used in several apps for some time
[...]

strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
MsgBox "Error " & Err & " in procedure " & strProc & " :  " & vbCrLf & Err.Description

I tried your code by copying it to a form class module and also a to normal modules. - Unfortunately, it doesn't work either way. strProc is always an empty string.

 

[Gasman]

I tried your code by copying it to a form class module and also a to normal modules. - Unfortunately, it doesn't work either way. strProc is always an empty string.

Why did it work for me then, only tested in a form module, but even without the reference library?

 

[sonic8]

Why did it work for me then, only tested in a form module, but even without the reference library?

The answer to this question is probably indirectly also the answer to mine.

FTR: I tried the code with and without the reference to the VBA Extensibility library. - It doesn't make any difference for me.

 

[Gasman]

The answer to this question is probably indirectly also the answer to mine.

FTR: I tried the code with and without the reference to the VBA Extensibility library. - It doesn't make any difference for me.

Possible versions then?
I am only on 2007?

 

[sonic8]

Possible versions then?
I am only on 2007?

I don't think the Access version can be relevant here. If it is a versions thing at all, it should be a VBA version or VBA Extensibility version dependency.

I tried with ...
Access 2010, VBA 7.0 (7.0.1640), VBE6EXT (5.3)
Access 365 (V2204), VBA 7.1 (7.1.1119), VBE6EXT (5.3)

But actually, I wonder why this code would work at all. It tries to retrieve the information from the ActiveCodePane of the VBE. What if the VBE was never opened? What if the code is executed in the runtime environment? What if it is in an ACCDE?

 

[NauticalGent]

MarkK,

Although I know the terminology (Call Stack, faux stack trace), I had to copy your code and then set a breakpoint to get a full better understanding of how it works. It was pretty slick to see it "bubbling up" all the way to the calling Sub.