Forum Discussion
Excel Automation Error and VBA Module Duplication
A recently stable Excel package became dramatically unstable about a week ago, after minor revisions, with numerous errors including Automation Error and duplication of all VBA modules. I opened it in Safe Mode then saved it and the package once again worked, but I am not confident that it the issues are fixed. Is there a way of comparing the corrupt and working versions to identify the code Excel has found and apparently altered in Safe Mode? The corrupted version has 3Kb difference.
5 Replies
- This is usually an indication the VBA project has become corrupted. One example of when this sometimes happens is if you are debugging a VBA project which contains User Defined Functions called from cells. If you save the file while in debug mode this sometimes happens in my experience. There might be several ways to get out of this state, one being exporting all VBA modules, then saving as xlsx file, closing and re-opening the xlsx, importing the VBA modules and then saving as xlsm.
Thanks Jan. That is probably what happened - yes, I was debugging and maybe save before the process was complete. To clarify, after exporting and saving as XLSX, do I need to remove the old modules or just import over them? Also, would saving in Safe Mode automatically do the same thing, or similar, by editing the corrupted code?
- If you FIRST close the newly saved xlsx and then reopen it, all VBA will have been removed from it (INCLUDING code in sheet modules and in ThisWorkbook, so you may want to check those too before closing the xlsx!).
Saving in safe mode is no different from saving in "normal" mode.
