Public Variable Question

[donwb]

Win XP Excel 2003

I believe that the value assigned to a Public Variable will survive
from module to module during Runtime.
If a Userform is made to Show during Runtime,
then remains static awaiting user input, is the value
that was assigned to the Public Variable still available,
before the Userform is Unloaded.
donwb

 

[JLGWhiz]

Yes.

 

[Dave Peterson]

It should be unchanged until you change it.

But there are things that you can do to lose the value.

Your code could have an "End" statement in it--not "End Function", not "End
Sub", not "End Select", just a plain old End statement:

if somevar = false then
end
end if

This is a quick, but horrible way to exit the sub/function.

You could Reset in the VBE (Run|Reset or hit the reset button) when you're
testing your code.

I like this technique:

Option Explicit
Public VarsAreDefined as boolean
Public SomeVar1 as string
public SomeVar2 as Variant

....

Then have a procedure that sets the variables:

Option Explicit
Sub DefineMyVars()
VarsAreDefined = true
SomeVar1 = "somestring"
SomeVar2 = thisworkbook.worksheets("Sheet1").range("A1").value
end sub

Then before you try to use any of those variables in any of your routines, you
can do:

if varsaredefined then
'keep going
else
call DefineMyVars
end if

 

[donwb]

Many thanks for the input Gents

I'm glad the bottom line to my question is yes,
because I didn't want to embark on wild goose chase.

I'll keep in mind your suggestions Dave

grateful
donwb

 

[Patrick Molloy]

yes.

If the idea is to maintain data from a userform, then don't unload the form,
just hide it.