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?

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))



"Tony Rollins" wrote:

> 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?

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.

"JMB" wrote:

> 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))
>
>
>
> "Tony Rollins" wrote:
>
> > 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?