Excel crashing on opening workbook - what to do?

[Carl Colijn]

Hi all,

I have developed a complex Excel 2007 .xlam add-in that recently got corrupted. I didn't notice the corruption (all saves went through OK), but when I closed Excel and re-opened the add-in Excel just crashed opening the file. Turning off macro's allowed me to re-open the file, after which I could rescue all changed VBA code to re-import it again in an older version (saving me 3+ hours of work). But this older version appearantly is also quite shaky, since if I compile my VBA code in it and then save it, Excel will again crash after re-opening the workbook. Unless I disable Macro's again; then it opens just fine (without any functionality of course). Or if I change some VBA source and don't re-compile the VBA before saving, the corruption also doesn't manifest, but I'd rather have compilation enabled (I use it regularly to catch coding errors; I do a Compile and/or Save every few minutes )

How can I get rid of this corruption? I've tried:
- Manually export/remove/re-import all VBA modules => corruption persists.
- Save to older 2003 .xla format => new .xla also crashed Excel.
- Save to .xlsm format => corruption persists.
- Let Excel Open/Repair it => Excel said it succeeded repairing it and indeed opens it, and when I save it it's +- 1/3 smaller. But the log just states "some parts may have been repaired or discarded" (_which_ parts, anyone?!) and afterwards the "repaired" .xlam still crashes Excel if I open it, compile my VBA, save, close, and re-open it.
- Removed all worksheets => corruption persists.
- Removed ribbon => corruption persists.
- Start with a fresh workbook, copy all sheets, copy all VBA (via Export/Import), set references, copy Ribbon => corruption comes along.
- Use www.vbusers.com 's Excel Workbook Rebuilder => if I choose Rebuild I end up with a 24KB file, and Repair ditto.
- Use VBACodeCleaner 5.0 => it can export the VBA perfectly, but it can't get rid of the corruption.
- Removed Workbook_Open code => file opened OK!

After inspecting which part of the Workbook_Open code was the culprit, I found 2 solutions;
1) the code sets a global

Private WithEvents g_oApp As Application

object, which in turn catches it's WorkbookActivate event so it can refresh (invalidate) it's ribbon when another workbook is activated. Right after Workbook_Open, I thus get a trigger to invalidate the ribbon. But right after opening the workbook, the ribbon itself fires it's own "Loaded" event, which I catch to set my g_oRibbon instance in order to be able to call .Invalidate on it later on. Thinking I had a race condition here I removed the code that accesses the ribbon from the WorkbookActivate event, and the workbook opened correctly from then on.
2) the add-in has some UDF's to be used in cooperating workbooks, so I register them with some calls to Application.MacroOptions (so end users can find them and don't have to memorize them). But since I'm an add-in, and since add-ins can't register macro's, I place a

ThisWorkbook.IsAddin = False
...register UDF's...
ThisWorkbook.IsAddin = True
ThisWorkbook.Saved = True

wrapper around the UDF-registration code. But removing the calls to Application.MacroOptions alone already did the trick, so it wasn't the addin-status switching.

Since 1) and 2) seem very "iffy" and divergent solutions, and since that code has already been in there since version 1 so to speak, I'm reluctant to conclude this code is the culprit (it's probably only the trigger for a real corruption problem).

Does anyone have any other idea I can try?

 

[RoryA]

Re 2, you may want to have a read of this article by MVP Jan Karel Pieterse.
Re 1, does the code check if your RibbonUI variable is nothing before it tries to do anything with it?

For the rest, I think you have tried most things other than perhaps rebuilding the workbook but instead of exporting and importing the modules, copy and paste the VBA code via Notepad.

 

[Carl Colijn]

Re 2, you may want to have a read of this article by MVP Jan Karel Pieterse.

Hi Rorya,

I had already read that page when I set this up initialy, but I'd rather not resort to this kind of trickery in this case - the project is for a client of mine, who will not like it when it starts falling apart after I've delivered the solution and will be gone again...

Re 1, does the code check if your RibbonUI variable is nothing before it tries to do anything with it?

Yes it does, and it's not Nothing (so that concludes the Ribbon's Loaded event fires before the Workbook's Activate).

For the rest, I think you have tried most things other than perhaps rebuilding the workbook but instead of exporting and importing the modules, copy and paste the VBA code via Notepad.

I was already affraid of such an answer The thing has grown huge and contains all sorts of elements - formula's, cell color codes, ribbon, ActiveX elements, VBA incl. userforms, references, named ranges, etc. I can only hope I'll not miss any of those nor screw it up when I re-build it, plus it's a laborous task to undertake...

Thanks for the suggestions though!