Referring to the Cell where a User Defined function is invoked

[DanBlum]

I have a user defined function that I have written. It is designed to be used in a table in the rows that display certain results. Rows 1-4 of the table are header rows which contains constants (dates and values) that apply to the remaining rows of that particular column. Rows 6 thru n have my user defined function and result in a value in that row and column. I am having trouble referencing the constants in the table. In the code segment below, RowNo, is the row number where the function is invoked. It is passed in as a parameter to the function. I tried the code below to pick up the constant that would be in row 3 of the "current" column. But I am getting surprising results. Some time I get the value in row 3 and sometimes row 2. I suspect my problem is that ActiveCell is not necessarily the cell from which the function is invoked....and that is my problem. How do I reference the cell where the function is invoked?

    ActiveCell.Offset(2 - RowNo, 0).Value

Also, I want to get the standard reference of the cell. For instance, I want something like
CurrentCell.col to return "G" not 7. . . if the current cell is in column G. (or do I have to do this conversion myself?) CurrentCell.row does not have this problem. I want this so that I no longer need to pass RowNo as a parameter to the function.

Thanks in advance for helping.

 

[AlphaFrog]

Don't you want your UDF to have a range argument for the constant in the header? That range would be Absolute row and relative column e.g.; A$2, so the column changes as you drag-copy the formula.

This is how to get the column letter from a cell reference

ColLetter = Split(Cells(1, 7).Address, "$")(1)

 

[Rick Rothstein]

I suspect my problem is that ActiveCell is not necessarily the cell from which the function is invoked....and that is my problem. How do I reference the cell where the function is invoked?

You are correct... the ActiveCell is the cell that is select on the worksheet, not the cell the formula is in. You will want to use Application.Caller... when called from a UDF, it is a reference to the cell the formula is in. So, if you wanted the column letter designation for the cell your UDF is in, you would get it like this (using what AlphaFrog posted as a base)...

ColLetter = Split(Application.Caller.Address, "$")(1)

 

[DanBlum]

Thanks. I prefer not to put any arguments in the header...if I can help it. But I had thought about that as a workaround to my problem. I think the Application.Caller approach is exactly what I was looking for. Thanks to both of you.