下标超出范围 - 运行时错误9

can you please advise why the below code does not select the visible sheets, but ends in a runtime error. This is driving me crazy. Thanks for any help.

你能告诉我为什么下面的代码没有选择可见的表格,但结果是运行时错误。这真让我抓狂。谢谢你的帮助。

Sub SelectSheets1()

    Dim mySheet As Object
    Dim mysheetarray As String

    For Each mySheet In Sheets
        With mySheet
            If .Visible = True And mysheetarray = "" Then
                mysheetarray = "Array(""" & mySheet.Name
            ElseIf .Visible = True Then
                mysheetarray = mysheetarray & """, """ & mySheet.Name
            Else
            End If
        End With
    Next mySheet        

    mysheetarray = mysheetarray & """)"                
    Sheets(mysheetarray).Select

End Sub

3 个解决方案

#1

Long story short - you are giving a string (mysheetarray) when it is expecting array. VBA likes to get what it expects.

长话短说 - 当你期待数组时,你会给一个字符串(mysheetarray)。 VBA喜欢得到它所期望的。

Long story long - this is the way to select all visible sheets:

长篇故事 - 这是选择所有可见工作表的方法:

Option Explicit

Sub SelectAllVisibleSheets()

    Dim varArray()          As Variant
    Dim lngCounter          As Long

    For lngCounter = 1 To Sheets.Count
        If Sheets(lngCounter).Visible Then
            ReDim Preserve varArray(lngCounter - 1)
            varArray(lngCounter - 1) = lngCounter
        End If
    Next lngCounter

    Sheets(varArray).Select

End Sub

#2

You should define Dim mySheet As Object as Worksheet.

您应该将Dim mySheet As Object定义为Workheet。

Also, you can use an array of Sheet.Names that are visible.

此外,您可以使用可见的Sheet.Names数组。

Code

Sub SelectSheets1()

    Dim mySheet As Worksheet
    Dim mysheetarray() As String
    Dim i As Long

    ReDim mysheetarray(Sheets.Count) '< init array to all existing worksheets, will optimize later
    i = 0
    For Each mySheet In Sheets
        If mySheet.Visible = xlSheetVisible Then
            mysheetarray(i) = mySheet.Name
            i = i + 1
        End If
    Next mySheet

    ReDim Preserve mysheetarray(0 To i - 1) '<-- optimize array size    
    Sheets(mysheetarray).Select

End Sub

#3

I have tried to explain the Sheets a little, HTH. Note: Sheets property is defined on Workbook and on Application objects, both works and returns the Sheets-Collection.

我试图解释一下Sheets,HTH。注意:Sheets属性在Workbook和Application对象上定义,两者都可以工作并返回Sheets-Collection。

Option Explicit

Sub SheetsDemo()
    ' All sheets
    Dim allSheets As Sheets
    Set allSheets = ActiveWorkbook.Sheets

    ' Filtered sheets by sheet name
    Dim firstTwoSheets As Sheets
    Set firstTwoSheets = allSheets.Item(Array("Sheet1", "Sheet2"))
    ' or simply: allSheets(Array("Sheet1", "Sheet2"))
    ' Array("Sheet1", "Sheet2") is function which returns Variant with strings

    ' So you simply need an array of sheet names which are visible
    Dim visibleSheetNames As String
    Dim sh As Variant ' Sheet class doesn't exist so we can use Object or Variant
    For Each sh In allSheets
        If sh.Visible Then _
            visibleSheetNames = visibleSheetNames & sh.Name & ","
    Next sh

    If Strings.Len(visibleSheetNames) > 0 Then
        ' We have some visible sheets so filter them out
        visibleSheetNames = Strings.Left(visibleSheetNames, Strings.Len(visibleSheetNames) - 1)
        Dim visibleSheets As Sheets
        Set visibleSheets = allSheets.Item(Strings.Split(visibleSheetNames, ","))

        visibleSheets.Select

    End If
End Sub

#1