VBA damage in Excel (Excel 2003/XP)

Topic

New Reply

Hi everybody:

I recently had a user experience an Excel crash because he had AutoSave turned on and it tried to save his file at the exact moment that he triggered some VBA code. The file was recoverable, but now one of the VBA routines keeps getting called over and over after the calling code in the Sheet_Recalculate event exits. I know how to recover damaged VBA projects in Access by opening the file decompiled and then compacting it, but does anyone know of a way to recover damaged Excel VBA sheet modules? (Deleting and recompiling didn’t help.)

Thank you, in advance, for your help.

Reply | Quote

Replies

Do you mean that you have deleted the worksheet code and this didn’t help?

Reply | Quote

Hi Hans:

My first line of defense was to delete the VBA code, save the workbook, open and repair it, add the VBA code back in and compile. This did not solve the problem. So, I created a new workbook and copied and pasted everything into it, added the VBA code, and it is fine now. Apparently, the worksheet was damaged by the Autosave process crashing my VBA.

I was just wondering if there is a way to repair Excel VBA projects as one can with Access.

Thanks for your help.

Reply | Quote

The problem with worksheet modules (and with ThisWorkbook) is that you cannot completely delete them – you can only delete their contents. This leaves behind the invisible corruption. There is no /decompile option for Excel.
The way you solved it is the best one.

Reply | Quote

I figured as much.

Thanks!

Reply | Quote

Try running Code Cleaner on it – you can get it here: http://www.appspro.com/Utilities/CodeCleaner.htm%5B/url%5D
If that doesn’t work, you may need to copy the data and code modules to a new workbook.

Reply | Quote

Thanks, but there is very little code to be cleaned (three functions!). It appears that the VBA project itself is damaged.

Reply | Quote