How to push an operation on an Excel stack to cancel?

Question

I am creating a VSTO Excel add-in that manages the values ​​of several cells. I would like to allow the user to undo and modify the changes created by the add-in through the standard Excel functions. I prefer to avoid using VBA.

Is it possible? If so, how?

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

Answer 1

The solution I was really working on was the following:

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

Obviously, this is localized for cell manipulation, so you cannot push arbitrary operations / callbacks onto the undo stack. In addition, I clearly violate the principles of using the clipboard in Windows, but if Microsoft provides the best API for such things (a hint), I would not have to.

Also, I did not go with the solution that I described in the first comment on David Zemens's answer, because I hit some security breaches in our environment (i.e. injecting VBA code into the book does not matter).

Anyway, thanks to everyone!

Answer 2

You did not specify which version of the Excel / .NET / VSTO runtime you want to use, but it does not really matter:]
Custom Undo simply cannot be done without entering VBA, where you will need to set the undo method to roll back your actions using compensation (i.e., Inverse operations) or by restoring the saved state.

Answer 3

I know you want to avoid VBA, but as others have said, it is actually impossible to get Undo .

Here is a VBA example that saves an existing selection as a 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 4

You can add operations to the stack using:

 Application.OnUndo text, procedureName 

Where Text is the text that appears with the Undo command (Edit menu), and schema_name is the name of one of your subscribers. You can also programmatically cancel operations using:

 Application.Undo 

I do not consider it possible to access existing cancellation operations; at least i never heard of. There may be a library that you can access on the Internet that will allow this.

Hope this helps.