In XL2K, after closing all module windows, I "lock project for viewing" and
password protect my addin. Save from the vbe, close XL.

When I reopen XL and my addin, the project is not locked.

I go back into Project properties / Protection and the lock check box and
password dialogs are empty. If I now tick the lock checkbox I'm asked for my
password (although no asterisks appear in the password textbox's). Enter
password, everything seems normal. Save, close, reopen and back to the same
situation.

The project is "clean" and correctly compiled.

Any ideas how to resolve without rebuilding the addin appreciated.

Regards,
Peter T

My bet is that you're opening a different file than you're saving.

Make sure that the addin's project is active before saving.

And as a workaround (I've never had the trouble you describe), you can change
the workbook's .addin property to false. (Select your project, hit F4 to see
properties and look for IsAddin.)

Then you can go back to excel and save that workbook as an addin (remember to
change the filetype) via that interface.

=====

Another way while in the VBE:
hit ctrl-g (to see the immediate window):
type this and hit enter:
workbooks("youraddinname.xla").save

Maybe this would help, too (also in the immediate window):
?workbooks("youraddinname.xla").fullname

(Just to verify that you're where you think you are.)

Peter T wrote:

In XL2K, after closing all module windows, I "lock project for viewing" and
password protect my addin. Save from the vbe, close XL.

When I reopen XL and my addin, the project is not locked.

I go back into Project properties / Protection and the lock check box and
password dialogs are empty. If I now tick the lock checkbox I'm asked for my
password (although no asterisks appear in the password textbox's). Enter
password, everything seems normal. Save, close, reopen and back to the same
situation.

The project is "clean" and correctly compiled.

Any ideas how to resolve without rebuilding the addin appreciated.

Regards,
Peter T

--

Dave Peterson

Hi Dave, thanks for responding. Comments in line -

My bet is that you're opening a different file than you're saving.

No, definately working with the same file. But I'd better not be that
dogmatic (see later).

Make sure that the addin's project is active before saving.

Yes always active, selected in vbe project's panel, then save from vbe.

And as a workaround (I've never had the trouble you describe), you can
change
the workbook's .addin property to false. (Select your project, hit F4 to
see
properties and look for IsAddin.)

I've tried locking & saving with IsAddin True (from vbe) and False (from
both Excel & vbe)

Then you can go back to excel and save that workbook as an addin (remember
to
change the filetype) via that interface.

Similar to one step above. But also locked what was an xls with SaveAs xla
while still a visible workbook. I've also tried copying the file to
different folders, renaming, trying each with different passwords (only one
open at a time). Each time it/they re-open unlocked. A password obviously
exists though no asteriks, after entering the correct pw for that file I can
tick the Lock checkbox. Check Fullname each time. Repeat the save, all to no
avail. Also emptied temporary folders and repeated in new sessions of Excel
and even Windows.

Another way while in the VBE:
hit ctrl-g (to see the immediate window):
type this and hit enter:
workbooks("youraddinname.xla").save

Well blow me down! that's just worked !!

Bit of a mystery but thank you very much.

Regards,
Peter T


Peter T wrote:

In XL2K, after closing all module windows, I "lock project for viewing"
and
password protect my addin. Save from the vbe, close XL.

When I reopen XL and my addin, the project is not locked.

I go back into Project properties / Protection and the lock check box
and
password dialogs are empty. If I now tick the lock checkbox I'm asked
for my
password (although no asterisks appear in the password textbox's). Enter
password, everything seems normal. Save, close, reopen and back to the
same
situation.

The project is "clean" and correctly compiled.

Any ideas how to resolve without rebuilding the addin appreciated.

Regards,
Peter T

--

Dave Peterson

I'm glad the workaround worked ok for you.

I've never had a problem saving from the VBE, but I've read a few posts where
the authors say they avoid it because of problems they've had.

In a different forum, Thomas Ramel posted some code that he adds to an addin.
You may like it:

=========================
From Thomas Ramel:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Saved = True Then Exit Sub
If MsgBox("Save changes in Add-In" & Chr(13) & Chr(13) & _
UCase(ThisWorkbook.VBProject.Name) & Chr(13) & Chr(13) & _
"?", vbYesNo) = vbYes Then
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
End If
End Sub

Now every time I changed something in the Code and forgot to save it
manually I get a reminder, wehe I close Excel.


Regards
Thomas Ramel



Peter T wrote:

Hi Dave, thanks for responding. Comments in line -

My bet is that you're opening a different file than you're saving.

No, definately working with the same file. But I'd better not be that
dogmatic (see later).

Make sure that the addin's project is active before saving.

Yes always active, selected in vbe project's panel, then save from vbe.

And as a workaround (I've never had the trouble you describe), you can
change
the workbook's .addin property to false. (Select your project, hit F4 to
see
properties and look for IsAddin.)

I've tried locking & saving with IsAddin True (from vbe) and False (from
both Excel & vbe)

Then you can go back to excel and save that workbook as an addin (remember
to
change the filetype) via that interface.

Similar to one step above. But also locked what was an xls with SaveAs xla
while still a visible workbook. I've also tried copying the file to
different folders, renaming, trying each with different passwords (only one
open at a time). Each time it/they re-open unlocked. A password obviously
exists though no asteriks, after entering the correct pw for that file I can
tick the Lock checkbox. Check Fullname each time. Repeat the save, all to no
avail. Also emptied temporary folders and repeated in new sessions of Excel
and even Windows.

Another way while in the VBE:
hit ctrl-g (to see the immediate window):
type this and hit enter:
workbooks("youraddinname.xla").save

Well blow me down! that's just worked !!

Bit of a mystery but thank you very much.

Regards,
Peter T

Peter T wrote:

In XL2K, after closing all module windows, I "lock project for viewing"
and
password protect my addin. Save from the vbe, close XL.

When I reopen XL and my addin, the project is not locked.

I go back into Project properties / Protection and the lock check box
and
password dialogs are empty. If I now tick the lock checkbox I'm asked
for my
password (although no asterisks appear in the password textbox's). Enter
password, everything seems normal. Save, close, reopen and back to the
same
situation.

The project is "clean" and correctly compiled.

Any ideas how to resolve without rebuilding the addin appreciated.

Regards,
Peter T

--

Dave Peterson

--

Dave Peterson

"Dave Peterson" wrote in message

I'm glad the workaround worked ok for you.

Not as glad as I am, thanks again!

I've never had a problem saving from the VBE, but I've read a few posts
where
the authors say they avoid it because of problems they've had.

Yes I've also read similar and don't normally save from the VBE. The problem
though couldn't be fixed by saving changing IsAddin to false, locking, then
saving in Excel. However the problem commenced having saved in the VBE, from
then on couldn't be fixed.

In my development project I already have something similar to Thomas Ramel's
code (below), it does other things to. But I had removed pending
distribution.

==============

FWIW, an unexpected observation -

The normal size of my old project varied from 1.4mb (no code ever run) to
2.2mb. In both cases just after using Rob Bovey's Codecleaner to strip and
clean, the larger file size if I then do a full compile and save.

But after saving both types of files from the immediate window the
respective file sizes reduced by about 300kb.

Seemed odd so I built a brand new identical file with similar sheets and
dragged modules from the old file to the new. The new saves to the same
lower file sizes respectively.

Me thought - must be some garbage built up in the old worksheets, even
though the project is mainly code and little in the way of worksheet stuff.
I removed all modules from the old file and looked at file size. Very small
and just as expected to account for the sheets, about 100kb.

Bearing in mind the old project was freshly stripped and cleaned, I'm
curious as to what that phantom 300kb accounted for. Even more curious as to
why this 300Kb suddenly disappeared ONLY because I saved from the immediate
window. Replicates with back-ups of old non-problematic files.

Regards,
Peter T


In a different forum, Thomas Ramel posted some code that he adds to an
addin.
You may like it:

=========================
From Thomas Ramel:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Saved = True Then Exit Sub
If MsgBox("Save changes in Add-In" & Chr(13) & Chr(13) & _
UCase(ThisWorkbook.VBProject.Name) & Chr(13) & Chr(13) & _
"?", vbYesNo) = vbYes Then
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
End If
End Sub

Now every time I changed something in the Code and forgot to save it
manually I get a reminder, wehe I close Excel.


Regards
Thomas Ramel



Peter T wrote:

Hi Dave, thanks for responding. Comments in line -

My bet is that you're opening a different file than you're saving.

No, definately working with the same file. But I'd better not be that
dogmatic (see later).

Make sure that the addin's project is active before saving.

Yes always active, selected in vbe project's panel, then save from vbe.

And as a workaround (I've never had the trouble you describe), you can
change
the workbook's .addin property to false. (Select your project, hit F4
to
see
properties and look for IsAddin.)

I've tried locking & saving with IsAddin True (from vbe) and False (from
both Excel & vbe)

Then you can go back to excel and save that workbook as an addin
(remember
to
change the filetype) via that interface.

Similar to one step above. But also locked what was an xls with SaveAs
xla
while still a visible workbook. I've also tried copying the file to
different folders, renaming, trying each with different passwords (only
one
open at a time). Each time it/they re-open unlocked. A password
obviously
exists though no asteriks, after entering the correct pw for that file I
can
tick the Lock checkbox. Check Fullname each time. Repeat the save, all
to no
avail. Also emptied temporary folders and repeated in new sessions of
Excel
and even Windows.

Another way while in the VBE:
hit ctrl-g (to see the immediate window):
type this and hit enter:
workbooks("youraddinname.xla").save

Well blow me down! that's just worked !!

Bit of a mystery but thank you very much.

Regards,
Peter T

Peter T wrote:

In XL2K, after closing all module windows, I "lock project for
viewing"
and
password protect my addin. Save from the vbe, close XL.

When I reopen XL and my addin, the project is not locked.

I go back into Project properties / Protection and the lock check
box
and
password dialogs are empty. If I now tick the lock checkbox I'm
asked
for my
password (although no asterisks appear in the password textbox's).
Enter
password, everything seems normal. Save, close, reopen and back to
the
same
situation.

The project is "clean" and correctly compiled.

Any ideas how to resolve without rebuilding the addin appreciated.

Regards,
Peter T

--

Dave Peterson

--

Dave Peterson

You mentioned that you had changed versions of Excel in you testing. I
notice file size changes in different versions of excel. I assume you have
eliminated this as a possible cause.

another thought is if you are comparing protected to unprotected.

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...
"Dave Peterson" wrote in message

I'm glad the workaround worked ok for you.

Not as glad as I am, thanks again!

I've never had a problem saving from the VBE, but I've read a few posts
where
the authors say they avoid it because of problems they've had.

Yes I've also read similar and don't normally save from the VBE. The
problem
though couldn't be fixed by saving changing IsAddin to false, locking,
then
saving in Excel. However the problem commenced having saved in the VBE,
from
then on couldn't be fixed.

In my development project I already have something similar to Thomas
Ramel's
code (below), it does other things to. But I had removed pending
distribution.

==============

FWIW, an unexpected observation -

The normal size of my old project varied from 1.4mb (no code ever run) to
2.2mb. In both cases just after using Rob Bovey's Codecleaner to strip and
clean, the larger file size if I then do a full compile and save.

But after saving both types of files from the immediate window the
respective file sizes reduced by about 300kb.

Seemed odd so I built a brand new identical file with similar sheets and
dragged modules from the old file to the new. The new saves to the same
lower file sizes respectively.

Me thought - must be some garbage built up in the old worksheets, even
though the project is mainly code and little in the way of worksheet
stuff.
I removed all modules from the old file and looked at file size. Very
small
and just as expected to account for the sheets, about 100kb.

Bearing in mind the old project was freshly stripped and cleaned, I'm
curious as to what that phantom 300kb accounted for. Even more curious as
to
why this 300Kb suddenly disappeared ONLY because I saved from the
immediate
window. Replicates with back-ups of old non-problematic files.

Regards,
Peter T


In a different forum, Thomas Ramel posted some code that he adds to an
addin.
You may like it:

=========================
From Thomas Ramel:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Saved = True Then Exit Sub
If MsgBox("Save changes in Add-In" & Chr(13) & Chr(13) & _
UCase(ThisWorkbook.VBProject.Name) & Chr(13) & Chr(13) & _
"?", vbYesNo) = vbYes Then
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
End If
End Sub

Now every time I changed something in the Code and forgot to save it
manually I get a reminder, wehe I close Excel.


Regards
Thomas Ramel



Peter T wrote:

Hi Dave, thanks for responding. Comments in line -

My bet is that you're opening a different file than you're saving.

No, definately working with the same file. But I'd better not be that
dogmatic (see later).

Make sure that the addin's project is active before saving.

Yes always active, selected in vbe project's panel, then save from
vbe.

And as a workaround (I've never had the trouble you describe), you
can
change
the workbook's .addin property to false. (Select your project, hit
F4
to
see
properties and look for IsAddin.)

I've tried locking & saving with IsAddin True (from vbe) and False
(from
both Excel & vbe)

Then you can go back to excel and save that workbook as an addin
(remember
to
change the filetype) via that interface.

Similar to one step above. But also locked what was an xls with SaveAs
xla
while still a visible workbook. I've also tried copying the file to
different folders, renaming, trying each with different passwords
(only
one
open at a time). Each time it/they re-open unlocked. A password
obviously
exists though no asteriks, after entering the correct pw for that file
I
can
tick the Lock checkbox. Check Fullname each time. Repeat the save, all
to no
avail. Also emptied temporary folders and repeated in new sessions of
Excel
and even Windows.

Another way while in the VBE:
hit ctrl-g (to see the immediate window):
type this and hit enter:
workbooks("youraddinname.xla").save

Well blow me down! that's just worked !!

Bit of a mystery but thank you very much.

Regards,
Peter T

Peter T wrote:

In XL2K, after closing all module windows, I "lock project for
viewing"
and
password protect my addin. Save from the vbe, close XL.

When I reopen XL and my addin, the project is not locked.

I go back into Project properties / Protection and the lock check
box
and
password dialogs are empty. If I now tick the lock checkbox I'm
asked
for my
password (although no asterisks appear in the password textbox's).
Enter
password, everything seems normal. Save, close, reopen and back to
the
same
situation.

The project is "clean" and correctly compiled.

Any ideas how to resolve without rebuilding the addin appreciated.

Regards,
Peter T

--

Dave Peterson

--

Dave Peterson

I've seen changes in filesizes (sometimes minor changes make the filesize go up,
other times, the filesize goes down). I just figured that that's the way excel
works!

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.)

I figure there are only a couple of things that I can do to keep the filesize
down--make sure the lastusedcell is correct on all the sheets and run Rob's
codecleaner). Everything that happens to the workbook after that ain't my fault
<vbg.

Peter T wrote:

"Dave Peterson" wrote in message

I'm glad the workaround worked ok for you.

Not as glad as I am, thanks again!

I've never had a problem saving from the VBE, but I've read a few posts
where
the authors say they avoid it because of problems they've had.

Yes I've also read similar and don't normally save from the VBE. The problem
though couldn't be fixed by saving changing IsAddin to false, locking, then
saving in Excel. However the problem commenced having saved in the VBE, from
then on couldn't be fixed.

In my development project I already have something similar to Thomas Ramel's
code (below), it does other things to. But I had removed pending
distribution.

==============

FWIW, an unexpected observation -

The normal size of my old project varied from 1.4mb (no code ever run) to
2.2mb. In both cases just after using Rob Bovey's Codecleaner to strip and
clean, the larger file size if I then do a full compile and save.

But after saving both types of files from the immediate window the
respective file sizes reduced by about 300kb.

Seemed odd so I built a brand new identical file with similar sheets and
dragged modules from the old file to the new. The new saves to the same
lower file sizes respectively.

Me thought - must be some garbage built up in the old worksheets, even
though the project is mainly code and little in the way of worksheet stuff.
I removed all modules from the old file and looked at file size. Very small
and just as expected to account for the sheets, about 100kb.

Bearing in mind the old project was freshly stripped and cleaned, I'm
curious as to what that phantom 300kb accounted for. Even more curious as to
why this 300Kb suddenly disappeared ONLY because I saved from the immediate
window. Replicates with back-ups of old non-problematic files.

Regards,
Peter T

In a different forum, Thomas Ramel posted some code that he adds to an
addin.
You may like it:

=========================
From Thomas Ramel:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Saved = True Then Exit Sub
If MsgBox("Save changes in Add-In" & Chr(13) & Chr(13) & _
UCase(ThisWorkbook.VBProject.Name) & Chr(13) & Chr(13) & _
"?", vbYesNo) = vbYes Then
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
End If
End Sub

Now every time I changed something in the Code and forgot to save it
manually I get a reminder, wehe I close Excel.


Regards
Thomas Ramel



Peter T wrote:

Hi Dave, thanks for responding. Comments in line -

My bet is that you're opening a different file than you're saving.

No, definately working with the same file. But I'd better not be that
dogmatic (see later).

Make sure that the addin's project is active before saving.

Yes always active, selected in vbe project's panel, then save from vbe.

And as a workaround (I've never had the trouble you describe), you can
change
the workbook's .addin property to false. (Select your project, hit F4
to
see
properties and look for IsAddin.)

I've tried locking & saving with IsAddin True (from vbe) and False (from
both Excel & vbe)

Then you can go back to excel and save that workbook as an addin
(remember
to
change the filetype) via that interface.

Similar to one step above. But also locked what was an xls with SaveAs
xla
while still a visible workbook. I've also tried copying the file to
different folders, renaming, trying each with different passwords (only
one
open at a time). Each time it/they re-open unlocked. A password
obviously
exists though no asteriks, after entering the correct pw for that file I
can
tick the Lock checkbox. Check Fullname each time. Repeat the save, all
to no
avail. Also emptied temporary folders and repeated in new sessions of
Excel
and even Windows.

Another way while in the VBE:
hit ctrl-g (to see the immediate window):
type this and hit enter:
workbooks("youraddinname.xla").save

Well blow me down! that's just worked !!

Bit of a mystery but thank you very much.

Regards,
Peter T

Peter T wrote:

In XL2K, after closing all module windows, I "lock project for
viewing"
and
password protect my addin. Save from the vbe, close XL.

When I reopen XL and my addin, the project is not locked.

I go back into Project properties / Protection and the lock check
box
and
password dialogs are empty. If I now tick the lock checkbox I'm
asked
for my
password (although no asterisks appear in the password textbox's).
Enter
password, everything seems normal. Save, close, reopen and back to
the
same
situation.

The project is "clean" and correctly compiled.

Any ideas how to resolve without rebuilding the addin appreciated.

Regards,
Peter T

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Hi Tom,

You mentioned that you had changed versions of Excel in you testing. I
notice file size changes in different versions of excel. I assume you
have
eliminated this as a possible cause.

I don't think I mentioned I had changed versions of Excel in my testing so
you must be pyschic!

I develop and test in multiple versions but go back and save changes in
XL97. On this occasion I had saved in XL2K's VBE purely for locking, no
intention to further develop this file.

I have only ever noticed small differences in file size when saving this
particular project in different versions. Possibily because whilst there is
some data there are only a handful of worksheet formulas, minimal cell
formats. It is mainly VBA. With all code removed less than 100Kb in all
versions.

another thought is if you are comparing protected to unprotected.

In the particular case leading to this post and the lock problem, the file
had been cleaned & saved in XL97, then a copy locked and saved in XL2K.
Difference in file size was trivial.

Thanks for your input,
Peter T

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

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

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

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

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

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