asked on

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

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

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Dettman (EE MVE)

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.

stephenlecomptejr

ASKER

One more situation - can I do this in Excel VBA? Sorry Jim, they won't let me use the library at my work.

Bill Prew

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

»bp

stephenlecomptejr

ASKER

This is so cool!

Jim Dettman (EE MVE)

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.

stephenlecomptejr

ASKER

Thank you both!

Anders Ebro (Microsoft MVP)

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