Procedure Name as a property?

[Guest]

Is there a way to refer to the currently running module and procedure name in
a msgbox?

 

[Chip Pearson]

Stu,

There is no built in way for a procedure to get its own name. I have code on
my web site that will insert a const declaration in to every procedure. The
value of the const is the name of the procedure in which it resides. See
www.cpearson.com/excel/InsertProcedureNames.aspx . You can then use that
const value in your MsgBox.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Maybe I'm missing something, but I think this falls into the "you have to
know the answer before I can give you the answer" type of thing. I could be
wrong.

But consider that for you to get the name of the current module and
procedure name, that procedure is going to have to be running, and where I
think you're looking for a generic answer like:
MsgBox "Module: " & magicRoutineToGetModuleName & vbCRLF & _
"Routine: " & magicRoutineToGetRoutineName

Since that's going to be in the routine which you are presumably writing,
then you know the answer and I'd just hard code it, since I don't know the
magic... routines in question. Example ... in a module named Module1 (until
you rename it):

Sub TellSecrets()
MsgBox "Module: Module1" & vbCRLF & "Routine: TellSecrets"
End Sub


Now you could set up a module-scope variable ahead of the first routine in a
module like this:

Const thisModuleName = "Module1" ' change as you change module's name

Sub FindSecrets()
Const myRoutineName = "FindSecrets" ' again, change for each routine
'and then you could call a public function to 'tell all'
TellSecrets thisModuleName, myRoutineName
End Sub

Public Function TellSecrets(modName as String, subName as String) as Variant
MsgBox "Module: " & modName & vbCRLF & _
"Routine: " & subName
End Function

 

[Guest]

Thanks, Chip, for your response. By the way, I used your routines for saving
variables to the user registry. Very, very cool. Works great!

Stu W

 

[Gary Keramidas]

just pointing out a typo in a comment

Ensus(r)e the ActiveProject is not locked.

 

[Chip Pearson]

Stu,

I'm glad the you were able to use the code. The server generates reams of
data about site usage (most popular pages, inbound links, length of session,
navigation paths,depth of session, etc), but the one thing it can't do is
tell me whether the readers found what they were looking for. It is always
nice to hear that something on the list was actually useful to a reader.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)