I have a large project with the following:

a. File size 1.78 Mb (suspiciously large)
b. 7 worksheets
c. Approx. 35 rectangles
d. 6 userforms
e. 13 code modules
f. No addins installed

This project will eventually be distributed throughout the entire company
and will be used frequently. Unfortunately, it takes approx. 16 seconds to
save on close. Not exactly elegant.

I was suspicious of the file size and unhappy with closing time and so made
a copy. I selectively deleted parts of the copy and checked the closing time
and file size after each deletion. No smoking gun found.

I ultimately ended up deleting everything including my xlb file. I also
rebooted. I added a new (empty) worksheet so that I could delete the last
sheet. Even though the project has no code modules, no userforms, not one
word of code, no shapes, no addins installed, and only one blank worksheet,
the file size is still listed as 1.04 Mb.

Of note, improvement in closing time is not linear: at 1.78 Mb was 16
seconds, at 1.45 Mb was 8 seconds and at 1.04 Mb is 3 seconds.

Any thoughts? I guess I should rebuild the whole thing. Also wondering if
there would be an advantage to putting most of it in an addin and thus
separate from the project. Theoretically, the save time will be improved
since the addin contents won't be saved on close ???

Extremely appreciative of contributions. Rebuilding this is going to be a
huge pain.

Greg

For starters try running VBA Codecleaner:
http://www.appspro.com/Utilities/CodeCleaner.htm

RBS


"Greg Wilson" wrote in message
...
I have a large project with the following:

a. File size 1.78 Mb (suspiciously large)
b. 7 worksheets
c. Approx. 35 rectangles
d. 6 userforms
e. 13 code modules
f. No addins installed

This project will eventually be distributed throughout the entire company
and will be used frequently. Unfortunately, it takes approx. 16 seconds to
save on close. Not exactly elegant.

I was suspicious of the file size and unhappy with closing time and so
made
a copy. I selectively deleted parts of the copy and checked the closing
time
and file size after each deletion. No smoking gun found.

I ultimately ended up deleting everything including my xlb file. I also
rebooted. I added a new (empty) worksheet so that I could delete the last
sheet. Even though the project has no code modules, no userforms, not one
word of code, no shapes, no addins installed, and only one blank
worksheet,
the file size is still listed as 1.04 Mb.

Of note, improvement in closing time is not linear: at 1.78 Mb was 16
seconds, at 1.45 Mb was 8 seconds and at 1.04 Mb is 3 seconds.

Any thoughts? I guess I should rebuild the whole thing. Also wondering if
there would be an advantage to putting most of it in an addin and thus
separate from the project. Theoretically, the save time will be improved
since the addin contents won't be saved on close ???

Extremely appreciative of contributions. Rebuilding this is going to be a
huge pain.

Greg

Thanks for the response. I've been using Code Cleaner for years. Didn't help.

I broke down and rebuilt the wb. Wasn't as bad as I was expecting. I wrote
the appended code which rebuilt the worksheets excluding borders and shapes.
Not the best I'm sure, but worked. I also repeated advice given by Jim Cone
recently when it was acting up: http://tinyurl.com/358e9k

The result was that it shrunk from 1.78 mb to 680 kb. Time to save now 9 to
10 seconds instead of 16. Still a little slow but I think that's just the way
it is. I am also experimenting with making the application invisible when
closing with the following snippet. This closes the application instantly
(make sure the VBE main window insn't open) and lets you do everything as far
as I can tell short of opening a new application.

With Application
If .Workbooks.Count = 1 Then .Visible = False
End With
'code that saves wb and closes...


Code to rebuild the worksheets and named ranges. Not complete and not the
best but did the job. Create a workbook named "Rebuild" first and make sure
it's open.

Sub CopyToNewWB()
Dim ws As Worksheet, ws2 As Worksheet
Dim c As Range, ma As Range
Dim x As Integer

With Workbooks("Rebuild.xls")
Do Until .Worksheets.Count = ThisWorkbook.Worksheets.Count
.Worksheets.Add
Loop
x = 0
For Each ws In ThisWorkbook.Worksheets
x = x + 1
Set ws2 = .Worksheets(x)
ws2.name = ws.name
If ws.name < "Time Sheet Review" Then
For Each c In ws.UsedRange.Cells
If c.MergeCells And c.Address = c.MergeArea(1, 1).Address Then
ws2.Range(c.MergeArea.Address).MergeCells = True
FormatRng ws2.Range(c.MergeArea.Address), c.MergeArea
Else
FormatRng ws2.Range(c.Address), c.MergeArea
End If
Next
End If
Next
For Each nm In ThisWorkbook.Names
.Names.Add nm.name, nm.RefersTo
Next
End With
End Sub

Private Sub FormatRng(rng1 As Range, rng2 As Range)
With rng1(1, 1)
If .Column = 1 Then .RowHeight = rng2(1, 1).RowHeight
If .Row = 1 Then .ColumnWidth = rng2(1, 1).ColumnWidth
.Formula = rng2(1, 1).Formula
.NumberFormat = rng2(1, 1).NumberFormat
.MergeArea.Locked = rng2.Locked
.Font.Size = rng2(1, 1).Font.Size
.VerticalAlignment = rng2(1, 1).VerticalAlignment
.HorizontalAlignment = rng2(1, 1).HorizontalAlignment
.Orientation = rng2(1, 1).Orientation
.Font.Color = rng2(1, 1).Font.Color
If rng2(1, 1).Interior.ColorIndex < xlNone Then _
.Interior.ColorIndex = rng2(1, 1).Interior.ColorIndex
.Font.name = rng2(1, 1).Font.name
.Font.FontStyle = rng2(1, 1).Font.FontStyle
End With
End Sub

Regards,
Greg

Apart from the shapes, what is in the worksheets?
If there is a lot of data in the sheets then maybe it is an option to move
this
away from Excel to something else like a text file or maybe a database.

RBS


"Greg Wilson" wrote in message
...
Thanks for the response. I've been using Code Cleaner for years. Didn't
help.

I broke down and rebuilt the wb. Wasn't as bad as I was expecting. I wrote
the appended code which rebuilt the worksheets excluding borders and
shapes.
Not the best I'm sure, but worked. I also repeated advice given by Jim
Cone
recently when it was acting up: http://tinyurl.com/358e9k

The result was that it shrunk from 1.78 mb to 680 kb. Time to save now 9
to
10 seconds instead of 16. Still a little slow but I think that's just the
way
it is. I am also experimenting with making the application invisible when
closing with the following snippet. This closes the application instantly
(make sure the VBE main window insn't open) and lets you do everything as
far
as I can tell short of opening a new application.

With Application
If .Workbooks.Count = 1 Then .Visible = False
End With
'code that saves wb and closes...


Code to rebuild the worksheets and named ranges. Not complete and not the
best but did the job. Create a workbook named "Rebuild" first and make
sure
it's open.

Sub CopyToNewWB()
Dim ws As Worksheet, ws2 As Worksheet
Dim c As Range, ma As Range
Dim x As Integer

With Workbooks("Rebuild.xls")
Do Until .Worksheets.Count = ThisWorkbook.Worksheets.Count
.Worksheets.Add
Loop
x = 0
For Each ws In ThisWorkbook.Worksheets
x = x + 1
Set ws2 = .Worksheets(x)
ws2.name = ws.name
If ws.name < "Time Sheet Review" Then
For Each c In ws.UsedRange.Cells
If c.MergeCells And c.Address = c.MergeArea(1, 1).Address
Then
ws2.Range(c.MergeArea.Address).MergeCells = True
FormatRng ws2.Range(c.MergeArea.Address), c.MergeArea
Else
FormatRng ws2.Range(c.Address), c.MergeArea
End If
Next
End If
Next
For Each nm In ThisWorkbook.Names
.Names.Add nm.name, nm.RefersTo
Next
End With
End Sub

Private Sub FormatRng(rng1 As Range, rng2 As Range)
With rng1(1, 1)
If .Column = 1 Then .RowHeight = rng2(1, 1).RowHeight
If .Row = 1 Then .ColumnWidth = rng2(1, 1).ColumnWidth
.Formula = rng2(1, 1).Formula
.NumberFormat = rng2(1, 1).NumberFormat
.MergeArea.Locked = rng2.Locked
.Font.Size = rng2(1, 1).Font.Size
.VerticalAlignment = rng2(1, 1).VerticalAlignment
.HorizontalAlignment = rng2(1, 1).HorizontalAlignment
.Orientation = rng2(1, 1).Orientation
.Font.Color = rng2(1, 1).Font.Color
If rng2(1, 1).Interior.ColorIndex < xlNone Then _
.Interior.ColorIndex = rng2(1, 1).Interior.ColorIndex
.Font.name = rng2(1, 1).Font.name
.Font.FontStyle = rng2(1, 1).Font.FontStyle
End With
End Sub

Regards,
Greg

There isn't a lot in the worksheets but there is a lot of code. 98 pages of
code if you wanted to print it based on an actual count (i.e. number of times
it fills the screen). The 6 userforms have a large effect I would think. I
currently database in text files and import/export.

The 98 pages of code sounds like a lot, but I don't think it's unusual. If
you count them in one of your own large projects you will probably be
surprised.

The tests showed that there was something in the file that couldn't be
cleaned up. As mentioned, it took 3 seconds to save the file when there was
nothing left in it except a single worksheet with one character in it (so
that Excel would prompt to save it). This compares with a fraction of a
second with a new wb containing the default 3 pages.

I was thinking of converting most of it to an addin. This is based on the
assumption that it would be much faster because addins aren't saved on close.
I was thinking that only the worksheets and class modules would therefore be
in the file that gets saved. Don' want to do this unless there's a huge
advantage.

Greg




"RB Smissaert" wrote:

Apart from the shapes, what is in the worksheets?
If there is a lot of data in the sheets then maybe it is an option to move
this
away from Excel to something else like a text file or maybe a database.

RBS


"Greg Wilson" wrote in message
...
Thanks for the response. I've been using Code Cleaner for years. Didn't
help.

I broke down and rebuilt the wb. Wasn't as bad as I was expecting. I wrote
the appended code which rebuilt the worksheets excluding borders and
shapes.
Not the best I'm sure, but worked. I also repeated advice given by Jim
Cone
recently when it was acting up: http://tinyurl.com/358e9k

The result was that it shrunk from 1.78 mb to 680 kb. Time to save now 9
to
10 seconds instead of 16. Still a little slow but I think that's just the
way
it is. I am also experimenting with making the application invisible when
closing with the following snippet. This closes the application instantly
(make sure the VBE main window insn't open) and lets you do everything as
far
as I can tell short of opening a new application.

With Application
If .Workbooks.Count = 1 Then .Visible = False
End With
'code that saves wb and closes...


Code to rebuild the worksheets and named ranges. Not complete and not the
best but did the job. Create a workbook named "Rebuild" first and make
sure
it's open.

Sub CopyToNewWB()
Dim ws As Worksheet, ws2 As Worksheet
Dim c As Range, ma As Range
Dim x As Integer

With Workbooks("Rebuild.xls")
Do Until .Worksheets.Count = ThisWorkbook.Worksheets.Count
.Worksheets.Add
Loop
x = 0
For Each ws In ThisWorkbook.Worksheets
x = x + 1
Set ws2 = .Worksheets(x)
ws2.name = ws.name
If ws.name < "Time Sheet Review" Then
For Each c In ws.UsedRange.Cells
If c.MergeCells And c.Address = c.MergeArea(1, 1).Address
Then
ws2.Range(c.MergeArea.Address).MergeCells = True
FormatRng ws2.Range(c.MergeArea.Address), c.MergeArea
Else
FormatRng ws2.Range(c.Address), c.MergeArea
End If
Next
End If
Next
For Each nm In ThisWorkbook.Names
.Names.Add nm.name, nm.RefersTo
Next
End With
End Sub

Private Sub FormatRng(rng1 As Range, rng2 As Range)
With rng1(1, 1)
If .Column = 1 Then .RowHeight = rng2(1, 1).RowHeight
If .Row = 1 Then .ColumnWidth = rng2(1, 1).ColumnWidth
.Formula = rng2(1, 1).Formula
.NumberFormat = rng2(1, 1).NumberFormat
.MergeArea.Locked = rng2.Locked
.Font.Size = rng2(1, 1).Font.Size
.VerticalAlignment = rng2(1, 1).VerticalAlignment
.HorizontalAlignment = rng2(1, 1).HorizontalAlignment
.Orientation = rng2(1, 1).Orientation
.Font.Color = rng2(1, 1).Font.Color
If rng2(1, 1).Interior.ColorIndex < xlNone Then _
.Interior.ColorIndex = rng2(1, 1).Interior.ColorIndex
.Font.name = rng2(1, 1).Font.name
.Font.FontStyle = rng2(1, 1).Font.FontStyle
End With
End Sub

Regards,
Greg

I think changing it to an .xla sounds like a good idea.
Another thing worth trying is use Andrew Baker's Workbook Rebuilder.
I have seen it that it cleaned a lot more than the free CodeCleaner although
I can't explain why that would be. I think you can download a trial version.

RBS


"Greg Wilson" wrote in message
...
There isn't a lot in the worksheets but there is a lot of code. 98 pages
of
code if you wanted to print it based on an actual count (i.e. number of
times
it fills the screen). The 6 userforms have a large effect I would think. I
currently database in text files and import/export.

The 98 pages of code sounds like a lot, but I don't think it's unusual. If
you count them in one of your own large projects you will probably be
surprised.

The tests showed that there was something in the file that couldn't be
cleaned up. As mentioned, it took 3 seconds to save the file when there
was
nothing left in it except a single worksheet with one character in it (so
that Excel would prompt to save it). This compares with a fraction of a
second with a new wb containing the default 3 pages.

I was thinking of converting most of it to an addin. This is based on the
assumption that it would be much faster because addins aren't saved on
close.
I was thinking that only the worksheets and class modules would therefore
be
in the file that gets saved. Don' want to do this unless there's a huge
advantage.

Greg




"RB Smissaert" wrote:

Apart from the shapes, what is in the worksheets?
If there is a lot of data in the sheets then maybe it is an option to
move
this
away from Excel to something else like a text file or maybe a database.

RBS


"Greg Wilson" wrote in message
...
Thanks for the response. I've been using Code Cleaner for years. Didn't
help.

I broke down and rebuilt the wb. Wasn't as bad as I was expecting. I
wrote
the appended code which rebuilt the worksheets excluding borders and
shapes.
Not the best I'm sure, but worked. I also repeated advice given by Jim
Cone
recently when it was acting up: http://tinyurl.com/358e9k

The result was that it shrunk from 1.78 mb to 680 kb. Time to save now
9
to
10 seconds instead of 16. Still a little slow but I think that's just
the
way
it is. I am also experimenting with making the application invisible
when
closing with the following snippet. This closes the application
instantly
(make sure the VBE main window insn't open) and lets you do everything
as
far
as I can tell short of opening a new application.

With Application
If .Workbooks.Count = 1 Then .Visible = False
End With
'code that saves wb and closes...


Code to rebuild the worksheets and named ranges. Not complete and not
the
best but did the job. Create a workbook named "Rebuild" first and make
sure
it's open.

Sub CopyToNewWB()
Dim ws As Worksheet, ws2 As Worksheet
Dim c As Range, ma As Range
Dim x As Integer

With Workbooks("Rebuild.xls")
Do Until .Worksheets.Count = ThisWorkbook.Worksheets.Count
.Worksheets.Add
Loop
x = 0
For Each ws In ThisWorkbook.Worksheets
x = x + 1
Set ws2 = .Worksheets(x)
ws2.name = ws.name
If ws.name < "Time Sheet Review" Then
For Each c In ws.UsedRange.Cells
If c.MergeCells And c.Address = c.MergeArea(1,
1).Address
Then
ws2.Range(c.MergeArea.Address).MergeCells = True
FormatRng ws2.Range(c.MergeArea.Address),
c.MergeArea
Else
FormatRng ws2.Range(c.Address), c.MergeArea
End If
Next
End If
Next
For Each nm In ThisWorkbook.Names
.Names.Add nm.name, nm.RefersTo
Next
End With
End Sub

Private Sub FormatRng(rng1 As Range, rng2 As Range)
With rng1(1, 1)
If .Column = 1 Then .RowHeight = rng2(1, 1).RowHeight
If .Row = 1 Then .ColumnWidth = rng2(1, 1).ColumnWidth
.Formula = rng2(1, 1).Formula
.NumberFormat = rng2(1, 1).NumberFormat
.MergeArea.Locked = rng2.Locked
.Font.Size = rng2(1, 1).Font.Size
.VerticalAlignment = rng2(1, 1).VerticalAlignment
.HorizontalAlignment = rng2(1, 1).HorizontalAlignment
.Orientation = rng2(1, 1).Orientation
.Font.Color = rng2(1, 1).Font.Color
If rng2(1, 1).Interior.ColorIndex < xlNone Then _
.Interior.ColorIndex = rng2(1, 1).Interior.ColorIndex
.Font.name = rng2(1, 1).Font.name
.Font.FontStyle = rng2(1, 1).Font.FontStyle
End With
End Sub

Regards,
Greg

Hi Greg,

The result was that it shrunk from 1.78 mb to 680 kb

Might be a result of code now non-compiled vs previously partly/fully
compiled, particularly if you have neither done Debug - compile nor run some
code from each module before saving (worth doing).

Time to save now 9 to 10 seconds instead of 16.

Still seems slow. I have larger project that saves faster in what I'll bet
is a much older system than yours. I notice the "linear" time difference
does not tally with what you quoted in your OP - "and at 1.04 Mb is 3
seconds".

Regards,
Peter T


"Greg Wilson" wrote in message
...
Thanks for the response. I've been using Code Cleaner for years. Didn't
help.

I broke down and rebuilt the wb. Wasn't as bad as I was expecting. I wrote
the appended code which rebuilt the worksheets excluding borders and
shapes.
Not the best I'm sure, but worked. I also repeated advice given by Jim
Cone
recently when it was acting up: http://tinyurl.com/358e9k

The result was that it shrunk from 1.78 mb to 680 kb. Time to save now 9
to
10 seconds instead of 16. Still a little slow but I think that's just the
way
it is. I am also experimenting with making the application invisible when
closing with the following snippet. This closes the application instantly
(make sure the VBE main window insn't open) and lets you do everything as
far
as I can tell short of opening a new application.

With Application
If .Workbooks.Count = 1 Then .Visible = False
End With
'code that saves wb and closes...


Code to rebuild the worksheets and named ranges. Not complete and not the
best but did the job. Create a workbook named "Rebuild" first and make
sure
it's open.

Sub CopyToNewWB()
Dim ws As Worksheet, ws2 As Worksheet
Dim c As Range, ma As Range
Dim x As Integer

With Workbooks("Rebuild.xls")
Do Until .Worksheets.Count = ThisWorkbook.Worksheets.Count
.Worksheets.Add
Loop
x = 0
For Each ws In ThisWorkbook.Worksheets
x = x + 1
Set ws2 = .Worksheets(x)
ws2.name = ws.name
If ws.name < "Time Sheet Review" Then
For Each c In ws.UsedRange.Cells
If c.MergeCells And c.Address = c.MergeArea(1, 1).Address
Then
ws2.Range(c.MergeArea.Address).MergeCells = True
FormatRng ws2.Range(c.MergeArea.Address), c.MergeArea
Else
FormatRng ws2.Range(c.Address), c.MergeArea
End If
Next
End If
Next
For Each nm In ThisWorkbook.Names
.Names.Add nm.name, nm.RefersTo
Next
End With
End Sub

Private Sub FormatRng(rng1 As Range, rng2 As Range)
With rng1(1, 1)
If .Column = 1 Then .RowHeight = rng2(1, 1).RowHeight
If .Row = 1 Then .ColumnWidth = rng2(1, 1).ColumnWidth
.Formula = rng2(1, 1).Formula
.NumberFormat = rng2(1, 1).NumberFormat
.MergeArea.Locked = rng2.Locked
.Font.Size = rng2(1, 1).Font.Size
.VerticalAlignment = rng2(1, 1).VerticalAlignment
.HorizontalAlignment = rng2(1, 1).HorizontalAlignment
.Orientation = rng2(1, 1).Orientation
.Font.Color = rng2(1, 1).Font.Color
If rng2(1, 1).Interior.ColorIndex < xlNone Then _
.Interior.ColorIndex = rng2(1, 1).Interior.ColorIndex
.Font.name = rng2(1, 1).Font.name
.Font.FontStyle = rng2(1, 1).Font.FontStyle
End With
End Sub

Regards,
Greg

Thanks Peter for responding. This is a repeat reply. My initial reply seems
to have been lost.

I used Code Cleaner before the rebuild with no success. I rebooted also. I
would think this would remove compiled code. I also noted the time difference
(3 secs. for 1.04 mb). I think this is because there are gaps in the file
somehow. The o/s perhaps thinks the file size is defined by the position of
the last info in the file ignoring gaps (???).

However, when tested against a new wb with the default 3 pages, saving time
was almost instant. So there appears to be something in the file that can't
be removed.

I tried Andrew Bakers Workbook Rebuilder with modest success at speeding up
the save time (perhaps a second). File size reduced to about 625 kb (after
first save) versus 690 kb for my own rebuilt. These values fluctuate quite a
bit.

Greg

"Peter T" wrote:

Hi Greg,

The result was that it shrunk from 1.78 mb to 680 kb

Might be a result of code now non-compiled vs previously partly/fully
compiled, particularly if you have neither done Debug - compile nor run some
code from each module before saving (worth doing).

Time to save now 9 to 10 seconds instead of 16.

Still seems slow. I have larger project that saves faster in what I'll bet
is a much older system than yours. I notice the "linear" time difference
does not tally with what you quoted in your OP - "and at 1.04 Mb is 3
seconds".

Regards,
Peter T


"Greg Wilson" wrote in message
...
Thanks for the response. I've been using Code Cleaner for years. Didn't
help.

I broke down and rebuilt the wb. Wasn't as bad as I was expecting. I wrote
the appended code which rebuilt the worksheets excluding borders and
shapes.
Not the best I'm sure, but worked. I also repeated advice given by Jim
Cone
recently when it was acting up: http://tinyurl.com/358e9k

The result was that it shrunk from 1.78 mb to 680 kb. Time to save now 9
to
10 seconds instead of 16. Still a little slow but I think that's just the
way
it is. I am also experimenting with making the application invisible when
closing with the following snippet. This closes the application instantly
(make sure the VBE main window insn't open) and lets you do everything as
far
as I can tell short of opening a new application.

With Application
If .Workbooks.Count = 1 Then .Visible = False
End With
'code that saves wb and closes...


Code to rebuild the worksheets and named ranges. Not complete and not the
best but did the job. Create a workbook named "Rebuild" first and make
sure
it's open.

Sub CopyToNewWB()
Dim ws As Worksheet, ws2 As Worksheet
Dim c As Range, ma As Range
Dim x As Integer

With Workbooks("Rebuild.xls")
Do Until .Worksheets.Count = ThisWorkbook.Worksheets.Count
.Worksheets.Add
Loop
x = 0
For Each ws In ThisWorkbook.Worksheets
x = x + 1
Set ws2 = .Worksheets(x)
ws2.name = ws.name
If ws.name < "Time Sheet Review" Then
For Each c In ws.UsedRange.Cells
If c.MergeCells And c.Address = c.MergeArea(1, 1).Address
Then
ws2.Range(c.MergeArea.Address).MergeCells = True
FormatRng ws2.Range(c.MergeArea.Address), c.MergeArea
Else
FormatRng ws2.Range(c.Address), c.MergeArea
End If
Next
End If
Next
For Each nm In ThisWorkbook.Names
.Names.Add nm.name, nm.RefersTo
Next
End With
End Sub

Private Sub FormatRng(rng1 As Range, rng2 As Range)
With rng1(1, 1)
If .Column = 1 Then .RowHeight = rng2(1, 1).RowHeight
If .Row = 1 Then .ColumnWidth = rng2(1, 1).ColumnWidth
.Formula = rng2(1, 1).Formula
.NumberFormat = rng2(1, 1).NumberFormat
.MergeArea.Locked = rng2.Locked
.Font.Size = rng2(1, 1).Font.Size
.VerticalAlignment = rng2(1, 1).VerticalAlignment
.HorizontalAlignment = rng2(1, 1).HorizontalAlignment
.Orientation = rng2(1, 1).Orientation
.Font.Color = rng2(1, 1).Font.Color
If rng2(1, 1).Interior.ColorIndex < xlNone Then _
.Interior.ColorIndex = rng2(1, 1).Interior.ColorIndex
.Font.name = rng2(1, 1).Font.name
.Font.FontStyle = rng2(1, 1).Font.FontStyle
End With
End Sub

Regards,
Greg

I've just discouvered that the slow save is limited to my laptop:
XL 2003 / XP Professional. Takes about 8 seconds at this point but appears
to be growing with each save.

On another computer running XL 2000 / 2000 Professional it takes only about
2 1/2 seconds. File size appears to be growing here too but slower.

Any ideas anyone?

Greg



"Greg Wilson" wrote:

I have a large project with the following:

a. File size 1.78 Mb (suspiciously large)
b. 7 worksheets
c. Approx. 35 rectangles
d. 6 userforms
e. 13 code modules
f. No addins installed

This project will eventually be distributed throughout the entire company
and will be used frequently. Unfortunately, it takes approx. 16 seconds to
save on close. Not exactly elegant.

I was suspicious of the file size and unhappy with closing time and so made
a copy. I selectively deleted parts of the copy and checked the closing time
and file size after each deletion. No smoking gun found.

I ultimately ended up deleting everything including my xlb file. I also
rebooted. I added a new (empty) worksheet so that I could delete the last
sheet. Even though the project has no code modules, no userforms, not one
word of code, no shapes, no addins installed, and only one blank worksheet,
the file size is still listed as 1.04 Mb.

Of note, improvement in closing time is not linear: at 1.78 Mb was 16
seconds, at 1.45 Mb was 8 seconds and at 1.04 Mb is 3 seconds.

Any thoughts? I guess I should rebuild the whole thing. Also wondering if
there would be an advantage to putting most of it in an addin and thus
separate from the project. Theoretically, the save time will be improved
since the addin contents won't be saved on close ???

Extremely appreciative of contributions. Rebuilding this is going to be a
huge pain.

Greg

Hi Greg,

I'm sure that's highly indicative, can't say of what though!

I'm sure you've excluded the following but just in case - temp files,
anti-virus.

I note you have rebuilt the wb, was that in both setups. In particular was
the original template sourced from respective versions.

Just for curiosity how long to save a simple data file with loads of one's
in cells and nothing else, say about 1 Mg, in each system. I typed A1:J10000
in the Name box, 1 in the Inputbar and ctrl-Enter. 100k one's saved for me
in well under one second to a 927Mb file. What would that do for you in
respective setups.

Regards,
Peter T


"Greg Wilson" wrote in message
...
I've just discouvered that the slow save is limited to my laptop:
XL 2003 / XP Professional. Takes about 8 seconds at this point but appears
to be growing with each save.

On another computer running XL 2000 / 2000 Professional it takes only
about
2 1/2 seconds. File size appears to be growing here too but slower.

Any ideas anyone?

Greg

<snip