Forum Discussion
New Excel glitch
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/page/2#comments.
