none
Excel 2016 build 8326 deletes data unexpectedly RRS feed

  • Question

  • Excel 2016 (16.0.8326.2096, 32-bit) deletes data unexpectedly in the following scenario:

    Open a new workbook and save it as a macro-enabled workbook (.xlsm file). Paste the following code into the ThisWorkbook module:

    Option Explicit
    Public WithEvents App As Application
    Private Sub Workbook_Open()
        Set App = Application
    End Sub
    Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Application.EnableEvents = False
        Application.Undo
        Application.Undo
        Application.EnableEvents = True
    End Sub

    Next, save the workbook and reopen it.  Enter any value in any cell to trigger the Application.SheetChange() event, then save the workbook.  Finally, enter another value in any other cell and observe that both values entered are now deleted.

    It may be worth nothing that calling Application.Undo() the second time is supposed to have the effect of calling Redo(), which is not available programmatically (this is how it works in Excel 2007-2013 and, until recently, Excel 2016).

    Thursday, September 7, 2017 9:25 PM

All replies

  • I have the same version but I cannot reproduce the problem. The effect of the code is that the cell flickers briefly, but the values remain - the second Undo acts as a Redo.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, September 7, 2017 9:57 PM
  • Unsure if this makes a difference, but this was observed by multiple users with Office 365, specifically. I thought the software is generally supposed to be the same regardless of whether you have Office 365, but you can only get the AutoSave feature if you have Office 365, so that is apparently not true.  This problem was observed with AutoSave both enabled and disabled, but because the problem appears after saving, I cannot help but wonder whether AutoSave in Office 365 has something to do with it.
    • Edited by yes98785 Thursday, September 7, 2017 10:58 PM
    Thursday, September 7, 2017 10:53 PM
  • I have a click-to-run version of Excel 2016, but it is not part of Office 365, and I don't store my documents in the cloud. Hopefully someone else can comment.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, September 8, 2017 7:45 AM
  • Hi yes98785,

    I test your code with O365 account.

    I could deleted both the value after saving the excel file first time to change the value. When I change cells value second time after saving, It won’t delete both value. In other situation, it works just like Hans testing result.

    I'm wondering why you need call Application.Undo twiceWhat do you want to do? You could go to File->FeedBack to submit a feedback for this issue.

    Best Regards,

    Terry

    Friday, September 8, 2017 10:06 AM
  • Terry,

    Thanks for replying.  To be clear, you were able to replicate the problem.  Whether it worked the second time is an interesting data point, but does not negate that it did not work the first time.

    This is just the minimum code required to reproduce the problem.  In practice, this is part of a much larger chunk of code essentially used to implement a custom Undo solution that solves for how Excel clears the Undo stack when code is run that modifies a workbook (a major problem itself).  So, between the Undo calls, we are performing some operations.  As I noted, the second Undo call simulates the missing Redo method.

    This behavior is observed only in Excel 2016 (works fine in 2013).  It appears that AutoSave was introduced in the July update, which sort of coincides with the increased number of problem reports we are seeing recently.

    I'll try File > Feedback, too, but that sounds more like mechanism for general product feedback by average users than critical bug reports by experienced developers.  We are an add-in developer with tens of thousands of users around the world and MVP-level technical experience, and have gone to great lengths to confirm this bug.




    • Edited by yes98785 Friday, September 8, 2017 10:58 AM
    Friday, September 8, 2017 10:34 AM
  • Hi yes98785,
    Yes, I could reproduce this issue. I'm still wondering why you need call the application twice, if you want to clear the undo stack directly, you could easily do this via copying any cell to itself.
    If you need any info need to test when making feedback, please feel free to let us know.
    Best Regards,
    Terry
    Tuesday, September 12, 2017 3:00 AM
  • Terry,

    As explained twice above, calling Application.Undo() twice simulates the missing Redo() method.  Why it works this way is a question for Microsoft developers.  Also, per my last post, there is no desire whatsoever to clear the Undo stack (quite the opposite, actually).

    I am unclear what your last sentence is offering, but it seems a dismissive attempt to close out this matter without actually addressing it.  Fortunately, I have made contact with Microsoft engineers through back channels who have acknowledged a "regression in behavior from previous versions" related to this matter.  I will post any updates from them here for the benefit of public record (unless you would like to do that).


    • Edited by yes98785 Tuesday, September 12, 2017 10:41 AM
    Tuesday, September 12, 2017 10:39 AM
  • Hi yes98785,

    Sorry for making confused. The reason why I ask calling Undo twice is it did not change anything from result. I assume there might be other logic in your production application.

    As my above reply, I could reproduce your issue. After checking the depth difference between my test and Hans’s, I assume this is caused by storing file Online due to this issue exist no matter whether the AutoSave is on, and the saving option will sync the difference between the file online and local. For the real root cause, I would suggest you keep contact from Microsoft engineers.

    As my test, I found we could save the workbook in code to avoid delete data. And I suggest you make a test with below code.

      Application.EnableEvents = False
    
        Application.ScreenUpdating = False
    
        Application.Undo
    
        Application.Undo
    
        ActiveWorkbook.Save
    
        Application.ScreenUpdating = True
    
        Application.EnableEvents = True

    Best Regards,

    Terry

    Wednesday, September 13, 2017 10:20 AM
  • Thanks, Terry.  Saving the workbook via code is not an acceptable workaround in our case, unfortunately.  Note that you can reproduce this bug with the file saved locally--it does not need to be saved online (OneDrive or SharePoint).  We are nearly certain that this has to do with AutoSave.
    Wednesday, September 13, 2017 6:09 PM
  • Hi yes98785,

    Thanks for this information. I made a test again by making a copy of the online file, and I could reproduce this issue locally.

    It’s sad this is not an acceptable workaround. I would suggest you keep contact MS Engineers through back channels.

    If you have any update, it would be appreciated if you could share us here.

    Best Regards,

    Terry

    Thursday, September 14, 2017 8:22 AM