Excel_Toolkit: Some undo functions disabled

[rwemay]

Description:

Undo doesn't work for some functions, for example add/remove columns/rows in a spreadsheet.

How to replicate:

Try to add/remove columns/rows in a spreadsheet with BHoM forumulae in and see if the undo function works.

Expected behaviour:

Test file(s):

[awakeman]

Are you experiencing this reliably or sporadically? I can't seem to reproduce myself.

[awakeman]

Found the issue: When we do things that modify the workbook, like selecting an item from globalsearch or the ribbon UI, we have to do so by queuing the change as a macro. According to the following answer on stack overflow:

there is not way to stop a worksheet-changing macro from clearing the undo stack.

https://stackoverflow.com/questions/7798575/undo-history-button-clear-after-run-macro-excel

As far as I have been able to tell you have to have this work run from a macro context because it is making changes to the sheet but I will check this to be certain. If that is the case we'll probably need to create our own Undo stack and override that of excel itself, no doubt this will be fraught with complexity to make it behave exactly as excel does normally.

[adecler]

After looking into this, I confirm this is related to calling QueueAsMacro. Some of those calls can be removed but it seems difficult to find a way around using macros. The undo stack is cleared when

• a formula is created with the global menu or the ribbon (Already registered BHoM formulas created with Excel intellisense are fine)
• an error is recorded and displayed in the cell

This problem is not due to Excel DNA and is part of Excel: when a macro is ran, the undo stack is cleared since macros can make large scale changes to multiple spreadsheets so an undo there would be a bit complicated (well, still possible but it looks like Microsoft didn't want to bother).

I don't want to have our own undo stack as it would potentially affect the normal usage of Excel (and would also be quite painful to make sure we cover everything). So I can only think of two possibilities:

• Avoid macros altogether. We are only setting the formula of the current cell so we only do local changes. I wonder if there is an alternative to macros for that situation.
• There might be some support for macros to provide their own undo step (since there is no general solution, it seems reasonable to provide a manual one to the user).

Since it looks like solving this is going to require a decent amount of investigation and time, I will put this on hold for this quarter unless I run out of things to do.

[adecler]

I had a final look at this today.

• Excel allows us to provide our own OnUndo method for a given macro. But it doesn't help us here since Excel still clears the entire undo stack on macro. So the stack will only contain that last undo from the macro.
• Doing our own undo stack is way too much work. Excel-DNA has given up on trying. Some have achieved decent results but their solution still doesn't cover all the possible cases.
• For the record, the undo stack can be accessed via xlexcel.CommandBars["Standard"].Controls["&Undo"] but this is obviously a read-only so no very useful.
• Macros are too critical for our plugin to be avoided. Especially for our formula handling/registering.

• I am marking this issue as parked as I don't see a solution to this. Hopefully we will not have too many of those type of issues but I created a wiki page to document them properly.

[adecler]

Closing as parked for a few years