How to recalculate a UDF - Rx

Jul 2, 2014 at 5:24 PM
Edited Jul 2, 2014 at 8:48 PM
My Excel AddIn uses ExcelDNA 0.32, C#, NetOffice, .NET 4.5.1
I have the following code for MyUDF function
I have a refresh button in my addin. When users click the button, the current selected cell e.g. cell A1 and assume its formula string is "=MyUDF(symbol, column, date)") I expect A1 will be re-calculated

So I does this in click event handler
  A1.Formula = "=MyUDF(symbol, column, date)"
  //A1.Calculate();    //I even uncomment out this line,  no difference 
this will lead to MyUDF implementation code, so
I step into MyUDF function and see it calls
    return ExcelAsyncUtil.Observe...
but after that, nothing happens. i.e. it never goes into delegate part of the code.
I guess the value of the function is cached, my question is how to remove the value from cache
so delegate part of the code can be executed? i.e. how to force recalculation?

I also replace ExcelAsyncUtil.Observe with ExcelAsyncUtil.Run, it does the same thing,
it does not run delegate part when refresh is clicked.

I must miss something.

Thanks
    [ExcelFunction(Description = "Returns a datapoint for the given symbol and column based on the given date/time.",
        IsMacroType = true)]
    public static object MyUDF(string symbol,
        string column, object onDate, object fillOpt, object frequency)
    {
        string key =  symbol + column + onDate + fillOpt + frequency + "_datapoint";
        return ExcelAsyncUtil.Observe("MyUDF", new object[] {symbol, column, onDate, fillOpt, frequency},
               delegate
                {
                    var task = Task.Factory.StartNew(() =>
                        {
                            object datapoint;  //if the value exists in cache, return it, otherwise will call web service
                            if (CachedDataPoints.ContainsKey(key))
                            {
                                datapoint = CachedDataPoints[key];
                            }
                            else
                            {
                                datapoint = Container.Resolve<IMyWebService>().GetDataPointSync(
                                    symbol, column, onDate, fillOpt, frequency);
                                if (String.IsNullOrEmpty(datapoint.ToString())) datapoint = "#ERROR";
                                //put value in cache so next time we can get it directly from cache instead of calling web service                                    
                                 CachedDataPoints.Add(key, datapoint);
                            }
                            return datapoint;
                        });

                    var observable = new ExcelTaskObservable(task);
                    return observable;
                });
    }
Coordinator
Jul 2, 2014 at 8:45 PM
Are you sure your Task is complete by the time you press the button? If not, the behaviour you see is what I would expect.

Does it recalculate when you press F2 and Enter?

-Govert
Jul 2, 2014 at 9:55 PM
Edited Jul 2, 2014 at 9:55 PM
How do I know when the task is complete?

It never comes inside delegate, it only goes to "return ExcelAsyncUtil.Observe " and then exits

When I click "Enter" or F2 + Enter, no calculation
Coordinator
Jul 2, 2014 at 10:15 PM
Suppose you replace Container.Resolve... by some dummy function that immediately returns a value - does it recalculate then?

-Govert