Defining public/global variables in more than one place

Using Excel 2003 (hopefully will persuade my company to allow us to upgrade our excel reports to 2010 at some point!)

I am trying to standardise the code in our executables to make coding/maintenance easier and have come up with the following idea:

1. Our Excel reports have a simple Auto_Open module that will be the first piece of code in every report we have and this has 1 public variable plus one actual command to call another module sub called Main_Processing. This other module will contain the report relevant logic.

2. This public variable defined in the Auto_Open module is a string defining the environment the code runs in such as Test, Development and thus can be altered simply for each environment. Lower level code uses this to pick up data files from the correct place for that environment.

3. This is the part my question relates to. Can the Main_Processing module also have public variables that are used in lower level sub routines and functions? So far, I have only defined global variables in one place at the top level, so the question is really about scope of lower level variables.

Cheers

PLEASE IGNORE

Did what I should have done before posting and tried it out.

All works fine when defined as Public at each level

I don't follow what you mean by "lower level sub routines", however are
variables can maintain scope (even while code is not running) at three
levels:

Global, visible throughout the project, declare at the top of a normal
module:
Public gsMyString As String

Module, visible throughout the module only, declare at the top of the
module:
Private msMyString as String

Procedure, visible only within a single routine, declare within the routine:
Static sMyString As String

Use of the End statement or anything while debugging that breaks and
prevents completion of code will reset all variables, something to keep in
mind

It's a matter of style but avoid use of such variables as "a convenience".
Rule of thumb, if the variable is not required to be stored between
subsequently running the code, pass variable as required between routines.

Regards,
Peter T


"stainless" > wrote in message
...
Using Excel 2003 (hopefully will persuade my company to allow us to upgrade
our excel reports to 2010 at some point!)

I am trying to standardise the code in our executables to make
coding/maintenance easier and have come up with the following idea:

1. Our Excel reports have a simple Auto_Open module that will be the first
piece of code in every report we have and this has 1 public variable plus
one actual command to call another module sub called Main_Processing. This
other module will contain the report relevant logic.

2. This public variable defined in the Auto_Open module is a string defining
the environment the code runs in such as Test, Development and thus can be
altered simply for each environment. Lower level code uses this to pick up
data files from the correct place for that environment.

3. This is the part my question relates to. Can the Main_Processing module
also have public variables that are used in lower level sub routines and
functions? So far, I have only defined global variables in one place at the
top level, so the question is really about scope of lower level variables.

Cheers