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

747skipper

New member
Local time
Today, 14:49
Joined
Sep 30, 2019
Messages
13
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
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:49
Joined
May 21, 2018
Messages
8,463
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

Super Moderator
Staff member
Local time
Today, 10:49
Joined
Jan 23, 2006
Messages
15,364
Do you have any output from any error handler?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:49
Joined
May 21, 2018
Messages
8,463
Here is a centralized error handler (which I personally will never use)

Code:
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
Code:
Exit_cmdClickMe_Click:
    Exit Sub
    
Err_cmdClickMe_Click:
    Call ErrorLog(Err.Description, Err.Number, Me.Name & " cmdClickMe_Click")
    Resume Exit_cmdClickMe_Click
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:49
Joined
Sep 21, 2011
Messages
14,044
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

Well-known member
Local time
Today, 23:49
Joined
Jan 6, 2022
Messages
1,489
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've got your good things, and you've got mine.
Local time
Today, 10:49
Joined
May 21, 2018
Messages
8,463
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.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:49
Joined
May 21, 2018
Messages
8,463
@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.
 

KitaYama

Well-known member
Local time
Today, 23:49
Joined
Jan 6, 2022
Messages
1,489
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:49
Joined
Feb 28, 2001
Messages
26,999
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:49
Joined
Feb 28, 2001
Messages
26,999
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

You've got your good things, and you've got mine.
Local time
Today, 10:49
Joined
May 21, 2018
Messages
8,463
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

Super Moderator
Staff member
Local time
Today, 10:49
Joined
Jan 23, 2006
Messages
15,364
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’m here to help
Staff member
Local time
Today, 07:49
Joined
Oct 29, 2018
Messages
21,358
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...
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:49
Joined
Feb 19, 2002
Messages
42,970
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.
 

MarkK

bit cruncher
Local time
Today, 07:49
Joined
Mar 17, 2004
Messages
8,178
The code that raises an error controls what text appears in the VBA.ErrObject's .Source and .Description properties. As a result, if you're disciplined in how you compose your error handling, you can produce a faux stack trace in the Source property of every error. Consider code like...
Code:
Sub TestCustomErrorSourceDescription()
On Error GoTo handler
    Debug.Print GetResult(10)
    Exit Sub
handler:
    ' print error showing customizations
    Debug.Print Err, Err.Source, Err.Description
End Sub

Public Function GetResult(Number As Single) As Single
On Error GoTo handler
    GetResult = 10 * DivideByZero(Number)
    Exit Function
handler:
    Err.Raise Err, "GetResult() | " & Err.Source
End Function

Public Function DivideByZero(Divisor As Single) As Single
On Error GoTo handler
    DivideByZero = Divisor / 0
    Exit Function
handler:
    Err.Raise Err, "DivideByZero() | " & Err.Source
End Function
See how each error handler can identify itself in the VBA.ErrObject.Source property.
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:49
Joined
Jan 23, 2006
Messages
15,364
@MarkK,
You have quite a collection of code snippet gems!;)

@Pat Hartman
Who is Roger?? I'm missing something. If you're referring to vbWatchdog, that's Wayne Phillips.
 

Users who are viewing this thread

Top Bottom