MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Variable scope & Lifetime limits causing trouble

  Asked By: Anpu    Date: Mar 18    Category: MS Office    Views: 2437

Perhaps one of you ran into and knows of a subtile "gotcha" in this
regard. I hope I don't get tongue tied in terminology here. I'm
sure the code is way too complex to paste here.

Short version:
A complete array [ three dimensions 108 elements total), gets nulled
out after filling it and I don't seem to be doing it. I feel that
while I define the array to have a scope for the whole project, VBA
is nulling it out - perhaps because it is not valid to assign values
in a form then use the values outside the form without some esoteric
enabler. { not too hard to find something esoertic with me at the
VBA controls}.

Long Detail:
I have a String array [three dimensions: ArrayName(9,4,3)] that keeps
getting nulled out... as in, empty entries/elements [= ""]. I define
the array in a Public "Dim" in one of my "regular" modules [module
level] (which resides in the "Modules" area of the VBA explorer).

It gets filled up with values in a "preferences" Form (residing
in the "Forms" area of the VBA Explorer). I fill the array elements
(from text boxes in the Form) before Hiding the form -- this code is
_IN_ the Form code. In the Form code, before Hiding the form, if I
Call a Sub (which resides in a regular module) that then Debug.Prints
the array elements, it shows that it is loaded A-OK. Then the
Form.Hide works and I am looking @ my spreadsheet.
Now hit a button or checkbox on my sheet which fires a Sub that
uses the data in the array and it is all null.
I tried placing the array's Public "Dim" in various (one or more)
modules/forms. (it can be in more than two places and not cause an
ambiguity error depending where you put it). Some places seem to
cause the 'nulling' more frequently than others.
Where I have it now (regular Module), it seems to not null-out as
much. I also use an Immediate "Print ArrayName(0,0,0)" to test
things immediately after hiding the Form and it is still full.
Early-on (with it defined in a regular Module) the immediate print
showed that it had been nulled. I now have that working, but hitting
the button/check box still results in a nulled out array. I re-read
4-5 pages from MSDN all about variable scope, lifetime, etc. and
AFAIK I have it 'global' and it *should* stay put between Hiding the
Form and executing the Sub in the Module, but alas....

I put a Watch on one of the array elements, but it does not show a re-
assignment (assuming I did this correctly) , so it appears that I am
not inadvertently 86'ing it in some fat-fingered code.

To mitigate:
Right now, every time before I use the array, I test one element for
a null and reload it (in a Sub - the same Sub that the Form uses to
load it) if it is null, but think it should stay filled untill I tell
it or reset the macro editor/enviromrnt or have an error, no??

I sprinkled Debug.Prints all over and even did a Sub to Debug.Print
the whole array and sprinkle Calls to it all over - including in the
Immediate window. (it shows a blank array if placed first in the
Module where it's used) I usually manage to fix the self-inflicted
problems I so efficiently create, but ..... . . . .



9 Answers Found

Answer #1    Answered By: Angel Watkins     Answered On: Mar 18

First, make sure you don't have any typo's in your variable
Using "Option Explicit" and declaring every variable, the
will give you an error if you accidentally make such a typo. Then,
make sure you declare the variable  as Public in only one place or
you can end up with the different variables with the same name, but
different scope.

The example below seems to allow the user form to change the array
every time sub One() is called without loosing the data.

Public MyArray(1 To 10) As String
Sub One()
End Sub
Sub Two()
End Sub

UserForm1 Module:
Private Sub CommandButton1_Click()
temp = MyArray(1)
MyArray(1) = TextBox1.Value
Call Two
End Sub

Answer #2    Answered By: Burkett Bernard     Answered On: Mar 18

Do you mean the WHOLE SHEET'n macros? It shows as 302K!

> > You can't define a public Dim. ...
> > use Public at the top of the module.
Was trying to be succinct: That's what I meant by [Public "Dim"].
The "Dim" is implied in a Public Declaration at the Module Level.
It's like the segment below...

I was thinking there may be some subtile thing with variable  scope
between Forms, Modules and Sheet code  (code in the Sheet1, Sheet2

> ... typo's in your variable names.

I usually type in lower case and when it snaps to upper, figure I
got it right. EXAMPLE NAME = ModeNFilt() However, this wouldn't
show up a "double Declare" if they're both spelled the same.

> Using "Option Explicit" *** YEP! Already.

> ...declare the variable as Public in only one place or
> ... different variables with ... different scope.

Allowing "double Declares" seems to fly in the face of the def.
of "Public". From all the stuff on scope  & lifetime @ MSDN, this
_is_ how I do understand it. Having one Public declaration at one
Module level should make it 100% Global for the whole Project...DONE!
Being allowed to have two Public declarations in two different
Module levels (in the Declarations section) would mess things up, and
one would think I should cause an Ambiguity error....
Perhaps I still have it declared twice and don't know it... When
debugging, I *DID intentionally double declare and got some versions
to run W/O an Ambiguity or compile error (some also DID error)

I better carefully do a Find across the Project...

> The example below seems to allow the user form to change the array
> every time sub One() is called without loosing the data.
> Brad

Only diff I see is that I do the Hide *IN* the Form code, and
after hiding, see the nulled array  in "Sub Two" like this. :

> Module1:
> Public MyArray(1 To 10) As String ' YEP!

> Sub One() ' Fires as the result of a Button1_Click
> UserForm1.Show
> End Sub

> Sub Two() ' Fires as the result of a different Button2_Click
Debug.Print "|"; MyArray(1); "|" ' Prints the Nulls >> ||
' I do the thing with the "|" to show when
' a number has the extra "sign" space, or
' contains a space / non-printing char.
> End Sub

> UserForm1 Module:
> Private Sub CommandButton1_Click()
> ' Don't know why you did this>> temp = MyArray(1)
> MyArray(1) = TextBox1.Value ' *** Yep!
Debug.Print MyArray(1) ' Prints the box.value A-OK
> UserForm1.Hide
> End Sub
*** Now click to Call Two...

Before I send the sheet (it's big) I better scrub the declarations
throughput the Project.

Answer #3    Answered By: Perdita Lopez     Answered On: Mar 18

Could it poss be a ByVal/ByRef problem do you think?

Answer #4    Answered By: Faith Hughes     Answered On: Mar 18


but DON'T "Before I send the sheet (it's big) I better scrub the
declarations throughput the Project." because I'll be looking at a
different problem.

Answer #5    Answered By: Dinh Tran     Answered On: Mar 18

Send it to me directly less any private stuff? At the moment I don't
see why it shouldn't work either.

Answer #6    Answered By: Ann Evans     Answered On: Mar 18

without looking at it too closely... just reading it...
You can't define a public Dim. If you want it global you have to use Public
at the top of the module.

I may be totally wrong and am looking at it now.

Answer #7    Answered By: Dan Romero     Answered On: Mar 18

From the "fool me twice" department...

Sorry for the false alarm. I should'a checked this.

Looks like an OS problem folks. Works fine on XP -- has stated
problem on NT. The machine I have open & free access to off hrs. is
NT, so I do my primary development on it...

Answer #8    Answered By: Clint Garcia     Answered On: Mar 18

If you have more detail I'd be interested.

telling that you do your "primary developement" at home outa work

Answer #9    Answered By: Wilfred Young     Answered On: Mar 18

Work changed from NT to XP...I have free access to an older NT lap-
top and can take it anywhere for my home projects (which 90% of my
VBA activities are) even home. Home machine is Win 95...

Don't know what more detail I can provide, nor what interests
you...NT issues with VBA?

The first was the DoEvents not "Doing" when on NT, but ok on XP, then

It's as I described.
1- Load a String array  by:
Calling the array loading sub from inside form [ Call ArrayLoad ].
2- Exit the form with a userform1.Hide
3- Fire a sub (with a command button in the sheet) to examine
(Debug.Print) any element of the array and get zilch. [ =""]

Bring the sheet/file into work and all is fine on XP Pro.

The code  snippet I reworked earlier shows the flow.

Didn't find what you were looking for? Find more on Variable scope & Lifetime limits causing trouble Or get search suggestion and latest updates.