vba - 如果不在数组中，则删除 Excel 工作表

Question

我在解决错误“时间执行错误#13:不兼容类型”时遇到问题。如果用户创建了一些未在数组中指定的工作表，它将被删除。有人可以帮忙吗？

sub DeleteNewSheets()

Dim ws, wsP As Worksheet
Dim ArrayOne As Variant

    Application.DisplayAlerts = False

    ArrayOne = Array("SheetA", "SheetB", "SheetC", "Sheet_n")


    Set wsP = ThisWorkbook.Worksheets(ArrayOne) ' <--- ERROR #13

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> wsP.Name Then ws.Delete
    Next ws

    Application.DisplayAlerts = True

end sub

Answer 1

你的代码行说:

Set wsP = ThisWorkbook.Worksheets(ArrayOne)

正在尝试将 Worksheet 对象设置为多个 Worksheet 的数组。这就像尝试将单个整数设置为整​​数数组。

尝试使用以下代码

Sub DeleteNewSheets()

    Dim ws As Worksheet
    Dim ArrayOne() As Variant
    Dim wsName As Variant
    Dim Matched As Boolean

    ArrayOne = Array("SheetA", "SheetB", "SheetC", "Sheet_n")

    Application.DisplayAlerts = False

    For Each ws In ThisWorkbook.Worksheets
        Matched = False
        For Each wsName In ArrayOne
            If wsName = ws.Name Then
                Matched = True
                Exit For
            End If
        Next
        If Not Matched Then
            ws.Delete
        End If
    Next ws

    Application.DisplayAlerts = True

End Sub