Excel 2013 crashes on compiling VBA code

Question

Over the past few years I've been writing a management package for my work but this week a critical error developed. As soon as I start to compile, Excel crashes with the "Excel has stopped working..." message. I have resinstalled Office with help from the Office 365 team so it was a very clean re-install, but the problem remains. I don't remember doing anything unusual in the code that would cause such a critical failure and I have commented out quite a few of the more recent lines. I followed instructions from "Adloki" on another forum about renaming Form Controls but this did not work. I've also tried opening the program without executing any code, but again, as soon as I click "Compile", Excel crashes. There are 18 modules and numerous sheets, so I don't know what code I can post that would help. I wonder whether anyone has faced a similar problem and can suggest any workarounds or solutions. I fear the file is corrupted and cannot be recovered. None of the recent backups work either, only if I go a long way back do they compile again, but unless I understand what's causing this, there's always the danger of making the same mistake again. I am using Office 365 for Business on a stand-alone PC with Excel 2013 and VBA 7.1 under Windows 8.1.

Thanks!

Answer 1

I realise that I'm replying to myself, but I thought I'd say how this was resolved in case others find it useful. In desperation, I contacted the author of Code Cleaner and he was brilliant. He took the 'corrupted' file and ran it through his own compiler without a hitch. He returned it to me and, wonder of wonders, it worked beautifully. His theory is that the relationship between Windows 8 and VBA within Excel 2013 is possibly unreliable as, in his experience, the same fault has developed on other similar configurations. My theory is that a critical flag is set by the code, even if it is clean, and mine was. Something triggers the flag and once set, it could only be unset by running the file through another compiler on a different system. This has taken me weeks to sort out and I had no replies from other forums, so thanks to Norman again for his suggestion. Other things that people in a similar predicament might try, apart from running Rob's Code Cleaner, include making all form control references consistent, i.e. [userform].controls.item("[control name"]) - as mentioned above, as well as the usual software updates. But sometimes it's a totally unpredictable and illogical solution that works.

Answer 2

Hi Feature Creature,

Thank you for your feedback.

I am delighted that your problem has been resolved.

===

Regards,

Norman

Answer 3

I've downloaded and installed the add-on. I tried a simple clean but although it popped up a successful message, Excel still crashed on compiling. I tried again with exporting and then importing the modules and also checking the "Code behind..." box. No luck, the compiler still crashes, every time.

Any ideas would be welcome.

Answer 4

Thanks Norman,

I'll have a look at that package. Might be just what I need. I'll post back details if and when the issue is resolved. All the best.

Ellis

Answer 5

Hi Feature Creature,

I would suggest that you download and run Rob Bovey's CodeCleaner Add-In. This is an indispensable part of my tool kit.

http://www.appspro.com/Utilities/CodeCleaner.htm

===

Regards,

Norman