Help me understanding variable scope

[Guest]

I create a public array defined on top of the macro to be global in 1 of the
worksheet. The array contains a range of a worksheet. I have a procedure
called by a checkbox_click routine to see if a readflag is true to skip
loading the range into the global array so that it doesn't keep reading every
single time. My problem is that once the procedure is ran by the click
routine, the global array content disappears. What can I do to keep the value
intact once the array is loaded? Another thought is to verify if the global
array has any values in it but since I define the array as variant, it
doesn't exist when it's not loaded. Is there a function in VBA that allows me
to check if an array is defined?

Thanks

 

[Dave Peterson]

I put my public variables in a General module--not behind a worksheet.

And maybe you could use:

if isarray(myVar) then
'hey it's an array!
else
'not an array
end if

 

[NickHK]

As well as Dave's suggestion, are you using "End" anywhere in your code ?
That will clear variables.

By the way, one quick/dirty way to test if an array has been initialised is.
It has a few minor drawbacks, that you can research if necessary.

Private Sub CommandButton2_Click()
Dim arr() As Long

Debug.Print "Is initilaised: " & CBool(Not (Not arr))

ReDim arr(0)
Debug.Print "Is initilaised: " & CBool(Not (Not arr))

End Sub

NickHK

 

[Guest]

Thanks for the suggestion from both of you. I end up using Dave's solution.
It's simple enough to implement.

Thanks again to both of you.

 

[NickHK]

For the record, one of the draw backs is trying to use this construction
with a custom type. e.g
Private type Point
X as single
Y as single
end type

With an array of this Type, I would get itermittent error #16 "Expression
too complex" at seemingly random/unrelated places in code.
Use proper methods of testing for an initialised array, or waste a few hours
trying to debug...<g>

NickHK