Where was I called from?

P

Prof Wonmug

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

Rick Rothstein

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

JLGWhiz

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
 
N

Niek Otten

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.
 
P

Prof Wonmug

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

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?)?
 
P

Prof Wonmug

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
Click to expand...

Thanks. I'll have to study that one.
 
P

Prof Wonmug

Look at Application.Caller (.Address)
Click to expand...

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.
Click to expand...

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

Rick Rothstein

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.
 
C

Charles Williams

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
 
Top