Welcome to Windows Help!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

VBA: Variable with name of function?

 
   Windows Help (Home) -> Microsoft Excel RSS
Next:  How to code a batch file for backup?  
Author Message
Prof Wonmug

External


Since: Jan 10, 2010
Posts: 5



(Msg. 1) Posted: Sun Jan 10, 2010 2:19 pm
Post subject: VBA: Variable with name of function?
Archived from groups: microsoft>public>excel (more info?)

Is there a variable containing the name of the VBA function currently
running?

I'd like to put it in the title of my Msgbox calls to automatically
identify where the information is coming:

Msgbox "Text of message",,vbFunName

 >> Stay informed about: VBA: Variable with name of function? 
Back to top
Login to vote
Rick Rothstein

External


Since: Nov 18, 2008
Posts: 7



(Msg. 2) Posted: Sun Jan 10, 2010 5:54 pm
Post subject: Re: Variable with name of function?
Archived from groups: per prev. post (more info?)

No, but you can do what you want with some code. Dim a variable in a Module
so that it can be seen by the rest of your code no matter where that code
is, then set this variable to whatever text you want within **every** sub
and function (set the value to the empty string for those subs and functions
that you don't need to report the name from).

 >> Stay informed about: VBA: Variable with name of function? 
Back to top
Login to vote
Prof Wonmug

External


Since: Jan 10, 2010
Posts: 5



(Msg. 3) Posted: Sun Jan 10, 2010 5:54 pm
Post subject: Re: Variable with name of function?
Archived from groups: per prev. post (more info?)

On Sun, 10 Jan 2010 12:54:38 -0500, "Rick Rothstein"
wrote:

>No, but you can do what you want with some code. Dim a variable in a Module
>so that it can be seen by the rest of your code no matter where that code
>is, then set this variable to whatever text you want within **every** sub
>and function (set the value to the empty string for those subs and functions
>that you don't need to report the name from).

How about this in any function that needs it?

Public Function MyUDF(values As Range, Optional F)
Dim FnName As String: FnName = "MyUDF"

Too bad VBA doesn't provide a vbFunName variable. Clearly it knows the
name. Why not make it available? (This is rhetorical. I know no one
here has any influence on MSFT.)
 >> Stay informed about: VBA: Variable with name of function? 
Back to top
Login to vote
Rick Rothstein

External


Since: Nov 18, 2008
Posts: 7



(Msg. 4) Posted: Sun Jan 10, 2010 6:37 pm
Post subject: Re: Variable with name of function?
Archived from groups: per prev. post (more info?)

No (if I understand your suggested code correctly)... if you declare FnName
within your function, then only your function can see it. That is why I said
to declare (Dim) it in a Module... doing that will make it visible to any
code procedure in your project. Then, all you need to do in each of your
functions or subroutines (or event procedures if you want to include them)
is this...

FnName = "MyUDF"

In order to get a better idea of how this variable (function and subroutines
as well) visibility works, you might want to search for "Understanding Scope
and Visibility" from the VB editor's search box and then select
"Understanding Scope and Visibility" from the list that appears.
 >> Stay informed about: VBA: Variable with name of function? 
Back to top
Login to vote
Prof Wonmug

External


Since: Jan 10, 2010
Posts: 5



(Msg. 5) Posted: Sun Jan 10, 2010 6:37 pm
Post subject: Re: Variable with name of function?
Archived from groups: per prev. post (more info?)

On Sun, 10 Jan 2010 13:37:15 -0500, "Rick Rothstein"
wrote:

>No (if I understand your suggested code correctly)... if you declare FnName
>within your function, then only your function can see it. That is why I said
>to declare (Dim) it in a Module... doing that will make it visible to any
>code procedure in your project. Then, all you need to do in each of your
>functions or subroutines (or event procedures if you want to include them)
>is this...
>
>FnName = "MyUDF"
>
>In order to get a better idea of how this variable (function and subroutines
>as well) visibility works, you might want to search for "Understanding Scope
>and Visibility" from the VB editor's search box and then select
>"Understanding Scope and Visibility" from the list that appears.

I think I generally understand variable scope. My purpose is simply to
put the name of the active function in the title of Msgboxes so I know
which function issued the Msgbox. I would think that a local variable
would do that, no?

PS: It makes it nearly impossible to follow the thread when the answer
is put at the top of the reply, rather than inserted into the existing
text.
 >> Stay informed about: VBA: Variable with name of function? 
Back to top
Login to vote
Chip Pearson

External


Since: Dec 21, 2008
Posts: 3



(Msg. 6) Posted: Mon Jan 11, 2010 4:44 pm
Post subject: Re: VBA: Variable with name of function?
Archived from groups: per prev. post (more info?)

There no way to get the name of the currently executing procedure.
However, you can add a Constant to each procedure with a value of the
procedure's name. I have complete code to automate this at
[URL="http://www.cpearson.com/Excel/InsertProcedureNames.aspx"]http://www.cpearson.com/Excel/InsertProcedureNames.aspx[/URL]

This will add to each procedure the following declaration:

Const C_PROC_NAME = "TheProcedureName"

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
[URL="http://www.cpearson.com"]www.cpearson.com[/URL]
[email on web site]




On Sun, 10 Jan 2010 09:19:55 -0800, Prof Wonmug wrote:

>Is there a variable containing the name of the VBA function currently
>running?
>
>I'd like to put it in the title of my Msgbox calls to automatically
>identify where the information is coming:
>
> Msgbox "Text of message",,vbFunName
 >> Stay informed about: VBA: Variable with name of function? 
Back to top
Login to vote
Harlan Grove

External


Since: Dec 4, 2008
Posts: 4



(Msg. 7) Posted: Tue Jan 12, 2010 10:31 pm
Post subject: Re: Variable with name of function?
Archived from groups: per prev. post (more info?)

Prof Wonmug wrote...
....
>I think I generally understand variable scope. My purpose is simply to
>put the name of the active function in the title of Msgboxes so I know
>which function issued the Msgbox. I would think that a local variable
>would do that, no?

You can do that directly. If the function itself has the Msgbox
statement, this only requires

Msgbox Title:=current_function_name_here, . . .

where current_function_name_here is a placeholder for the function
name as a literal string. Else if you're displaying Msgbox's in called
subroutines, make the function name an argument to the subroutine and
pass the current procedure's name to the subroutine.

To be fair, there are few lightweight languages which provide tokens/
keywords which evaluate to the current procedure's name.

>PS: It makes it nearly impossible to follow the thread when the answer
>is put at the top of the reply, rather than inserted into the existing
>text.

This is a religious issue in Excel newsgroups. Welcome to a world
where the majority believes in a flat earth, the Easter Bunny, that
the check is indeed in the mail, and that top-posting responses makes
sense.
 >> Stay informed about: VBA: Variable with name of function? 
Back to top
Login to vote
Rick Rothstein

External


Since: Nov 18, 2008
Posts: 7



(Msg. 8) Posted: Wed Jan 13, 2010 2:09 am
Post subject: Re: Variable with name of function?
Archived from groups: per prev. post (more info?)

The main problem I had in trying to answer your question was trying to
imagine how you were going to use what you asked for. If all you want to do
is have the function or subroutine announce itself to the world, you don't
need any variable for that... just put in a MsgBox statement and hard code
the function or subroutine's name as its argument. Since you asked about
using a variable, I imagined there might be a catch-all routine which, for
whatever reason, needed to know what function or subroutine that called it.
To do that, you would need a variable with enough scope so that it could be
seen by all the possible subroutines or functions in your project; hence, my
suggestion to use a Module to declare this "global" variable in. Then,
having every routine (except your catch-all routine, of course) write its
name to the variable would allow a catch-all routine to know what function
or subroutine called it because it could examine the same "global" variable.
Anyway, that was the thrust behind my previous responses.

As for top versus bottom posting... that seems to be a newsgroup determined
standard. Before I started volunteering here in the Excel newsgroups, I
volunteered over in the compiled version of VB newsgroups. There, they
bottom posted as you suggested; however, when I came to the Excel
newsgroups, I found the standard was to top post the replies. Either can
work as long as the OP and the various responders stays in sync (all either
posting responses to the top or the bottom)... the real problem comes when
the OP ignores the method used by the responder and uses the opposite
method, thus jumbling the sequential order of the responses.
 >> Stay informed about: VBA: Variable with name of function? 
Back to top
Login to vote
Prof Wonmug

External


Since: Jan 10, 2010
Posts: 5



(Msg. 9) Posted: Wed Jan 13, 2010 3:29 am
Post subject: Re: Variable with name of function?
Archived from groups: per prev. post (more info?)

On Tue, 12 Jan 2010 17:31:11 -0800 (PST), Harlan Grove
wrote:

>Prof Wonmug wrote...
>...
>>I think I generally understand variable scope. My purpose is simply to
>>put the name of the active function in the title of Msgboxes so I know
>>which function issued the Msgbox. I would think that a local variable
>>would do that, no?
>
>You can do that directly. If the function itself has the Msgbox
>statement, this only requires
>
>Msgbox Title:=current_function_name_here, . . .
>
>where current_function_name_here is a placeholder for the function
>name as a literal string.

That's fine unless I change the name of the function. I suppose I
could use the Replace facility, but I'd probably forget as often as
not. Having a Dim and assignment statements right after the function
name is more likely to catch my eye.

>Else if you're displaying Msgbox's in called
>subroutines, make the function name an argument to the subroutine and
>pass the current procedure's name to the subroutine.
>
>To be fair, there are few lightweight languages which provide tokens/
>keywords which evaluate to the current procedure's name.
>
>>PS: It makes it nearly impossible to follow the thread when the answer
>>is put at the top of the reply, rather than inserted into the existing
>>text.
>
>This is a religious issue in Excel newsgroups. Welcome to a world
>where the majority believes in a flat earth, the Easter Bunny, that
>the check is indeed in the mail, and that top-posting responses makes
>sense.

OK.
 >> Stay informed about: VBA: Variable with name of function? 
Back to top
Login to vote
Prof Wonmug

External


Since: Jan 10, 2010
Posts: 5



(Msg. 10) Posted: Wed Jan 13, 2010 3:31 am
Post subject: Re: Variable with name of function?
Archived from groups: per prev. post (more info?)

On Tue, 12 Jan 2010 21:09:38 -0500, "Rick Rothstein"
wrote:

>The main problem I had in trying to answer your question was trying to
>imagine how you were going to use what you asked for. If all you want to do
>is have the function or subroutine announce itself to the world, you don't
>need any variable for that... just put in a MsgBox statement and hard code
>the function or subroutine's name as its argument. Since you asked about
>using a variable, I imagined there might be a catch-all routine which, for
>whatever reason, needed to know what function or subroutine that called it.
>To do that, you would need a variable with enough scope so that it could be
>seen by all the possible subroutines or functions in your project; hence, my
>suggestion to use a Module to declare this "global" variable in. Then,
>having every routine (except your catch-all routine, of course) write its
>name to the variable would allow a catch-all routine to know what function
>or subroutine called it because it could examine the same "global" variable.
>Anyway, that was the thrust behind my previous responses.

I mainly wanted to know if there was a built-in variable with the
name. I got that answer (sigh). Such an easy feature to include.

>As for top versus bottom posting... that seems to be a newsgroup determined
>standard. Before I started volunteering here in the Excel newsgroups, I
>volunteered over in the compiled version of VB newsgroups. There, they
>bottom posted as you suggested; however, when I came to the Excel
>newsgroups, I found the standard was to top post the replies. Either can
>work as long as the OP and the various responders stays in sync (all either
>posting responses to the top or the bottom)... the real problem comes when
>the OP ignores the method used by the responder and uses the opposite
>method, thus jumbling the sequential order of the responses.

I don't want to start a war, but I can see zero advantages to top
posting.
 >> Stay informed about: VBA: Variable with name of function? 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
Function "Indirect" - od morning, I am Italian. I have a problem with Excel , if you have time to answer to me: I have to convert a great sheet with direct formulas to an Indirect one like that: 1 a b c d e 2 1 3 1 4 1 5 1 6 = sum (A1:A5) ...

INDEX MATCH HELL ooops.. I mean HELP! - I know (or at least I think I do) that I can do this with INDEX MATCH. Here is what I want to do. I want to place this formula in cell C4 of Sheet 1. What I want this formula to do is to match the value of B2 of Sheet 1 with a list that is in Sheet 2...

Notation Bugs - Did anybody notice that Excel handles math notations (symbols) in an incorrect way? Try this: write -2 in cell A1. Write =1-A1^2 in a different cell. Write =-A1^2+1 in another cell. The two expressions are equivalent, shouldn't they? Well, the former..

How to create a single line separated by commas from a mat.. - Hello, I've the following problem. I've a table made of several columns. Each colum represents a characteristic regarding to a subject (e.g. Name, Surname, Home Street, etc...). In the rows I put the different people. I need to export these info to a....

Excel password Recovery - Hi All, i forgot my excel password, can any tell me how to recover the password there is any free tool is avail in web, Regards, Deen
   Windows Help (Home) -> Microsoft Excel All times are: Pacific Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]