How to get the cell value from another worksheet and assign it to the return value of UDF?

Here is the little example of my two worksheets.

Original Sheet:

As you can see, this worksheet has two cells waiting to be recalculated at the later stage. (i.e. cells with red #Eval marker)

Result Sheet:

In the above Result sheet, we have got two results, Strawberry and Peach respectively. These two results are prepared for two #Eval cells and need to replace the #Eval marker.

Note that #Eval is actually returned by the UDF, indicating this cell needs to be recalculated. The recalculation will be initiated manually by clicking a button or something.

Also note that the position is mapped across two sheets -- if #Eval is located in Cell A3, then there will also be a result showing up in Cell A3 in the result sheet as well.

So, my primary goal here is to replace #Eval cell with its corresponding result. But I am having troubles with passing the results into my UDF -- I don't know how to programmatically get the reference / address of cell that is currently being recalculated.

Below is the code I have currently got, which I don't think it's been implemented corr开发者_运维知识库ectly. Can anyone help? Or is there any other way to implement this?

Thanks in advance.

//assign value from result sheet back to result variable 
private string getResultFromResultSheet(Excel.Worksheet originalSheet, Excel.Worksheet resultSheet)
{
    string DataResult = null;            

    //Excel.Range resultSheetRange = resultSheet.UsedRange;
    //string addresse = resultSheetRange.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);

    Excel.Range originalSheetRange = originalSheet.UsedRange;  //<= I think it's incorrect here
    string os_currentAddress = originalSheetRange.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);

    Excel.Range currentRRange = null;

    currentRRange = resultSheet.get_Range(os_currentAddress, Type.Missing);
    if (currentRRange != null)
    {
        DataResult = currentRRange.Text;

    }
    return DataResult;
}

Personally I would go for a rethink and write your UDF the way Excel expects UDFS to be:

• pass ALL data from cells into your UDF as parameters (if you don't do this Excel does not know when to recalculate your UDF and you will have to make the UDF volatile, which is a BAD IDEA)
• the UDF should return its result(s) to the cell(s) it occupies
• as Govert says: if you need the range object for the cells the UDF occupies (usually to find out the dimensions of the calling range) you can use Application.Caller
• don't use .Text (that gives you the formatted result of the cell which is dependent on whatever the user does and may well contain ###) use .Value2 instead

Then your UDF in the original sheet =DataResult(ResultSheet!A1)

If this approach does not fit your overall task you probably need to create a C# macro that gets called by some event trigger or button instead of a UDF.

Calling Application.Caller from within your UDF will return a Range object for the calling cell. Your UDF could then read the data from the corresponding location in the other sheet.