下拉列表选“Example”时Excel AutoFilter方法执行失败问题排查

我设置了两个下拉列表，分别用于选择语言和员工职位，原本功能正常，但新增“Example”职位选项后，功能出现异常。列名称与已声明的常量完全匹配：

• FEV Mitarbeiter / Employee
• externer Mitarbeiter / external Employee
• TME Mitarbeiter / Employee
• Student / Student
• Example /Example

报错信息为AutoFilter method of Range class failed，怀疑是单元格已激活筛选导致，但不确定。

函数代码 #

Option Explicit
Sub set_language_and_view(sLanguage As String, sEmployeeCategory As String)
Dim i As Integer
Application.ScreenUpdating = False

For i = 1 To Sheets.Count
    If Sheets(i).Cells(ROW_LIST_CHECK, COL_LIST_CHECK) = s_CHECK Then
        'Identified List-Sheet
        With Sheets(i)
            '============= SET LANGUAGE
            If sLanguage = sGERMAN Then
                'Show German
                .Columns(COL_LIST_CATEGORY_DE).Hidden = False
                .Columns(COL_LIST_TASK_DE).Hidden = False
                .Columns(COL_LIST_LINK_DE).Hidden = False
                'Hide English
                .Columns(COL_LIST_CATEGORY_ENG).Hidden = True
                .Columns(COL_LIST_TASK_ENG).Hidden = True
                .Columns(COL_LIST_LINK_ENG).Hidden = True
            Else
                'Show English
                .Columns(COL_LIST_CATEGORY_ENG).Hidden = False
                .Columns(COL_LIST_TASK_ENG).Hidden = False
                .Columns(COL_LIST_LINK_ENG).Hidden = False
                'Hide German
                .Columns(COL_LIST_CATEGORY_DE).Hidden = True
                .Columns(COL_LIST_TASK_DE).Hidden = True
                .Columns(COL_LIST_LINK_DE).Hidden = True
            End If
            
            '============ SET EMPLOYEE CATEGORY
            Dim d As Variant
            Dim col As Integer

            Set d = CreateObject("Scripting.Dictionary")
            With d
                .Add sFEVEMPLOYEE, COL_LIST_FEV_EMPLOYEE
                .Add sEXTERNALEMPLOYEE, COL_LIST_EXTERNAL_EMPLOYEE
                .Add sTMEEMPLOYEE, COL_LIST_TME_EMPLOYEE
                '.Add sEXAMPLE, COL_LIST_EXAMPLE
                .Add sSTUDENTWORKER, COL_LIST_STUDENT_WORKER
            End With
            
            .Activate
        
            col = d.Item(sEmployeeCategory)
               If .FilterMode = True Then .AutoFilter.ShowAllData
            .Range(Cells(ROW_LIST_FILTER, COL_LIST_FIRST), Cells(ROW_LIST_FILTER, COL_LIST_LAST)).AutoFilter Field:=col, Criteria1:"<>"
        End With
    End If
Next i
Sheets(ws_DETAILS).Activate
Application.ScreenUpdating = True
End Sub

常量声明 #

Option Explicit

' ----- DETAILS SHEET
Public Const ws_DETAILS = "Details"
Public Const COL_DETAILS_INFO = 3
Public Const ROW_DETAILS_EMPLOYEE_CATEGORY = 12
Public Const ROW_DETAILS_LANGUAGE = 13

' ----- ALL CHECKLISTS
Public Const ROW_LIST_FILTER = 4
Public Const COL_LIST_FIRST = 1

Public Const COL_LIST_CATEGORY_DE = 4
Public Const COL_LIST_CATEGORY_ENG = 5

Public Const COL_LIST_TASK_DE = 6
Public Const COL_LIST_TASK_ENG = 7

Public Const COL_LIST_LINK_DE = 9
Public Const COL_LIST_LINK_ENG = 10

Public Const COL_LIST_FEV_EMPLOYEE = 12
Public Const COL_LIST_EXTERNAL_EMPLOYEE = 13
Public Const COL_LIST_TME_EMPLOYEE = 14
Public Const COL_LIST_STUDENT_WORKER = 15

Public Const COL_LIST_EXAMPLE = 16
Public Const COL_LIST_LAST = COL_LIST_EXAMPLE   

'-------Identified Sheet as List
Public Const COL_LIST_CHECK = 1
Public Const ROW_LIST_CHECK = 4
Public Const s_CHECK = "Status / Status"

'-------CONSTANTS
Public Const sGERMAN = "Deutsch"
Public Const sENGLISH = "English"
Public Const sFEVEMPLOYEE = "FEV Mitarbeiter / Employee"
Public Const sEXTERNALEMPLOYEE = "externer Mitarbeiter / external Employee"
Public Const sTMEEMPLOYEE = "TME Mitarbeiter / Employee"
Public Const sSTUDENTWORKER = "Student / Student"
Public Const sEXAMPLE = "Example /Example"

调用代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
Dim language As String, employee_category As String

    If Target.Column = COL_DETAILS_INFO And (Target.Row = ROW_DETAILS_LANGUAGE Or Target.Row = ROW_DETAILS_EMPLOYEE_CATEGORY) Then
        language = Cells(ROW_DETAILS_LANGUAGE, COL_DETAILS_INFO)
        employee_category = Cells(ROW_DETAILS_EMPLOYEE_CATEGORY, COL_DETAILS_INFO)
        Call set_language_and_view(language, employee_category)
    End If

End Sub

问题分析与修复方案 #

核心问题点 #

1. 字典缺失Example项：新增Example职位后，代码中注释掉了.Add sEXAMPLE, COL_LIST_EXAMPLE，选择Example时d.Item(sEmployeeCategory)会抛出“键不存在”错误，终止后续代码执行，进而触发AutoFilter相关报错。
2. AutoFilter语法错误：代码中.AutoFilter Field:=col, Criteria1:"<>"的参数赋值少了等号，正确写法应为Criteria1:="<>"。
3. 未指定Cells的父对象：.Range(Cells(...), Cells(...))中的Cells未绑定到当前With块的工作表，若当前激活的不是目标工作表，会导致Range引用错误。
4. 筛选清除逻辑不严谨：仅用.FilterMode判断可能遗漏部分筛选状态，建议结合.AutoFilterMode处理。

修复后的函数代码 #

Option Explicit
Sub set_language_and_view(sLanguage As String, sEmployeeCategory As String)
Dim i As Integer
Application.ScreenUpdating = False

For i = 1 To Sheets.Count
    If Sheets(i).Cells(ROW_LIST_CHECK, COL_LIST_CHECK) = s_CHECK Then
        'Identified List-Sheet
        With Sheets(i)
            '============= SET LANGUAGE
            If sLanguage = sGERMAN Then
                'Show German
                .Columns(COL_LIST_CATEGORY_DE).Hidden = False
                .Columns(COL_LIST_TASK_DE).Hidden = False
                .Columns(COL_LIST_LINK_DE).Hidden = False
                'Hide English
                .Columns(COL_LIST_CATEGORY_ENG).Hidden = True
                .Columns(COL_LIST_TASK_ENG).Hidden = True
                .Columns(COL_LIST_LINK_ENG).Hidden = True
            Else
                'Show English
                .Columns(COL_LIST_CATEGORY_ENG).Hidden = False
                .Columns(COL_LIST_TASK_ENG).Hidden = False
                .Columns(COL_LIST_LINK_ENG).Hidden = False
                'Hide German
                .Columns(COL_LIST_CATEGORY_DE).Hidden = True
                .Columns(COL_LIST_TASK_DE).Hidden = True
                .Columns(COL_LIST_LINK_DE).Hidden = True
            End If
            
            '============ SET EMPLOYEE CATEGORY
            Dim d As Variant
            Dim col As Integer

            Set d = CreateObject("Scripting.Dictionary")
            With d
                .Add sFEVEMPLOYEE, COL_LIST_FEV_EMPLOYEE
                .Add sEXTERNALEMPLOYEE, COL_LIST_EXTERNAL_EMPLOYEE
                .Add sTMEEMPLOYEE, COL_LIST_TME_EMPLOYEE
                .Add sEXAMPLE, COL_LIST_EXAMPLE ' 取消注释，加入Example项
                .Add sSTUDENTWORKER, COL_LIST_STUDENT_WORKER
            End With
            
            ' 先检查字典是否存在该键，避免报错
            If d.Exists(sEmployeeCategory) Then
                col = d.Item(sEmployeeCategory)
                
                ' 严谨清除现有筛选
                If .AutoFilterMode Then
                    If .FilterMode Then .AutoFilter.ShowAllData
                End If
                
                ' 修正Range引用，绑定Cells到当前工作表
                .Range(.Cells(ROW_LIST_FILTER, COL_LIST_FIRST), .Cells(ROW_LIST_FILTER, COL_LIST_LAST)).AutoFilter Field:=col, Criteria1:="<>"
            End If
        End With
    End If
Next i
Sheets(ws_DETAILS).Activate
Application.ScreenUpdating = True
End Sub

额外优化建议 #

• 在调用字典Item方法前，先通过d.Exists(sEmployeeCategory)判断键是否存在，避免因下拉列表值异常导致崩溃。
• 移除.Activate调用，通过明确指定父对象的方式操作工作表，减少激活切换带来的潜在问题。

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