Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function to determine if a cell contains a formula | Excel Discussion (Misc queries) | |||
Function to determine whether cell contains a formula? | Excel Worksheet Functions | |||
Value of cell to determine range in MAX Function | Excel Worksheet Functions | |||
Is there a way to determine which cell the function =min() used? | Excel Worksheet Functions | |||
Is there a function to determine whether a cell contains a formul. | Excel Worksheet Functions |