Un-corrupt an Excel 2007 add-in - out of options?

[Carl Colijn]

Hi all,

I've created an Excel 2007 .xlam add-in for one of my customers, and the project has become quite big and complex with lots of advanced VBA. However, the file has become corrupt along the way; the corruption has probably already crept in a long time ago since the add-in seems to work OK until you perform a certain action, after which it becomes corrupt instantaneously. The corruption manifests itself in manifold ways;
- if the add-in is saved after compiling it, then Excel crashes as soon as the add-in is re-opened with macro's enabled (disabled macro's don't crash Excel);
- I've got code that shows/hides ComboBoxes on top of validation list cells; if the add-in is compiled this code crashes Excel as well;
- just saving changes to the add-in makes Excel crash from time to time;
- the code that operates Word (open/close/new doc/set docvars/update fields etc.) recently started erroring out while it ran perfectly fine before I updated a totally unrelated part of the VBA code.
In other words: it has become a fragile PITA to work with.

I've tried working with the final (corrupt) add-in version by applying a variety of de-corruption and cleaner tools to it - all to no avail (forgot which ones I tried though). I then exported all VBA code, deleted the modules and re-imported them again - still corrupt. After a few days of some increasingly more involved trial-and-error tests I finally gave up and re-build the add-in from scratch, making sure to either re-build the functionality entirely in the GUI and only copy/pasting TEXTUAL property values etc., and only importing the TEXTUAL .cls/.bas modules. I've spend yet another day on this, only to find out that when I compile and then save the final version of the add-in it YET AGAIN crashes Excel when re-opening it and when showing the comboboxes...

I'm afraid I've wasted yet another day on this and feel like I'm hopelessly out of options. For the last de-corruption trial I took the following steps, saving each step seperately and compiling along the way to check for successfulness, but of course only compiling in a copy:
0) applied all updates on my Office 2007 and Windows XP installation by visiting Microsoft Update
1) exported all source code from the add-in as .bas/.frm/.cls files
2) copied the add-in, stripped out all but the sheets (as well as the Form elements on the sheets), and saved it as an xlsx
3) created a new .xlsx workbook
4) manually re-created the needed sheets (add them and set their names)
5) copied/pasted the content of each sheet (text/formatting/formula's) into the new version
6) imported the exported .bas/.cls files (NOT the .frm files!)
7) manually re-created dummy forms as placeholders (same name and public interface, internals and GUI left blank)
8) inserted the ribbon code
9) set up the needed VBA references, also adding placeholder needed Common Controls to one of the forms for their implicit reference
10) copied over the content of the ThisWorkbook module to make the code live on startup
11) added Forms controls to the sheets and linked them to their macro's
12) ran needed configuration code to set up the needed named ranges in the add-in
13) saved as add-in (.xlam)
14) re-created all forms (one by one) by adding controls by hand from the toolbox, then manually synched all property values for each control (and the forms as well), and finally copy/pasted the underlying VBA source out of NotePad
15) kept my fingers crossed and hoped I've not missed any functionality...

The resulting file is now 1/5 smaller than the original (cleaned) version, which is a good sign I hope (see point 15 above though). But when I now open the result, compile it, save it, and re-open it, Excel again crashes on me. And when I compile, modify the code a bit, save it, and re-open it, the code that should dynamically show the comboboxes on related workbooks crashes Excel as well. Maybe the VBA has just become too complex for Excel 2007 to handle?

Long post, but better thorough than incomplete. Does anyone have any other options? I think making a 2003 add-in out of this is not an option due to the ribbon interface I added. Excel 2010 is not an option either, since their office has only 2007 rolled out. Making it a compiled com/dll add-in is not an option as well, since the client (with only VBA experience) wants to be able to maintain it himself after I'm gone (so pure VBA is the only option, albeit is has become quite too complex in parts already I'm affraid ).

Thanks in advance for any suggestions!
Kind regards,
Carl Colijn