How to get address of cell calling UDF?
 

I'm drawing a blank....

Within a UDF, how can I get the address of the cell calling the UDF?

(Without passing ADDRESS(ROW(A1),COLUMN(A1)), if A1 is the calling cell.)

ActiveCell.Adress does not work. Activecell is the selected cell or the
first cell in a selected range at the time that the UDF is called.

I want what I would call ThisCell.Address.

For example, suppose I have the following UDF:

Function myudf()
Msgbox ThisCell.Address 'replace ThisCell
End Function

And I call it as follows from 3 cells:

A1: =RAND() + myudf()
A2: =RAND() + myudf()
A3: =RAND() + myudf()

If B1 (or any cell) is selected when I press F9, I expect 3 msgboxes (not
necessarily in order):

$A$1
$A$2
$A$3

How to get address of cell calling UDF?
 

Application.Caller

It returns a Range, so you can get the typical Range information from it.
For example...

Addr = Application.Caller.Address

--
Rick (MVP - Excel)


"JoeU2004" wrote in message
...
I'm drawing a blank....

Within a UDF, how can I get the address of the cell calling the UDF?

(Without passing ADDRESS(ROW(A1),COLUMN(A1)), if A1 is the calling cell.)

ActiveCell.Adress does not work. Activecell is the selected cell or the
first cell in a selected range at the time that the UDF is called.

I want what I would call ThisCell.Address.

For example, suppose I have the following UDF:

Function myudf()
Msgbox ThisCell.Address 'replace ThisCell
End Function

And I call it as follows from 3 cells:

A1: =RAND() + myudf()
A2: =RAND() + myudf()
A3: =RAND() + myudf()

If B1 (or any cell) is selected when I press F9, I expect 3 msgboxes (not
necessarily in order):

$A$1
$A$2
$A$3

How to get address of cell calling UDF?
 

"Rick Rothstein" wrote:
Addr = Application.Caller.Address

Thanks.


----- original message -----

"Rick Rothstein" wrote in message
...
Application.Caller

It returns a Range, so you can get the typical Range information from it.
For example...

Addr = Application.Caller.Address

--
Rick (MVP - Excel)


"JoeU2004" wrote in message
...
I'm drawing a blank....

Within a UDF, how can I get the address of the cell calling the UDF?

(Without passing ADDRESS(ROW(A1),COLUMN(A1)), if A1 is the calling cell.)

ActiveCell.Adress does not work. Activecell is the selected cell or the
first cell in a selected range at the time that the UDF is called.

I want what I would call ThisCell.Address.

For example, suppose I have the following UDF:

Function myudf()
Msgbox ThisCell.Address 'replace ThisCell
End Function

And I call it as follows from 3 cells:

A1: =RAND() + myudf()
A2: =RAND() + myudf()
A3: =RAND() + myudf()

If B1 (or any cell) is selected when I press F9, I expect 3 msgboxes (not
necessarily in order):

$A$1
$A$2
$A$3