从Excel VBA损坏和自动化错误中恢复 [英] Recovery from Excel VBA corruption and Automation errors

问题描述

我有一本Excel工作簿，其中有很多工作-宏，外部和实时数据源等-在上个月的一周中大约一次自我崩溃.

I have an Excel workbook with a lot going on – macros, external and realtime data sources, etc. – that has been breaking itself roughly once a week for the last month.

运行宏并获取以下内容时，破损通常会表现出来:

The breakage usually manifests itself when running a macro and getting:

Run-time error '-2147319767 (80028029)':

Automation error
Invalid forward reference, or reference to uncompiled type.

调试器确定的故障点永远没有意义-相同的代码已经工作了数周.我一直在使用的修复方法是回滚到工作簿的保存版本，该版本不会在运行宏时引发错误，并且它始终包含与被破坏完全相同的VBA代码.因此，我得出结论说，幕后的东西正在被破坏.

The point of failure identified by the debugger never makes sense – the same code has been working for weeks. And the fix I have been using has been to roll back to a saved version of the workbook that didn't throw errors running macros, and it always contains the exact same VBA code that was breaking. So I conclude that something behind the scenes is getting corrupted.

这是怎么回事?有办法避免这种情况吗?有什么方法可以解决的，比回滚到工作簿的早期保存版本更好吗?

What's going on? Is there a way to avoid this? Is there a way to fix it that's better than rolling back to an earlier saved version of the workbook?

推荐答案

有很多一个推论可以迫使Excel执行以下操作:重新生成其伪代码.

There are plenty of questions regarding this error, and the code changes that fix them never make sense either. The one thing they all have in common is that they make a change to the VBA code that, one deduces, forces Excel to regenerate its pseudo-code.

进一步的研究导致人们经常提到Excel工作簿损坏以及名为 Excel VBA代码清除器.该实用程序的作者解释了发生了什么:

Further research leads one to frequent mentions of Excel workbook corruption, as well as to a free utility called Excel VBA Code Cleaner. The authors of that utility explain what's going on:

在创建VBA程序的过程中，大量垃圾代码生成在您的文件中.如果您不定期清理文件，您将开始经历由这种多余的行李引起的奇怪问题.清理项目涉及导出其所有内容VBComponents转换为文本文件，删除这些组件，然后导入文本文件中的组件.

During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents to text files, deleting the components and then importing the components back from the text files.

不幸的是，他们还没有发布可在64位Excel中运行的实用程序版本.但是，人们可以手动执行相同的操作-保存所有VB代码，删除所有模块，然后重新创建它们并将代码重新粘贴回去.

Unfortunately they haven't published a version of their utility that works in 64-bit Excel. But one can perform the same thing manually – save all VB code, delete all modules, then recreate them and past the code back in.

更新: VBA代码反编译器是另一个免费软件实用程序，似乎可以完成同样的事情.还有关于Office如何在其文件中编译和保留VBA代码的更详细的描述.

UPDATE: VBA Code Decompiler is another freeware utility that appears to accomplish the same thing. There is also a more detailed description of how Office compiles and persists VBA code in its files.

这篇关于从Excel VBA损坏和自动化错误中恢复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！