VBA删除不在指定数组中的工作表时触发Run-time error'9'下标越界求助

错误原因 #

你触发的Run-time error '9'是因为正向遍历工作表并执行删除操作时，工作表集合的索引会动态变化。

举个具体场景：当只剩Nike和一张随机名称的工作表时，你初始获取的WS_Count=2，循环从I=1到2：

1. I=1时，判断Nike在数组中，跳过删除；
2. I=2时，删除随机名称的工作表，此时工作簿只剩1张表；
3. 循环继续执行到I=2，但Worksheets(2)已经不存在，直接触发下标越界错误。

解决方案 #

方法1：反向遍历工作表（推荐） #

从最后一张工作表开始往前遍历删除，这样删除操作不会影响未遍历到的工作表的索引，彻底避免越界问题。

修改后的完整代码：

' 判断值是否在数组中的自定义函数
Function in_array(my_array As Variant, my_value As String) As Boolean
    in_array = False
    Dim I As Integer
    For I = LBound(my_array) To UBound(my_array)
        If my_array(I) = my_value Then
            in_array = True
            Exit For
        End If
    Next
End Function

' 删除不在指定数组中的工作表
Sub DeleteUnwantedSheets()
    Dim my_array As Variant
    Dim I As Integer
    
    my_array = Array("Nike", "Adidas")
    
    ' 反向遍历：从最后一张表开始，步长为-1
    For I = ActiveWorkbook.Worksheets.Count To 1 Step -1
        If Not in_array(my_array, ActiveWorkbook.Worksheets(I).Name) Then
            ' 关闭删除确认提示，提升执行效率
            Application.DisplayAlerts = False
            ActiveWorkbook.Worksheets(I).Delete
            Application.DisplayAlerts = True
        End If
    Next I
End Sub

方法2：先收集要删除的表，再批量删除 #

先遍历所有工作表，把需要删除的表存入集合，最后统一删除，避免遍历过程中工作表集合动态变化的问题。同时可以用VBA内置的Application.Match代替自定义函数，简化代码：

Sub DeleteUnwantedSheets()
    Dim my_array As Variant
    Dim ws As Worksheet
    Dim sheetsToDelete As New Collection
    
    my_array = Array("Nike", "Adidas")
    
    ' 收集所有需要删除的工作表
    For Each ws In ActiveWorkbook.Worksheets
        ' Match找不到值时返回错误，用IsError判断
        If IsError(Application.Match(ws.Name, my_array, 0)) Then
            sheetsToDelete.Add ws
        End If
    Next ws
    
    ' 批量删除收集到的工作表
    Application.DisplayAlerts = False
    Dim sheetObj As Worksheet
    For Each sheetObj In sheetsToDelete
        sheetObj.Delete
    Next sheetObj
    Application.DisplayAlerts = True
End Sub

额外注意事项 #

• Excel不允许删除工作簿中的最后一张工作表，如果你的目标数组中没有任何存在的工作表，代码会报错，建议添加判断逻辑确保至少保留一张表；
• 给函数和变量添加类型声明（如As Boolean、As Variant），可以提升代码稳定性和可读性。

内容的提问来源于stack exchange，提问作者Kokopas