在宏运行期间保存undo stack [英] Save undo stack during macro run

问题描述

我想知道是否有一种方法来保存在宏运行后撤消动作的能力。
我不在乎宏的结果 - 只需要撤消用户在宏之前完成的操作。

I am wondering if there is a way to save ability to undo actions after macro has been run. I do not care about results of macro - just need to undo actions that were done by user before macro.

背景：
我有一个在workheet_change事件上的宏，用于记录在此工作表上更改的时间和时间。我不希望它限制用户撤销他/她的动作的能力。

Background: I have a macro on the worksheet_change event that logs who and when made the change on this worksheet. I do not want it to restrict user's ability to undo his/her actions.

推荐答案

没有简单的方法来做到这一点，但这是可能的。这样做的方法是创建三个宏，并使用一些全局变量来保存状态：

There is no easy way to do this, but it's possible. The approach to this is to create three macros, and use some global variables to save state:

1. MyMacro


2. MyStateSavingMacro


3. MyStateRevertingMacro

例如我的宏更改活动工作表的范围 A1：A10 中的单元格。所以，每当运行我的宏的代码被调用时，它执行

E.g. My macro changes Cells in Range A1:A10 of the active sheet. So, whenever the code to run my macro is called, it executes

Sub MyMacro()       
    Call MyStateSavingMacro() 
    ' Copies contents and formulae in range A1:A10 to a global data object

    '... Code for MyMacro goes here
    '
    '................

    Call Application.OnUndo("Undo MyMacro", "MyStateRevertingMacro")
    'This puts MyStateRevertingMacro() in the Undo queue
    'So pressing ctrl-Z invokes code in that procedure
End Sub


Sub MyStateSavingMacro()
    ' Code to copy into global data structures anything you might change
End Sub

Sub MyStateRevertingMacro
    ' Code to copy onto the spreadsheet the original state stored in the global variables
End Sub

所以就是这样。这不漂亮，但可以做到。
参考： http：/ /msdn.microsoft.com/en-us/library/office/ff194135%28v=office.15%29.aspx

So there it is. It's not pretty, but can be done. Ref: http://msdn.microsoft.com/en-us/library/office/ff194135%28v=office.15%29.aspx

编辑：
要在运行MyMacro之前保留撤销队列，不便的解决方案是创建一个4-5 MyStateRevertingMacro_1 ，_2等的链，您可以在其中应用您的Worksheet_Change日志记录系统中的信息，然后链接每个这些的$ code Application.OnUndo ，所以 Application.OnUndo 对于每个恢复宏将引用之前的状态回归代码。

To preserve the Undo queue prior to your MyMacro being run, the inelegant solution would be to create a chain of 4-5 MyStateRevertingMacro_1, _2, etc. where you can apply the information from your Worksheet_Change logging system and then chain-up the Application.OnUndo in each of those, so Application.OnUndo for each of those Reverting Macros would refer the previous state reversion code.

这篇关于在宏运行期间保存undo stack的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！