Worksheets to wsArray() ?

[theta]

I have a named range (dynamic) at :

wb1.Worksheets("INDEX").Range("ExcludedTabs")

I open up another workbook (wb2). Now I would like to build an array of all worksheets in wb2, provided the worksheets is not matched/found in the range "ExcludedTabs"

What is the best way to do this?

Also I would like to omit the need to reference worksheet "INDEX" as this may change

Many thanks

 

[shg]

Maybe (completely untested):

Sub Test()
    Dim avsWks      As Variant
    Dim wkb         As Workbook
    Dim wb1         As Workbook

    Set wkb = Workbooks("Bob.xls")
    Set wb1 = Workbooks("Joe.xls")
    avsWks = GetDemWks(wkb, wb1.Worksheets("INDEX").Range("ExcludedTabs"))

    If IsArray(avsWks) Then
        MsgBox "Got " & UBound(avsWks) & " worksheets"
    Else
        MsgBox "Nada"
    End If
End Sub

Function GetDemWks(wkb As Workbook, r As Range) As Variant
    Dim asWks()     As String
    Dim sWks        As String
    Dim wks         As Worksheet
    Dim iWks        As Long
    Dim nWks        As Long

    ReDim asWks(1 To wkb.Worksheets.Count)

    With WorksheetFunction
        For iWks = 1 To wkb.Worksheets.Count
            sWks = wkb.Worksheet(iWks).Name
            If .CountIf(r, sWks) = 0 Then
                nWks = nWks + 1
                asWks(nWks) = sWks
            End If
        Next iWks
    End With

    If nWks Then
        ReDim Preserve asWks(1 To nWks)
        GetDemWks = asWks
    End If
End Function