vba 偶发自动化错误：发生异常，可能是基于引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23526974/
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
Sporadic Automation Error: Exception Occurred, Possibly Reference Based
提问by BenW301
My team has had this strange error bugging us during development. From time to time when running VBA on an excel tool that my team is building, Excel will crash and the "Microsoft has stopped working" pop-up will appear and when the user selects the 'close the program' option another pop-up stating "Automation Error Exception Occurred" appears. Now here is where it gets weird:
我的团队在开发过程中遇到了这个奇怪的错误。有时在我的团队正在构建的 excel 工具上运行 VBA 时，Excel 会崩溃并出现“Microsoft 已停止工作”弹出窗口，当用户选择“关闭程序”选项时，另一个弹出窗口说明出现“发生自动化错误异常”。现在这里变得很奇怪：
It only happens on the machines running 2007, 2010 and 2013 machines never have this issue.
Only happens every couple weeks (every 3-5 versions of the tool). These same machines have been running and testing the tool all day every day up until the point it occurs.
There is no noticeable difference between the versions that work and those that don't from a code perspective.
The have been several instances where a machine will run the VBA code fine and suddenly the error will start occurring. Once, a 2007 machine encounters the error, that file will never work on that machine or any other 2007 machine again. BUT, if given a fresh copy of the same exact file, sometimes the problem will disappear.
after some online review, I have tracked the error down to three references being used: OLE Automation, Microsoft Office 12.0 Object Library, Microsoft CDO for Windows 200 Library. If any of these three are disabled in any combination the VBA will start running bug-free.
它只发生在运行 2007、2010 和 2013 机器的机器上，从来没有这个问题。
仅每两周发生一次（每 3-5 个版本的工具）。这些相同的机器每天都在运行和测试工具，直到它发生为止。
从代码的角度来看，有效的版本和无效的版本之间没有明显的区别。
有几个实例，机器可以正常运行 VBA 代码，但突然开始发生错误。一旦 2007 机器遇到错误，该文件将永远不会再次在该机器或任何其他 2007 机器上工作。但是，如果给出相同文件的新副本，有时问题会消失。
在进行了一些在线后，我将错误追溯到正在使用的三个引用：OLE 自动化、Microsoft Office 12.0 对象库、Microsoft CDO for Windows 200 库。如果以任何组合禁用这三个中的任何一个，VBA 将开始无错误运行。
My questions are:
我的问题是：
- What could cause an error that would allow a tool to work a dozen times on a machine (with or without changes being saved) and suddenly stop running and never run again until replaced with a fresh copy?
- What possible interactions between the three above references could be related to this?
- 什么可能导致错误，使工具在机器上运行十几次（保存或不保存更改）并突然停止运行并且永远不会再次运行，直到被新副本替换？
- 上述三个参考文献之间的哪些可能的相互作用可能与此有关？
回答by BenW301
So after a lot of debugging we finally found the error. One of the programmers had a script that ran prior to the form loading in which he was pre-populating some of the listboxes in the form. Apparently because this SUB existed outside the form and/or because the form had not been initialized yet, this was what was causing excel to crash. Anyway, hope the answer helps anyone else out there who runs into the same thing.
所以经过大量的调试我们终于发现了错误。其中一位程序员有一个脚本，该脚本在加载表单之前运行，他在该脚本中预先填充了表单中的一些列表框。显然是因为这个 SUB 存在于表单之外和/或因为表单尚未初始化，这就是导致 excel 崩溃的原因。无论如何，希望答案能帮助其他遇到同样事情的人。
