Using Excel 2003.
Recently I posted two questions. One about a problem saving an add-in
(15/12/03) and the other one about a popup commandbar needing a repaint
(17/12/03).
I have now solved both problems and I thought it might be worth to pass this
information on the group.
I understand that there is a limit to the size of code Modules in that if
you go above it you may get unexpected problems. Can't remember now what the
size in bytes is, but I now it roughly equates to about 2000 lines of code.
I understand that it is not sure if the same applies to code in UserForms. I
think Stephen Bullen commented on this.
As I had one form with more than 5000 lines of code I thought it might be
worth it to reduce this to less than 2000 lines by shifting code to Modules.
After having done this I solved both problems as mentioned above.
So my conclusion is that it is advisable to keep any module, probably also
Class Modules, below 2000 lines of code.
Would be interested to hear any comments on this from the Excel experts.

RBS

The limit cited in the past is 64K. (if you export the module, the
resulting file size should be less than 64K). This is a guideline and the
caution is based on experience as there is no published official source that
cites this (that I have heard of or seen referenced).

--
Regards,
Tom Ogilvy

RB Smissaert wrote in message
...
Using Excel 2003.
Recently I posted two questions. One about a problem saving an add-in
(15/12/03) and the other one about a popup commandbar needing a repaint
(17/12/03).
I have now solved both problems and I thought it might be worth to pass
this
information on the group.
I understand that there is a limit to the size of code Modules in that if
you go above it you may get unexpected problems. Can't remember now what
the
size in bytes is, but I now it roughly equates to about 2000 lines of
code.
I understand that it is not sure if the same applies to code in UserForms.
I
think Stephen Bullen commented on this.
As I had one form with more than 5000 lines of code I thought it might be
worth it to reduce this to less than 2000 lines by shifting code to
Modules.
After having done this I solved both problems as mentioned above.
So my conclusion is that it is advisable to keep any module, probably also
Class Modules, below 2000 lines of code.
Would be interested to hear any comments on this from the Excel experts.

RBS

Thanks. How about the size of Userforms?

RBS

"Tom Ogilvy" wrote in message
...
The limit cited in the past is 64K. (if you export the module, the
resulting file size should be less than 64K). This is a guideline and
the
caution is based on experience as there is no published official source
that
cites this (that I have heard of or seen referenced).

--
Regards,
Tom Ogilvy

RB Smissaert wrote in message
...
Using Excel 2003.
Recently I posted two questions. One about a problem saving an add-in
(15/12/03) and the other one about a popup commandbar needing a repaint
(17/12/03).
I have now solved both problems and I thought it might be worth to pass
this
information on the group.
I understand that there is a limit to the size of code Modules in that
if
you go above it you may get unexpected problems. Can't remember now what
the
size in bytes is, but I now it roughly equates to about 2000 lines of
code.
I understand that it is not sure if the same applies to code in
UserForms.
I
think Stephen Bullen commented on this.
As I had one form with more than 5000 lines of code I thought it might
be
worth it to reduce this to less than 2000 lines by shifting code to
Modules.
After having done this I solved both problems as mentioned above.
So my conclusion is that it is advisable to keep any module, probably
also
Class Modules, below 2000 lines of code.
Would be interested to hear any comments on this from the Excel experts.

RBS

Not sure what you mean by the size of Useforms - A module is a module - so
if you mean Userform module, the the recommendation would be 64K limit.

If you mean the size of other files associated with Userforms, I don't think
you have options there - if you run into a case where the size of the
Userform itself causes problems, you will have to redesign your approach
(use two forms for example).

--
Regards,
Tom Ogilvy

RB Smissaert wrote in message
...
Thanks. How about the size of Userforms?

RBS

"Tom Ogilvy" wrote in message
...
The limit cited in the past is 64K. (if you export the module, the
resulting file size should be less than 64K). This is a guideline and
the
caution is based on experience as there is no published official source
that
cites this (that I have heard of or seen referenced).

--
Regards,
Tom Ogilvy

RB Smissaert wrote in message
...
Using Excel 2003.
Recently I posted two questions. One about a problem saving an add-in
(15/12/03) and the other one about a popup commandbar needing a
repaint
(17/12/03).
I have now solved both problems and I thought it might be worth to
pass
this
information on the group.
I understand that there is a limit to the size of code Modules in that
if
you go above it you may get unexpected problems. Can't remember now
what
the
size in bytes is, but I now it roughly equates to about 2000 lines of
code.
I understand that it is not sure if the same applies to code in
UserForms.
I
think Stephen Bullen commented on this.
As I had one form with more than 5000 lines of code I thought it might
be
worth it to reduce this to less than 2000 lines by shifting code to
Modules.
After having done this I solved both problems as mentioned above.
So my conclusion is that it is advisable to keep any module, probably
also
Class Modules, below 2000 lines of code.
Would be interested to hear any comments on this from the Excel
experts.

RBS

Thought I had fixed the save problem, but I had the same problem again. All
the modules are small enough, so perhaps I have to look at the other files
associated with the biggest UserForm.
The only way to overcome the problem of the saving of the .xla file getting
stuck is saving all the modules to text before a save, Ctrl + Alt + Del when
in a stuck save, go back to the previous .xla file that did save, reimport
the code from the text files and save again. All this is a big nuisance as
there seems to be no way to predict when the problem will appear.
Would there be any limit to the overall size of the .xla file? Mine is now
1.65 Mb.

RBS


"Tom Ogilvy" wrote in message
...
Not sure what you mean by the size of Useforms - A module is a module - so
if you mean Userform module, the the recommendation would be 64K limit.

If you mean the size of other files associated with Userforms, I don't
think
you have options there - if you run into a case where the size of the
Userform itself causes problems, you will have to redesign your approach
(use two forms for example).

--
Regards,
Tom Ogilvy

RB Smissaert wrote in message
...
Thanks. How about the size of Userforms?

RBS

"Tom Ogilvy" wrote in message
...
The limit cited in the past is 64K. (if you export the module, the
resulting file size should be less than 64K). This is a guideline
and
the
caution is based on experience as there is no published official
source
that
cites this (that I have heard of or seen referenced).

--
Regards,
Tom Ogilvy

RB Smissaert wrote in message
...
Using Excel 2003.
Recently I posted two questions. One about a problem saving an
add-in
(15/12/03) and the other one about a popup commandbar needing a
repaint
(17/12/03).
I have now solved both problems and I thought it might be worth to
pass
this
information on the group.
I understand that there is a limit to the size of code Modules in
that
if
you go above it you may get unexpected problems. Can't remember now
what
the
size in bytes is, but I now it roughly equates to about 2000 lines
of
code.
I understand that it is not sure if the same applies to code in
UserForms.
I
think Stephen Bullen commented on this.
As I had one form with more than 5000 lines of code I thought it
might
be
worth it to reduce this to less than 2000 lines by shifting code to
Modules.
After having done this I solved both problems as mentioned above.
So my conclusion is that it is advisable to keep any module,
probably
also
Class Modules, below 2000 lines of code.
Would be interested to hear any comments on this from the Excel
experts.

RBS

For a workbook, 1.65 MB would not be significant, but I can't say for an
XLA. I doubt that the size of the xla is the problem (but it isn't
something I have explored). In most cases, an xla is designed so it
doesn't need to be saved. Maybe you want to write a textfile or something
to store you changing values so you don't need to save you XLA.

--
Regards,
Tom Ogilvy

RB Smissaert wrote in message
...
Thought I had fixed the save problem, but I had the same problem again.
All
the modules are small enough, so perhaps I have to look at the other files
associated with the biggest UserForm.
The only way to overcome the problem of the saving of the .xla file
getting
stuck is saving all the modules to text before a save, Ctrl + Alt + Del
when
in a stuck save, go back to the previous .xla file that did save, reimport
the code from the text files and save again. All this is a big nuisance as
there seems to be no way to predict when the problem will appear.
Would there be any limit to the overall size of the .xla file? Mine is now
1.65 Mb.

RBS


"Tom Ogilvy" wrote in message
...
Not sure what you mean by the size of Useforms - A module is a module -
so
if you mean Userform module, the the recommendation would be 64K limit.

If you mean the size of other files associated with Userforms, I don't
think
you have options there - if you run into a case where the size of the
Userform itself causes problems, you will have to redesign your approach
(use two forms for example).

--
Regards,
Tom Ogilvy

RB Smissaert wrote in message
...
Thanks. How about the size of Userforms?

RBS

"Tom Ogilvy" wrote in message
...
The limit cited in the past is 64K. (if you export the module, the
resulting file size should be less than 64K). This is a guideline
and
the
caution is based on experience as there is no published official
source
that
cites this (that I have heard of or seen referenced).

--
Regards,
Tom Ogilvy

RB Smissaert wrote in message
...
Using Excel 2003.
Recently I posted two questions. One about a problem saving an
add-in
(15/12/03) and the other one about a popup commandbar needing a
repaint
(17/12/03).
I have now solved both problems and I thought it might be worth to
pass
this
information on the group.
I understand that there is a limit to the size of code Modules in
that
if
you go above it you may get unexpected problems. Can't remember
now
what
the
size in bytes is, but I now it roughly equates to about 2000 lines
of
code.
I understand that it is not sure if the same applies to code in
UserForms.
I
think Stephen Bullen commented on this.
As I had one form with more than 5000 lines of code I thought it
might
be
worth it to reduce this to less than 2000 lines by shifting code
to
Modules.
After having done this I solved both problems as mentioned above.
So my conclusion is that it is advisable to keep any module,
probably
also
Class Modules, below 2000 lines of code.
Would be interested to hear any comments on this from the Excel
experts.

RBS

My .xla file has a lot of code and this has to be saved.

RBS

"Tom Ogilvy" wrote in message
...
For a workbook, 1.65 MB would not be significant, but I can't say for an
XLA. I doubt that the size of the xla is the problem (but it isn't
something I have explored). In most cases, an xla is designed so it
doesn't need to be saved. Maybe you want to write a textfile or something
to store you changing values so you don't need to save you XLA.

--
Regards,
Tom Ogilvy

RB Smissaert wrote in message
...
Thought I had fixed the save problem, but I had the same problem again.
All
the modules are small enough, so perhaps I have to look at the other
files
associated with the biggest UserForm.
The only way to overcome the problem of the saving of the .xla file
getting
stuck is saving all the modules to text before a save, Ctrl + Alt + Del
when
in a stuck save, go back to the previous .xla file that did save,
reimport
the code from the text files and save again. All this is a big nuisance
as
there seems to be no way to predict when the problem will appear.
Would there be any limit to the overall size of the .xla file? Mine is
now
1.65 Mb.

RBS


"Tom Ogilvy" wrote in message
...
Not sure what you mean by the size of Useforms - A module is a
module -
so
if you mean Userform module, the the recommendation would be 64K
limit.

If you mean the size of other files associated with Userforms, I don't
think
you have options there - if you run into a case where the size of the
Userform itself causes problems, you will have to redesign your
approach
(use two forms for example).

--
Regards,
Tom Ogilvy

RB Smissaert wrote in message
...
Thanks. How about the size of Userforms?

RBS

"Tom Ogilvy" wrote in message
...
The limit cited in the past is 64K. (if you export the module,
the
resulting file size should be less than 64K). This is a
guideline
and
the
caution is based on experience as there is no published official
source
that
cites this (that I have heard of or seen referenced).

--
Regards,
Tom Ogilvy

RB Smissaert wrote in message
...
Using Excel 2003.
Recently I posted two questions. One about a problem saving an
add-in
(15/12/03) and the other one about a popup commandbar needing a
repaint
(17/12/03).
I have now solved both problems and I thought it might be worth
to
pass
this
information on the group.
I understand that there is a limit to the size of code Modules
in
that
if
you go above it you may get unexpected problems. Can't remember
now
what
the
size in bytes is, but I now it roughly equates to about 2000
lines
of
code.
I understand that it is not sure if the same applies to code in
UserForms.
I
think Stephen Bullen commented on this.
As I had one form with more than 5000 lines of code I thought it
might
be
worth it to reduce this to less than 2000 lines by shifting code
to
Modules.
After having done this I solved both problems as mentioned
above.
So my conclusion is that it is advisable to keep any module,
probably
also
Class Modules, below 2000 lines of code.
Would be interested to hear any comments on this from the Excel
experts.

RBS

1.65 MB is not that large for an XLA.

But make sure you use Rob Boveys code cleaner (www.AppsPro.com) on a regular
basis, and also delete all your temp files: forms create a lot of temp files
which can build up (particularly if Excel crashes or has to be cancelled)
and can also get corrupted.

The 64 MB limit for a module was a real problem with Excel 5, and although I
have not personally met any problems caused by module size since moving all
my development work to Excel 97/2000 there have been reports of problems. On
the other hand I have also had modules over 128Mb without any problems.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"RB Smissaert" wrote in message
...
My .xla file has a lot of code and this has to be saved.

RBS

"Tom Ogilvy" wrote in message
...
For a workbook, 1.65 MB would not be significant, but I can't say for an
XLA. I doubt that the size of the xla is the problem (but it isn't
something I have explored). In most cases, an xla is designed so it
doesn't need to be saved. Maybe you want to write a textfile or
something
to store you changing values so you don't need to save you XLA.

--
Regards,
Tom Ogilvy

RB Smissaert wrote in message
...
Thought I had fixed the save problem, but I had the same problem
again.
All
the modules are small enough, so perhaps I have to look at the other
files
associated with the biggest UserForm.
The only way to overcome the problem of the saving of the .xla file
getting
stuck is saving all the modules to text before a save, Ctrl + Alt +
Del
when
in a stuck save, go back to the previous .xla file that did save,
reimport
the code from the text files and save again. All this is a big
nuisance
as
there seems to be no way to predict when the problem will appear.
Would there be any limit to the overall size of the .xla file? Mine is
now
1.65 Mb.

RBS


"Tom Ogilvy" wrote in message
...
Not sure what you mean by the size of Useforms - A module is a
module -
so
if you mean Userform module, the the recommendation would be 64K
limit.

If you mean the size of other files associated with Userforms, I
don't
think
you have options there - if you run into a case where the size of
the
Userform itself causes problems, you will have to redesign your
approach
(use two forms for example).

--
Regards,
Tom Ogilvy

RB Smissaert wrote in message
...
Thanks. How about the size of Userforms?

RBS

"Tom Ogilvy" wrote in message
...
The limit cited in the past is 64K. (if you export the module,
the
resulting file size should be less than 64K). This is a
guideline
and
the
caution is based on experience as there is no published official
source
that
cites this (that I have heard of or seen referenced).

--
Regards,
Tom Ogilvy

RB Smissaert wrote in message
...
Using Excel 2003.
Recently I posted two questions. One about a problem saving an
add-in
(15/12/03) and the other one about a popup commandbar needing
a
repaint
(17/12/03).
I have now solved both problems and I thought it might be
worth
to
pass
this
information on the group.
I understand that there is a limit to the size of code Modules
in
that
if
you go above it you may get unexpected problems. Can't
remember
now
what
the
size in bytes is, but I now it roughly equates to about 2000
lines
of
code.
I understand that it is not sure if the same applies to code
in
UserForms.
I
think Stephen Bullen commented on this.
As I had one form with more than 5000 lines of code I thought
it
might
be
worth it to reduce this to less than 2000 lines by shifting
code
to
Modules.
After having done this I solved both problems as mentioned
above.
So my conclusion is that it is advisable to keep any module,
probably
also
Class Modules, below 2000 lines of code.
Would be interested to hear any comments on this from the
Excel
experts.

RBS