If you think it could be some sort of corruption, remember to keep lots of
backups!

I don't have any better advice--well, maybe it's time to recreate that workbook
from scratch. But in general, that's a lot of work and who knows if it'll help.



Peter T wrote:

Everything you say Dave makes perfect sense, and does with most of my files.
But not with my long term development file. I've done various tests again
with backups and get the same as I described previously. Maybe there's some
corruption with the wb that has not manifested itself, but that's only a
guess.

Regards,
Peter T

"Dave Peterson" wrote in message
If you run Rob's code cleaner (which exports the code to a text file and
deletes
that module), I'm not sure how anything code related (bloat included)
could live
through that.

And I've never noticed significant filesize changes between an add in and
a
normal workbook. But that article is pretty old--maybe there used to
be????

Peter T wrote:

Thanks Dave for your further suggestions. Time, hmm, a sore subject <g

I did as you suggested (didn't take long!), imported all code into a new
wb,
same number of sheets but each totally empty. Saved non-compiled (no
code
run) & full compiled versions. +700kb difference in size, as expected.

I'm fairly confident file bloat is not related to worksheets and have
discounted the state of compile. As I mentioned yesterday, I removed all
modules and other code from the "old" bloated project and saved.
Resulting
size similar to creating a new wb + sheets from scratch.

During the process of development new code, proc's, modules etc are
added,
moved & deleted with multiple file saves during the process. I suspect
what
occurs is references (for want of a better word) to extinct stuff remain
in
the workbook, hence gradual size increase.

In a sense this is not normally a problem during development providing
everything works. Although I don't understand why that occurs I know it
does, so when done I build a new workbook and import the code.

I've just re-read the Don Baarns article, this remark caught my eye -

"Turning the code into an Add-In (<snip) can also return a file to its
previous size."

I develop in an xls before converting to an xla. I have never noticed a
decrease in size after conversion. But perhaps there's a clue in that
remark
that relates to my un-expected size decrease after saving from the
immediate
window (at the time an xla & IsAddin true).

Regards,
Peter T

"Dave Peterson" wrote in message
...
I've read the Baarnes' article before--but thanks for the links.

Someday when you have time (hehe), maybe you could createworkbook and
copy
all
the modules there--but don't put any data into any worksheet.

Then modify the code, compile, and save to see what happens. This
might
help
you isolate the filesize difference (is it really worksheet related or
code
related?).

If it's related to worksheets, maybe you could try to isolate one of
them
to see
if it's just one--then rebuild that single worksheet.



Peter T wrote:

Hi Dave,

But after you've run Rob's codecleaner, if you saved one version
with
the
code
compiled (either by running/testing code or by debug|compile
VBAProject)
and
another without this extra step, then that could add to the
difference
in
size.
(Although, I don't think I've seen the file change size by as much
as
300k.)

My particular project expands by about 700Kb between doing a
"Codecleaner"
and a debug full compile. Seems a lot but I think normal, about
15,000
lines
of code, many controls and stuff.

Off topic & FWIW, for a final project I've found the best compromise
between
size & performance is not to compile, but start with a "clean"
project
and
run at least one proc' from each "frequently used" module. Perhaps
also
remove Option Explicit (not sure about that). * See link below.

Over a period of time, and without building a new wb, I have moved a
few
thousand lines to a referenced dll. I've noticed the difference
between
non-compiled (no code run) & compiled increasing. Ie, non compiled
file
getting smaller, compiled size only slightly smaller.

As I mentioned to Tom, I don't think XL version and/or protected is
a
significant factor with my project.

In the course of development I've often found file size to gradually
increase, irrespective of the amount of code. I'm always careful
about
the
"lastcell" etc. It did not come as a total surprise yesterday when I
built a
new identical project and file size decreased (with similar 700kb
diff'
between non-compiled & compiled).

What was a surprise was by saving from the immediate window my old
project
reduced in size to close to that of a similar brand new project,
300Kb
smaller in both states of compile. The other surprise of course was
this
solved my lock problem!

Regards,
Peter T

* I read this by Don Baarns a long time ago and found highly
informative
http://archive.baarns.com/excel/develop/vbaperfm.asp

There are no links on that page, so -
http://archive.baarns.com/IE4/index_devonly.asp

<snip

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson