What is name of macro being used

[PBAPaul]

I have been inspired by Nikita6003's response to "Run excel macro in batch??" to create my own logging routine.

What I want to do in an error logging routine is to call it with the name of the macro that initiated the error but realise that I do not know how to find the name of the macro being used.

I want to do the following:

Sub ThisMacro
on error goto Errorhandler
my code .....
Errorhandler:
ThisMacro = ?? Name of this macro??
Call MyErrorRoutine ThisMacro,error details
end sub

Any ideas please?

 

[RobBroekhuis]

I don't think there is an automatic property you can use. But you can certainly declare a public variable:

Public ThisMacro

and in each of your subs, before the on error goto statement, put

ThisMacro="MyFirstMacro"

etc.
Rob

 

[PBAPaul]

Thank you Bob

I was hoping that there was an automatic property. It would then make my proposed routine almost self contained.

 

[rmikesmith]

PBAPaul,

I'm sure Rob is correct. This has come up in other forums. I have done essentially what Rob is advocating in my own code, passing the name of a procedure to an error handler. Not the ideal solution, but do-able.


Regards,
Mike

 

[Nikita6003]

Hiya PBAPaul, here's the actual routine I use:

Public Sub Errorhandler(p_errError As Error, p_sCallingModule As String)
'My error handling routine - either writes to a database or to a text file;
'if it runs in the overnight batch it also writes warnings/errors to an excel sheet so users can check progress
'If it doesn't I popup a message box specifying error & calling route
End Sub

And yes - it's called with the macro name pasted by me:

Call Errorhandler(Err, "ImportDeltaBloomberg")

The only other way might be using the VBA Extensibility object model (just add the reference to your xl project) but I've never really looked at this in any great detail - I know you can get names on a MODULE level but I think you can't for Procs wihin that module.

Here's a couple of link with some interesting stuff on using the VBE

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

http://www.erlandsendata.no/english/vba/vbe/index.htm

http://gioconda.di.uminho.pt/UMLe/archive/documents/vbaddins.pdf

Good luck - & if you find out anything plz report back

Cheers
Nikki

 

[PBAPaul]

Nikki

Thanks for the post, but it's all your fault that I started this!:-D

I have done a bit of digging without any success. I am now thinking about other possible ways of getting the operating macro name.

I will definitely post a solution if I find one.

Regards

Paul