VBA多字段排序报错求助：按用户指定单元格字段排序失败

需要通过VBA实现按用户选择的1至6个层级对Excel表格tb_Gantt排序，排序字段（Sort1、Sort2等）的名称存储在单元格中。编写的代码运行报错，无法正确将范围名称插入排序命令，怀疑是排序字段变量的格式问题。

Sub Sort()
    
    Dim v_Sort_L1 As String
    Dim v_Sort_L2 As String
    Dim v_Sort_L3 As String
    Dim v_Sort_L4 As String
    Dim v_Sort_L5 As String
    Dim v_Sort_L6 As String
    Dim N_levels As Integer
    
    ' 获取用户选择的排序字段，若用户选了3个，剩余字段值为"Not Used"
    v_Sort_L1 = "tb_Gantt[" & Range("tb_sort_fields").Cells(1, 2).Value & "]"
    v_Sort_L2 = "tb_Gantt[" & Range("tb_sort_fields").Cells(2, 2).Value & "]"
    v_Sort_L3 = "tb_Gantt[" & Range("tb_sort_fields").Cells(3, 2).Value & "]"
    v_Sort_L4 = "tb_Gantt[" & Range("tb_sort_fields").Cells(4, 2).Value & "]"
    v_Sort_L5 = "tb_Gantt[" & Range("tb_sort_fields").Cells(5, 2).Value & "]"
    v_Sort_L6 = "tb_Gantt[" & Range("tb_sort_fields").Cells(6, 2).Value & "]"
    
    ' 获取用户选择的排序层级数量
    N_levels = Range("Num_Sort_Levels").Value
    
    Worksheets("Gantt").Select
    Range("tb_Gantt[[#Headers],[Description]]").Select
        
    ActiveWorkbook.Worksheets("Gantt").ListObjects("tb_Gantt").Sort.sortFields.Clear
    If N_levels = 1 Then
        Worksheets("Gantt").ListObjects("tb_Gantt").Sort.sortFields.Add2 _
                               Key:=Range(v_Sort_L1), Order1:=xlAscending
        Stop
    End If
    
    If N_levels = 2 Then
        Worksheets("Gantt").ListObjects("tb_Gantt").Sort.sortFields.Add2 _
            Key:=Range("tb_Gantt[" & v_Sort_L1 & "]"), Order1:=xlAscending
        Worksheets("Gantt").ListObjects("tb_Gantt").Sort.sortFields.Add2 _
            Key1:=Range("tb_Gantt[" & v_Sort_L2 & "]"), Order2:=xlAscending, DataOption:=xlSortNormal
    End If
    
' 后续还有3-6层级的判断，此处省略
    
    With ActiveWorkbook.Worksheets("Gantt").ListObjects("tb_Gantt").Sort
       .Header = xlYes
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
    End With
End Sub

核心错误点 #

1. 变量格式重复拼接：原代码中v_Sort_L1已经拼接成tb_Gantt[字段名]的格式，但在2层级判断里又套了一层tb_Gantt[xxx]，导致最终范围字符串变成tb_Gantt[tb_Gantt[字段名]]，Excel无法识别这个无效范围。
2. 冗余的单元格选择：Select操作不仅低效，还容易触发意外错误，直接操作表格对象即可。
3. SortFields参数误用：Add2方法不需要Key1、Order2这类带数字后缀的参数，直接用Key、Order即可；且通过ListObject直接获取列范围比手动拼接更可靠。

修正后的代码 #

Sub SortGanttTable()
    Dim wsGantt As Worksheet
    Dim tblGantt As ListObject
    Dim sortFieldsRange As Range
    Dim numSortLevels As Integer
    Dim i As Integer
    Dim sortColName As String
    
    ' 初始化对象，避免Select操作
    Set wsGantt = ThisWorkbook.Worksheets("Gantt")
    Set tblGantt = wsGantt.ListObjects("tb_Gantt")
    Set sortFieldsRange = wsGantt.Range("tb_sort_fields")
    numSortLevels = wsGantt.Range("Num_Sort_Levels").Value
    
    ' 清空原有排序规则
    tblGantt.Sort.SortFields.Clear
    
    ' 循环添加排序层级
    For i = 1 To numSortLevels
        sortColName = sortFieldsRange.Cells(i, 2).Value
        ' 跳过标记为"Not Used"的无效字段
        If sortColName <> "Not Used" Then
            tblGantt.Sort.SortFields.Add2 _
                Key:=tblGantt.ListColumns(sortColName).Range, _
                Order:=xlAscending, _
                DataOption:=xlSortNormal
        End If
    Next i
    
    ' 应用排序设置
    With tblGantt.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

修正说明 #

• 直接操作表格列：通过tblGantt.ListColumns(sortColName).Range获取排序列范围，彻底避免手动拼接字符串的格式错误。
• 循环简化逻辑：用For循环替代多个If判断，代码更简洁，自动支持1-6层级的排序，无需重复编写判断代码。
• 跳过无效字段：自动过滤"Not Used"的字段，避免添加无效排序规则。
• 移除冗余操作：去掉所有Select操作，直接引用工作表和表格对象，提升代码稳定性和执行效率。

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