UDF is recalculated but shows #Value in Excel

Jun 25, 2014 at 8:34 PM
Edited Jun 25, 2014 at 9:14 PM
I use ExcelDNA 0.32, C#, NetOffice, .NET 4.5.1
I have the following code for MyUDF function
I found an issue
When value in reference cell changes, MyUDF will be recalculated,
I stepped into the following code and it looked datapoint has a valid value
However, in Excel it simply shows "#Value"
So I must miss something. Please advise. Thanks
    [ExcelFunction(Description = "Returns a datapoint for the given symbol and column based on the given date/time.",
        IsMacroType = true)]
    public static object MyUDF(
        [ExcelArgument(AllowReference = true])string symbol,
        [ExcelArgument(AllowReference = true)]string column,
        [ExcelArgument(AllowReference = true)]object onDate,
        [ExcelArgument(AllowReference = true)]object fillOpt,
        [ExcelArgument(AllowReference = true)]object frequency)
    {
        if (IsInFunctionWizard()) return MyConstants.MyUDFDesc;

        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
Jun 25, 2014 at 8:51 PM
That looks very complicated.

Most likely the #VALUE comes from an unhandled exception. You can wrap your function in an exception handler and return some extra information if an exception is thrown. Otherwise it might help to simplify things to the point where you have a small self-contained example about which to ask.

-Govert
Jun 25, 2014 at 9:16 PM
Edited Jun 25, 2014 at 9:41 PM
Oh, sorry. I should have removed unrelated info. Now I removed unrelated info from the original post. Hopefully it will be clear now. I step into the function in Visual Studio, I see datapoint is a valid number, but in Excel, it is still "#Value"

I put try catch around the code, and see an exception saying

Name:InvalidOperationException
Message:SynchronizationManager must be registered for async and observable support
Target:System.Object GetValue()
Stack: at ExcelDna.Integration.Rtd.AsyncObservableState.GetValue()
at ExcelDna.Integration.Rtd.AsyncObservableImpl.RegisterObservable(AsyncCallInfo callInfo, IExcelObservable observable)
at ExcelDna.Integration.Rtd.AsyncObservableImpl.ProcessObservable(String functionName, Object parameters, ExcelObservableSource getObservable)
at ExcelDna.Integration.ExcelAsyncUtil.Observe(String callerFunctionName, Object callerParameters, ExcelObservableSource observableSource)
at MyProduct.MyUDF(String symbol, String column, Object onDate, Object fillOpt, Object frequency)

Another thing in AutoOpen
I put ExcelAsyncUtil.Initialize(); but it is said the method is obsolete and should be removed. So I comment out it.
Does it cause the issue? I will try. thanks
Jun 25, 2014 at 9:47 PM
Now I put back ExcelAsyncUtil.Initialize(); it seems working now. Thanks
Coordinator
Jun 25, 2014 at 10:01 PM
Excel-DNA version 0.32 does not require that you call ExcelAsyncUtil.Initialize().

You might be using the .xll from the older version - you need to upgrade both the ExcelDna.Integration.dll and the ExcelDna.xll copy you use.

-Govert
Jun 26, 2014 at 4:27 PM
Thanks. Yes, I got the older version of xll, Now the issue is fixed with the 0.32 xll.