vba Excel 工作簿打开事件宏并不总是运行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5438548/
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
Excel Workbook Open Event macro doesn't always run
提问by ScottSM
I've got a Workbook_Open event macro (and it's in ThisWorkbook) that doesn't always run.
我有一个并不总是运行的 Workbook_Open 事件宏（它在 ThisWorkbook 中）。
- If Excel is closed and I double-click the .xls file from Windows Explorer, it does run.
- If I launch Excel from Start/Programs (with no .xls file) and then open the file, it does run.
- If I've already opened the file in Excel, but then close the file (leaving Excel open) and reopen it, then the macro does not run.
- 如果 Excel 已关闭并且我在 Windows 资源管理器中双击 .xls 文件，它会运行。
- 如果我从“开始”/“程序”（没有 .xls 文件）启动 Excel，然后打开该文件，它会运行。
- 如果我已经在 Excel 中打开了该文件，但随后关闭了该文件（让 Excel 保持打开状态）并重新打开它，则该宏不会运行。
I've got security set to medium and I enable macros whenever it opens.
我已将安全设置为中等，并且在打开时启用宏。
What do I need to do to get the macro to run whenever I open it, not just the first time for this Excel session?
我需要做什么才能让宏在每次打开时运行，而不仅仅是第一次打开这个 Excel 会话？
(btw, this is Excel 2003)
（顺便说一句，这是 Excel 2003）
回答by Lance Roberts
I thought that this was the most cogent articleon this problem (which is a long-standing never explained completely erratic bug that Excel exhibits).(dead link)
我认为这是关于这个问题的最有说服力的文章（这是一个长期存在的从未解释过的 Excel 表现出的完全不稳定的错误）。（死链接）
In short, in many cases it's a timing thing caused because the workbook is trying to calculate stuff when it opens and that gets in the way of the Workbook_Open event. The way to test on yours to see if that it for this situation, is to first rename any UDFs or Macros called by cells so that they won't get called and see if that changes anything.
简而言之，在许多情况下，这是由于工作簿在打开时试图计算内容而导致的计时问题，这妨碍了 Workbook_Open 事件。测试您的方法以查看它是否适用于这种情况的方法是首先重命名单元格调用的任何 UDF 或宏，以便它们不会被调用并查看是否会改变任何内容。
回答by DrMarbuse
I experienced the same problem.
我遇到了同样的问题。
I tested a workbook on my computer without any troubles. After destributing it to my customers I was told, that some combo-boxes stayed empty. These are usually filled from inside the workbook_openroutine.
I tried different things to enable the workbook_open-Event - without success.
我在我的电脑上测试了一个工作簿，没有任何问题。将它分发给我的客户后，我被告知，一些组合框是空的。这些通常是从workbook_open例程内部填充的。我尝试了不同的方法来启用workbook_open-Event - 没有成功。
Finally, I found that disabling all userdefined Functions (UDF) lead to correct execution of workbook_open.
最后，我发现禁用所有用户定义的函数 (UDF) 会导致正确执行workbook_open.
As my workbook is opened from another file, I will try to set calculation to manual first and then run the workbook_openmanually. This may be done by defining it
由于我的工作簿是从另一个文件打开的，我将尝试先将计算设置为手动，然后workbook_open手动运行。这可以通过定义它来完成
public sub workbook_open
instead of
代替
private sub workbook_open
Strange, that excel does not time this by itself...
奇怪的是，那个 excel 本身并没有计时......
回答by Rob
A late answer (better than none).
迟到的答案（总比没有好）。
I've had this problem now a few times (with Excel 2010). The solution that has always worked (so far) was: remove conditional formatting, in particular if it contains UDF as conditions. As @LanceRoberts wrote in an above post, it's ultimately due to UDF calculations "overriding" the Open event, but I've found that those are particularly harmful if used in conditional formats.
我现在遇到过这个问题几次（使用 Excel 2010）。一直有效的解决方案（到目前为止）是：删除条件格式，特别是如果它包含 UDF 作为条件。正如@LanceRoberts 在上面的一篇文章中所写的那样，这最终是由于 UDF 计算“覆盖”了 Open 事件，但我发现这些在条件格式中使用时特别有害。
回答by Ira Burton
I was experiencing almost identical behavior, and found that it is due to a bug that occurs if conditional formatting rules are erroring out. It turns out that if the conditional formatting rules are based on any setup by the macros, and that causes the conditional formatting to error, the Workbook_Open macro will not even attempt to run.
我遇到了几乎相同的行为，并发现这是由于条件格式规则出错时发生的错误。事实证明，如果条件格式规则基于宏的任何设置，并且导致条件格式错误，Workbook_Open 宏甚至不会尝试运行。
To test, make a copy of your file, and delete all conditional formatting from the workbook. Save and reopen. If it fixes your issue, then rework the conditional formatting rules to not depend on functions/values that will be broken before the Workbook_Open macro runs.
要进行测试，请复制您的文件，然后从工作簿中删除所有条件格式。保存并重新打开。如果它解决了您的问题，则重新设计条件格式规则，使其不依赖于在 Workbook_Open 宏运行之前将被破坏的函数/值。
回答by Arturo Llano
This happens when a workbook is closed with an Application.EnableEvents set to false, and then you open another workbook within the same instance of excel opened. To avoid this, make sure that all of your processes that disable events, reenable them before terminating. Special attention to "End" commands, error handlers and "exit sub" sentences in the middle of your program.
当在 Application.EnableEvents 设置为 false 的情况下关闭工作簿，然后在打开的同一 excel 实例中打开另一个工作簿时，会发生这种情况。为避免这种情况，请确保所有禁用事件的进程在终止之前重新启用它们。特别注意程序中间的“End”命令、错误处理程序和“exit sub”语句。
回答by user8222516
What causes it is that your other archive, the one you openned first, have a Workbook_Openprocedure; Excel doesn't excute it a second time.
是什么原因导致你的另一个档案，你第一个打开的，有一个 Workbook_Open程序；Excel 不会第二次执行它。
回答by George French
This happened to me also and took me hours to figure out.
这也发生在我身上，我花了几个小时才弄清楚。
Turns out the TODAY()function in Excel was causing the problem. Once deleted from my worksheet everything worked again. Very strange bug.
结果是 Excel 中的TODAY()函数导致了问题。从我的工作表中删除后，一切又恢复了。很奇怪的bug。
回答by Five Nine
To add to the Arturo Llano post: The following code was used to monitor the Workbook_Open event and then run ProcessX whenever a workbook was opened.
添加到 Arturo Llano 帖子：以下代码用于监视 Workbook_Open 事件，然后在打开工作簿时运行 ProcessX。
ProcessX contained an End statement. The result was that it worked only the first time. The End wiped out AppX, so there was no further monitoring of events. Removing End fixed the problem. (Using End is bad practice anyway as it stops everything without any kind of cleanup or termination of other resources).
ProcessX 包含一个 End 语句。结果是它只在第一次起作用。End 消灭了 AppX，因此没有进一步的事件监控。删除 End 解决了问题。（无论如何，使用 End 是不好的做法，因为它会在没有任何类型的清理或终止其他资源的情况下停止一切）。
'Code in: Personal.xlsb ThisWorkbook
'代码：Personal.xlsb ThisWorkbook
Public WithEvents AppX As Application
Private Sub Workbook_Open()
Set AppX = Application
End Sub
Private Sub AppX_WorkbookOpen(ByVal wb As Workbook)
'A 1-second delay to allow opening to complete before ProcessX starts.
Application.OnTime Now + TimeValue("00:00:01"), "ProcessX"
End Sub
回答by Flymann
I encountered the same problem, and I avoid it using the security settings. I use the options settings then confidentiality center, then "params of confidentiality center" (sorry but its a translation of the french version :-p) then "files approuved" or something like this. And add the file containing the excel workbook in. And its finnaly worked after that.
我遇到了同样的问题，我使用安全设置避免了它。我使用选项设置，然后是机密中心，然后是“机密中心的参数”（抱歉，它是法语版本的翻译：-p）然后是“文件已批准”或类似的东西。并添加包含 excel 工作簿的文件。然后它最终工作。
Looked everywhere and never find that solution.
到处寻找，始终找不到解决方案。
Hope it'll help someone
希望它会帮助某人
回答by bvukas
A few suggestions:
几点建议：
- Try signing the workbook with a digital certificate. Add this certificate to the Trusted Certificates store then try again.
- If this is machine-specific, try re-installing Office.
- 尝试使用数字证书签署工作簿。将此证书添加到受信任的证书存储区，然后重试。
- 如果这是特定于机器的，请尝试重新安装 Office。
Make sure you have the latest service pack(s) applied.
确保您应用了最新的服务包。
