Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 20
Default The expanding XLS spreadsheet

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


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 170
Default The expanding XLS spreadsheet


"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


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,089
Default The expanding XLS spreadsheet

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




  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 20
Default The expanding XLS spreadsheet

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




  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 68
Default The expanding XLS spreadsheet

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








  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 20
Default The expanding XLS spreadsheet

Excel 97 format? Really? I've been noticing exactly the opposite... I found
that saving in Excel 2002/XP format was a fraction of the size of saving it
in Excel 97 format.


I'm trying it now again, to see if it's any different this time.


As for recreating the worksheets, yes, you're correct, with that number, it
would take an IMMENSE amount of time to do all of that, and I could still
wind up with the same (or similar) problem.


Unfortunately, one of the requirements for this application is that it would
be standalone (an Excel spreadsheet - no install required other than copying
the XLS to the user's hard drive), so using a COM component isn't an option.
Besides, if as you say the problem is more the worksheets than the code, I'm
not sure it would save much.


Thanks!
-Scott

"Bill Lunney" wrote in message
...
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








  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 68
Default The expanding XLS spreadsheet

I do a lot of work for clients where I can't deploy ANY files to a PC except
the XLS or perhaps an XLA. Remember it is possible to encapsulate the
majority of your code base into an XLS which can then be called by other
workbooks. This is only really relevent if you're going to have multiple
copies of the same structure but execute exactly the same code. It just
saves having this big lump of code attached to each workbook and also
greatly reduces updating the code as you do it in one place and all the
clients refer to the one 'core' file.

Yep from what you say I'd concentrate on the worksheets. Should be simple
enough to do a test. I'd just take any worksheet with it's code recreate it
by hand in another workbook, copy this 10 times or however many worksheets
you have in the source, save it and compare the file sizes. This way you
get a quick idea if it's worthwhile looking at sheet recreation.

--

Regards,


Bill Lunney
www.billlunney.com

"Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in
message ...
Excel 97 format? Really? I've been noticing exactly the opposite... I

found
that saving in Excel 2002/XP format was a fraction of the size of saving

it
in Excel 97 format.


I'm trying it now again, to see if it's any different this time.


As for recreating the worksheets, yes, you're correct, with that number,

it
would take an IMMENSE amount of time to do all of that, and I could still
wind up with the same (or similar) problem.


Unfortunately, one of the requirements for this application is that it

would
be standalone (an Excel spreadsheet - no install required other than

copying
the XLS to the user's hard drive), so using a COM component isn't an

option.
Besides, if as you say the problem is more the worksheets than the code,

I'm
not sure it would save much.


Thanks!
-Scott

"Bill Lunney" wrote in message
...
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










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Expanding spreadsheet with formulas Redsphynx Excel Discussion (Misc queries) 2 April 8th 08 03:05 AM
expanding numbers? Pendal Excel Worksheet Functions 5 December 22nd 07 08:45 PM
Expanding capacity of a spreadsheet / workbook akkrug New Users to Excel 2 October 10th 06 12:55 PM
Expanding Selection aposatsk Excel Discussion (Misc queries) 3 August 16th 06 05:21 PM
Expanding data Excels Cracking me up! Excel Worksheet Functions 2 November 9th 05 03:07 PM


All times are GMT +1. The time now is 07:07 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"