Forum Discussion

MOBMD's avatar
MOBMD
Copper Contributor
Feb 03, 2019

New Excel glitch

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 SI...
New Excel glitch
Eva Vogel's avatar
Eva Vogel
Iron Contributor
Feb 03, 2019

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.

  • MOBMD's avatar
    MOBMD
    Copper Contributor
    Feb 03, 2019

    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

Resources