VBA判断列筛选状态禁止插入行时报运行时错误'9'下标越界

问题概述 #

实现规则：仅当工作表E列、F列均未处于筛选状态时，才允许执行插入新行操作。
已编写的addNewRow宏逻辑如下：

• 对名为Overall Combination的工作表执行Unprotect解除密码保护
• 定义允许插入行的起始行号常量TopRow = 10，获取当前活动单元格所在行号rowNum
• 判定条件满足时（rowNum > TopRow，且AutoFilter.Filters(5).On、AutoFilter.Filters(6).On均为False，即判定E、F列未开启筛选），在当前行位置插入新行
• 插入后依次复制上一行O列、Q列至V列、X列至AI列区域的内容，通过PasteSpecial粘贴xlPasteFormulas公式与xlPasteFormats格式到新行对应位置
• 粘贴完成后清空剪贴板，选中新行D列单元格，在新行S列（第19列）位置添加无Caption标题、开启3D显示效果的CheckBox复选框
• 判定条件不满足时弹出提示框，告知用户'Pneu. Cabinet'或'Valve Node'列处于筛选状态时无法插入新行
• 逻辑执行完成后重新对工作表设置密码保护，设置AllowFiltering:=True保留筛选权限

报错现象：代码运行至判断AutoFilter.Filters状态的If语句行时，触发Run-time error '9': Subscript out of range（运行时错误'9'：下标越界），将代码中ActiveSheet替换为指定工作表对象后错误仍然存在。

错误根因 #

触发该下标越界错误的核心原因是对AutoFilter.Filters集合的规则存在理解偏差，具体分为3类场景：

1. 工作表未启用自动筛选：当工作表AutoFilterMode = False（即没有开启筛选功能）时，工作表的AutoFilter对象为空，直接访问AutoFilter.Filters必然触发错误，和是否绑定指定工作表对象无关。
2. 硬编码Filters下标与实际筛选区域不匹配：Filters集合的下标是相对于当前筛选区域Range的列序号，不是工作表的全局列号。如果创建筛选时选中的区域不是从A列开始，或者筛选区域总列数不足6列，直接访问Filters(5)、Filters(6)就会因为下标超出集合实际范围触发越界。
3. 列映射逻辑错误：即便筛选区域列数足够，如果筛选区域起始列不是A列，硬编码的Filters(5)对应的根本不是工作表E列，Filters(6)也不是F列，即使不报错，判断结果也完全不符合预期。

修复方案 #

替换原有直接访问AutoFilter.Filters(5)、AutoFilter.Filters(6)的判断逻辑，先校验筛选状态，再动态计算E、F列在筛选区域中的相对位置，代码示例如下：

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Overall Combination")

Dim eColFiltered As Boolean, fColFiltered As Boolean
eColFiltered = False
fColFiltered = False

' 先判断工作表是否开启筛选
If ws.AutoFilterMode Then
    Dim filterRng As Range
    Set filterRng = ws.AutoFilter.Range
    ' 计算E列、F列在筛选区域内的相对列号
    Dim eRelPos As Long, fRelPos As Long
    eRelPos = 5 - filterRng.Column + 1
    fRelPos = 6 - filterRng.Column + 1
    
    ' 校验相对位置在筛选区域列范围内，避免越界
    If eRelPos >= 1 And eRelPos <= filterRng.Columns.Count Then
        eColFiltered = ws.AutoFilter.Filters(eRelPos).On
    End If
    If fRelPos >= 1 And fRelPos <= filterRng.Columns.Count Then
        fColFiltered = ws.AutoFilter.Filters(fRelPos).On
    End If
End If

' 替换原有If判断条件
If rowNum > TopRow And Not eColFiltered And Not fColFiltered Then
    ' 此处保留原有的插入新行、复制内容、添加复选框逻辑
Else
    MsgBox "'Pneu. Cabinet'或'Valve Node'列处于筛选状态时无法插入新行"
End If

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