How do I programmatically control Excel’s Undo list?

Question

One eveident missing thing in Excel is also the fact that it completely lack the feature allowing a plugin or scripted extension to start a transaction of modification and insert it in the Undo list, so that it can be undone

Developers do not necessarily want to access the full undo list (managed by Excel itself or other plugins), but at least they should be able to make modifications to a document within their own undoable transaction (with a plugin API that allows the plugin to undo his own modification when instructed to do so).

Supporting Undo would also help solving such issue caused by conditional formatting.

It is not acceptable that any modificaiton made to the document by an extension or script, even when this script is created and embedded in the document itself by the same author for modifying his own accessible data) causes the Undo list to be arbitrarily and automatically cleared. This clearing should only be limited to the current transaction up to the commit that inserts all modification of that transaction in the undo list.

For now there only existst hacks (such as forcing a script to modify the document by emulating keyboard/mouse events mapped to builtin Excel functions that Excel will insert in its undo list). But this does not work for scripts whose data source or behavior depends on a connnected remote service; this could work however with undoable transactions whose undo action will be managed by the script without having to manage all the rest of the Undo list lile in the currently proposed hacks.

So without changinf the principles of the exposed Excel DOM API and breaing compatibility, most of functions in this API would just look if the plugin/extension has started a transaction, and then it would not clear at all the undo list but will delay all modifications until they are commited explicitly.

Then each plugin would manage the content of that transaction to determine what needs to be undone in it (in a smaller grain known by that plugin only managing its own internal subtransactions).

We need transactional support for Excel with only two new APIs (start transaction which returns a, optional transaction handle owned by the current thread running the plugin: Excel records what is being performed via its standard API, and the extension optionally manage its associated structure of actions to determine what can be undone and how : the other API is to commit and close that transaction, which is then recorded in the Excel's Undo list or to rollback and close that transaction as well; Excel cancels all actions performed in the document since the start of that transaction and does not insert anything in the Undo list which is preserved).

[New question split by moderator from a question about cell text alignment]

CELL TEXT ALIGNMENT NOT WORKING IF CONDITIONAL FORMAT APPLIED - Microsoft Community

Answer 1

Re: Verdy’s reply to Bob

From all appearance’s Jan Karel’s macros provide the functionality you are seeking. Please give them a try. Note in the Conclusion there are some additional tidbits. It appears that people are using his tutorial with great success.

When working with Undo, please keep in mind that Undo behavior has changed over the years. If you use code to manage undo in Office 2011, it will not necessarily behave the same in Office 2004 and earlier. Likewise, undo in Excel for Windows may require different coding from undo in Excel for Mac.

There is a fundamental difference in the way Excel on the Mac handles workbook windows. Be sure to test undo with multiple workbooks open and make your code robust so that it works in all situations.

Answer 2

This answer attempts to address the question as posted (not the follow-up).

Excel MVP Jan Karel Pieterse has a tutorial titled 

Creating An Undo Handler To Undo Changes Done By Excel VBA

I did not attempt to implement the instructions, as I have no immediate need to manage Undo. I did a quick scan of the instructions and I think this code should run OK on your Mac. If you run into any glitches, please feel free to post a question regarding a specific command not working as expected and described in Jan Karel’s article.

Answer 3

First of all, this should be in another thread as it has nothing whatsoever to do with the topic of the original post.

Done!

Answer 4

> Additionally, I feel it is the developer's sole responsibility to supply an undo function if that is needed.

I completely disagree, Excel is informed of the changes made by the extension to the document itself (via the addin's use of the Excel DOM API to modify the document contents).

> in the additiion. Only the Add-in or scripted extension knows what it did,

> and to what extent changes were made.

It is only true for actions that were performed outside the document (for example on a remote service). These actions will have their own local transactions and the plugin can coordinate these transactions. After all Windows already has an API for coordinating transactions in distinct services.

> Therefore, it is solely its responsibility to back out those changes.

Which it cannot do on the Excel document content itself, because everything is lost in the Excel Undo buffer, including modifications that were NOT performed by the addin itself.

> I think it is completely unreasonable to expect the application to have any responsibility to perform these functions.

I think it is the responsability of Excel to NOT clear its Undo buffer compeltely from everything that the adding did NOT modify itself and that it did NOT record (and cannot understand or even see if these other actions were performed by another independant addin.

So the Micosoft assumption in Excel si completely wrong. This is just bad design and clear desire to not develop the necessary transaction support (including with transaction coordination).

Excel is not different from a relational database server for this case, or from a transactional service. It is a database server containing data that can (and should) be historied, and coordinated with other services.

Microsoft can support it in MSSQL, I don't see why it cannot support it too in Office applications (Excel, Word, Publisher, Outlook).

And it's definitely not up to Excel to decice what cannot be safely undone. Clearing the Undo buffer immediately is just... stupid.

And this greatly limits the usability of all addins and the extensibility of Excel if it does not host itself all what only addins can perform (including form input addins, calendar selection addins, email addins : of course you cannot undo the fact that an email was sent, but it's up to the addin to decide what can be undone in a way that leaves useful data; for example the fact of changing the storage folder of an email by moving it to another one can be undone, while preserving the content of the edited email and allowing this edit to be undone).

Only the addin can decide how to roll back its own internal actions, but it can also selectively decide to keep or not the changes in the Excel document and this should be extremely easy to do if Excel did not extended the minor edits by the addin to force them to become major edits to everything else that the adding did not modify.

Finally Excel offers absolutely no stable support for the addin to monitor what has been done to the ducument by Excel itself completely outside what the addin effectively controls (or what it can access to, an addin cannot touch everything in the document, it is sandboxed for many data items, that are still normally undoable by Excel itself with its builtin edit commands).

As a consequence, using any addin to modify data in a Excel document is extremely dangerous (an accidental click or keystroke in the addin may ruin the document content completely and hours of edits in Excel. With addins, you don't gain any time, you loose your work time too frequently and users are instructed to NOT embed any addin in their documents.

And anoter argument: if you can save an Excel document with its addins, these saves can be historied and rolled back. The content of the undo buffer should then also be saveable as well in the docuemtn itself or in versions of the document.

Several applications do that without problem (notably version control systems that offer selective commits or rollbacks; they also support branches as well and mechanisms to merge compatible changes in a branch into another branch, and ten to close one branch as a stable version tat can be exported, for example to create a delivery and release it).

Answer 5

First of all, this should be in another thread as it has nothing whatsoever to do with the topic of the original post. Next, If you really think this is important, use send feedback from the help menu to tell Microsoft, and suggest it be in a future release.

Additionally, I feel it is the developer's sole responsibility to supply an undo function if that is needed in the additiion. Only the Add-in or scripted extension knows what it did, and to what extent changes were made. Therefore, it is solely its responsibility to back out those changes. I think it is completely unreasonable to expect the application to have any responsibility to perform these functions.

Finally, if you wish to continue this discussion, please start another thread.