Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get Name of Current VBA Function

Tags:

vba

ms-access

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 
like image 639
maxhugen Avatar asked Sep 25 '10 02:09

maxhugen


People also ask

What is a procedure name in VBA?

VBA Naming Conventions Procedure NamesName them after what they're doing, using a verb. If accurately naming a procedure is not possible, likely the procedure is doing too many things and needs to be broken down into smaller, more specialized procedures.

Is there a Find function in VBA?

The FIND function of VBA searches for a specified value in the range defined by the user. To search, a VBA code is written by entering some or all arguments of the FIND function. One can specify the direction of search, order of search, data to be searched, the format of the search value, and so on.

How to Get user name in VBA?

Excel VBA Get Username from System Environment VariablesOpen Command prompt and type 'Set' and press Enter. This command will display list of all the Environment variables and values stored in it. In this list, we have an Environment variable “USERNAME” and it will have the active logged in username for that session.


1 Answers

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: VBA Error "Bubble Up")

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.

like image 190
jtolle Avatar answered Sep 28 '22 07:09

jtolle