My file is 28 MB w/ code & forms; ~40MB with data - is this too bi

[robs3131]

Hi all,

I'm in process of finishing a spreadsheet which has a lot of code (33
modules), 12 forms (each of which has code attached, and 56 sheets (each of
which has some code attached). As mentioned in the subject line, my file is
28 MB w/ code & forms only and ~40MB with data.

I'm wondering if there's some rule of thumb threshold for file size that
when you get above that size, you can expect problems (ie - the file not
responding, or any other problems that I don't know of)? Also, I'm wondering
if there are some quick things that can be done to reduce the file size
without removing any of the functionality.

Thanks!

 

[Francois via OfficeKB.com]

Hi all,

I'm in process of finishing a spreadsheet which has a lot of code (33
modules), 12 forms (each of which has code attached, and 56 sheets (each of
which has some code attached). As mentioned in the subject line, my file is
28 MB w/ code & forms only and ~40MB with data.

I'm wondering if there's some rule of thumb threshold for file size that
when you get above that size, you can expect problems (ie - the file not
responding, or any other problems that I don't know of)? Also, I'm wondering
if there are some quick things that can be done to reduce the file size
without removing any of the functionality.

Many people have much larger files without problems.

Assuming that you have a reasonable spec PC you shouldn't have any problems ,
but the actual performance of the PC may or may not be slow if you have a lot
of calcs etc.

if you really want to know, why don't you add a few sheets with a lot of
calcs etc ....then you will know.

 

[Dave Peterson]

That 28MB sounds pretty big without any data.

Can you try a small experiment?

Create a new workbook.
Drag all your forms and general modules to the new workbook's project.

Pick out the worksheet with the most code and controls on it.
Copy and paste the code into the new workbook's worksheet module.
Add similar controls (I don't care if they actually work).

Copy that worksheet 55 times within the new workbook.
Save the new workbook and see what size you get.

If it's close to 28MB, then the experiment was a waste of your time.

If it's lots less, you may want to see if you can find the thing/object (picture
maybe) causing the bloat.

 

[gimme_this_gimme_that]

Hi Robert,

Probably not without removing functionality.

OTOH - you might not require all the functionality you have.

You could split the VBA modules into separate categories:

1. Those required to fetch and sort data, and
2. Those required to manipulate drop downs and Excel controls.

Modules in category 1 can be placed into an Add In.

A similar analysis applies to your WorkSheets:

1. Those required to fetch and sort data can be placed in an Add-In.

---

Now. If the Workbook is an audit and you're sharing the data with an
auditor - you have fewer options than if the workbook is used as an
application.

If the Workbook is used as an application you can store the data in a
database instead of Worksheets.

 

[robs3131]

Thank you all for your input and suggestions. Time permitting, I'm going to
try some of your suggestions to see if they help. I am encouraged in that it
doesn't seem that the file is too big. It would be nice to cut down the size
though.

Thanks,

 

[gimme_this_gimme_that]

You can try saving in XML format. My experience, though, is that that
doesn't help.

 

[robs3131]

I went ahead and deleted all rows from each worksheet that could be deleted
and it made a HUGE difference -- the size of the file without data went down
to 4.48MB! Thanks for your help.