Knowing the name of the current sub in code

[747skipper]

OK a bit of a weird request, but does anyone know if I can obtain the name of the current sub or function at run time, in code?

I have a top level error handler to close a form and return to menu, acting as a back stop if an error I haven't allowed for occurs.
This covers several subs and functions and it would be really useful to know which one triggered it. (think line numbers in pre history)

I could declare a global variable and set it in each sub but I wondered if there is an easier way?

Thanks

 

[sonic8]

Why did it work for me then, only tested in a form module, but even without the reference library?

The answer to this question is probably indirectly also the answer to mine.

FTR: I tried the code with and without the reference to the VBA Extensibility library. - It doesn't make any difference for me.

 

[Gasman]

The answer to this question is probably indirectly also the answer to mine.

FTR: I tried the code with and without the reference to the VBA Extensibility library. - It doesn't make any difference for me.

Possible versions then?
I am only on 2007?

 

[sonic8]

Possible versions then?
I am only on 2007?

I don't think the Access version can be relevant here. If it is a versions thing at all, it should be a VBA version or VBA Extensibility version dependency.

I tried with ...
Access 2010, VBA 7.0 (7.0.1640), VBE6EXT (5.3)
Access 365 (V2204), VBA 7.1 (7.1.1119), VBE6EXT (5.3)

But actually, I wonder why this code would work at all. It tries to retrieve the information from the ActiveCodePane of the VBE. What if the VBE was never opened? What if the code is executed in the runtime environment? What if it is in an ACCDE?

 

[NauticalGent]

MarkK,

Although I know the terminology (Call Stack, faux stack trace), I had to copy your code and then set a breakpoint to get a full better understanding of how it works. It was pretty slick to see it "bubbling up" all the way to the calling Sub.

 

[747skipper]

@MajP
All true. But the object of this query was to learn something.
Even I could write the name of each block into it and arrange to use it in an error handler.
What I wanted to know was was there an equivalent to me.name which would return the sub or function name, and now I know there isn't.
I've also learnt about Call by name (however much you dislike it)
So not entirely a waste of time!

 

[MajP]

All true. But the object of this query was to learn something.

If you want to learn something, then I recommend you learn how to use VB extensibility. You can put your error handler in each sub and then write code to modify the passed parameter so that you do not have to manually modify it. Learn how to write code to write code.

Programming In The VBA Editor

www.cpearson.com

 

[KitaYama]

But actually, I wonder why this code would work at all. It tries to retrieve the information from the ActiveCodePane of the VBE. What if the VBE was never opened? What if the code is executed in the runtime environment? What if it is in an ACCDE?

I think you are correct. If I run the code from a function or Sub in VBE while VBE is open I receive the function name.
But if I run it from a button event while VBE is closed, I receive a blank message box.

I tested it in 365 & 2019
Both the same result,

Edit : In both versions the mentioned reference was not necessary.

 

[isladogs]

Thinking about this further, in my original app for schools that used this code, I did indeed have the VBE open minimised in the background.
The FE was a highly locked down ACCDB file and all users had full versions of Access.
It worked well for its intended purpose at the time and is working in the screenshot below:

However, as I said in post #21 and in other subsequent posts, it certainly isn't 100% reliable.

Most of my code based on VB Extensibility is based on Chip Pearson's article:
Programming In The VBA Editor (cpearson.com)

However this code snippet was something I developed myself...