UDF keeps recalculating/flashing

Jun 26, 2014 at 4:37 PM
I use ExcelDNA 0.32, C#, NetOffice, .NET 4.5.1
I have the following code for MyUDF function
When I have one MyUDF, it works fine.
But if I have several MyUDFs, their values keep flashing, please see http://screencast.com/t/0MaR65Nr

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 26, 2014 at 6:23 PM
A function that is marked IsMacroType=true and has one or more parameters of type object type that are marked as AllowReference=true is handled as volatile by Excel. So a completed cell (where the async value has already been returned) will recalculate every time the sheet changes, as you show.

It does not look like you need either IsMacroType=true or AllowReference=true in this function, so you should not set these special attributes.

-Govert
Jun 26, 2014 at 6:47 PM
Edited Jun 26, 2014 at 7:40 PM
I need AllowReference = true b/c all these parameters can be an Excel cell Reference like A1 , A2, etc. I will remove IsMacroType = true.
Will that fix the issue? I will test that
Where can I find the document about volatile functions in Excel? thanks


I added the following code at the very beginning of MyUDF
            var caller = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
            var bookAndSheetName = XlCall.Excel(XlCall.xlSheetNm, caller);
            var cellRefText = XlCall.Excel(XlCall.xlfReftext, caller);  //throw an exception when IsMacroType = true
            Debug.WriteLine("caller:" + cellRefText); 
I removed AllowReference = true from all parameters, & IsMacroType = true,
I got an exception
Name:XlCallException
Message:Exception of type 'ExcelDna.Integration.XlCallException' was thrown.
Target:System.Object Excel(Int32, System.Object[])
Stack: at ExcelDna.Integration.XlCall.Excel(Int32 xlFunction, Object[] parameters)
at MyAddIn.MyFunctions.MyUDF(String symbol, String column, Object onDate, Object fillOpt, Object frequency)

b) I keep IsMacroType = true, remove AllowReference = true from all parameters
UDFs do not flash any more again I am not 100% sure if the issue is fixed b/c flashing issue did not happen every time

c) I comment out
//var cellRefText = XlCall.Excel(XlCall.xlfReftext, caller); //throw an exception when IsMacroType = true
then removed IsMacroType = true, AllowReference = true,
UDFs do not flash any more again I am not 100% sure if the issue is fixed b/c flashing issue did not happen every time

I can go with c) but why IsMacroType=true will influence XlCall.Excel(XlCall.xlfReftext, caller);

Thanks
Coordinator
Jun 26, 2014 at 7:02 PM
Edited Jun 26, 2014 at 7:03 PM
I think you misunderstand AllowReference=true. It makes no difference when the parameter type is not object.
For object parameters it means you receive might be an ExcelReference object instead of the value pointed to by the reference. This is nearly never needed.

You can try with and without AllowRefernce, and see what the result of frequency.GetType() is.

-Govert
Jun 26, 2014 at 8:19 PM
Oh, Got it. Thanks

I removed AllowReference = true
However I still need IsMacro=Type for ExcelDnaUtil.IsInFunctionWizard() not working (see https://exceldna.codeplex.com/discussions/445708)
I use
        try { return !(bool)XlCall.Excel(XlCall.xlfGetTool, 4, "Standard", 1); }
        catch { return ExcelDnaUtil.IsInFunctionWizard(); }
Is ExcelDnaUtil.IsInFunctionWizard() fixed in 0.32?
When I debug it, it seems ExcelDnaUtil.IsInFunctionWizard() is fixed. Just want to double check.

Thanks
Coordinator
Jun 26, 2014 at 8:28 PM
Edited Jun 26, 2014 at 8:51 PM
Indeed, your code with the xlfGetTool call requires IsMacroType=true.

The built-in ExcelDnaUtil.IsInFunctionWizard() should work fine in Excel-DNA 0.32. If not, please let me know.

-Govert
Jun 26, 2014 at 8:31 PM
Thanks!

If I replace
  try { return !(bool)XlCall.Excel(XlCall.xlfGetTool, 4, "Standard", 1); }
    catch { return ExcelDnaUtil.IsInFunctionWizard(); }
with
ExcelDnaUtil.IsInFunctionWizard();

remove the debug part
      var caller = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
        var bookAndSheetName = XlCall.Excel(XlCall.xlSheetNm, caller);
        var cellRefText = XlCall.Excel(XlCall.xlfReftext, caller);  //throw an exception when IsMacroType = true
        Debug.WriteLine("caller:" + cellRefText); 
Then I will NOT need IsMacroType=true, right?
Jun 26, 2014 at 9:00 PM
Got it. I think I do not need any XlCall.xlfGetTool except for debug in MyUDF. Thanks a lot!