How can a VB function determine the cell within which it is runnin

[Guest]

I currently have a VB function that takes a string argument and a column
number argument. It scans a key column for the string (using the MATCH
worksheet function) and returns the the value at column number in the matched
row:
=kval("XXXXX", COLUMN(E1))
for example, to get the cell value from column E of the row that contains
"XXXXX" in the key column.

Frequently, I use the function to return a value from the same column that
the function reference is in:
=kval("XXXXX", COLUMN())

I would like to be able to compute the value that COLUMN() (or ROW()) would
supply within the function itself. How can I do this?

 

[Guest]

Try Application.Caller. For example:

Function kval(strTest As String, Optional lngCol As Long = 0) As Long
With Application
.Volatile True
If lngCol = 0 And TypeName(.Caller) = "Range" Then
lngCol = .Caller.Column
End If
End With

kval = lngCol

End Function


Perhaps you've simplified your explanation for our benefit, but have you
considered using built in lookup functions?

Since you are using match within your UDF, I would think you should be able
to make it work w/INDEX. For example:
=INDEX(DataCol, MATCH("XXXXX", KeyCol, 0))

 

[Guest]

Thank you very much, this is exactly what I'm looking for.

To your question, my function is intended to be a wrapper for built in
functions so I can simplify cell expression syntax. The application involves
several hundred such references, so any content reduction I can make
simplifies life a lot.