订阅超出范围 - 运行时错误9

Question

您能否建议为什么以下代码不选择可见纸，而是以运行时错误结束。这真让我抓狂。感谢您的任何帮助。

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

Answer 1

长篇小说短 - 当它期望数组时，您正在给字符串（mysheetarray）。VBA喜欢得到它的期望。

长篇小说 - 这是选择所有可见床单的方式：

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

Answer 2

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

另外，您可以使用可见的 Sheet.Name s数组。

代码

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

Answer 3

我试图稍微解释 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