Trouble with Public Dynamic Array

[eyesbee]

Hi,

I've been looking online for the last couple of hours for a solution to my problem. I've created a sub that works if I declare the array within the sub (see commented out declaration line below). However, I want to use the array in another module. So I've added in the Public line at the top of the code (see below).

' Declare the array that will hold information about the specific queries to run
Public arrCheckedBoxes() As String

Public arrCheckedBoxes() As String

Public Sub CheckBox_Run_Click()

Dim intChecked As Integer
'Dim arrCheckedBoxes() As String

ThisWorkbook.Worksheets("Control Panel").Select

' Count the number of "Run" boxes that are checked
intChecked = 0
For Each chkbox In ActiveSheet.CheckBoxes
    If Left(chkbox.Name, 3) = "Run" Then
        If chkbox.Value = xlOn Then
            intChecked = intChecked + 1
            ReDim Preserve arrCheckedBoxes(intChecked)
            arrCheckedBoxes(intChecked) = chkbox.Name
        End If
    End If
Next chkbox
'MsgBox (intChecked)

End Sub

I get a compile error saying "Variable not defined". The following code is highlighted when the error message box pops up: "ReDim Preserve arrCheckedBoxes" -- full line of code is "ReDim Preserve arrCheckedBoxes(intChecked)".

I can't really pass the array on to the other sub because the other sub runs off a click event. All I'm aiming to do at the moment is read the list of elements of arrCheckedBoxes() in another module's subroutine.

Thanks in advance .

 

[eyesbee]

I had a feeling that the 'Option Explicit' statement specified in another module may be causing this error. I worked around it by adding the ReDim statement into the sub as follows:

Public Sub CheckBox_Run_Click()Dim intChecked As Integer
'Dim arrCheckedBoxes() As String
ReDim arrCheckedBoxes(0) As String
' The rest of the sub remains intact ...

Case closed unless anybody cares to explain? I'm still confused. The helpfile on 'Explicit' says, if used at module level, it should only impact the module it appears in.