Must reduce file size

[Sam]

I have an excel app with a lot of vba and a lot of formulas. It has always
been around 10MB but after adding much more vba functionality (UserForms and
code mostly) it has grown by about 5MB. Many of the worksheets go past row
5000, even when I select all cells below row 200 for all columns and delete
(shift up & left). How can I reduce the file size? What components make up
file size other than data? Is there a way to evaluate these components?

Thanks

 

[Ferris]

I have an excel app with a lot of vba and a lot of formulas. It has always
been around 10MB but after adding much more vba functionality (UserForms and
code mostly) it has grown by about 5MB. Many of the worksheets go past row
5000, even when I select all cells below row 200 for all columns and delete
(shift up & left). How can I reduce the file size? What components make up
file size other than data? Is there a way to evaluate these components?

Thanks

I would check to see if you're using any graphics for the backgrounds
of the user forms. I can't imagine you have 5MB of actual code (would
take more lines of code than you could type in a couple of days,
probably). How many userforms do you have?

 

[Gary Keramidas]

--


Gary

 

[Gary Keramidas]

try code cleaner

http://www.appspro.com/Utilities/CodeCleaner.htm

 

[gimme_this_gimme_that]

Sometimes you can hit the lucky jackpot by saving in Excel 5.0, XML or
OpenXML.

OTOH, I've seen instances where saving in other formats resulted in a
*larger* file. Go figure.

It's unlikely your going to find a significant reduction in size by
removing VBA.

What you could do is split up the worksheet into worksheets and add-in
worksheets. That is, if some of the worksheets aren't used by users
and are there because they are database query resultsets, then put
those worksheets into an add-in that only administrators see. Same
logic applies to forms.

Note that while 10MB is large, if you WinZip it the resulting zip file
will be less than a MB.

As to your questions. Sorry data is usually the source problem.

 

[Mark Ivey]

Two ideas...

1. Do you have any Pivot Tables in this workbook? I have seen my file size
grows considerably with a Pivot. If so, you can copy the data out of the
pivot into a standard table and remove the pivot to free up a considerable
amount of space.

2. The only other idea might be formatting. Are the spreadsheets formatted
all the way over or all the way down for any reason. I have seen this make a
workbook much larger than it should be. Try deleting all blank rows and
columns and resaving it. The rows and columns should rebuild themselves with
NO formatting (freeing up a lot of memory for the workbook).

Just my thoughts...

Mark Ivey