New Excel glitch

Copper Contributor

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?

3 Replies

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.

Eva,

 

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,

 

=UNIQUE(EOMONTH(SEQUENCE(DAYS(DATE(2036,12,31),EOMONTH(TOC!$D$3,1)),,EOMONTH(TOC!$D$3,0),1),0))

 

there is no problem.  On the other hand, when this formula is placed in numerous worksheets via my macros, as such,

 

Range("AH3").Formula="=UNIQUE(EOMONTH(SEQUENCE(DAYS(DATE(2036,12,31),EOMONTH(TOC!$D$3,1)),,EOMONTH(TOC!$D$3,0),1),0))"

 

then the new CALC engine misinterprets the formula as needing forced implicit intersection, and renders the following formula,

 

=SINGLE,UNIQUE(EOMONTH(SEQUENCE(DAYS(DATE(2036,12,31),EOMONTH(TOC!$D$3,1)),,EOMONTH(TOC!$D$3,0),1),0))).

 

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?

 

Thanks,

 

MOBMD

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....