Problem with UDF
 

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

Problem with UDF
 

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

Problem with UDF
 

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

Problem with UDF
 

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