Non-Volatile UDF stuck in calculation loop when supplied a volatile function parameter and ExcelAsyncUtil.QueueAsMacro is used

[John Reagan]

Hi,

We're working on UDF implementation which outputs a table of data outside the function area via a separate XlCall to a hidden macro type UDF. The macro type UDF uses ExcelAsyncUtil.QueueAsMacro to output its data to the sheet. A beta user reported an issue that the UDF was stuck in a calculation loop when the user supplied a volatile function as an argument. After investigating the issue, 

it appears that I may not be able to support this scenario if I need to use QueueAsMacro. I'm hoping someone here can at least confirm my understanding of the excel calculation model is accurate.

It seems that in automatic calculation mode, volatile functions are executed when they are needed in the dependency tree OR when Excel does any sort of calculation.

Excel calculates when data is added, rows are shown/hidden, and in a variety of other events. In my case, the volatile function supplied as a parameter is executed before my UDF;

it's return value is passed to my UDF which executes and changes data on the sheet(via the call to the hidden Resize macro UDF & QueueAsMacro call). This change to data on the sheet then causes the 

volatile function to be re-evalutated, which in turn causes my UDF to execute. I am then in a loop.

Has anyone had any similar experiences with volatile UDF parameters?

If I cannot do what I need to with the separate hidden UDF, is there someway I could detect a volatile function is in my list of arguments?

Thank you in advance.

Psuedo-code:

VBA Volatile UDF:

Function VolatileUDF()

Application.Volatile

VolatileUDF = "12/12/2001"

End Function

Public, non-volatile UDF:

[ExcelFunction(Name = "SIMPLE_UDF", Description = "A function for accessing Backstop data.", Category = "Backstop Functions")]

public static object Simple_Backstop(string functionId, object[,] subjects, params string[] args)

{

 return XlCall.Excel(XlCall.xlUDF, "Resize" as object, new object[,]);

}

Macro function signature:

 

[ExcelFunction(Name = "Resize", IsHidden = true, IsMacroType = true)]

public static object Resize(object[,] data) {

//output some data here

            ExcelAsyncUtil.QueueAsMacro(() =>

            {

                var kvpTarget = new ExcelReference(10,

                            20,

                            1,

                            5);

                var kvpRange = XL.Application.Range[XlCall.Excel(XlCall.xlfReftext, kvpTarget, true)];

                kvpRange.Value2 = "Fooey";

            });

         return "Hello";

}