vba 在宏运行期间保存撤消堆栈
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24185942/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Save undo stack during macro run
提问by Eduard3192993
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.
我想知道是否有办法在宏运行后保存撤消操作的能力。我不关心宏的结果 - 只需要撤消用户在宏之前完成的操作。
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.
背景：我在 worksheet_change 事件上有一个宏，用于记录谁以及何时在此工作表上进行了更改。我不希望它限制用户撤消他/她的操作的能力。
采纳答案by hnk
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:
没有简单的方法可以做到这一点，但这是可能的。解决这个问题的方法是创建三个宏，并使用一些全局变量来保存状态：
- MyMacro
- MyStateSavingMacro
- MyStateRevertingMacro
- 我的宏
- 我的状态保存宏
- MyStateRevertingMacro
E.g. My macro changes Cells in Range A1:A10of the active sheet. So, whenever the code to run my macro is called, it executes
例如，我的宏更改了活动工作表A1:A10范围内的单元格。因此，每当调用运行我的宏的代码时，它都会执行
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
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
所以就是这样。这不漂亮，但可以做到。参考：http: //msdn.microsoft.com/en-us/library/office/ff194135%28v=office.15%29.aspx
Edit:
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.OnUndoin each of those, so Application.OnUndofor each of those Reverting Macros would refer the previous state reversion code.
编辑：要在运行 MyMacro 之前保留撤消队列，不雅的解决方案是创建 4-5 MyStateRevertingMacro_1、 _2 等的链，您可以在其中应用 Worksheet_Change 日志系统中的信息，然后将Application.OnUndoin每一个，因此Application.OnUndo对于每个还原宏，都将引用之前的状态还原代码。
回答by Denis Vakula
You can use the hidden mirror sheet to do this. Of course it will work if your worksheet is simple enough. You must decide which cells are editable and copy them TO mirror sheet, which are not editable and copy them FROM mirror sheet. And your macro should work only in the mirror sheet. That's it.
您可以使用隐藏的镜子板来做到这一点。当然，如果您的工作表足够简单，它会起作用。您必须决定哪些单元格可编辑并将它们复制到镜像表，哪些不可编辑并从镜像表复制它们。你的宏应该只在镜像表中工作。就是这样。
回答by user8263530
This one is a bit old now but in case anyone is still trying to get this to work - I just tried setting the undo stack to be able undo the formatting on a column that a macro had reformatted and noticed that by doing that the full undo command worked (before I added this bit the undo was unavailable) - does not matter what the custom undo code contains (in my case I had not even created the routine yet), the application undo works perfectly
这个现在有点旧了，但如果有人仍然试图让它工作 - 我只是尝试设置撤消堆栈以能够撤消宏重新格式化的列上的格式，并注意到通过这样做完全撤消命令有效（在我添加这一点之前撤消不可用） - 自定义撤消代码包含什么（在我的情况下我什至还没有创建例程），应用程序撤消工作完美
Application.OnUndo "Undo Amount Format", "sUndo_Col2"
Application.OnUndo "Undo Amount Format", "sUndo_Col2"
