What property do I need to qeury to obtain the name of the workbook,
sheet, and cell where a UDF or macro was called?

Try Worksheet.name in the Immediate Window

Prof Wonmug wrote:

What property do I need to qeury to obtain the name of the workbook,
sheet, and cell where a UDF or macro was called?

Are these what you are looking for...ActiveWorkBook.Name, ActiveSheet.Name,
ActiveCell.Address?

--
Rick (MVP - Excel)


"Prof Wonmug" wrote in message
...
What property do I need to qeury to obtain the name of the workbook,
sheet, and cell where a UDF or macro was called?

Here is something out of the VBA help files.

This example displays information about how Visual Basic was called.

Select Case TypeName(Application.Caller)
Case "Range"
v = Application.Caller.Address
Case "String"
v = Application.Caller
Case "Error"
v = "Error"
Case Else
v = "unknown"
End Select
MsgBox "caller = " & v


"Prof Wonmug" wrote in message
...
What property do I need to qeury to obtain the name of the workbook,
sheet, and cell where a UDF or macro was called?

Look at Application.Caller (.Address)
Remember that the active sheet/book doesn't have to be the one that a UDF
was called from
It all depends on what you're trying to achieve. Maybe you should post that.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Prof Wonmug" wrote in message
...
What property do I need to qeury to obtain the name of the workbook,
sheet, and cell where a UDF or macro was called?

On Sat, 9 May 2009 15:48:44 -0400, "Rick Rothstein"
wrote:

Are these what you are looking for...ActiveWorkBook.Name, ActiveSheet.Name,
ActiveCell.Address?

Yup. Thanks.

In these examples, is the first field called an "object" and the
second a "property"?

Is there a nice concise list of the first field, with sublists for the
second (and third?)?

On Sat, 9 May 2009 16:01:36 -0400, "JLGWhiz"
wrote:

Here is something out of the VBA help files.

This example displays information about how Visual Basic was called.

Select Case TypeName(Application.Caller)
Case "Range"
v = Application.Caller.Address
Case "String"
v = Application.Caller
Case "Error"
v = "Error"
Case Else
v = "unknown"
End Select
MsgBox "caller = " & v


"Prof Wonmug" wrote in message
.. .
What property do I need to qeury to obtain the name of the workbook,
sheet, and cell where a UDF or macro was called?

Thanks. I'll have to study that one.

On Sat, 9 May 2009 22:29:15 +0200, "Niek Otten"
wrote:

Look at Application.Caller (.Address)

Both of those get an error in the immediate window when the UDF is
running.

Remember that the active sheet/book doesn't have to be the one that a UDF
was called from
It all depends on what you're trying to achieve. Maybe you should post that.

I'm just trying to determine which cell in which sheet called the UDF.
Rick's solution gave me what I needed.

In these cases, yes; however, if the second ones ever performed an action as
opposed to simply returning a value, then they would be called methods.

--
Rick (MVP - Excel)


"Prof Wonmug" wrote in message
...
On Sat, 9 May 2009 15:48:44 -0400, "Rick Rothstein"
wrote:

Are these what you are looking for...ActiveWorkBook.Name,
ActiveSheet.Name,
ActiveCell.Address?

Yup. Thanks.

In these examples, is the first field called an "object" and the
second a "property"?

Is there a nice concise list of the first field, with sublists for the
second (and third?)?

Rick's solution only works if the UDF is called from whatever sheet happens
to be the active sheet:
If you call the UDF from 2 separate worksheets it is bound to give the wrong
answer.

As Niek was pointing out inside a UDF you need:

Application.Caller.Address for the address of the cell that called the UDF
Application.Caller.Parent.Name for the name of the worksheet containing the
cell that called the UDF

(Application.Caller returns a Range object referring to the cell that called
the UDF)

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Prof Wonmug" wrote in message
...
On Sat, 9 May 2009 22:29:15 +0200, "Niek Otten"
wrote:

Look at Application.Caller (.Address)

Both of those get an error in the immediate window when the UDF is
running.

Remember that the active sheet/book doesn't have to be the one that a UDF
was called from
It all depends on what you're trying to achieve. Maybe you should post
that.

I'm just trying to determine which cell in which sheet called the UDF.
Rick's solution gave me what I needed.