none
Excel Undo/Redo

    Question

  • We are creating an Excel Add-in using VSTO. We plan to support Excel 2007, 2010 and 2013, but are willing to sacrifice uniformity if newer versions have important features not available in older versions.

    We would like to implement Undo/Redo, but reading the post this forum this seems impossible. Is that still true? Has the situation changed for Excel 2013?

    As far as I can tell the Undo button becomes unavailable as soon as one of my (C#) methods are executed. This should mean that there is no way to tie into Excel's Undo stack. Is that true?

    It seems we have to resort into making our own Undo button so users at least can undo the last steps made by our Add-in, but it seems impossible to make something work with Excel's own Undo stack (for its own changes) and our Undo stack (for handling whatever state changes we need to keep track of). Any pointers for how to deal with this will be most welcome!

    Thanks

      Lars

    Friday, October 26, 2012 9:26 AM

Answers

  • Hello Lars,

    As soon as the Excel sheet is modified through code (either VBA or VSTO), the Undo stack gets cleared. That is why the Undo/Redo button goes disabled. This is by design. To test this, you can add data to the Excel sheet and run the following macro from Excel VBA:

    Application.ActiveCell.Value2 = "Hello"

    The behavior doesn't change in Excel 2013 as well.

    I think there is no way to workaround this.

    Thanks,

    Sreerenj G Nair

    • Marked as answer by Lars_DK Wednesday, November 14, 2012 2:03 PM
    Tuesday, November 13, 2012 1:15 PM
    Answerer

All replies

  • Visit John Walkenbach's site:

    http://www.j-walk.com/ss/excel/tips/tip23.htm

    Friday, October 26, 2012 2:15 PM
  • Hi Lars_DK,

    Would you please clarify your goal more detail?

    I will provide a snippet for you, please tell more additional aim for further research:

            private static Office.CommandBars getCommandBars()
            {
                return (Office.CommandBars)Globals.ThisWorkbook.Application.GetType().InvokeMember("CommandBars"
                    , System.Reflection.BindingFlags.GetProperty,
                    null,
                    Globals.ThisWorkbook.Application,
                    null,
                    System.Globalization.CultureInfo.InvariantCulture);
            }
    
            private static string getLastUndo()
            {
                string result = string.Empty;
                Office.CommandBars oCommandBars = getCommandBars();
                Office.CommandBar oCommandBar = oCommandBars["Standard"];
                Office.CommandBarControl oCommandBarControl = (Office.CommandBarControl)oCommandBar.Controls[14];
                MessageBox.Show(oCommandBarControl.Caption);
                MessageBox.Show(oCommandBarControl.accChildCount.ToString());
                try
                {
                    if (oCommandBarControl is Office.CommandBarComboBox)
                    {
                        Office.CommandBarComboBox ocbcb = (Office.CommandBarComboBox)oCommandBarControl;
                        for (int i = 1; i < oCommandBarControl.accChildCount; i++)
                        {
                            MessageBox.Show(ocbcb.get_List(i));
                        }
                    }
                    else
                    {
                        MessageBox.Show("No");
                    }
                }catch(Exception ex){
                    MessageBox.Show(ex.Message);
                }
                return result;
            }

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Monday, October 29, 2012 6:23 AM
    Moderator
  • Hi Tom

    Our Add-in adds a tab to the Ribbon. About half of the buttons on our tab retrieves or calculates data and inserts it in the current Excel sheet. When the data are inserted the Undo button is disabled by Excel (and the accChildCount in your code example goes to 0).

    I really can't see why the Undo button would be disabled by my inserting data into Excel. I wish Excel would just behave as if I had typed in all the data using the keyboard. Alternatively that Excel would give access to the Undo handling from our Add-in (C#) and that the Undo stack would not be reset by Excel.

    I.e. I'm perfectly happy about having to store the data that I'm overwriting in the Excel sheet and having to set the data back to what it was upon receiving some event, but since Excel seems to remove everything on the Undo list that doesn't seem possible.

    Do anyone know where the current Undo behaviour is documented when it comes to VSTO Add-ins?

    Thanks

      Lars

    Monday, October 29, 2012 3:28 PM
  • Hi Lars,

    I will involve some experts into your thread to see whether they can help you. There might be some time delay, thanks for you patience.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, October 31, 2012 7:18 AM
    Moderator
  • Hello Lars,

    As soon as the Excel sheet is modified through code (either VBA or VSTO), the Undo stack gets cleared. That is why the Undo/Redo button goes disabled. This is by design. To test this, you can add data to the Excel sheet and run the following macro from Excel VBA:

    Application.ActiveCell.Value2 = "Hello"

    The behavior doesn't change in Excel 2013 as well.

    I think there is no way to workaround this.

    Thanks,

    Sreerenj G Nair

    • Marked as answer by Lars_DK Wednesday, November 14, 2012 2:03 PM
    Tuesday, November 13, 2012 1:15 PM
    Answerer
  • Hi Sreerenj 

    Thank you for your reply - although it is not the reply I'm looking for.

    Can you add a suggestion to the developers to add support for Undo when using VSTO projects.

      Lars

    Wednesday, November 14, 2012 2:03 PM
  • Hi Lars,

    Thanks for asking this question. I had the exact same problem as yours.

    However, just like you are, I'm also very disappointed to hear that this is not supported by design.

    Ishan. 


    • Edited by iudssl Sunday, February 17, 2013 4:04 PM
    Sunday, February 17, 2013 2:07 PM
  • Hi Sreerenj,

    Isn't the expectation of an addin to augment the existing functionality of an application? When we develop some kind of a productivity addin, the last thing we would expect is for that to wipe-off the undo stack for every interop call we make, for undo being such an indispensable functionality.

    Isn't there anyway at all to workaround this, at least to preserve the undo/redo stacks between interop calls? I seriously find it difficult to believe that this is not supported by design.

    I'm working on an Excel addin and so far spent more than two weeks on experimenting on adding the required functionality. I noticed that the undo gets cleared when my extended ribbon tab is brought in to play, but thought that there is someway to support undo/redo through interop. This now sounds like a show stopper to me as it will be seriously hard to push this addin to users given the loss of undo.

    Thanks,

    Ishan.

    Sunday, February 17, 2013 2:24 PM
  • May be instead of modifying object properties directly in VBA or VSTO, couldn't we emulate user input in the worksheet and send events to the Excel UI ?

    Basically, changing the value of a cell should be equivalent to selecting it (Activate, Select) then sending a Click event, then a SelectAll event, then pasting a string

    But the real bad thing in Excel is the fact that it zaps the Undo stack. This should NEVER happen when we set properties. Excel should instead check if the cell is editable or locked.

    Ideally, we should have a a way to create an undoable reference to the original object, to which we can then set properties as if it was the original. Excel would then record the previous value of the property, and if the new value is different, it will insert the original object reference, the property name, and its current value in its Undo stack, then it will apply the new value to that property.

    Something like:

    * Application.StartUndoable "description", True 'Flag is used to indicate if we force edits on locked/uneditable/protected cells

    * Let ExcelObject.SimpleProperty = newValue  or Set ExcelObject.ObjectProperty = newObject (which can be repeated)

    * Application.SuspendUndoable

    * Application.ResumeUndoable

    * Application.EndUndoable (this finalize all modifications that can be undone in one step by pressing CTRL+Z or with the Edit>Undo menu).

    Some objects methods are utilities that internally just set one or several properties. The undo buffer needs not record these methods but the result o their actions (e.g. sorting tables, or animating a chart). Some active components (like videos) have internnal status that do not change their content (e.g. Play) but just the initial state (start time). But moving a scrolbar should be like moving gauges : theur modify some values, but not all of them need to be recorded. That's why se shoud be able to suspend the recording in undo buffers.

    The Excel objects supporting this should be WorkBook properties, the WirkSheets collection, the Charts collection, their contents (ranges, cells,), their state (active cell, view states like divisions of panes, and the protection state, oncluding undoing password changes and resttoring the original password or encrypted state, the the VBA or VSTO code can change them by providing the appropriate credentials to unlock thngs)

    Most basix Add-ins (like those with UserForms) should be helpers to create/modify data, but all these modifications should be as easy to undo as if they were done by manual edit (except that the VBA code should probably have an "Apply" button which will perform many actions in one group.

    The UserForms would also be undoable themselves the same way, if they are controled by another addin pdofying the state of another addin, so all addings should also have their undo buffer. for their current state, as long as they are loaded up to the point where they are terminated.

    But not everything needs to be recorded in the Undo buffer of the workheet or in the Addin, that's why methods should control what to record.

    Recording changes in the Undobuffer can be compressed: only the initial and final state of the modified list of properties would need to to be compared., i.e. only properties that are part of the savable document. This means that each savable docuemnt has its own undo/redo buffer. The list of these documents is i nthe Application object.

    Sunday, June 30, 2013 12:32 AM
  • Another way to design it:

    Worksheet.StartUndoable "Insert some date"

    With ActiveCell

    .Value=#12/31/2000#

    .NumberFormat = "mm/dd/yyyy'

    End With

    finalized by:

    Worksheet.EndUndoable

    or

    Worksheet.CancelUndoable.

    In case of any abort of the VBA code, the EndUndoable is called impliclty in the error handler, if it can. If it cannot save this in the Undobuffer (not anough memory, or other errors, it may display an alert like "This action cannot be undone, do you want to continue?" IF we press "No" it will call CancelUndoable). This alert may appear at any time, and in that case, as long as the Undoable action is running, all other changes to the document are ignored, but an event may be raised to allow aboring the VBA code early (the handler will then call CanceUndoable, or it could propose to save a backup of the document before continuing.

    Ideally, Excel should expose a method to perform an autosave (just like autosaved bakups being performed every few minutes) and restoring the document from this autosave backup., this will keep mpeory low and will allow the execurion to continue running, where the undobuffer just records the name of the backup that can be restored by replacing the current corrupted document to undo. Excel will manage the name of these autosaved backups itself. IF Excel crashes, it will be able to restart and restore them automatically, but the user will be able to choose the one to keep to restart the edits, and will cean the autosaved old backups with the normal Excel interface.

    Sunday, June 30, 2013 12:55 AM
  • This is a huge bug in Excel VSTO/VBA.

    Sreerenj please record it as a bug on Connect and have it submitted for fixing as soon as possible.

    It really is a massive block to using this technology in a business in any serious way.

    You design a .NET project to work with Excel then your users realise that they lose CTRL-Z when they use that technology!

    Guess what the user says? It is farcical that MS response is oh yea thats just how it works, this is by design' but sorry that doesn't cut the mustard.

    Why is it designed like this, can we have the technical explanation, we are programmers after all.

    Tuesday, September 29, 2015 6:33 PM