Best place to declare variables

[Taras]

Where is the best place to declare variables, in terms of
memory utilization, etc. if the same variable names are
used in different modules, although their values do not
(always) need to be passed between modules.

ex:
Module A is used to summarize data on Sheet1 of a
Workbook into Sheet2
Module B is used to summarize data on Sheet3 of a
Workbook into Sheet4
There are no common functions/subs between Module A and
Module B (Module C has functions/subs called by both
Module A and B - but that could be a red herring)

In Module A and Module B I use the same variable names -
just for consistency.

Is it more efficient to declare the variables with DIM in
Module A & Module B or to declare them as Public
elsewhere?

 

[Bill Manville]

Is it more efficient to declare the variables with DIM in
Module A & Module B or to declare them as Public
elsewhere?

Module-level variables (whether Public or not) have space permanently
allocated. So having 2 sets will consume twice as much space.

However, I would not advocate using Public variables in case you end up
changing the sequence in which things are done and get conflicting
usage of the same variable between procedures in different modules.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[Taras]

Bill,
Thanks for your input.

I think that as long as I ensure I initialize the
variable in the first sub that I use it in, I should be
OK.

What is your opinion on declaring the variables in a sub
that is executed when the spreadsheet is opened so that
they would be available to any macro following.

The sheet is rarely used without running some macro.
Taras

 

[Howard Kaikow]

Variables should be declared with the narrowest scope possible.

For example, if you have variables i and j, commonly used for indexing, in
many procedures, each procedure should declared its own i and j.

 

[J.E. McGimpsey]

I know hardly anything about XL's memory utilization, but I'll take
liberty with your use of "etc." in your question. It's personal
opinion only:

Variable memory is no longer a concern for me in any but the most
extreme circumstances. Nor is speed (of accessing variables, at
least). What I focus on is maintenance - it's cheaper for you to add
a GB of RAM than to have me spend an extra few hours trying to
decipher and debug your code.

I use three rules in commercial work:

1) All variables are local to their procedure. Variables must be
passed between procedures as arguments. There are no exceptions to
this rule.

2) In extreme circumstances, or if I can't think of any other way to
do it and the deadline for delivery is within 12 hours, Rule 1 may
be violated and a global variable used. In this case, procedures
sharing the global must be grouped in the same module and globals
must be private to that module. There must be no duplication of
global variable names between modules. Procedure variable names must
not override globals. There are *absolutely* no exceptions to this
rule.

3) At gunpoint, or when forced by customer specifications, Rule 2
may be violated, but *all* globals must be public and declared in a
separate module. Global variable names are perforce not duplicated.

These rules assure me that when I go to modify a client's code six
months or a year from now, I (or another developer) won't spend any
more time than necessary chasing variable names and scope.

 

[Bill Manville]

What is your opinion on declaring the variables in a sub
that is executed when the spreadsheet is opened so that
they would be available to any macro following.

If you really mean *declaring* the variables, it won't work.
Variables declared within a procedure are available only within that
procedure.

Because module-level variables lose their values when the project is
recompiled (e.g. after you click the Reset button in the VB editor or
the End button in a VB error message, or execute an End statement etc),
I try to avoid relying on their values between one macro run and the
next. If I need to have such module-level variables I will use a
procedure to set them all, including a Boolean ValidVars:

Sub CheckVars()
If ValidVars Then Exit Sub
' set all module level variables
ValidVars = True
End Sub

and I call CheckVars from the start of each procedure that can be run
as the result of a user action.

Hope this helps

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[onedaywhen]

Re: your three rules.

Generally, I find them very honorable. I follow (1) where possible and
have NEVER yet resorted to (3), however I'm left wondering about (2):
how do your classes maintain their property values if you don't use
private module-level variables?

 

[Dave Peterson]

Reminds me of:

In 1942, Isaac Asimov came with three laws of Robotics.

The Three Laws of Robotics are:

1. A robot may not injure a human being, or, through inaction, allow a human
being to come to harm.
2. A robot must obey the orders given it by human beings except where such
orders would conflict with the First Law.
3. A robot must protect its own existence as long as such protection does not
conflict with the First or Second Law.

Subsequently a fourth law was added, the the "Zeroth Law", which is:
0. A robot may not injure humanity or, through inaction, allow humanity to
come to harm.