Excel VBA - 创建工作表名称数组

Question

我试图创建一个包含所有工作表名称的数组，从活动工作簿中的第4个工作表开始。在尝试重新定义数组大小时，我在第4行上遇到了错误。我漏掉了什么？当前有6个工作表，第3个是隐藏的（如果有变化会发生什么）。

Dim i As Integer
Dim sheetsToSkip() As Variant
For i = 4 To Sheets.Count
    ReDim Preserve sheetsToSkip(UBound(sheetsToSkip) + 1)
    sheetsToSkip(UBound(sheetsToSkip)) = Sheets(i).Name
Next i

- Tibbs

---

循环的第一次迭代中，sheetsToSkip 没有 Ubound。 - BigBen

为什么不直接创建一个工作表的数组或集合呢？ - BigBen

https://dev59.com/YMfss4cB2Jgan1znw5cG - cyboashu

Answer 1

这里有一些快速的例子，如果您首先计算“有效”的工作表数量（如果您避免按名称包括工作表）-那会有所帮助。

-- 版本1 - 不要费心检查（只需将所有工作表名称获取到数组中即可）

Sub SheetNamesToArray()

Dim MyWb As Workbook
Dim MySheet As Worksheet
Dim SheetNameArray() As String  ' Array of Sheet Names
Dim LP As Integer ' ( Generic Loop Variable )

Set MyWb = ActiveWorkbook

    ':: Redim to workbook count
    ReDim SheetNameArray(MyWb.Sheets.Count)
    
    ':: Check Array Size
    Debug.Print UBound(SheetNameArray) & " = " & MyWb.Sheets.Count

    ':: Set Array from Sheet Names
    For LP = 1 To MyWb.Sheets.Count
        SheetNameArray(LP) = Sheets(LP).Name
        Debug.Print SheetNameArray(LP) & " :" & Sheets(LP).Name
    Next LP


End Sub

-- 版本2 - 检查名称，仅保存有效的（使用Inst测试多个工作表是否具有特定字符串名称）

Sub SheetNamesToArray_And_Ignore_Names()

Dim MyWb As Workbook
Dim MySheet As Worksheet
Dim SheetNameArray() As String  ' Array of Sheet Names
Dim LP, LPx As Integer ' ( Generic Loop Variable(s) )
Dim MyValidSheetsCount As Integer  ' Array Size for only 'valid' sheets


    ':: Apply to active workbook
    Set MyWb = ActiveWorkbook

    For LP = 1 To MyWb.Sheets.Count
        ':: Skip over names to ignore..
        If InStr(1, Sheets(LP).Name, "Four", vbTextCompare) = 0 Then
            '::Iterate count of 'valid' sheets ::
            MyValidSheetsCount = MyValidSheetsCount + 1
        End If
    
    Next LP

    ':: Redim to workbook count
    ReDim SheetNameArray(MyValidSheetsCount)
    
    LPx = 0
    
    ':: Set Array from Sheet Names
    For LP = 1 To MyWb.Sheets.Count

        ':: Same test again, this time add to array, and iterate index variable ( LPX )
        If InStr(1, Sheets(LP).Name, "Four", vbTextCompare) = 0 Then
            SheetNameArray(LPx) = Sheets(LP).Name
            '::Iterate array index ::
            LPx = LPx + 1
        End If

    Next LP

    Debug.Print "Found " & LPx & " valid sheets" & vbNewLine
    
    ':: Check only the 'valid' sheets are recorded
    For LP = 0 To LPx - 1
      Debug.Print "(" & Format(LP, "000#") & ")  : " & SheetNameArray(LP)
    Next LP

End Sub

使用VBA，可能有多种方法可以获得相同的结果。

对于原始问题，如果您只是想跳过4而不关心它们可能是否按顺序排列，请执行以下操作：

Sub ReallySimpleJustUseNumbers()

':: N.B.  Sheets are indexed starting at 1, arrays begin at 0 ..

Dim MyWb As Workbook
Dim MySheet As Worksheet
Dim SheetNameArray() As String  ' Array of Sheet Names
Dim LP As Integer ' ( Generic Loop Variable )

Set MyWb = ActiveWorkbook

    ':: Are there greater than four sheets in this workbook ?
    If MyWb.Sheets.Count > 4 Then
        ':: Redim to workbook count
        ReDim SheetNameArray(MyWb.Sheets.Count - 4)
    Else
        ':: Polite Exit
        MsgBox "Too Few Sheets buddy"
        Exit Sub
    End If
    
    For LP = 4 To MyWb.Sheets.Count
        SheetNameArray(LP - 4) = MyWb.Sheets(LP).Name
    Next LP
    
    ':: Output array
    For LP = 0 To UBound(SheetNameArray)
        Debug.Print LP & " --- " & SheetNameArray(LP)
    Next LP

End Sub

这将简单地开始填充数组，如定义的表格数-四个初始表格，索引从0开始

- Instant Excel . com

Answer 2

问题在于你的数组sheetsToSkip在第一个循环周期中没有上限。你可以在第一个循环中检查sheetsToSkip是否为数组，然后要么为其第一次分配维度，要么调整其大小。

代码建议：

Sub CreateSheetNameArray()
    Dim i As Integer
    'Brakets are removed
    Dim sheetsToSkip As Variant

    For i = 4 To Sheets.Count
        If IsEmpty(sheetsToSkip) Then
            ReDim sheetsToSkip(0 To 0)
        Else
            ReDim Preserve sheetsToSkip(UBound(sheetsToSkip) + 1)
        End If
        sheetsToSkip(UBound(sheetsToSkip)) = Sheets(i).Name
    Next i
End Sub

请注意，ReDim Preserve 的执行速度较慢。如果你有很多循环周期，可以考虑在循环之前先定义数组的维度。

- Michael Wycisk

Answer 3

@BigBen 正在正确的轨道上。你只需要 ReDim 一次：

Public Function ListWorkssheets()

    Dim SheetsToSkip()  As String
    Dim Count           As Integer
    Dim Index           As Integer
    
    Count = ThisWorkbook.Worksheets.Count
    ReDim SheetsToSkip(1 To Count)
    
    For Index = 1 To Count
        SheetsToSkip(Index) = ThisWorkbook.Worksheets((Index + 3 - 1) Mod Count + 1).Name
    Next
    
    ' Verify.
    For Index = LBound(SheetsToSkip) To UBound(SheetsToSkip)
        Debug.Print Index, SheetsToSkip(Index)
    Next

End Function

- Gustav