public variable declaration, memory efficient?

[slarbie]

I have what's turned into a fairly massive spreadsheet project (file size
approaching (7 MG), getting a bit overloaded with heavily programmed forms
and intricate functionalities. Recently have been running into messages "not
enough system resources to display" when I'm trying to debug. I have been
routinely exporting the forms and modules and reimporting them after making
any substantial changes, so the vba compiler can clean itself up.

So, to finally get to my questions! Is it excessive of me to have 20
different variables declared as Public? And is it more or less efficient to
declare them as Public than within each procedure as they're needed?

Any other suggestions on smartly conserving system resources? Does it help
to reduce the length of individual procedures? i.e. break them into smaller
tasks? Does it help to spread them out accross more modules?

Any advice you have will be most appreciated!

 

[JLatham]

Without knowing more about the requirements of the application, I can only
make some general statements.

#1 - Only use the Public variables where you need them to be referenced in
more than one code module, that is - to pass information between modules. I
generally would not consider 20 Public variables as excessive in a large
project.

#2 - Each code module takes some overhead just to define its existance. So
adding more modules simply increases system resource usage. Now, having said
that, you may be tempted to toss as many processes as possible into one or
two large modules. That may create maintenance and code management problems
for you, so be careful of that aspect of it.

One thing you might consider for your user forms, if you're not already
doing it, is to UNLOAD them when you're done with them rather than just
..HIDEing them. Of course this has the side effect of deleting any entries
you made into them which in turn makes those entries unavailable for use in
other code after you've UNLOADed them unless you move the entered data into
variables or onto worksheets.

 

[slarbie]

It's good to know about the "unloading". I did have the forms doing that
already, but hadn't really thought about it in terms of memory usage. I
guess it just makes sense. I solved my immediate problem by changing the
process so that two of the bigger forms are not open at the same time.

And the need to pass variables from one procedure to another was part of
using that many publics, but also for the variables used over and over again.
But good to know the number isn't too extraordinary.

Thanks so much for taking the time to answer those for me!