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" <(E-Mail Removed)> wrote in message
news:078E941C-8833-488E-8061-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:92506EEE-217C-424F-BB75-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:92506EEE-217C-424F-BB75-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:92506EEE-217C-424F-BB75-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:7F6C5A36-1393-410B-97BC-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:92506EEE-217C-424F-BB75-(E-Mail Removed)...
>> > 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 again. I downloaded the trial version and gave it a go. It initially
reduced the listed file size hugely to about 450 kb. However, after the first
save it was up to about 625. Save time not much improved, perhaps a second.
Looks like a good product.

The downside of making it an *.xla is that referencing the addin increases
the load time. Right now it's instant.

Greg



"RB Smissaert" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:7F6C5A36-1393-410B-97BC-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:92506EEE-217C-424F-BB75-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
> news:92506EEE-217C-424F-BB75-(E-Mail Removed)...
> > 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
> >
> >
> >
>
>
>

What I do after editing my main .xla is compile, then save then run the WB
Rebuilder.
I do this every time, just to be on the safe side as this is really large
..xla, over 6 Mb with
all the comments, and empty lines, leading and trailing tabs/spaces etc.
stripped. This is with
nil at all in the sheets.
Not sure now if there has to be a difference in loading an .xla compared to
a .xls, but my
main .xla is not loaded as an add-in (so not ticked under Tools, Addins),
but just opened as a
normal .xls file would be opened.

Will have to figure out how to do it, but I have actually a licence for the
WB Rebuilder to give away,
if you are interested.

RBS



"Greg Wilson" <(E-Mail Removed)> wrote in message
news:ED666538-AAE7-47BF-B92E-(E-Mail Removed)...
> Thanks again. I downloaded the trial version and gave it a go. It
> initially
> reduced the listed file size hugely to about 450 kb. However, after the
> first
> save it was up to about 625. Save time not much improved, perhaps a
> second.
> Looks like a good product.
>
> The downside of making it an *.xla is that referencing the addin increases
> the load time. Right now it's instant.
>
> Greg
>
>
>
> "RB Smissaert" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:7F6C5A36-1393-410B-97BC-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:92506EEE-217C-424F-BB75-(E-Mail Removed)...
>> >> > 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
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>>
>>