Get Name of Current VBA Function

Question

For error handling code, I would like to get the name of the current VBA function (or sub) that the error occurred in. Does anyone know how this could be done?

[EDIT] Thanks all, I had hoped that an undocumented trick existed to self-determine the function, but that obviously doesn't exist. Guess I'll stay with my current code:

Option Compare Database: Option Explicit: Const cMODULE$ = "basMisc"

Public Function gfMisc_SomeFunction$(target$)
On Error GoTo err_handler: Const cPROC$ = "gfMisc_SomeFunction"
    ...
exit_handler:
    ....
    Exit Function
err_handler:
    Call gfLog_Error(cMODULE, cPROC, err, err.Description)
    Resume exit_handler
End Function

Answer 1

Not using any built-in VBA way. The best you'll be able to do is repeat yourself by hardcoding the method name as a constant or regular method-level variable.

Const METHOD_NAME = "GetCustomer"

 On Error Goto ErrHandler:
 ' Code

ErrHandler:
   MsgBox "Err in " & METHOD_NAME

You may be able to find something handy in the MZ Tools for VBA. It's a developer add-in for the VB family of languages. Written by an MVP.

Answer 2

VBA doesn't have any built-in stack trace that you can access programatically. You'd have to design your own stack and push/pop onto that to accomplish something similar. Otherwise, you'll need to hard code your function/sub names into the code.

Answer 3

There's nothing to get the current function name, but you can build a fairly lightweight tracing system using the fact that VBA object lifetimes are deterministic. For example, you can have a class called 'Tracer' with this code:

Private proc_ As String

Public Sub init(proc As String)
    proc_ = proc
End Sub

Private Sub Class_Terminate()
    If Err.Number <> 0 Then
        Debug.Print "unhandled error in " & proc_
    End If
End Sub

and then use that class in routines like:

Public Sub sub1()
    Dim t As Tracer: Set t = New Tracer
    Call t.init("sub1")

    On Error GoTo EH

    Call sub2

    Exit Sub

EH:
    Debug.Print "handled error"
    Call Err.Clear
End Sub

Public Sub sub2()
    Dim t As Tracer: Set t = New Tracer
    Call t.init("sub2")

    Call Err.Raise(4242)
End Sub

If you run 'sub1', you should get this output:

unhandled error in sub2
handled error

because your Tracer instance in 'sub2' was deterministically destroyed when the error caused an exit from the routine.

This general pattern is seen a lot in C++, under the name "RAII", but it works just fine in VBA too (other than the general annoyance of using classes).

EDIT:

To address David Fenton's comment that this is a relatively complicated solution to a simple problem, I don't think the problem is actually that simple!

I'm taking it for granted that we all agree that we don't want to give every single routine in our VBA program its own error handler. (See my reasoning here: http://stackoverflow.com/questions/1418777/vba-error-bubble-up/1419147#1419147)

If some internal routines don't have their own error handlers, then when we do catch an error, all we know is that is happened in the routine with the error handler that fired or in a routine somewhere deeper in the call stack. So the problem as I understand it is really one of tracing the execution of our program. Tracing routine entry is easy of course. But tracing exit can indeed be quite complicated. For example, there might be an error that gets raised!

The RAII approach allows us to use the natural behavior of VBA object life management to recognize when we've exited a routine, whether through an 'Exit', 'End', or error. My toy example is just meant to illustrate the concept. The real "tracer" in my own little VBA framework is certainly more complex, but also does more:

Private Sub Class_Terminate()
    If unhandledErr_() Then
        Call debugTraceException(callID_, "Err unhandled on exit: " & fmtCurrentErr())
    End If

    If sendEntryExit_ Then
        Select Case exitTraceStatus_
            Case EXIT_UNTRACED
                Call debugTraceExitImplicit(callID_)
            Case EXIT_NO_RETVAL
                Call debugTraceExitExplicit(callID_)
            Case EXIT_WITH_RETVAL
                Call debugTraceExitExplicit(callID_, retval_)
            Case Else
                Call debugBadAssumption(callID_, "unrecognized exit trace status")
        End Select
    End If
End Sub

But using it is still pretty simple, and amounts to less boilerplate than the "EH in every routine" approach anyway:

Public Function apply(functID As String, seqOfArgs)
    Const PROC As String = "apply"
    Dim dbg As FW_Dbg: Set dbg = mkDbg(MODL_, PROC, functID, seqOfArgs)

...

Automatically generating the boilerplate is easy, although I actually type it in and then automatically check to make sure routine/arg names match as part of my tests.

Answer 4

I use the error handler button within the free MZTools for VBA. It automatically adds the lines of code along with the sub/function name. Now if you rename the sub/function you have to remember to change the code.

MZTools has many nice functions built in as well. Such as an improved find screen and the best of all is a button showing you all the places where this sub/function is called.