Inconsistent return value of UDF

Jul 10, 2014 at 3:18 PM
Again I use excelDNA 0.32, .Net4.5.1, netoffice

There is very strange issue with my UDF
It seems the first time it returns "#N/A", once the real data or error message comes back, it does not update or refresh the cell with the new value
When I F2+enter to the same cell repeatedly, the value of UDF alternates between "#N/A" and error message or value. Please see issue @ http://screencast.com/t/0MaR65Nr

My UDF is as following

[ExcelFunction(Description = "Returns a datapoint for the given symbol and column based on the given date/time.")]
public static object MyUDF(string symbol, string column, object onDate, string fillOpt, string frequency)
{
    string key =  symbol + column + onDate + fillOpt + frequency + "_datapoint";
    return ExcelAsyncUtil.Run("MyUDF", new object[] {symbol, column, onDate, fillOpt, frequency},
           delegate
           {


                        object datapoint;  //if the value exists in cache, return it, otherwise will call web service
                        if (CachedDataPoints.ContainsKey(key))
                        {
                            datapoint = CachedDataPoints[key];
                        }
                        else
                        {
            //this will return a value if there is valid data point or Error message if not 
                            datapoint = Container.Resolve<IMyWebService>().GetDataPointSync(symbol, column, onDate, fillOpt, frequency);
                            if (String.IsNullOrEmpty(datapoint.ToString())) datapoint = "NA";
                            //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;
           });
}
Thanks
Coordinator
Jul 10, 2014 at 5:47 PM
I suggest you lift the cache check outside the delegate, and only call ExcelAsyncUtil.Run for the case where the value is not found in the cache.

Could the error you see be due to datapoint being an unsupported datatype?
Could you code be throwing an exception, that might then be returned to Excel as #VALUE?

-Govert
Jul 10, 2014 at 9:45 PM
I did as you suggest, but it still did not work
datapoint is of type object
I put try-catch in MyUDF, there is no exception

It seems after ExcelAsyncUtil.Run is done, it does not re-calculate MyUDF.
Now MyUDF returns "#N/A" for the first time, after that it will return the value from cache (that could be a number or error message or NA)
Coordinator
Jul 10, 2014 at 9:59 PM
Could you check that this simple function works right:
    public static object TestAsync(string input)
    {
        return ExcelAsyncUtil.Run("TestAsync", new object[] { input }, () =>
            {
                Thread.Sleep(5000);
                return "Done: " + input;
            });
    }
When you enter something like =TestAsync("XXX") into a cell you should see #N/A immediately and then after 5 seconds you should get Done: XXX.

If this does not work correctly, the problems might be that you are in Manual update mode, or that the Application.RTD.ThrottleInterval is not right (just type ?Application.RTD.ThrottleInterval into the VBA immediate window to check).

-Govert
Jul 10, 2014 at 10:13 PM
Edited Jul 10, 2014 at 11:18 PM
TestAsync works as expected, #N/A and then Done:xxx
I checked Excel, the throttle is 1 second and calculation mode is Auto
Jul 11, 2014 at 2:38 PM
I just came to test TestAsync again this morning. I put a lot of TestAsync and now watch it stops working
It simply return #N/A, I have to F2+enter to make it return "Done:xxx"
see http://screencast.com/t/O4HhX2IsMP1x

The behavior is not consistent. I am so confused now
Please help

Thanks
Coordinator
Jul 11, 2014 at 2:51 PM
That TestAsync function will be very slow if there many calls - it's is just meant as a quick test whether the RTD feature is working.
Every call will tie up a ThreadPool thread, and you'll run out very quickly (5-10 calls at a time). To make a scalable version you need to use Tasks properly etc.

If you're confused, you can start again with a fresh add-in, add only that one function into the add-in, then in a fresh Excel instance and a clean book and check that it works for two or three cells. That checks that your RTD works as expected.

Then you can continue doing research on more advanced stuff.

Good luck,
Govert
Jul 11, 2014 at 3:52 PM
Thanks, Govert.
I changed to use ExcelAsyncUtil.Observe, but still not work, the same issue occurs.
i.e. First time it returns #N/A, then refresh again, return some value. Refresh third time, return #N/A, etc
What's next step I can try? Do you mean I need debug into source code? thanks
            return ExcelAsyncUtil.Observe("MyUDF", new object[] { symbol, column, onDate, fillOpt, frequency },
                    delegate
                    {
                        var task = Task.Factory.StartNew(() =>
                        {
                            datapoint = Container.Resolve<IRelInfoService>().GetRelDataPointSync(symbolWOPath, column, onDate2, fillType3, frequency2);
                            if (String.IsNullOrEmpty(datapoint.ToString()))
                            {
                                datapoint = "NA";
                            }
                            return datapoint;
                        });
                        var observable = new ExcelTaskObservable(task);
                        return observable;
                    });
Jul 11, 2014 at 6:44 PM
Edited Jul 11, 2014 at 7:13 PM
Hmm, now I change TestAsync as below, it does not work any more, see http://screencast.com/t/0MuynCkWTxO
Please what else I need do? I must miss something. thanks
    public static object TestAsync(object input)
    {
        return ExcelAsyncUtil.Observe("TestAsync", new object[] { input },
                   delegate
                   {
                       var task = Task.Factory.StartNew(() =>
                       {
                           Thread.Sleep(5000);
                           return input;
                       });
                       var observable = new ExcelTaskObservable(task);
                       return observable;
                   });
    }