vba 是什么导致 Worksheet_Activate 上出现“自动化错误未指定错误”？

What is causing "Automation error Unspecified error" on Worksheet_Activate?

提问by Farfromunique

I have a worksheet named "Dates" (object name is A_Dates) that needs to be calculated when it is activated (It may be worth noting that this is in my Personal macro workbook). I regularly have workbooks open that have too many calculations in the for me to have auto-calculation on. So I have auto-calc set to manual, and the following code in the worksheet:

我有一个名为“Dates”（对象名称为 A_Dates）的工作表，需要在激活时进行计算（可能值得注意的是，这在我的个人宏工作簿中）。我经常打开工作簿，其中有太多的计算，我无法进行自动计算。所以我将自动计算设置为手动，工作表中的以下代码：

Private Sub Worksheet_Activate()
   A_Dates.Calculate
End Sub

This has worked fine for the last 3 months, day-in and day-out. Yesterday, it stopped working. It now throws this error on the declaration line:

这在过去的 3 个月里日复一日地运作良好。昨天，它停止工作。它现在在声明行上抛出这个错误：

Microsoft Visual Basic
Automation error
Unspecified error
[OK] [Help]

I have tried changing how I reference the sheet, using:

我尝试使用以下方法更改我引用工作表的方式：

Sheets("Dates").Calculate

and

和

ActiveSheet.Calculate

to no avail. I've also included error handling:

无济于事。我还包括错误处理：

On Error Resume Next

which doesn't prevent it. I've even gone so far as:

这并不能阻止它。我什至做到了：

Private Sub Worksheet_Activate()
   On Error GoTo headache
   Sheets("Dates").Calculate
Exit Sub

headache:
   Exit Sub
End Sub

and it still shows up. I am totally at a loss. Help?

它仍然出现。我完全不知所措。帮助？

Additional Information

附加信息

I have the following references, and use all of them in various macros in this workbook:

我有以下参考资料，并在本工作簿的各种宏中使用了所有这些参考资料：

Visual Basic for Applications

Visual Basic 应用程序

• Microsoft Excel 12.0 Object Library
• OLE Automation
• Microsoft Office 12.0 Object Library
• Microsoft Scripting Runtime
• Microsoft Forms 2.0 Object Runtime
• Microsoft HTML Object Library
• Microsoft Internet Controls
• Microsoft ActiveX Data Objects 2.8 Library
• Microsoft ActiveX Data Objects Recordset 2.8 Library

• Microsoft Excel 12.0 对象库
• OLE自动化
• Microsoft Office 12.0 对象库
• Microsoft 脚本运行时
• Microsoft Forms 2.0 对象运行时
• 微软 HTML 对象库
• 微软互联网控制
• Microsoft ActiveX 数据对象 2.8 库
• Microsoft ActiveX 数据对象记录集 2.8 库

采纳答案by Farfromunique

I found the issue. One of my forms had a control on it that apparently stopped working, and this had a ripple effect.

我发现了这个问题。我的一个表单上有一个明显停止工作的控件，这产生了连锁反应。

The control in question was "Microsoft ProgressBar Control, version 6.0". I do not know WHY it stopped working, but removing the form (and, of course, all references to it) resolved the issue.

有问题的控件是“Microsoft ProgressBar Control, version 6.0”。我不知道为什么它停止工作，但删除表单（当然还有所有对它的引用）解决了这个问题。

回答by Troy Brooks

We ran into the same problem, but with a twist - we have several people all using the same macros successfully, but one is having the "Automation Error" "Unspecified Error" problem. The other answer helped meidentify that the problem might be due to the "Microsoft ProgressBar Control". (Thank you very much)

我们遇到了同样的问题，但有一个转折——我们有几个人都成功地使用了相同的宏，但有一个是“自动化错误”“未指定错误”问题。 另一个答案帮助我确定问题可能是由于“Microsoft ProgressBar Control”造成的。（非常感谢）

But instead of removing the form, I unregistered and registered the MSCOMCTL.OCX on the user's PC that was having problems and he's back in business again. I wish I knew what caused the registration of the control to go south - this is not the first time I've had to track down problems with this control.

但是我没有删除表单，而是在用户的 PC 上注销并注册了 MSCOMCTL.OCX，该用户出现问题，他又重新开始营业了。我希望我知道是什么导致控件的注册向南 - 这不是我第一次不得不追踪这个控件的问题。

To unregister and register the control:

要取消注册和注册控件：

Use an “Elevated command prompt” (command prompt run as an administrator), issue the following commands:

使用“Elevated command prompt”（以管理员身份运行的命令提示符），发出以下命令：

 Regsvr32 /u c:\windows\SysWOW64\MSCOMCTL.OCX

 Regsvr32 c:\windows\SysWOW64\MSCOMCTL.OCX

NOTE: the /u unregisters the ocx

注意： /u 取消注册 ocx

回答by Dick Kusleika

Whenever I get strange errors like this, the first thing I do is clean the code with http://www.appspro.com/Utilities/CodeCleaner.htm. It's a free add-in, but you can do it manually too. Just copy your code out of the module and into a text file (or right click and Export). Then delete the code in the module, compile and save, and put the code back in.

每当我遇到这样的奇怪错误时，我做的第一件事就是使用http://www.appspro.com/Utilities/CodeCleaner.htm清理代码。这是一个免费的插件，但您也可以手动完成。只需将您的代码从模块中复制到文本文件中（或右键单击并导出）。然后把模块里的代码删掉，编译保存，把代码放回去。

When Excel compiles "on the fly" it's creating p-code which then gets compiled to machine code. Sometimes, particularly with heavy editing, the p-code gets corrupted. Copying the code out, deleting it, and copying back in forces Excel to regenerate the p-code.

当 Excel“即时”编译时，它会创建 p 代码，然后将其编译为机器代码。有时，尤其是在大量编辑时，p 代码会损坏。将代码复制出来，删除它，然后再复制回来，会强制 Excel 重新生成 p 代码。

I've solved some really strange behavior with this method. Hopefully it works for you too.

我已经用这种方法解决了一些非常奇怪的行为。希望它也适用于您。

回答by Ian

My fix on this problem was to add the following references in VBA - Tools - References.

我对这个问题的解决方法是在 VBA - 工具 - 引用中添加以下引用。

Microsoft ActiveX Data Objects 2.8 Library Microsoft ActiveX Data Objects Recordset 2.8 Library

Microsoft ActiveX 数据对象 2.8 库 Microsoft ActiveX 数据对象 Recordset 2.8 库

回答by Gabe F

I saw this error pop up as a dialog box right before Excel stopped working. I was debugging a new script that opens several hundred excel files and pulls some info from each one. To resolve, I added several 'DoEvents' commands and my issue with this error went away immediately.

在 Excel 停止工作之前，我看到此错误作为对话框弹出。我正在调试一个新脚本，它打开数百个 excel 文件并从每个文件中提取一些信息。为了解决这个问题，我添加了几个“DoEvents”命令，我的这个错误问题立即消失了。

回答by Steve

I just had this problem, no luck with removing the loading controls or adding/moving references.

我刚刚遇到了这个问题，在删除加载控件或添加/移动引用方面没有运气。

Fixed by adding a DoEventsfor Internet Explorer like so:

通过DoEvents为 Internet Explorer添加一个像这样修复：

Do
  DoEvents
Loop Until ie.readyState = READYSTATE_COMPLETE

Note too, that this value can bounce change between "complete" and "busy" so a more robust loop here may be beneficial.

还要注意，这个值可以在“完成”和“忙碌”之间反弹变化，所以这里更强大的循环可能是有益的。

回答by Patrick

Just a note for other people coming with "automation error". I had one too and found it to be a special case of integer overflow where it would break in the loop and give automation error instead of the integer overflow error. If you didnt find a solution above check if you are using the correct dimensions.

只是给其他人带来“自动化错误”的注释。我也有一个，发现它是整数溢出的一种特殊情况，它会在循环中中断并给出自动化错误而不是整数溢出错误。如果您没有找到上述解决方案，请检查您使用的尺寸是否正确。

回答by codechick

I had two "exact" blocks of VBA code, one executed, the other had the automation error. I ran the Debugger and it got stuck on the code block below:

我有两个“精确”的 VBA 代码块，一个执行了，另一个有自动化错误。我运行了调试器，它卡在了下面的代码块上：

Do While ie.readyState = READYSTATE_COMPLETE
Application.StatusBar = "Going to StackOverflow ..."
DoEvents
Loop

So, I pasted the code block that worked and the one that didn't into separate Microsoft word documents and then used the Review / Compare feature. It revealed the error:

因此，我将有效的代码块和无效的代码块粘贴到单独的 Microsoft Word 文档中，然后使用“查看/比较”功能。它揭示了错误：

Do While ie.readyState =READYSTATE_COMPLETE (equals sign - incorrect)

Do While ie.readyState =READYSTATE_COMPLETE（等号 - 不正确）

vs.

对比

Do While ie.readyState <>READYSTATE_COMPLETE (double brackets - correct)

Do While ie.readyState <>READYSTATE_COMPLETE (双括号 - 正确)

I highly suggest using Word to assist where visually it just isn't clear.

我强烈建议使用 Word 来帮助视觉上不清楚的地方。

Yes!

是的！

回答by zubasub_19

Try to compile your program to a lower version of the OS.

尝试将您的程序编译到较低版本的操作系统。