1.04 Mb with nothing in file ???

[Greg Wilson]

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

 

[RB Smissaert]

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

RBS

 

[Greg Wilson]

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

 

[RB Smissaert]

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

 

[Peter T]

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]

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]

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]

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

 

[Greg Wilson]

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

 

[RB Smissaert]

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]

I am indeed interested since I was seriously considering buying the product.
In exchange, I have a native Excel calendar control that I hope is worthy.
It's actuality a toolbar but looks and acts very similar to a MonthView
control. Should end the hassle with distributing stuff since it's entirely
native and self-contained. Tested for accuracy with no problems (relies on
VBA's date functions so not a surprise). Fits nicely in a code module.
Graphics look good in XP at least.

Greg

 

[Greg Wilson]

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

 

[RB Smissaert]

OK, I will look into it and come back off-list. Shouldn't be a problem.

RBS

 

[Peter T]

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

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>

 

[Greg Wilson]

Same response time for me - about half a second. I also tried it with the
range A1:Z10000. Took about 1 second.

I defragged it last night with no improvement. I put the file on a network
drive today and saved it there using the same computer. Took 2 to 3 seconds.
Deleted the .xlb file, emptied the recycle bin, cleaned out the Windows Temp
folder, turned off my anti-virus (I think). All to no avail. FWIW, you can
hear it click away writing to the hard drive.

I also tried another program of mine which has several code modules and two
UFs at about 850 kb. Took about 8 seconds. I'm under the impression that this
is just the way it is with xl2003 and XP and code modules. I think I just
started paying attention because of management's request to install it on all
the computers in a sister office (and later to ours). The pre-rebuild time of
16 secs. when it was at 1.78 Mb I think was the result of many worksheet
additons and deletions etc. It has undergone a lot of developement.

Greg

 

[bart.smissaert]

I am indeed interested since I was seriously considering buying the product.
In exchange, I have a native Excel calendar control that I hope is worthy.
It's actuality a toolbar but looks and acts very similar to a MonthView
control. Should end the hassle with distributing stuff since it's entirely
native and self-contained. Tested for accuracy with no problems (relies on
VBA's date functions so not a surprise). Fits nicely in a code module.
Graphics look good in XP at least.

Greg

Greg,

If you contact me off-list (I need your e-mail address) then I will
sort this out.

RBS

 

[Peter T]

I'm under the impression that this

is just the way it is with xl2003 and XP and code modules.

You might be right but I'm not so sure, still seems slow. What's it now, 8
sec for roughly a 1Mb project.? I would have thought if anything xl2003 &
wXP would be faster than xl2000 w2000, but it seems not for you.

Deleted the .xlb file,

Although occasionally the xlb can bloat I can't imagine how it would affect
this issue.

More long shots before saving:
- close all module windows and then the VBE
- Save triggers a recalc, if suspicious replace all formulas with "=" to
"#="

Regards,
Peter T

 

[Greg Wilson]

Close to 9 secs. for 700 kb. About 5 secs. when macros disabled. File size
seems to be growing too but does reverse also. I'm thinking about installing
code that monitors it's size over time, logging to a text file. That way I
can see if it stabalizes at some point.

I can't think of any code that is unusual besides an Autpen macro. Tested
with it commented out with no effect. Only non-default reference is to Forms
2.0. Only has two API declarations: GetKeyState and GetCursorPos.

Saves in about 2 1/2 secs. on xl2000 running 2000 Professional. But on
another machine running exactly the same thing takes about 7 seconds. So I'm
at a complete loss.

The UFs seem to be the worst offenders on file size. I exported all modules
and then reimported them, checking the file size after each import. I've been
googling for info on file growth. There is an issue with Pivot Tables.

- Save triggers a recalc, if suspicious replace all formulas with "=" to
"#="

I commented out about 1/3 of the formula with vertually no effect.

Thanks hugely for the help.

Greg

 

[Peter T]

Close to 9 secs. for 700 kb. About 5 secs. when macros disabled.

Is that all macros disabled or just close event code disabled?

File size
seems to be growing too but does reverse also. I'm thinking about installing
code that monitors it's size over time, logging to a text file. That way I
can see if it stabalizes at some point.

I wouldn't get too hung up over minor fluctuations in file size while
developing. There can be a 2-3 fold difference in size between totally
uncopompiled (just after a clean), fully compiled, and states in between
(after running code for first time). Also other normally harmless garbage
can creep in that's unlikely to impact significantly on save time..

Saves in about 2 1/2 secs. on xl2000 running 2000 Professional. But on
another machine running exactly the same thing takes about 7 seconds. So I'm
at a complete loss.

Now that really is intriguing! Everything same with the overall state of the
file and Excel + VBE just before the save?
Were the two files made from scratch in respective systems? If not, and the
faster save relates to the system the file was made on might suggest some
difference in references (inlc ocx /controls) assuming both file systems
operating normally.

Afraid I'm out of ideas, not that I had any useful ones in the first place.
However it might be worth focusing on what's potentially different between
otherwise similar systems.. Hopefully you will come up with a little pearl
for the archives

Regards,
Peter T

 

[Greg Wilson]

Thanks for staying with this one for this long.

Is that all macros disabled or just close event code disabled?

That was with all macros disabled.

As I mentioned, I normally don't pay much attention to file size. This may a
non-issue. I'll keep an eye on it though. If I come up with the answer I'll
post it to the ng.

Bart gave me a free registration for Andrew Baker's Workbook Rebuilder. I
will put it to good use. So this has had a silver lining.

Thanks again.

Greg