I really have trouble with how UDFs are treated by this design, especially when a model is created on a machine that supports DA and then later viewed on a machine that doesn't... or vice-versa.
For example, say I am a software vendor writing the FooMaster add-in, which contains a single (awesome) UDF called Foo. Foo always returns a double value. There are no arrays involved. FooMaster has been around for 30 years and has thousands of customers who have used it for forever.
Problem Scenario #1 (Minor)
- Fred makes a workbook in Excel 2016 that calls =Foo() in some cell and saves it, just like he always does.
- Fred sends his model to Wilma who is using an Excel with DA support.
- Wilma opens the model and sees =@Foo() in a cell.
- Wilma is confused because she's not seen the @ symbol before. Office 365 just was updated and all of sudden Fred's models are all weird.
- She does some reading learns it has something to do with the handling of arrays and says to herself... "But Foo has nothing to do with arrays! What is going on? The FooMaster people must have messed up! Or Fred's been smoking something with Barney again."
Problem Scenario #2 (Major)
- Harry has just gotten a DA enabled version of Excel.
- He makes a new workbook with "=Foo()" in it. (He doesn't type =@Foo() because why should he? Foo has nothing to do with arrays and he's been typing these function this way for forever.)
- He sends his workbook to Hermione who's still slumming with Office 2016.
- Hermione opens the model, and sees a CSE array! Hermione freaks out because she hates CSE arrays because they are scary.
- Worse, FooMaster has a bunch of routines that scan workbooks looking for =Foo() functions so they can be tabulated and edited. But FooMaster doesn't handle CSE array functions.
- All of a sudden FooMaster workbooks can no longer be freely exchanged between users!
One possible cure for this would be to let a UDF creator be able to specify that a function will never return an array.
Thank you for your consideration.
Erik
Microsoft