We help IT Professionals succeed at work.

In Access or Excel VBA - how to debug.print the current sub or function name I'm utilizing?

stephenlecomptejr
on
In Access or Excel VBA - how to debug.print the current sub or function name I'm utilizing?
As an explanation please note the following code:

Let's say I do in the Immediate window, I type Debug.Print UserName
as regards also giving me my windows sign-in name I also want it to print the name of the function also

Is there anyway to do this via code?
Cause then I wouldn't have to manually type the "modCommon @ UserName_feo" for my error log for each and every sub and function I have?

'grabs windows user sign in
Public Function UserName() As String
On Error GoTo Err_Proc

  Dim sValue As String
  sValue = ""
  Dim WshNetwork As Object
  Set WshNetwork = CreateObject("WScript.Network")
  sValue = WshNetwork.UserName
  Set WshNetwork = Nothing
Exit_Proc:

 '*** this is the line I'd like to get working...
  'Debug.Print (<---- the name of the function?)

  UserName_feo = sValue
  Exit Function
    
Err_Proc:
  Call LogError_feo(Err.Number, Err.Description, "modCommon @ UserName")
  Resume Exit_Proc
    
End Function

Open in new window

Comment
Watch Question

Test your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:
I've looked into this before and while it seemed like there were some "hacks" proposed on sites I found, none of them actually worked in the real world.  The bottom line I came away with was that VBA doesn't expose the current procedure name, and there was no way to get at it even with some trickery.

It's been a while since I visited the topic, maybe something new has happened, although I doubt it.  So from what I know the answer to your question is that there is no way to do this automatically.

My approach is typically to use a consistent variable (or Const name) and assign it at the top of each Function or Subroutine.  Then if I want that in error handling or debug logging I can always reference that same variable name.  But I had to assign it myself.  I seem to recall using my third party code editor (SlickEdit) and regular expressions to add the variable to each proc right after it was defined with a Function or Subroutine statement.  But it took a little while to get it right since there can be Public and Private etc to weed out, and I know I don't still have the regex (which was a bit editor specific anyway).  I'd only consider that if you had many procs to do this in, otherwise just do it manually.

Another approach to adding in that variable can be found below, but for my needs it was too complicated and not worth digesting at the time.



»bp
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Outside of using vbaWatchDog:

https://www.everythingaccess.com/vbwatchdog/introduction.htm

which hooks into the internals of VBA, you need to do something along the lines of what bill outlined (either a constant or variable).

Jim.

Author

Commented:
One more situation - can I do this in Excel VBA?  Sorry Jim, they won't let me use the library at my work.
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
You can use the work around I proposed in Excel VBA, yes.


»bp

Author

Commented:
This is so cool!
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Something else you might want to do, although if you can't use a library, you might be stuck.

VBA has an undocumented VBA.ERL  property, which gives you the line number of the error.   All you need to do is use line numbers:

130           oXL.Workbooks.Open (strFilepath)
140           oXL.ActiveSheet.Name = "Recon 945 Data"
150           oXL.ActiveSheet.Columns("A:A").NumberFormat = "mm/dd/yyyyy hh:mm"
160           oXL.ActiveSheet.Columns.AutoFit

170           oXL.ActiveWorkbook.Save
180           oXL.Workbooks.Close
190           oXL.Quit

 and then in your error handler:

Error_Procedure:
320       UnexpectedError ModuleName, RoutineName, Version, Err.Number, Err.Description, Err.Source, VBA.Erl
330       Resume Exit_Procedure

  Really helps to pinpoint what is triggering an error.   MZ Tools (which used to be free, but even with not is still quite reasonable) can number all the lines in a project for you.  There's also code floating around to do that which you can put in your applications.

Jim.

Author

Commented:
Thank you both!
Anders Ebro (Microsoft MVP)Microsoft Developer
CERTIFIED EXPERT

Commented:
I use MZTools. It has an error handler template, and when you "use" that, it automatically can fill in the proc name, and module name. Of course it is still a static string, so it doesn't get auto-updated if your change the name of your proc, but it does make it alot easier to have error handling.
If you want a free alternative to vbWatchdog, you can have a look at my crash reporter code found here:
Access Crash Reporter