Dynamic-aware Excel incorrectly flagging our custom XLL functions as being array functions?

[GabrielMichaud]

Hi,

I've been having a major problem over the past few days with the insider build of Excel that has the new dynamic array features enabled. Some of my Excel spreadsheets have become completely unusable and I had to redo a lot of them (problem started slowly and work was done in the files before we noticed the problem).

Excel seems to have marked formulas using our custom XLL functions as legacy CSE array functions, even though these functions only return single values. This problem has cascaded into other issues such as cells showing { =#VALUE# }, and cells not disconnecting from the RTD server. As per the following link https://support.office.com/en-us/article/dynamic-array-formulas-in-non-dynamic-aware-excel-696e164e-... , "Excel determines if that formula has the potential to return an array. If it could return an array, we will save it as a dynamic array".

Can anyone from Microsoft clarify exactly how Excel knows the function can return an array? Our custom XLL functions are built using ExcelDNA and they all rely on a RTD server for background processing; the return type is 'object' so maybe it has something to do with the problem...

[#2]

Hello, we have the same problem that even functions that return only one string are considered to be potential array functions. This is true for XLL functions and for easy VBA functions ....

A simple function

Public Function SayHello () As String
     SayHello = "Hello"
End Function

will be added in the new Excel version

= @ SayHello ()

and if you then enter the formula without @

= SayHello ()

will be in the old Excel version

{= SayHello ()}

...?

So if the functions can not return an array or object (Variant), then the behavior is not understandable in that though the UDF can only return a single object ....

[#3]

@HaTom 

Could you clarify where the problems are occurring? 

Are old workbooks failing to calculate in the DA versions of Excel or is it when you complete a round trip and try to get a modified workbook back into the production versions of Excel?

There are certainly occasions when I have seen '@'s sprinkled around like confetti but I think the workbook still calculates.

[#4]

Hi GabrielMichaud,

As UDF's are able to return arrays or ranges, Excel must save it as a CSE array for Pre-DA Excel to ensure it calculates the same way. The only exception is where it is consumed by a function that expects arrays or it is implicitly intersected using @. So for instance, you can enter =SUM(MYUDF()) or =@MyUDF() in DA Excel and it will not be array entered in Pre-DA Excel.

For a full list of functions that can return an array visit this page: https://support.office.com/en-us/article/excel-functions-that-return-ranges-or-arrays-7d1970e2-cbaa-...

We recently fixed the RTD array disconnect issue and have released it to insiders for testing. Please let me know if it has addressed the disconnect issues.

Regards, Joe

Excel Program Manager

[#5]

@JoeMcDaid thank you very much for the follow up! I am glad to hear that the RTD array disconnect issue has been fixed, and it's now 100% clear to me why Excel is behaving this way. It did not appear to be consistently converting existing formulas to CSE formulas when I first started playing with this feature, is this something that has been improved or can you share the circumstances when it's supposed to happen?

Lastly - are you considering adding any ability to flag the custom function itself as not being an array function, so that we wouldn't need to prefix it with @ to avoid it being saved as a CSe array functino? My only concern at this point with this is that files can be shared with users of older versions of Excel which will still have the RTD array disconnect issue.