Global/Public Array

[joannevanderwest]

Hi

I am struggling to declare and fill in a global/public array.
Here is my code



Public Function myArray() As Variant
Dim WS_Count As Integer
Dim i As Integer
Dim x As Integer
Let x = 0
WS_Count = ActiveWorkbook.Worksheets.Count
For i = 1 To WS_Count
If Left(ActiveWorkbook.Worksheets(i).Name, 5) = "Sheep" Then

ReDim Preserve myArray(x) As Variant
myArray(x) = Worksheets(i).Name
x = x + 1
End If
Next i
End Function

I am getting Redim errors

I have a userform with a button. Everytime I click the button I want to go to the next array and then save information to the tab. ( The array is the tabname)

I am a newbie

Thank you

 

[Domenic]

Try creating a temporary array in which to store the desired sheet names, and then transfer the contents to myArray...

Public Function myArray() As Variant

    Dim tempArray() As Variant
    Dim WS_Count As Integer
    Dim i As Long
    Dim x As Long
    
    WS_Count = ActiveWorkbook.Worksheets.Count
    
    ReDim tempArray(1 To WS_Count)
    
    x = 0
    For i = LBound(tempArray) To UBound(tempArray)
        If Left(ActiveWorkbook.Worksheets(i).Name, 5) = "Sheep" Then
            x = x + 1
            tempArray(x) = ActiveWorkbook.Worksheets(i).Name
        End If
    Next i
    
    ReDim Preserve tempArray(1 To x)
    
    myArray = tempArray
    
End Function

Then you can get your array of sheet names something like this...

Sub test()

    Dim x As Variant
    Dim i As Long
    
    x = myArray()
    
    For i = LBound(x) To UBound(x)
        Debug.Print x(i)
    Next i
    
End Sub

Hope this helps!

 

[Rick Rothstein]

Here is another way to write the myArray function...

Public Function myArray() As Variant
  Dim WS As Worksheet, Combined As String
  For Each WS In Worksheets
    If Left(WS.Name, 5) = "Sheep" Then Combined = Combined & "/" & WS.Name
  Next
  myArray = Split(Mid(Combined, 2), "/")
End Function

You can use Domenic's subroutine to test it

 

[joannevanderwest]

Thank you!