Problem with UDF

[Liosan]

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

 

[Guest]

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

 

[Liosan]

Thanks Simon, that's exactly what I needed :

 

[Dave Peterson]

How about:

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