Size of a Workbook

[Basharat A. Javaid]

I have a workbook with numerous sheets and lengthy procedures and functions.
The size of the workbook has reached 3mb at a bare minimum which obviously
increases as more data is entered. I have over 200 clients with repeating
projects annually. The workbook is used for each client and saved as a
separate file with that client's name and the year. So for a given client,
I could have many years' files - as many as 15 years so far. Obviously,
over time, this can take up a lot of hard drive space - which is not the
primary problem given the prices of hard drives.

The main problem is that processing (recalculating) takes a long time even
though I have 2.4 GHz P4 with 512MB RAM.

Two questions:
1. How to reduce the workbook's size (of course without deleting
sheets/modules or splitting the workbook) - is there a utility in Excel
similar to the compacting database in Access.

2. How to speed up recalculation.

Thanks.


Basharat.

[Ron Coderre]

This suggestion might work for at least one problem: Recalc time

If you have the following conditions:

1)Large tables of information that contain the same formulas copied down to
the bottom of the table
AND
2)The information for old data rarely, if ever, changes.

Then
Sselect all of the table formulas except the first row.
Copy/Paste Values right over the same formulas....Done.

Note: Leave the first row of formulas intact. That way, whenever you need
to recalc a row, or rows, you can just copy those formulas to where you need
them.

If you have tens of thousands of formulas, the recalc time should decrease
appreciably. (You might also decrease the file size.)

Does that help?

Regards,
Ron

[Basharat A. Javaid]

Not really.
Each of the number crunching sheets in the application starts off with one
row with formulas and a row for totals. A separate sheet holds a given
client's employees' data. When the application is run, certain procedures
insert additional rows (depending on # of employees) in the number crunching
sheets and copy down the formulas from the first row. This process is
performed on the sheets in a sequence and recalc is done after the process
is completed for a sheet - e.g. sheet a, b, c ....

Of course the run time increases with number of employees. For one employee
(where no additional rows are inserted) it takes only about 30 second. But
for 30 employees the process takes about 4 minutes.
The waiting can get frustrating when one is trying different scenarios (even
though the row inserting process is not done after the first scenario).

So manual copying and pasting is not a solution.

Basharat.

"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
> This suggestion might work for at least one problem: Recalc time
>
> If you have the following conditions:
>
> 1)Large tables of information that contain the same formulas copied down
> to the bottom of the table
> AND
> 2)The information for old data rarely, if ever, changes.
>
> Then
> Sselect all of the table formulas except the first row.
> Copy/Paste Values right over the same formulas....Done.
>
> Note: Leave the first row of formulas intact. That way, whenever you need
> to recalc a row, or rows, you can just copy those formulas to where you
> need them.
>
> If you have tens of thousands of formulas, the recalc time should decrease
> appreciably. (You might also decrease the file size.)
>
> Does that help?
>
> Regards,
> Ron
>