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

Why not just enter this formula in the cell?:

=ADDRESS(ROW(),COLUMN())

It will return the address in absolute format, so if you type it into
B9 it will return $B$9.

Hope this helps.

Pete

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

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

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