How do I know which cell a function is called from?
Hi Murami,
Application.Caller.Address
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"Murami" wrote in message ...
|I am calling a function like the following
|
| Function getcurrpos(arg1 As Variant) As String
| getcurrpos = Application.ActiveCell.Address
| End Function
|
| arg1 is simply a dummy -- the first time I call the function I type in the
| cell reference of any cell other than the one I am calling my function from.
| eg I call the function in B5 and the argument is D6
|
| The result of the function is $B$5 as I might expect.
|
| BUT ....
|
| If I change the value in D6 then the function refires and the location is
| not the calling cell B5 but the cell of the argument that was just selected.
| This is because D6 is the active cell at this time.
|
| What I really need is a way to tell which cell the function was called from
| in the first place, which probably means that I shouldn't use ActiveCell but
| use something else instead. Does anyone know how I can do this? Does the
| function have any knowledge of its locality at all?
|
| thanks for your thoughts
|
| cheers
|
| Murami
|