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

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



matelot wrote:

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

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

"matelot" wrote in message
...
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

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" wrote:

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

"matelot" wrote in message
...
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

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

"NickHK" wrote in message
...
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

"matelot" wrote in message
...
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