Is there a way for a VBA/Excel function to identify the
cell (row and column) from which a call is made to it.

For example

A1: MyFunc( $B1)
A2: MyFunc( $B2)
A3: MyFunc( $B3)
etc.

Function MyFunc( X as Integer) as Integer
MyFunc = RowCalledFrom() + ColumnCalledFrom + X
End Function

So, is there something to perform the role of the mythical
functions RowCalledFrom() and ColumnCalledFrom()?

One alternative would be to change MyFunc a bit to pass the
row and column explicitly:

A1: MyFunc2( Row(), Column(), $B1)
A2: MyFunc2( Row(), Column(), $B2)
etc.

Function MyFunc2(
R as Intgeger, C as Integer, X as Integer) as Integer
MyFunc = R + C + X
End Function

But it would be nice if there a way for MyFunc to know the
row and column without explicitly being passed them. Is there?

Note that this has nothing to do with the Active cell or
the Selection.

Thanks. --David.

David,

Application.Caller will return a Range reference to the cell from
which the function is called.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"David Lewis" wrote in
message ...
Is there a way for a VBA/Excel function to identify the
cell (row and column) from which a call is made to it.

For example

A1: MyFunc( $B1)
A2: MyFunc( $B2)
A3: MyFunc( $B3)
etc.

Function MyFunc( X as Integer) as Integer
MyFunc = RowCalledFrom() + ColumnCalledFrom + X
End Function

So, is there something to perform the role of the mythical
functions RowCalledFrom() and ColumnCalledFrom()?

One alternative would be to change MyFunc a bit to pass the
row and column explicitly:

A1: MyFunc2( Row(), Column(), $B1)
A2: MyFunc2( Row(), Column(), $B2)
etc.

Function MyFunc2(
R as Intgeger, C as Integer, X as Integer) as Integer
MyFunc = R + C + X
End Function

But it would be nice if there a way for MyFunc to know the
row and column without explicitly being passed them. Is there?

Note that this has nothing to do with the Active cell or
the Selection.

Thanks. --David.