Scott

"And that's about it"
100 sheets x 400 lines of code = 40,000 lines of code + 5,200 lines = 45,200
lines
200 sheets x 400 lines of code = 80,000 lines of code + 5,200 lines = 85,200
lines

PLUS all the other stuff

I don't know how the size of the workbook relates to the amount of code but
that sounds a lot to me

Is the code in all those sheets really that different? Is every sheet
unique or are there groups of sheets ? First thought would be to move all
the (duplicated) code to standard modules, perhaps one per sheet type, and
then have one line calls from the worksheet.

For example:

' Sheet1 - Type "one"
Private Sub Worksheet_Change(ByVal Target As Range)
TypeOneSheet_Worksheet_Change(....)
End Sub

TypeOneSheet_Worksheet_Change(....)
' original worksheet change code
End Sub

* Any code that IS common, move to a standard module.
* Remove Select/Selection pairs
* get rid of the redundant code
and so on

* Use Rob Bovey's Code Cleaner

I'm sure you'll get lots more suggestions

Regards

Trevor


"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?


Thanks!
-Scott