Knowing the name of the current sub in code (1 Viewer)

[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

 

[isladogs]

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]

@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.

 

[MajP]

I have a top level error handler to close a form

The sub's error handler should pass its name to the centralized error handler method as an argument of the centralized error handler.

 

[jdraw]

Do you have any output from any error handler?

 

[MajP]

Here is a centralized error handler (which I personally will never use)

Function ErrorLog(strErrDesc As String, lngErrNum As Long, strWhereFrom As String) As Boolean
On Error GoTo Err_ErrorLog
    
    Dim strMsg As String
    
    ErrorLog = True
    
    ' Call the LogErrorToTable function to record the error in a table
    Call LogErrorToTable(strErrDesc, lngErrNum, strWhereFrom)
    
    
    strMsg = strMsg & "There has been an error in the application." & vbCrLf & vbCrLf
    strMsg = strMsg & "Error Number: " & lngErrNum & vbCrLf
    strMsg = strMsg & "Error Description: " & strErrDesc & vbCrLf
    strMsg = strMsg & "Error Location: " & strWhereFrom & vbCrLf & vbCrLf
    strMsg = strMsg & "Please note the above information has been written to the application Error Log." & vbCrLf & vbCrLf
    
    MsgBox strMsg, vbInformation, "Error Log."

    Exit Function

Err_ErrorLog:
    
    ErrorLog = False
    
    MsgBox Err.Description, vbExclamation, "Error detected."

End Function

Here is how you would call it

Exit_cmdClickMe_Click:
    Exit Sub
    
Err_cmdClickMe_Click:
    Call ErrorLog(Err.Description, Err.Number, Me.Name & " cmdClickMe_Click")
    Resume Exit_cmdClickMe_Click

 

[Gasman]

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

Not a weird request at all.
In fact you would think it would be a given, exactly for that reason.

 

[KitaYama]

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

@MajP Once I tried to do something like OP and I failed because I couldn't return the sub or function name.
You have passed the name of the sub as a string. It means if the same error log is going to be run from another sub, you have to edit the strWherefrom parameter.

Me and possibly the OP need the name of the sub to be passed automatically. Is there any possible way?

 

[MajP]

You have passed the name of the sub as a string. It means if the same error log is going to be run from another sub, you have to edit the strWherefrom parameter

Yes, so what is your point? You have to put error handling code in the sub and call the centralized error handler. So spend the extra millisecond passing the argument. Your logic makes no sense.

Me and possibly the OP need the name of the sub to be passed automatically. Is there any possible way?

No.

 

[KitaYama]

So spend the extra millisecond passing the argument.

When you have a lot of forms and modules, the milliseconds count.

No.

Thanks

 

[MajP]

@MajP Once I tried to do something like OP and I failed because I couldn't return the sub or function name.

And, I bet you spent a ton more time trying and failing then it would take to go back and pass in an additional argument to an error handler.
If you had hundreds or thousands of modules in some enormous database then you could write code this VBA extensibility to update your code with the argument.

VBA Visual Basic Editor - Extensibility

VBA Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com

 

[KitaYama]

And, I bet you spent a ton more time trying and failing then it would take to go back and pass in an additional argument to an error handler.

I really appreciate your input. I use MZ-Tools and that was why I was trying to do so. Because MZ-Tools adds the name of the subs and functions to headings and it doesn't need the user to spend milliseconds on it.
Yes, I spent a lot of time and failed. But I enjoyed every second of it and I learnt it's not possible.

I only thought someone with your experience may have a solution.

 

[jdraw]

@MajP
Can you tell us your rationale for (which I personally will never use)?

 

[The_Doc_Man]

There is something on the program stack that holds the name of the current procedure because you can do a "View Stack" and see it. However, you would have to do some things that are not directly available to users to programmatically examine the contents of the stack. Doing so would violate something in the EULA about "reverse-engineering" - and MS really doesn't like it when someone attempts to do so. Access remains a "black box" to us precisely because MS wants it that way.

I did some research on this and the consensus is "No" unless you do some complex things on your own, usually using another language than VBA.

 

[jdraw]

Wayne Phillips (EverythingAccess.com ) offers a commercial, global error handling routine.

 

[The_Doc_Man]

My previous answer is incorrect; not that you can't do this, but WHY you can't. I was not thinking it through. Here is why you cannot know where an error occurred using a generic handler.

The reason it is not possible is because of the way Access (and, for that matter, Windows itself) handles errors. There is one and ONLY one place to get information about the subroutine in which an error occurs. That is from the failing sub/function itself.

In Access, you only have code that runs subroutines via Events because the MAIN segment is the code of MSACCESS.EXE itself. Let's imagine that you have established the "catch-all" handler, probably via the Form_Error event. (You can't establish it in Access MAIN code because that is not available to you.) Regardless of the exact method of establishing the generic handler, you trigger the faulty code sequence because some other event happened.

Now say that "normal" event XXX triggers the Form_XXX event routine, which in the normal course of operation calls sub A, which in turn calls sub B. Let's say for sake of argument that sub B takes an error. Let us further say that none of sub A, sub B, and the Form_XXX event code have declared error handlers, and thus your Form_Error handler has to catch this one. But what happens between the time that the actual error occurs and the time that the event handler catches the error?

After an error in sub B, standard VBA error handling will look for a handler declared in sub B, but we said there isn't one. That means that the context of B is REMOVED from the call stack. The error is unsatisfied so therefore is passed along to A, but we said sub A has no handler either. Therefore, the context of A is ALSO removed from the stack and the error is resignaled up the call stack. What about Form_XXX event? No handler, we said, so that event routine context goes away too. Finally, there is an error handler, your generic handler code. BUT by the time the generic error handler gains control, the context of the faulty routine AND its two layers of caller are ALL gone from the stack. There is no way to know what routine actually failed by the time the generic routine catches the error.

Therefore, the ONLY solution that CAN precisely identify the error location down to the routine-name level is to have low-level error handlers with some identifying information in them. MajP says he never used or will use the variation that he showed where your local event handler includes the name of the sub in its call to a generic error handler, but I have used at least a variant of what he showed and it DOES work. Kind of tedious to set up - but absolutely IS a possible solution.

 

[MajP]

Can you tell us your rationale for (which I personally will never use)?

I have tried a lot of centralized error handling routines and found they are more work than benefit. I use MZTOOLS so it is very easy for me to add error harndlers to all procedures.

 

[jdraw]

I think it would be possible to customize the Error Handler in MzTOOLS V8 to make a call to a central logger. I'm not saying it's a good idea, just that it seems possible without too much effort. It's so easy to add ErrorHandler code with MZTools.
I noted that KitaYama said I use MZ-Tools and that was why I was trying to do so. So opening MZTools options and adding some code to call the logging function would seem to address things. I think you would need to include user, datetime stamp to the fields/data being recorded. Especially with multi users. An interesting idea in that you could query the logTable to identify recurring errors that may help with training and/or communications.

Perhaps someone on the forum with MZTools has tried similar or has time to experiment.

 

[theDBguy]

I use MZ-Tools and that was why I was trying to do so. Because MZ-Tools adds the name of the subs and functions to headings and it doesn't need the user to spend milliseconds on it.

JMHO, I'm pretty sure the makers of MZ-Tools are very smart. And I'm guessing if there's a way to automatically get the name of the Sub, they probably wouldn't have created their tool to "hard code" the function and sub names in the error handlers. So, I am thinking that was the best they could offer, since it's probably not possible or very easy to do if done the other way. Just my 2 cents...

 

[Pat Hartman]

I also don't use central error handlers. However, that doesn't mean that I don't do centralized logging of errors. The point is that each procedure needs to collect whatever data the central procedure requires and the central procedure just logs the data passed to it. The centralized procedure has one and only one logical path. The only option would be to email or not and the calling procedure needs to specify that option depending on how critical a particular error is. The initial error handler makes the decision as to whether to continue or to cancel the procedure after logging the error.

I would be interested in knowing if Roger's function can identify the failing procedure when it has no error handling. If a low level procedure raises an error and there is no error handler, control is passed up the line to the lowest level procedure that does have error handling and that is the procedure that informs you of the error.