Get name of sub-routine or function

[ineedhelplease]

I have an access application that has nearly 70 procedures or functions that run daily, with more being added as people make changes to the application.

I have created a sub - prcLog(prcName as string) - that will log the start and end of each sub or function, for the purpose of tracking how long each procedure runs along with other reporting metrics. In addition to recording various performance metrics, I will use this sub (prcLog) to identify which sub or function quits running if an error occurs.

At this time I pass the current running sub's name manually to the prcLog sub as shown below:

Dim prcName as string
prcName = "thissub'sname"
prcLog(prcName)

The problem with this is when a new sub is created and if the individual is not paying attention and forgets to change the prcName to match the sub's name, anything logged for that sub is useless.

To the question; is there a way I can get the current subs name with code, so that this can be passed to the prcLog sub dynamically? Or is there a way for the prcLog sub to get the sub or function’s name that has called it?

Any help would be useful.

Thank you.

 

[CautionMP]

ineedhelplease,
This question comes up every couple of months in the forums and I have yet to see a solution. It's too bad because I would love this (and access to the call stack) for error logging purposes.

A couple of quick thoughts. Using a reference to Microsoft Visual Basic for Applications Extensibility 5.3 you could:[ol][li]Write a macro for the VBE that would find all calls to [tt]prcLog()[/tt] and correctly adds the current procedure name as the argument.[/li][li]Write a macro that helps your 'people' to correctly add the call to [tt]prcLog()[/tt][/li][/ol]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Rofeu]

no quick way of doing this as far as I know, but it can be done.

You can obtain a list of procedures in a module, so IF you have 1 function or sub per module you will have it.

Take a look at this site:

http://www.cpearson.com/excel/vbe.htm#ListProcedures

This is obviously not very pratical, but if you can somehow determine the line on which your code is, you should be able to modify the code to give you the current procedure. Not sure how to go about that though.

Cheers,

Roel