Very Large Spreadsheet Gets Corrupted in Various Different Manners

First off, I want to thank everyone who makes this the best Excel help forum on the web. I have learned so much from this forum, and recommend it to everyone interested in Excel.

I have several large spreadsheets (900MB in XLSM format, 750MB XLSB) that frequently get corrupted. Sometimes they crash while I'm in them, sometimes they just won't open after supposedly saving/closing just fine, and sometimes they crash while saving/closing. Yes, I know, I am a fool and I'm not utilizing Excel correctly. I need to clean up my spreadsheets, or use different software... etc. But what if I'm not a fool, my spreadsheets are extremely efficient, and I am utilizing Excel correctly?

My Machines

• Intel Core i7-6950X with 128GB of Registered ECC RAM, 1TB Samsung 960 Pro SSD, Windows 7 Pro 64 bit with Microsoft Office 2010 Pro 64 bit
• I have never been close to running out of RAM, the max I've seen utilized on the Task Manager is 35%.
• I have two of these exact machines, which might be important. One is in New York and the other is in California. The problems started around the time I started using this dual PC setup.
• I use LogMeIn to open these large spreadsheets from both locations, with Google Drive or OneDrive to sync them. I though Google Drive was corrupting them, so I switched to OneDrive.
• I try to set the default printer to Adobe PDF on both PCs, since I know differing/missing default printers can cause errors. I believe it did hurt me for a while before I realized it.
• Non-Microsoft Excel add-ins (potentially the problem); Bloomberg Excel Tools, SNL Office Excel, Name Manager Utility, Stock Market Functions Add-in. However, I have tried disabling all add-ins (even COM add-ins) and it doesn't seem to help. Plus they always end up magically re-enabling themselves... Lastly, I need these add-ins.

My Spreadsheets

• Not that it matters but if you're inclined to help I'll satisfy your curiosity: They are algorithms to pick stocks and backtest the hypothetical trades, I work on Wall Street.
• There are no external links, no visible or hidden named ranges with errors, no ActiveX, no custom formulas, no data connections, and no blank cells taking up space (i.e., the ctrl + end trick).
• There are no wasted formulas and as few array formulas as possible (sometimes they are required)
• In case it matters, there were data connections (ODBC, MySQL 5.3), but I thought they were the problem and recently replaced them with hard-coded tables (they are simply ranges, not formatted Excel "tables") pulled in via VBA
• There are a few userforms, and <10 VBA modules with <100 lines of code each. All modules are very small in size (few KBs at most)
• Each spreadsheet only has 15-20 sheets, nothing crazy
• One sheet in each model is huge (60,000 columns by 2,000 rows). Not a single error or wasted cell though.
• I run manual calculations, but it only takes ~15 seconds to calculate the whole thing

The Errors / Corruption Issues

• All over the map...
• To be safe, I always open a copy of the file NOT being synced by OneDrive (i.e., a copy on my Desktop and not in the OneDrive folder).
• Today I got the "Module not Found" error (when I press OK Excel crashes) upon opening an XLSM file that I successfully saved 5 minutes ago. The VBA project has 6 references, and they're all legit and available. The only one I added manually was ActiveX Data Object 6.1 which I need to upload to MySQL. I tried removing a few of them and it didn't fix the problem. So I revert back to the good Excel file from last Friday, but anytime I update and save it...it gets corrupted again.
• In the past the spreadsheets just wouldn't open, and the appcrash was due to EXCEL.EXE or VBE7.something but Googling the errors never led anywhere. Perhaps we could explore this more?

I have rebooted my PC, repaired MS Office, and installed all Windows and Office Updates.

So right now, I have a perfectly good spreadsheet that opens and runs macros with no problem. If I save it right now it will corrupt itself, it happened 4x today.

I will pay someone to help me figure this out. Any software or consultant suggestions?

I did find a solution. Take the following steps;

1. Before you save the file, open the Visual Basic editor and on the top menu click Debug -> Compile VBA Project

2. Save-As when you save the file (new file name so it’s a fresh file)

3. Close Excel completely

4. Open Excel in safe mode by holding the Control key when you open Excel

5. Since that opens a blank workbook, in safe mode Excel go to File -> Open and open your new spreadsheet you just saved

6. Open VB editor and click Debug -> Compile again. Maximize the VB window if its not already. Then close it.

7. Save-As to another new file name.

8. Close safe mode excel and open the file from #7 above normally...hold your breath

This works 90% of the time on my 300MB to 1.8GB Excel files (xlsm and xlsb).

Cheers,
Jason

Thanks Ger. Yes, I have rebuilt hundreds of workbooks from scratch exactly as you've described. In this situation it's ~8 hours, because the named ranges and time it takes to rebuild the calc trees. However, it 100% doesn't fix the problem, the files keep getting corrupted upon saving (they never crash while I'm actually using them). I'm still willing to pay someone thousands of dollars to find the real problem.

I have dozens of very large MySQL databases on AWS - I'm not using spreadsheets to warehouse data (and Ctrl + End takes me to the end of my formulas). My spreadsheets are as efficient as can be while still performing their function (financial analysis & backtesting).

Lastly OneDrive has been pretty solid - syncing ~600GBs across 4 PCs throughout the country. It has been much better than Google Drive for me.