vba - 在使图表可见后使工作表可见时，可见属性失败？

Question

以下代码有效:

Sub ShowSheets(vSheets() As Variant)

    Dim i As Long

    'Make worksheets visible
    For i = LBound(vSheets, 1) To UBound(vSheets, 1)
        If TypeName(ThisWorkbook.Sheets(vSheets(i, 1))) = "Worksheet" Then
            ThisWorkbook.Worksheets(vSheets(i, 1)).Visible = xlSheetVisible
        End If
    Next i

    'Make chart sheets visible
    For i = LBound(vSheets, 1) To UBound(vSheets, 1)
        Debug.Print (TypeName(ThisWorkbook.Sheets(vSheets(i, 1))))
        If TypeName(ThisWorkbook.Sheets(vSheets(i, 1))) = "Chart" Then
            ThisWorkbook.Charts(vSheets(i, 1)).Visible = xlSheetVisible
        End If
    Next i

End Sub

虽然以下代码失败:

Sub ShowSheets(vSheets() As Variant)

    Dim i As Long

    'Make chart sheets visible
    For i = LBound(vSheets, 1) To UBound(vSheets, 1)
        Debug.Print (TypeName(ThisWorkbook.Sheets(vSheets(i, 1))))
        If TypeName(ThisWorkbook.Sheets(vSheets(i, 1))) = "Chart" Then
            ThisWorkbook.Charts(vSheets(i, 1)).Visible = xlSheetVisible
        End If
    Next i

    'Make worksheets visible
    For i = LBound(vSheets, 1) To UBound(vSheets, 1)
        If TypeName(ThisWorkbook.Sheets(vSheets(i, 1))) = "Worksheet" Then
            ThisWorkbook.Worksheets(vSheets(i, 1)).Visible = xlSheetVisible
        End If
    Next i

End Sub

失败并显示错误消息:

Run-time error '-2147417848 (80010108)'

Method 'Visible' of object '_Worksheet'failed

成功的代码和失败的代码之间唯一的不同之处在于，在成功的代码中，我首先检查工作表，然后检查图表，而在失败的代码中，我颠倒了该顺序。导致失败的始终是图表之后的第一个工作表。

为什么第二个代码失败？

更多信息:两种情况下的情况都是相同的。 vSheets(i, 1) 始终等于工作表名称。工作表和工作簿不 protected 。

编辑:这是调用例程

Sub ToggleSheets()
    'Save sheets in tToggleSheets.DatabodyRange
    Dim wksTables As Worksheet
    Dim loSheets As ListObject
    Dim vSheets() As Variant
    Dim bAnySheetVisible As Boolean

    Set wksTables = GetSheetByCodename(ThisWorkbook, "wTables")
    Set loSheets = wksTables.ListObjects("tToggleSheets")
    vSheets = loSheets.DataBodyRange

    'Check if all sheets are hidden
    bAnySheetVisible = AnySheetVisible(vSheets)

    If bAnySheetVisible = True Then
        'If any is visible, hide all
        Call HideSheets(vSheets)
    Else
        'Else unhide all
        Call ShowSheets(vSheets)
    End If
End Sub

Answer 1

问题不在于您发布的代码中。我的子 MAIN() “按原样”运行您的代码。

主要:

1. 隐藏除第一个工作表之外的所有工作表(工作表和图表)
2. 发出MsgBox来验证隐藏状态
3. 调用您的例程

    Sub ShowSheets(vSheets() As Variant)

    Dim i As Long

    'Make chart sheets visible
    For i = LBound(vSheets, 1) To UBound(vSheets, 1)
        Debug.Print (TypeName(ThisWorkbook.Sheets(vSheets(i, 1))))
        If TypeName(ThisWorkbook.Sheets(vSheets(i, 1))) = "Chart" Then
            ThisWorkbook.Charts(vSheets(i, 1)).Visible = xlSheetVisible
        End If
    Next i

    'Make worksheets visible
    For i = LBound(vSheets, 1) To UBound(vSheets, 1)
        If TypeName(ThisWorkbook.Sheets(vSheets(i, 1))) = "Worksheet" Then
            ThisWorkbook.Worksheets(vSheets(i, 1)).Visible = xlSheetVisible
        End If
    Next i

End Sub
Sub MAIN()
    Dim vvSheets(1 To 8, 1 To 1) As Variant
    Dim i As Long, msg As String

    msg = ""
    For i = 1 To Sheets.Count
        If i > 1 Then
            Sheets(i).Visible = False
        End If
        vvSheets(i, 1) = Sheets(i).Name
        msg = msg & vbCrLf & Sheets(i).Name
    Next i
    MsgBox msg

    Call ShowSheets(vvSheets)
End Sub

您的代码运行没有错误。我怀疑该错误可能与调用例程中数组的变暗或填充方式有关。

我的初始配置: