Someone please set me straight or confirm my understanding.

If I declare a variable as Public in any standard module, in the
Declarations section, then that variable can be accessed by any subroutine
in any standard module, AND any userform module. Right? What about the
ThisWorkbook module or individual sheet modules?

Concerning custom user forms: Loading the form into memory executes the
Initialize event (not the Activate event) of the form, correct? If so, then
this might be a good place to indicate to whatever subroutine that might be
interested that the form is now in memory. And unloading the form resets
whatever was done during Initialization, right?

Is there a Forms collection by which I can cycle through all loaded forms?

Thanks!

-gk-

"TBA" wrote in message
...
Someone please set me straight or confirm my understanding.

If I declare a variable as Public in any standard module, in the
Declarations section, then that variable can be accessed by any subroutine
in any standard module, AND any userform module. Right? What about the
ThisWorkbook module or individual sheet modules?

Public variables in a standard module have global scope, so they can be
accessed from any other standard module, and any class modules, including
userforms, ThisWorkbook, and worksheet modules.


Concerning custom user forms: Loading the form into memory executes the
Initialize event (not the Activate event) of the form, correct? If so,
then
this might be a good place to indicate to whatever subroutine that might
be
interested that the form is now in memory. And unloading the form resets
whatever was done during Initialization, right?

Initialize is invoked when a form is loaded, activate when it is shown. But
remember. if you call form.Show before loading, the form will automatically
load, so it illinitialize then activate. Similarly, if yo load when it is
already loaded, nothing happens. As you say, un load clears all memory
grabbed by the form, and frees any objects.


Is there a Forms collection by which I can cycle through all loaded forms?

Not that I am aware of, but you could create your own.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...

"TBA" wrote in message
...
Someone please set me straight or confirm my understanding.

If I declare a variable as Public in any standard module, in the
Declarations section, then that variable can be accessed by any
subroutine
in any standard module, AND any userform module. Right? What about the
ThisWorkbook module or individual sheet modules?

Public variables in a standard module have global scope, so they can be
accessed from any other standard module, and any class modules, including
userforms, ThisWorkbook, and worksheet modules.


Concerning custom user forms: Loading the form into memory executes the
Initialize event (not the Activate event) of the form, correct? If so,
then
this might be a good place to indicate to whatever subroutine that might
be
interested that the form is now in memory. And unloading the form
resets
whatever was done during Initialization, right?

Initialize is invoked when a form is loaded, activate when it is shown.
But
remember. if you call form.Show before loading, the form will
automatically
load, so it illinitialize then activate. Similarly, if yo load when it is
already loaded, nothing happens. As you say, un load clears all memory
grabbed by the form, and frees any objects.


Is there a Forms collection by which I can cycle through all loaded
forms?

Not that I am aware of, but you could create your own.

--

HTH

Bob Phillips

Thanks Bob!

One small follow up question, though.

When do these variables become "legit"? For example when the Excel file is
opened are all Modules evaluated and therefore the Public variables become
immediately accessible? For example, can my first Call, say in the
ThisWorkbook Open event, call a sub in a module that has no variables
declared, and instead those variables are declared Public in seperate module
that hasn't been called or used yet?

-gk-

One small follow up question, though.

When do these variables become "legit"? For example when the Excel file
is
opened are all Modules evaluated and therefore the Public variables become
immediately accessible? For example, can my first Call, say in the
ThisWorkbook Open event, call a sub in a module that has no variables
declared, and instead those variables are declared Public in seperate
module
that hasn't been called or used yet?

-gk-



Public variables declared in the module declaratives section are available
as soon as the workbook is opened. Thus the Workbook_Open event can
legitimately address these variables, although of course they will all be an
initialised state. You can, and I usually do, have a module where all your
globals are declared, and in your terminology, this module would never be
called or used, but it is used via its variables.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Very interesting. I've always considered public variable to be
unnecessary (I've never used one yet) but your posts confirm for me
that their use is not intuitive either!

That reminds me: J.E. McGimpsey (ping?) posted a while back he/she
never uses private (module level) variables, accept under duress,
which prompted me to ask: how do you maintain property values in
classes? I'd be interested in a reply.

--

"TBA" wrote in message ...
"Bob Phillips" wrote in message
...

"TBA" wrote in message
...
Someone please set me straight or confirm my understanding.

If I declare a variable as Public in any standard module, in the
Declarations section, then that variable can be accessed by any
subroutine
in any standard module, AND any userform module. Right? What about the
ThisWorkbook module or individual sheet modules?

Public variables in a standard module have global scope, so they can be
accessed from any other standard module, and any class modules, including
userforms, ThisWorkbook, and worksheet modules.


Concerning custom user forms: Loading the form into memory executes the
Initialize event (not the Activate event) of the form, correct? If so,
then
this might be a good place to indicate to whatever subroutine that might
be
interested that the form is now in memory. And unloading the form
resets
whatever was done during Initialization, right?

Initialize is invoked when a form is loaded, activate when it is shown.
But
remember. if you call form.Show before loading, the form will
automatically
load, so it illinitialize then activate. Similarly, if yo load when it is
already loaded, nothing happens. As you say, un load clears all memory
grabbed by the form, and frees any objects.


Is there a Forms collection by which I can cycle through all loaded
forms?

Not that I am aware of, but you could create your own.

--

HTH

Bob Phillips

Thanks Bob!

One small follow up question, though.

When do these variables become "legit"? For example when the Excel file is
opened are all Modules evaluated and therefore the Public variables become
immediately accessible? For example, can my first Call, say in the
ThisWorkbook Open event, call a sub in a module that has no variables
declared, and instead those variables are declared Public in seperate module
that hasn't been called or used yet?

-gk-