Hello people,

If I have a user-defined function that I use to, say, calculate
cumulative total (I know I don't need VBA for this, it's just a
example). Is there any way I can retrieve the address of the cell wher
the UDF was called? or determine whether it was called by the use
inside the worksheet or by a diferent function?

If the answer to the first question is no, I will have to pass the cel
address explicitly as a parameter of the UDF. Whats the best way o
doing this? The obvious (for me) solution - passing the column and ro
numbers as integers - seems cumbersome. Is it possible to use the A
notation here?

Thanks for any help,

Liosa

--
Liosa
-----------------------------------------------------------------------
Liosan's Profile: http://www.excelforum.com/member.php...fo&userid=2629
View this thread: http://www.excelforum.com/showthread.php?threadid=39639

Liosan

The Application.Caller method gives you the address of the cell from which
VBA was called. See the Help for some caveats though...

To get the address of a cell you can use the CELL worksheet function, e.g.
=CELL("address",E5) in E5 will give $E$5
--
HTH

Simon


"Liosan" wrote:


Hello people,

If I have a user-defined function that I use to, say, calculate a
cumulative total (I know I don't need VBA for this, it's just an
example). Is there any way I can retrieve the address of the cell where
the UDF was called? or determine whether it was called by the user
inside the worksheet or by a diferent function?

If the answer to the first question is no, I will have to pass the cell
address explicitly as a parameter of the UDF. Whats the best way of
doing this? The obvious (for me) solution - passing the column and row
numbers as integers - seems cumbersome. Is it possible to use the A1
notation here?

Thanks for any help,

Liosan


--
Liosan
------------------------------------------------------------------------
Liosan's Profile: http://www.excelforum.com/member.php...o&userid=26296
View this thread: http://www.excelforum.com/showthread...hreadid=396395

Thanks Simon, that's exactly what I needed :

--
Liosa
-----------------------------------------------------------------------
Liosan's Profile: http://www.excelforum.com/member.php...fo&userid=2629
View this thread: http://www.excelforum.com/showthread.php?threadid=39639

How about:

If TypeOf Application.Caller Is Range Then
'called from cell
Else
'not called from cell
End If

Liosan wrote:

Hello people,

If I have a user-defined function that I use to, say, calculate a
cumulative total (I know I don't need VBA for this, it's just an
example). Is there any way I can retrieve the address of the cell where
the UDF was called? or determine whether it was called by the user
inside the worksheet or by a diferent function?

If the answer to the first question is no, I will have to pass the cell
address explicitly as a parameter of the UDF. Whats the best way of
doing this? The obvious (for me) solution - passing the column and row
numbers as integers - seems cumbersome. Is it possible to use the A1
notation here?

Thanks for any help,

Liosan

--
Liosan
------------------------------------------------------------------------
Liosan's Profile: http://www.excelforum.com/member.php...o&userid=26296
View this thread: http://www.excelforum.com/showthread...hreadid=396395

--

Dave Peterson