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

Question

Here is the little example of my two worksheets.

Original Sheet:

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:

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 correctly. 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;
}

score 2 · Accepted Answer · answered Sep 22 '11 at 14:29

2

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.

answered Sep 22 '11 at 14:29

Charles Williams

23,121
5
38
38

Thanks very very much for the help. Just 1 more question though, I seem to have type conversion problem when I use **.Value2**. I think it works fine if the original data is text based value, but when it comes to date type data, it will give me incorrect result. `string result = (string)ResultRange.Value2;` is my code I am using currently. Perhaps I should convert .Value2 to Object first? – woodykiddy Sep 22 '11 at 16:25
Excel date values are doubles, not strings – Charles Williams Sep 22 '11 at 18:04
Since an Excel cell can contain any of a double, an error, a string or a boolean its usally safest to convert to Object unless you want an error if the cell data is not of the required type – Charles Williams Sep 22 '11 at 18:07
I have got a problem with Application.Caller. It somehow returns -2146826265. I've also post the question in here http://stackoverflow.com/questions/7887360/why-application-get-callertype-missing-returns-a-negative-integer – woodykiddy Oct 25 '11 at 10:17

score 1 · Answer 2 · answered Sep 22 '11 at 14:07

1

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.

answered Sep 22 '11 at 14:07

Govert

16,387
4
60
70

Thanks very very much. It is exactly what I was looking for. I am still pretty newbie to the Excel Programming. Plus some part of the API documentation on MSDN is not really well documented, which makes it a bit hard to learn/reference. :( I guess a little help from you guys is really needed. Thanks again for the answers, everyone. – woodykiddy Sep 22 '11 at 16:30
There seems to be a problem using Application.Caller. For some reason, it returns a negative integer, instead of the range object as it should. Why is that? How can I only get range object? – woodykiddy Oct 25 '11 at 10:13

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

2 Answers2