Global/Public Array
Results 1 to 4 of 4

Thread: Global/Public Array

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Global/Public Array

    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

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,824
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Global/Public Array

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

    Code:
    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...

    Code:
    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!

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,070
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Global/Public Array

    Here is another way to write the myArray function...
    Code:
    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Re: Global/Public Array

    Thank you!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •