Developing an Array Function that Gets all the Worksheets Name property with Conditions

[Luthius]

Guys
I would like to develop a function that can retrieve/get the Worksheet names from the current workbook as an array function.
The criteria is only Visible WorkSheets and the name <> "Setup".

I tried the code below but it is NOT working properly
Can you guys help me

[COLOR=#0000ff]Public Function[/COLOR] GetSpreadSheetList() [COLOR=#0000ff]As String[/COLOR]()

[COLOR=#0000ff]    Dim[/COLOR] ws [COLOR=#0000ff]As [/COLOR]Worksheet
    [COLOR=#0000ff]Dim[/COLOR] wb [COLOR=#0000ff]As[/COLOR] Workbook
    [COLOR=#0000ff]Dim[/COLOR] item [COLOR=#0000ff]As[/COLOR] Variant
    [COLOR=#0000ff]Dim[/COLOR] arr() [COLOR=#0000ff]As[/COLOR] String
    
    [COLOR=#0000ff]Set[/COLOR] wb = Application.ThisWorkbook
    
    [COLOR=#0000ff]For Each[/COLOR] ws [COLOR=#0000ff]In[/COLOR] wb.Worksheets
        [COLOR=#0000ff]If [/COLOR]ws.Name <> "Setup" [COLOR=#0000ff]And[/COLOR] ws.Visible [COLOR=#0000ff]Then[/COLOR]
            [COLOR=#0000ff]ReDim Preserve [/COLOR]arr([COLOR=#0000ff]LBound[/COLOR](arr) [COLOR=#0000ff]To[/COLOR] [COLOR=#0000ff]UBound[/COLOR](arr))
            arr(item) = ws.Name
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]    Next[/COLOR]
    
    [COLOR=#0000ff]Set [/COLOR]wb = [COLOR=#0000ff]Nothing[/COLOR]
[COLOR=#0000ff]End Function[/COLOR]

 

[Eric W]

How about:

Public Function GetSpreadSheetList() As Variant

    Dim ws As Worksheet
    Dim wb As Workbook
    Dim arr() As String
    Dim ctr As Long
    
    Set wb = Application.ThisWorkbook

    For Each ws In wb.Worksheets
        If ws.Name <> "Setup" And ws.Visible Then
            ctr = ctr + 1
            ReDim Preserve arr(1 To ctr)
            arr(ctr) = ws.Name
        End If
    Next

    Set wb = Nothing
    GetSpreadSheetList = WorksheetFunction.Transpose(arr)
    
End Function