Help; why is my workbook 130mb!?!?

[GBagley]

I have a fairly large VBA program (about 10k lines altogether, spread across maybe 40 different modules and 10 userforms) and the workbook that it runs off of has been getting increasingly large, to the point where it takes forever to open/save, and I often get memory errors, 'cannot display completely', or 'catastrophic failure' messages

The workbook is 130mb currently, and I haven't been able to figure out why. It has about 45 different sheets but there are only a few that hold a large amount of data (the rest are relatively small). When the program is running, a lot of data is being passed through various sheets, but for the most part my VBA code is explicity deleting the larger chunks of data when they're done being used.

As a test, I ran the code from this page, which saves each sheet to a seperate file:
http://dmcritchie.mvps.org/excel/saveas.htm

Altogether, the individually saved sheets only make up about 30mb. What could it be about my full workbook that is taking up so much space? I did a test to loop through and display the usedrange for each sheet, and nothing there was out of the norm. I'm explicity creating and then deleting any data connections and named ranges that are being used while the program runs. Any other ideas?

 

[sous2817]

If I had to guess, you've got some formatting or maybe some references set to entire sheet(s), which is causing Excel to bloat. It's probably worthwhile to search this board as well as google for "Excel file bloat" to get some ideas on how to check for bloat.

Seems your code is too large to put it in a forum post, so your best bet is going to be to learn what causes file bloat and then start investigating your own code.


***whoops, just read your post a bit closer and looks like you're covered my suggestions.***

 

[GBagley]

Interesting...I searched around a little and found some tips on the .xlsb format. When I save to the Binary format, the workbook is now 26mb! From what little I've read, .xlsb is a full fidelity format, and works fine with macros. Is this correct? My program seems to run fine.

http://www.mrexcel.com/forum/showthread.php?t=502578&page=2
http://blogs.msdn.com/b/davbosch/archive/2006/08/29/730183.aspx

I hadn't heard of the binary format before. Will have to read up on it a little more. Seems odd that there would be such a huge difference (I had been using the .xlsm format)

 

[xenou]

I occasionally see posts here (once a month or so) from users with sudden, strange, and inexplicable file bloat with xlsx. I don't think we know what the problem is - to me it seems rare and random, as though it were a bug. But there may be some common thread.

I think the term "full fidelity" means, in fact, the the xlsb binary format will work 100% with all excel features - and I have never heard otherwise. Just stick with it for now, and you'll be fine. The only thing about xml is that it will store more information due to the need for opening and closing tags for all xml elements - though I think it would have to be an extreme case that would lead to such a dramatic case of file size difference. I've been using xlsb (I don't know why really - mainly I don't have to choose a different format depending on whether or not I have macros in my file or not). In any case, it's been wonderful. XML formats have the appeal of excellent interoperability among different platforms and systems, but you probably won't notice a difference, in your own everyday use, between xlsx and xlsb.

ξ

 

[GBagley]

Sounds good to me. xlsb is my new fave

I saved it from xlsb back to xlsm, and the new xlsm file was about 50mb. Then I saved that back to xlsb, and then new xlsb file was 19mb. Very odd...