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

This works for me. I am on 2010.

ErrorHandler:
    Dim procName As String
    procName = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
    MyErrorHandler err, Me.Name, getUserID(), procName
    Resume Exithere

Answer 2

Code is ugly but it works. This example will add error handling code to each function that also contains a string with the function name.

Function AddErrorCode()
    Set vbc = ThisWorkbook.VBProject.VBComponents("Module1")
    For VarVBCLine = 1 To vbc.codemodule.CountOfLines + 1000
        If UCase(vbc.codemodule.Lines(VarVBCLine, 1)) Like UCase("*Function *") And Not (UCase(vbc.codemodule.Lines(VarVBCLine, 1)) Like UCase("*Function FunctionReThrowError*")) Then
            If Not (vbc.codemodule.Lines(VarVBCLine + 1, 1) Like "*Dim VarFunctionName As String*") Then
                     vbc.codemodule.InsertLines VarVBCLine + 1, "Dim VarFunctionName as String"
                     vbc.codemodule.InsertLines VarVBCLine + 2, "VarFunctionName = """ & Trim(Mid(vbc.codemodule.Lines(VarVBCLine, 1), InStr(1, vbc.codemodule.Lines(VarVBCLine, 1), "Function") + Len("Function"), Len(vbc.codemodule.Lines(VarVBCLine, 1)))) & """"
                    VarVBCLine = VarVBCLine + 3
            End If
        End If
         If UCase(vbc.codemodule.Lines(VarVBCLine, 1)) Like UCase("*End Function*") Then
            If Not (vbc.codemodule.Lines(VarVBCLine - 1, 1) Like "*Call FunctionReThrowError(Err, VarFunctionName)*") And Not (UCase(vbc.codemodule.Lines(VarVBCLine - 1, 1)) Like UCase("*Err.Raise*")) Then
                vbc.codemodule.InsertLines VarVBCLine, "ErrHandler:"
                vbc.codemodule.InsertLines VarVBCLine + 1, "Call FunctionReThrowError(Err, VarFunctionName)"
                VarVBCLine = VarVBCLine + 2
            End If
        End If
    Next VarVBCLine
   If Not (vbc.codemodule.Lines(1, 1) Like "*Function FunctionReThrowError(ByVal objError As ErrObject, PasFunctionName)*") Then
        vbc.codemodule.InsertLines 1, "Function FunctionReThrowError(ByVal objError As ErrObject, PasFunctionName)"
        vbc.codemodule.InsertLines 2, "Debug.Print PasFunctionName & objError.Description"
        vbc.codemodule.InsertLines 3, "Err.Raise objError.Number, objError.Source, objError.Description, objError.HelpFile, objError.HelpContext"
        vbc.codemodule.InsertLines 4, "End Function"
    End If
End Function

Answer 3

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

Answer 5

vbWatchdog is a commercial solution to the problem. It is very reasonably priced for its capabilities. Among other features it offers full access to the VBA call stack. I know of no other product that does this (and I've looked).

There are several other features including variable inspection and custom error dialog boxes, but the access to the stack trace alone is worth the price of admission.

NOTE: I am in no way affiliated with the product except that I am an extremely satisfied user.

Answer 6

Seriously? Why do developers continue to solve the same problem over and over again? Send get the procedure name into the Err object using Err.Raise...

For the Source parameter pass in:

Me.Name & "." & Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)

I know it's not the shortest one liner but if you can't afford a commercial product to enhance the VBA IDE or if, like many of us, are restricted to working in a locked down environment then this is the easiest solution.