Excel 2003 workbook growing massive

[Dave Mills]

I have a workbook that has quite a lot of code. I share the workbook so 3 people
can update it at the same time. Every few weeks I stop sharing it and open and
close it a couple of time. Sometime this is the modify the code.

Recently it has started to grow wildly. It was about 2MB but 2 days ago was 20MB
and today 61MB. When this has happened before I stopped sharing and modified it
then saved, opened and re-saves and it went back to normal. This time it is
staying at 61MB. I opened if in Excel 2007 and saved it and it grew to 97MB.

Any ideas how I get it small again.

Alternatively I could copy the 3 worksheets to a new workbook and export and
import the code modules. Would that work do you think.

 

[JP]

One thing you can do is use a code cleaning add-in, like
http://www.appspro.com/Utilities/CodeCleaner.htm to scrub your code
modules.

Searching for and resetting the last used range on each sheet should
reclaim some space. And although I've never tested it, I've heard that
removing unused custom formats should work as well.

You might also want to consider revising your process. Perhaps you can
have one workbook act as the 'database', with data collection forms
for each of your three users. Then you could (manually or
programmatically) aggregate the data and append it to the (non-shared)
'database' workbook.

--JP

 

[Dave Mills]

One thing you can do is use a code cleaning add-in, like
http://www.appspro.com/Utilities/CodeCleaner.htm to scrub your code
modules.

I'll try that, it may improve the code anyway.

Searching for and resetting the last used range on each sheet should
reclaim some space. And although I've never tested it, I've heard that
removing unused custom formats should work as well.

Tried that.

You might also want to consider revising your process. Perhaps you can
have one workbook act as the 'database', with data collection forms
for each of your three users. Then you could (manually or
programmatically) aggregate the data and append it to the (non-shared)
'database' workbook.

This is a good idea. I would prefer to have the data in SQL in any case. Aplenty
here for me to sink about, thanks