How to push an operation onto the Excel undo stack?

Question

I'm building a VSTO Excel add-in that manipulates the values of multiple cells. I would like to allow the user to undo and redo the changes created by the add-in via the standard Excel features. I prefer to avoid using VBA.

Is this possible? If so, how?

Another question: is it possible to examine the existing undo/redo stack?

Answer 1

You failed to mention which version of Excel/.NET/VSTO runtime you wish to use, but it does not really matter :]
Custom Undo just cannot be done without injecting VBA, where you would need to set an undo method for rolling back your actions, by using compensation (i.e. reverse operations) or by restoring a saved state.

Answer 2

I know you want to avoid VBA but as others have mentioned, this is not really possible to get the Undo.

Here is a VBA example that preserves the existing selection as custom data type so that it can be undone later.

Option Explicit
'Stores info about current selection'
Public OldWorkbook As Workbook
Public OldSheet As Worksheet
Public OldSelection() As SaveRange

'Custom data type for undoing'
Type SaveRange
    Value As Variant
    Address As String
End Type

Public Sub YourSubRoutine()
    'A simple subroutine that acts on a Range selection'
    Dim UserRange As Range

    Set UserRange = Selection


    If UserRange Is Nothing Then
        Exit Sub
    End If

'## The next block of statements '
'## Save the current values for undoing '
    Set OldWorkbook = ActiveWorkbook
    Set OldSheet = ActiveSheet

    Application.ScreenUpdating = False

    '## Code to manipulate the Selection range '
    '## Code to manipulate the Selection range '
    '## Code to manipulate the Selection range '

    '## Specify the Undo Sub '
    Application.OnUndo "Undo the YourSubRoutine macro", "UndoYourSubRoutine"

End Sub
Public Sub UndoYourSubRoutine()

Dim i As Integer

'   Undoes the effect of the YourSubRoutine '

'   Tell user if a problem occurs '
    On Error GoTo Problem

    Application.ScreenUpdating = False

'##  Make sure the correct workbook and sheet are active '
    OldWorkbook.Activate
    OldSheet.Activate

'## Restore the saved information '
    For i = 1 To UBound(OldSelection)
        Range(OldSelection(i).Address).Value = OldSelection(i).Value
    Next i
    Exit Sub

'   Error handler'
Problem:
    MsgBox "Can't undo"

End Sub

Answer 3

You can add operations to the stack by using:

Application.OnUndo text, procedureName

Where Text is the text that appears with the Undo command (Edit menu) and procedureName is a name of one of your subs. You can also programatically undo operations by using:

Application.Undo

I don't think it's possible to access existing undo operations; at least I've never heard of any. There may be a library that you can access online that will allow this.

Hope this helps.

Answer 4

The solution I actually went with was the following:

• Save a copy of the clipboard state
• Clear the clipboard
• Generate the data I want in a tab/newline delimited format, push it to the clipboard
• Simulate a Ctrl+V operation to Excel
• Clear the clipboard
• Restore the original clipboard state

Obviously this is localized to cell manipulations, so you can't push arbitrary operations/callbacks to the undo stack. Also, I'm clearly violating principles around clipboard use in Windows, but if Microsoft exposed a better API for such things (hint), I wouldn't have to.

Additionally, I didn't go with the solution I described in the first comment on David Zemens' answer because I hit some security violations in our environment (i.e., injecting VBA code into a workbook is a no-no).

Anyways, thanks everybody!