I have a series of Excel workbooks which I have developed over the years. I am currently using Excel 2013. The workbooks and their macros were first created in previous versions of Excel. I can't remember exactly which Excel2003? Excel2007? They have numerous sheets, formatting, bunch of data, formula, the usual, with supporting macros.
In 2013, when running macros, Excel 2013 completely crashes periodically. To be clear, it might work find 10 times in a run, and then crash all of a sudden, crashing completely out of Excel. Once it has crashed, if I re-enable macros on reopening the sheet (before recompiling), everything crashes again.
However, if I reopen Excel, reopen the workbook, and go to VBA, and immediately recompile before enabling macros, then things are ok and the workbook will work again for awhile.
I read the web somewhere that some unknown "junk" exists behinds the scenes in files created in previous versions of excel, and this can cause the crashing in Excel 2013. This is where I learned that the recompiling trick solves the problem (at least temporarily).
Does anyone have any tricks which can solve this problem for me once and for all? The crashes are inconvenient, mess up my work flow, and will eventually cause me some damage (data lost, time lost, errors, etc etc).
For example, is there a 100% reliable program which could copy over everything from an old workbook to a new fresh workbook (data, formulas, formatting, macros, etc etc), without bringing any of the previous version "junk" which may be causing my crash? Or... is there some certain VBA code which was ok in old versions but known to cause crashes in new version which I could delete/change?
I known this question lacks a bit of specificity... but I am kind of floundering. Any help would be appreciated.
[I did a little more research... in VBA/Tools I unchecked the "Compile On Demand" box... I think this means it will recompile everything before it runs any macros... maybe this will solve the problem?]