ExcelAsyncUtil.QueueAsMacro is breaking the UNDO history

[Jiri Pik]

Hello:

 

Each time I run ExcelAsyncUtil.QueueAsMacro, the entire Excel’s Undo history is deleted. 

See the solution at https://www.dropbox.com/s/8wcxg01sz8stpkc/UndoTester.zip and in Excel, run the method =TestAsyncArrayAutoResize("HI").

Is there a way to preserve the Excel’s UNDO history when running ExcelAsyncUtil.QueueAsMacro???

[Govert van Drimmelen]

Hi Jiri,

This is what Excel always does when a macro is run - whether Excel-DNA or VBA or whatever.

You can set a fresh macro as the current "Undo" macro by calling Application.OnUndo, but you'll have to keep track of all the required information to do the 'Undo'. In any event, the previous items in the user's Undo stack will be lost.

I don't know of any approach that would work around this.

Regards,

Govert

[Jiri Pik]

Govert:

 

thanks for this. I believe there is a way to capture the contents of the Undo stack before you call the QueueAsMacro and then to recover it.

 

I am on this and will advise if I get anywhere.

 

All my best.

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/groups/opt_out.

[Govert van Drimmelen]

Hi Jiri,

Something like you describe would be a breakthrough!

Please write back if you find some way.

Regards,

Govert

[Jiri Pik]

Hi:

 

I will summarize all the research findings in a blog article once I finish.

 

Key insights:

1.       The Undo stack is available at

 

dynamic xlApp = ExcelDnaUtil.Application;

var undoControl = xlApp.CommandBars("Standard").Controls("&Undo").Control;

var list = new List<string>();

for (var i = 1; i <= undoControl.ListCount; ++i)

{

    list.Add(undoControl.List[i]);

}

 

However, this is only read-only and I cannot write there anything back. I have been unable to find any web page where this has been done.

Does anybody know how to write to that Undo Control???? There are some articles claiming that it’s not possible by design, but I guess there must be a C++ way. Anybody knows how?

 

2.       The Default ApplicationOnUndo are only 1-step back undo support and it entirely wipes the default undo stack.

3.  Having said that, there is nothing which stops me from implementing my own undo using the code below where I would keep a stack of all changes and somehow trimming the stack when the sheet gets closed etc etc etc

dynamic xlApp = ExcelDnaUtil.Application;

((AppEvents_Event)xlApp).SheetChange += (sh, target) =>

{

    try

    {

        ExcelAsyncUtil.QueueAsMacro(() =>

        {

            var formula = target.Formula;

        });

    }

    catch

    {

    }

};

 

And then assigning the undo to my own shortcut key, say CTRL+SHIFT+Z.

 

There are some exceptional cases to be considered, but it’s easily doable.

 

What are your thoughts on this? I can prepare a simple library for this & share it for a feedback.

[Patrick O'Beirne]

Jiri, you have probably already seen this but just in case...

http://social.msdn.microsoft.com/Forums/office/en-US/9ef5ac45-9c62-4f17-a074-e9e8326941b0/capture-undo-history-before-event-in-excel?forum=exceldev

http://www.jkp-ads.com/articles/undowithvba00.asp

[Jiri Pik]

Yes, I have seen this, thank you. but they do not address the of undo when you have undo for formulas of flashing real time prices or expansion of excel results.

All my best

-----Original Message-----
From: exce...@googlegroups.com [mailto:exce...@googlegroups.com] On Behalf Of Patrick O'Beirne
Sent: Wednesday, January 22, 2014 9:53 AM
To: exce...@googlegroups.com
Subject: Re: [ExcelDna] Re: ExcelAsyncUtil.QueueAsMacro is breaking the UNDO history

[Jiri Pik]

Hi Everyone:

 

I got a working Undo solution whose architecture works with macros, Excel-DNA functions etc.

 

Check out the article http://jiripik.me/2014/01/27/how-to-support-excel-undo-and-redo-with-excel-dna/

 

Any feedback is much appreciated.

[Naju Mancheril]

Are you storing other actions in your stack besides cell value changes? For example, if I delete a column, can I undo that as well with Ctrl+Shift+Z?

[Jiri Pik]

Yes, I have just tried it and it works well.

 

BUT there are some extreme cases which still needs to be taken care of. Left as an exercise …. hehehehe

 

From: exce...@googlegroups.com [mailto:exce...@googlegroups.com] On Behalf Of Naju Mancheril
Sent: Monday, January 27, 2014 5:28 PM
To: exce...@googlegroups.com
Subject: Re: [ExcelDna] Re: ExcelAsyncUtil.QueueAsMacro is breaking the UNDO history

 

Are you storing other actions in your stack besides cell value changes? For example, if I delete a column, can I undo that as well with Ctrl+Shift+Z?

--

[Govert van Drimmelen]

Hi Jiri,

I tried your project - it's an interesting start!

(To make it work on my machine I had to change the Excel path and the xll path on the Project Properties -  Debug page, and change the Post-Build command line to copy EccelDna.xll instead of ExcelDna64.xll).

I think when processing the Excel events (like Application.SheetChange) you need not wrap the work in ExcelAsyncUtil.QueueAsMacro. Also, the Undo will run as a macro already, so you need no ExcelAsyncUtil.QueueAsMacro here either. One consequence on having the event handlers call a macro is that the Excel Undo stack is now cleared on every sheet change. I'm not sure this is what one wants.

When I tried it, entering a few different cells on a sheet, I could only Undo a single action with Ctrl+Z. Not sure why that is, because you seem to store multiple actions.

I had another thought - if you were able to keep track of all the Excel events, including stuff like formatting changes, one might be able to make a custom Macro Recorder this way. The action records you keep would be exactly what is needed to write out a macro script.

Anyway - thanks for posting this!

Regards,

Govert

[Jiri Pik]

Govert:

 

Thanks for the feedback! Yes, it’s just a proof of concept which seems to work and now it’s time to get into the details. hehehe

 

Re: Listeners I am having funny problems with crashing Excel which can be only solved with the attribute [HandleProcessCorruptedStateExceptions]

 

 

Ø  When I tried it, entering a few different cells on a sheet, I could only Undo a single action with Ctrl+Z.

 

You should try CTRL+SHIFT+Z. That’s what I am listening to, but yes, I will overwrite the default keyboard shortcut later.

 

I will be working on the improvements as time allows (very little for this), if anybody wants to help out, it would be much appreciated – the project is public source on github.

 

All my best.

[Gareth Hayter]

Hi Jiri

Did you make any further progress on this? Also, the URL you posted above for the project no longer seems to be valid. Is your code accessible somewhere else?

Many thanks,

Gareth.

[Jiri Pik]

Gareth:

Progress

I do know how to make it more robust by intercepting the Commands structure of Excel but have no time for this now, unfortunately.

Code

The code has been removed since I am redesigning the entire website and since the code needs some changes.

For more options, visit https://groups.google.com/d/optout.

[Leo Gurdian]

Hi Jiri,

I think you're into something great here.  This is a huge problem on the the extensibility of Office since 'undo' is a critical piece to people's workflows. 

Would you please kindly share the repo on GitHub ? I'd like to study it. I went to your page but you have no public repos.

Thanks,

-Leo

[Robson Ferreira]

Hello guys,

Resurrecting this topic. I'm trying to find some way to preserve undo after running a macro. Is there any C API for this?

Based on the code found on this topic, I can read the undo list, but I'm unable to restore the undo list after macro execution. Did anyone actually did this? It is possible? 

Thanks,

Robson Ferreira

[Robert Goodman]

Definitely following this...