How do I know which cell a function is called from?
Function getcurrpos() As String
getcurrpos = Application.ThisCell.Address
End Function
if you're using xl2003 or later.
"Murami" wrote:
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
|