Getting Procedure Name

[Neil]

Can I get the name of a procedure from within the procedure? In my error
handler, I write the error to an error table. I'd like to write the name of
the procedure that's writing the error. But, rather than customizing each
error handler with the procedure name, it would be nice to be able to call a
system variable or function that gives me the procedure name and module
name. Is that possible?

[Allen Browne]

Unfortunately, VBA does not expose the name of the executing procedure, nor
the name of that module.

In case you are not aware, there is a great little utility you can download
from:
www.mztools.com
Choose the one for VBA.
Install.
It adds a toolbar to the VBA window.
You can now drop your error handler into the current procedure just by
clicking the toolbar button. And it's configurable.

For the module name, what I personally do is to declare a private constant
in the General Declarations section of every module, and assign it the name
of that module, e.g.:
Private Const conModName = "Form_frmInvoice"
Use the constant name in your error handler where you want it to pass the
module name. This arrangement requires no change to the code at all when you
copy'n'paste procedures between modules.

(You can use Module.Name, but that fails in an MDE.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Neil" <no****@nospam.netwrote in message
news:gt*****************@newssvr21.news.prodigy.ne t...

Can I get the name of a procedure from within the procedure? In my error
handler, I write the error to an error table. I'd like to write the name
of the procedure that's writing the error. But, rather than customizing
each error handler with the procedure name, it would be nice to be able to
call a system variable or function that gives me the procedure name and
module name. Is that possible?

[John Spencer]

You do realize that you just posted copyrighted material.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===
bobh wrote:

On Nov 15, 10:20 am, "Neil" <[e-mail]:

>Can I get the name of a procedure from within the procedure? In my error
handler, I write the error to an error table. I'd like to write the name of
the procedure that's writing the error. But, rather than customizing each
error handler with the procedure name, it would be nice to be able to call a
system variable or function that gives me the procedure name and module
name. Is that possible?

Hi, there is this module I use - originaly from Access97 Developers
handbook. I have modified it a bit but it still works and I use it in
all my XP apps today.
bobh.

Option Compare Database
Option Explicit

' From Access 97 Developer's Handbook by Litwin, Getz, and Gilbert.
(Sybex)
' Copyright 1997. All Rights Reserved.

Function adhClipboardSetText(strText As String) As Variant
' Puts some text on the Windows clipboard
' From Access 97 Developer's Handbook by Litwin, Getz, and Gilbert.
(Sybex)
' Copyright 1997. All Rights Reserved.

Public Sub adhTestClipboard()
' Tests putting some text on the clipboard then reading it off again
' From Access 97 Developer's Handbook by Litwin, Getz, and Gilbert.
(Sybex)
' Copyright 1997. All Rights Reserved.

Public Sub adhReportClipboardError(ByVal intError As Integer)
' Reports an error received from the clipboard
' From Access 97 Developer's Handbook by Litwin, Getz, and Gilbert.

Public Function adhClipboardGetText() As Variant
' Gets some text on the Windows clipboard
' From Access 97 Developer's Handbook by Litwin, Getz, and Gilbert.
(Sybex)
' Copyright 1997. All Rights Reserved.
' Out:

[Neil]

Thanks, Allen. I remember looking at Mztools some time ago and deciding not
to get it. I'll have to have a second look. Thanks!

Neil

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:47***********************@per-qv1-newsreader-01.iinet.net.au...

Unfortunately, VBA does not expose the name of the executing procedure,
nor the name of that module.

In case you are not aware, there is a great little utility you can
download from:
www.mztools.com
Choose the one for VBA.
Install.
It adds a toolbar to the VBA window.
You can now drop your error handler into the current procedure just by
clicking the toolbar button. And it's configurable.

For the module name, what I personally do is to declare a private constant
in the General Declarations section of every module, and assign it the
name of that module, e.g.:
Private Const conModName = "Form_frmInvoice"
Use the constant name in your error handler where you want it to pass the
module name. This arrangement requires no change to the code at all when
you copy'n'paste procedures between modules.

(You can use Module.Name, but that fails in an MDE.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Neil" <no****@nospam.netwrote in message
news:gt*****************@newssvr21.news.prodigy.ne t...

>Can I get the name of a procedure from within the procedure? In my error
handler, I write the error to an error table. I'd like to write the name
of the procedure that's writing the error. But, rather than customizing
each error handler with the procedure name, it would be nice to be able
to call a system variable or function that gives me the procedure name
and module name. Is that possible?

[James A. Fortune]

John Spencer wrote:

You do realize that you just posted copyrighted material.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===

See:

U.S. Copyright Office - Fair Use:

http://www.copyright.gov/fls/fl102.html

I would argue that the effect of the use here would be to increase the
potential market for the book rather than decrease it. Then again,
after seeing the amount of code posted, purportedly to get the name of
the procedure, it might have a negative affect :-).

James A. Fortune
MP*******@FortuneJames.com

[Arch]

On Fri, 16 Nov 2007 16:31:55 -0500, "James A. Fortune"
<MP*******@FortuneJames.comwrote:

>John Spencer wrote:

>You do realize that you just posted copyrighted material.

'================================================ ====
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================ ====

See:

U.S. Copyright Office - Fair Use:

http://www.copyright.gov/fls/fl102.html

I would argue that the effect of the use here would be to increase the
potential market for the book rather than decrease it. Then again,
after seeing the amount of code posted, purportedly to get the name of
the procedure, it might have a negative affect :-).

James A. Fortune
MP*******@FortuneJames.com

I suspect that if any of those guys (Litwin, Getz, or Gilbert) were
approached, they would readily grant permission to post that function.
I can't agree, however, that this falls under "Fair Use" guidelines. I
think it is rather blatant copyright violation.

My 2 cents worth,
Arch

[lyle]

On Nov 15, 10:20 am, "Neil" <[e-mail]:

Can I get the name of a procedure from within the procedure? In my error
handler, I write the error to an error table. I'd like to write the name of
the procedure that's writing the error. But, rather than customizing each
error handler with the procedure name, it would be nice to be able to call a
system variable or function that gives me the procedure name and module
name. Is that possible?

It would be a couple of hours work I suppose to write code that would
put into each procedure as line two and three:
Dim VBProcedureName$
VBProcedureName = "Module13:Temp"
for Procedure "Temp" in Module 13.
or whatever. I suppose one would want the Module13 bit to
differentiate among various Temp private subs in various Modules.

But to what purpose?
I write my own error handling code only when I feel I need to be sure
that nothing non-VBAish is left dangling. A low level file handle is
one of these. Somewhere in the mists of time when Access was only a
gleam in the Wicked Witch of the West's eye I learned that these
should be closed. So when I open one, I do it within error handling
that will close it on error.
But almost none of my modules require that kind of fail-safe garbage
deletion. And almost none has its own error-handling.
Yes, I know, thousands of developers who write code superior to mine
are going to protest here. Such is life.

Having run the code once, then what? If we run it again we'll get
Dim VBProcedureName$
VBProcedureName = "Module13:Temp"
Dim VBProcedureName$
VBProcedureName = "Module13:Temp"

which will raise a compile error. So now we have to be sure the lines
are not already there. And if we change the name of a procedure ... I
suspect that ifs just keep rolling on here.

[Neil]

I don't know -- I just put an error handler in every proc, and it's worked
out well for me. For things like closing files or other things that I always
want to do, I put those under the exit label, and have the error handler
transfer to there.

In any case, you gave me an idea with the below: I could use a proc name
variable in the error handler, and set the variable at the top of the proc.
That would allow me to use a standard template for error handlers (with a
default proc name of ""), and then fill in the names as needed (in the more
problematic routines). Probably, if I had the VBProcName = "" in the
template, I'd just automatically fill it in when I start a new proc, anyway.

But, you know, it would be simple to do something like this with a Word
macro -- just plop the whole module into Word, record/write a macro that
does what's needed, and then plop the module text back into Access. Only
problem is: losing the link to event procedures. If I could be sure that the
links to control's event procedures would remain in place after I paste in
the new procs from Word, then this would be fine. But I've had bad
experiences with that in the past. Any notes/tips/etc. on trying to get the
procs to remain linked or to relink them after an event such as this?

Thanks!

Neil

"lyle" <ly************@gmail.comwrote in message
news:21**********************************@i37g2000 hsd.googlegroups.com...

On Nov 15, 10:20 am, "Neil" <[e-mail]:

>Can I get the name of a procedure from within the procedure? In my error
handler, I write the error to an error table. I'd like to write the name
of
the procedure that's writing the error. But, rather than customizing each
error handler with the procedure name, it would be nice to be able to
call a
system variable or function that gives me the procedure name and module
name. Is that possible?

It would be a couple of hours work I suppose to write code that would
put into each procedure as line two and three:
Dim VBProcedureName$
VBProcedureName = "Module13:Temp"
for Procedure "Temp" in Module 13.
or whatever. I suppose one would want the Module13 bit to
differentiate among various Temp private subs in various Modules.

But to what purpose?
I write my own error handling code only when I feel I need to be sure
that nothing non-VBAish is left dangling. A low level file handle is
one of these. Somewhere in the mists of time when Access was only a
gleam in the Wicked Witch of the West's eye I learned that these
should be closed. So when I open one, I do it within error handling
that will close it on error.
But almost none of my modules require that kind of fail-safe garbage
deletion. And almost none has its own error-handling.
Yes, I know, thousands of developers who write code superior to mine
are going to protest here. Such is life.

Having run the code once, then what? If we run it again we'll get
Dim VBProcedureName$
VBProcedureName = "Module13:Temp"
Dim VBProcedureName$
VBProcedureName = "Module13:Temp"

which will raise a compile error. So now we have to be sure the lines
are not already there. And if we change the name of a procedure ... I
suspect that ifs just keep rolling on here.

[Deano]

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:47***********************@per-qv1-newsreader-01.iinet.net.au...

Unfortunately, VBA does not expose the name of the executing procedure,

nor

the name of that module.

In case you are not aware, there is a great little utility you can

download

from:
www.mztools.com

Ta for the tip on that utility. Just being able to clear the immediate
window with a single click is useful :)

[Neil]

Does it have the ability to *close* the Immediate window with a single
click? I've always hated how you can press Ctrl+G to open the Immediate
window; but pressing Ctrl+G a second time doesn't close it. I hate having to
close it with the mouse all the time. That would be a great feature if it
had it!
"Deano" <de***@mailinator.comwrote in message
news:5q************@mid.individual.net...

>
"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:47***********************@per-qv1-newsreader-01.iinet.net.au...

>Unfortunately, VBA does not expose the name of the executing procedure,

nor

>the name of that module.

In case you are not aware, there is a great little utility you can

download

>from:
www.mztools.com

Ta for the tip on that utility. Just being able to clear the immediate
window with a single click is useful :)

[Deano]

"Neil" <no****@nospam.netwrote in message
news:uw**************@nlpi061.nbdc.sbc.com...

Does it have the ability to *close* the Immediate window with a single
click? I've always hated how you can press Ctrl+G to open the Immediate
window; but pressing Ctrl+G a second time doesn't close it. I hate having

to

close it with the mouse all the time. That would be a great feature if it
had it!

No it doesn't do that. Given that Access doesn't seem to support the
feature it seems unlikely that this toolkit would either.

[Neil]

Well, I was hoping for a miracle. You can't blame a fellow for dreaming, can
you? :-)

"Deano" <de***@mailinator.comwrote in message
news:5q************@mid.individual.net...

>
"Neil" <no****@nospam.netwrote in message
news:uw**************@nlpi061.nbdc.sbc.com...

>Does it have the ability to *close* the Immediate window with a single
click? I've always hated how you can press Ctrl+G to open the Immediate
window; but pressing Ctrl+G a second time doesn't close it. I hate having

to

>close it with the mouse all the time. That would be a great feature if it
had it!

No it doesn't do that. Given that Access doesn't seem to support the
feature it seems unlikely that this toolkit would either.

[Deano]

"Neil" <no****@nospam.netwrote in message
news:gq***************@nlpi070.nbdc.sbc.com...

Well, I was hoping for a miracle. You can't blame a fellow for dreaming,

can

you? :-)

Nah, guess not. Useful download though, haven't tried out all the stuff but
being able to drop in pre-written error handlers etc is useful. I tested to
make sure it wouldn't screw anything up and so far it looks like it works
just fine.

[Neil]

I just have error handlers stored in files, and I drop them in using Insert
| File. But being able to customize the error handler according to the proc
name would be useful.

"Deano" <de***@mailinator.comwrote in message
news:5q************@mid.individual.net...

>
"Neil" <no****@nospam.netwrote in message
news:gq***************@nlpi070.nbdc.sbc.com...

>Well, I was hoping for a miracle. You can't blame a fellow for dreaming,

can

>you? :-)

Nah, guess not. Useful download though, haven't tried out all the stuff
but
being able to drop in pre-written error handlers etc is useful. I tested
to
make sure it wouldn't screw anything up and so far it looks like it works
just fine.