02-02-2019 05:08 PM
02-02-2019 05:08 PM
Since this latest 2/1/2019 update, there is a new glitch with the Office Insider Dynamic Array functions. It seems that many, many more VBA-written formulas are now erroneously auto-inserting the SINGLE function, apparently due to the Excel Calc engine assuming a need for Implicit Intersection. This incorrect insertion of the SINGLE function is much more pervasive than it was as recently as the morning of 2/1/2019 (prior to the update) -- in the same workbooks, despite no interval change in the macros within those workbooks. Is there a way to disable auto-insertion of the SINGLE function, at least until this glitch is corrected with the next build?
02-03-2019 01:42 AM
hello @MOBMD ! At least you can figure out what the new functions are doing with modern Arrays. Ironically I saw your post here left of the link to the ignite presentation of it. Pls. see that on https://www.youtube.com/watch?v=5nQgqmlcDmQ
One question: How is it, that SINGLE function is autoinserted in your workbook? Can you pls. post a screenshot to that behaviour? Thx and Greets, Eva.
02-03-2019 12:13 PM
I'm not certain that a screenshot would be beneficial or necessary. Suffice it to say, since the latest 2/1/19 update of Office 365 Insider, many of my formulas have been incorrectly corrupted with the SINGLE function while being entered via my macros. In other words, if I input the formula manually, as such,
there is no problem. On the other hand, when this formula is placed in numerous worksheets via my macros, as such,
then the new CALC engine misinterprets the formula as needing forced implicit intersection, and renders the following formula,
This usage of the SINGLE function completely corrupts my workbook, and, in fact, I receive a VBA error message that prevents updating of my worksheets.
In summary, the erroneous formula wrapping with the SINGLE function is most pervasive (in my experience) when the formula is created with a VBA macro, and that glitch appeared only after the latest update, since my worksheets have not otherwise changed in the interim.
If there were any way to disable auto-input of the SINGLE function, that would be fantastic. Would it be possible for you to pass this on to Joe McDaid?
02-04-2019 12:16 PM
Hi @MOBMD , it's enough to mention Joe here with @ , or even better in comments to his post https://techcommunity.microsoft.com/t5/Excel-Blog/Preview-of-Dynamic-Arrays-in-Excel/ba-p/252944/pag....
by FAM1088 on April 14, 2020