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.
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?
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.
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:
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.
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.
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.