How can a VB function determine the cell within which it is runnin
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? |
How can a VB function determine the cell within which it is runnin
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? |
How can a VB function determine the cell within which it is ru
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? |
All times are GMT +1. The time now is 06:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com