如何动态禁用和启用宏？

I would like to test an Excel VBA app I made.
However the VBA code messes around with the visibility of cells and that's a pest when editing the sheet.

我想测试一下我制作的Excel VBA应用程序。然而，VBA代码与细胞的可见性混在一起，这在编辑工作表时是一种害虫。

Is there an option is enable and disable macro's on the fly without having to

是否有一个选项是启用和禁用宏的动态，而不必

Close the sheet
Change the macro settings
Reopen the sheet
Close the sheet
Change the macro settings.
etc.

5 个解决方案

#1

As far as I know, you can't enable / disable macros from an opened workbook on the fly.
Yet, you shouldn't have to because macros are only triggered thanks to a user click.

据我所知，您无法动态启用/禁用已打开的工作簿中的宏。但是，您不应该这样做，因为只有用户点击才能触发宏。

The only case I would see is for the Event Procedures (Worksheet_Change or else).
You could then create procedures to activate / deactivate events and call them from buttons in your worksbook:

我会看到的唯一情况是事件过程（Worksheet_Change或其他）。然后，您可以创建激活/停用事件的过程，并从作业簿中的按钮调用它们：

Sub enableEvents()
    Application.EnableEvents = True
End Sub

Sub disableEvents()
    Application.EnableEvents = False
End Sub

You can also try these tips from Chris Pearson website using global vars you would change depending on your needs:

您还可以使用全球变量从Chris Pearson网站尝试这些提示，您可以根据需要进行更改：

Public AbortChangeEvent As Boolean

And check if afterwards:

然后检查是否：

Private Sub Worksheet_Change(ByVal Target As Range)
    If AbortChangeEvent = True Then
        Exit Sub
    End If
    '
    ' rest of code here
    '
End Sub

#2

To disable macros on the fly, use "Application.EnableEvents = False" via the Immediate window in the VBA editor (and "Application.EnableEvents = True" to turn them back on).

要动态禁用宏，请通过VBA编辑器中的立即窗口使用“Application.EnableEvents = False”（和“Application.EnableEvents = True”将其重新打开）。

#3

You can also hold down SHIFT when you open a document to disable macros.

您还可以在打开文档以禁用宏时按住SHIFT键。

#4

As of Excel 2010* there's actually an option on the "Developer" tab which allows you to disable macros as easy as ABC. Design Mode lets you do just that!

从Excel 2010 *开始，“开发人员”选项卡上实际上有一个选项，允许您像ABC一样简单地禁用宏。设计模式让你做到这一点！

*Maybe even earlier versions.

*甚至可能是早期版本。

#5

I have Office 2013 and I found a button on the VBA window that did this for me very easily.

我有Office 2013，我在VBA窗口找到了一个按钮，非常容易为我做这个。

There's a play, pause and stop button on the toolbar. In Excel, they are actually called, Run Macro, Break and Reset.

工具栏上有播放，暂停和停止按钮。在Excel中，它们实际上被称为“运行宏”，“中断”和“重置”。

Click the Break button (pause) and any running macros should stop running. I only tested it on one macro but seems reasonable to presume that this will work in general.

单击“中断”按钮（暂停），任何正在运行的宏都应该停止运行。我只在一个宏上测试过它，但似乎有理由认为这样可以正常工作。

Also, I believe those buttons were there for many versions of Excel, so it's worth checking earlier versions.

此外，我相信这些按钮适用于许多版本的Excel，因此值得检查早期版本。

#1