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

Question

我正在尝试创建一个包含所有工作表名称的数组，从事件工作簿中的第四个工作表开始。当我尝试重新调整数组尺寸时，我在第 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

Answer 1

@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