Typically code is not what adds significantly to file size. It's the
worksheets themselves.
Recreation of the worksheets is the best and only way to get a reasonable
file size reduction (at least have the potential of). However with that
many worksheets this may not be practical. I'm no longer surprised by the
gains that can be achieved by doing this purely because over time even a
clean-ish looking sheet can contain lots of invisible formatting.
Try ensuring your file is saved in Excel 97 only format. Saving in other
formats can double the size due to compatibility constraints. Although
reading the rest of the post suggests you've already experimented with this.
If your confident that the worksheets are reasonably cleanly designed (and
there's no stray random formatting etc. everywhere) then it's clear you have
to target the code use.
Perhaps consider using a COM component if your architecture allows for it.
This way you can centralise a lot of your code base and save repetition.
However public functions would, to a large degree, do the same thing in your
case.
--
Regards,
Bill Lunney
www.billlunney.com
"Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in
message ...
I downloaded that "cleaner" program, and tried it. Unfortunately, it seems
that it only "cleans" the VBA code that's actually in modules. The problem
is that the vast majority of my code is actually in the code behind
individual sheets.
In fact, running that "cleaner" program only saved me a few megs off of a
150MB file. Which isn't enough to even make a difference, unfortunately.
Is there a way (manual or automatic, although with over 200 worksheets I'd
prefer automatic) that I can "clean" up the garbage associated with all of
the worksheets' VBA code?
Thanks!
-Scott
"Keith Willshaw" wrote in message
...
"Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in
message ...
I've been working on an Application (in Excel 2002) for awhile.
Lately,
it's
been growing out of control. To give you an idea, when I save the
thing
(in
Excel 2002/XP format, we won't even TALK about what happens if I save
it
in
Excel 97 format), it's pushing 150MB.
At this point, the application consists of the following:
- About 110 worksheets - each with two TabStrip controls (with
anywhere
from
5 to 30 tabs), and approximately 4-5 buttons, as well as two small
images
- Each worksheet has about 400 lines of VBA code (although about 150
lines
of that is temporary code, commented out, and will be gone when this
goes
to
production).
- Two main (shared/common) VBA modules that have about 4000 and 1200
lines
(respectively) of VBA code
And that's about it... But somehow it's ballooned up to 150MB, and is
starting to give my computer fits as I work on it. To add insult to
injury,
realistically, there will probably (when all is said and done) be over
200
worksheets (each with probably around 200-300 lines of code each)
Now, it's way too late to rewrite this in a better way (and for that
matter,
although the code is similar on each of the worksheets, it's unique
enough
that I cannot just call a shared function for most of it). Is there
any
way
I can get this thing to shrink back down to a managable size?
I wouldn't mind if it was as much as 50-60MB, once I've got all 200
worksheets. But that it's above 150MB already, and I'm barely half-way
there, has me quite concerned.
Can anyone offer ideas/suggestions?
Dowload and run a VBA Codecleaner such as that on the
appspro site. Each time you edit a VBA file it gets bigger as
VBA cant seem to recover used space. This utility will recover
that space and lets you strip comments and blank lines
from code (useful for shipping externallu but keep a copy)
http://www.appspro.com/utilities/Cleaner.asp
Keith