VBA列表对象排序函数随机报1004错误求助：Method range of object-'Global' failed

多年前编写了一个VBA函数，用于对ListObject指定列做A-Z排序，目前随机出现错误1004：Method range of object-'Global' failed。有时报错后按F5继续运行又能正常执行，无法定位问题。

补充说明：gracode_Dico是全局字典，用于通过ListObject名称获取对应代码值，报错发生在代码行.SortFields.Add2 key:=Range(sRange), SortOn:= _。

原函数代码：

'Boucle sur All Listobjects si leur nom est t_, puis trie selon le code de la table.
Private Function GRA_Tri_By_Code(theworkbook As Workbook)

Dim sRange As String, ws As Worksheet, lo As ListObject

For Each ws In theworkbook.Worksheets
    If Left(ws.Name, 2) = "t_" Then
    For Each lo In ws.ListObjects
        If Left(lo.Name, 2) = "t_" Then
            Select Case lo.Name
            Case "t_cable_patch201", "t_ltech_patch201", "t_zpbo_patch201", "t_cab_cond", "t_cond_chem", "t_love"
                'Debug.Print "Impossible de trier " & lo.Name
            Case Else
                sRange = lo.Name & "[[#All],[" & gracode_Dico(lo.Name) & "_code]]"
                'sRange = lo.Name & "[[#All],[" & "ba" & "_code]]"
                With lo.Sort
                    .SortFields.Clear
                    .SortFields.Add2 key:=Range(sRange), SortOn:= _
                    xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                    .Header = xlYes: .MatchCase = False: .Orientation = xlTopToBottom: .SortMethod = xlPinYin: .Apply
                End With
            End Select
        End If
    Next lo
    End If
Next

End Function

已尝试的无效操作：使用ws.Activate（不偏好该方式）、指定Range父对象如ws.Range(sRange)或lo.Range(sRange)。

问题根源 #

报错核心是结构化引用的父对象不明确：你拼接的sRange是基于工作表的结构化引用，但直接用Range(sRange)会依赖当前激活工作表，而循环中工作表切换可能导致引用失效；另外lo.Range(sRange)的写法本身错误，ListObject的Range方法不接受完整的结构化引用字符串。

修复方案 #

放弃拼接结构化引用的方式，直接通过ListObject的ListColumns属性获取目标列范围，彻底避免父对象模糊问题：

修改后的代码：

'Boucle sur All Listobjects si leur nom est t_, puis trie selon le code de la table.
Private Function GRA_Tri_By_Code(theworkbook As Workbook)
    Dim targetColName As String, ws As Worksheet, lo As ListObject
    Dim targetCol As ListColumn '新增列对象变量，用于校验列存在性
    
    For Each ws In theworkbook.Worksheets
        If Left(ws.Name, 2) = "t_" Then
            For Each lo In ws.ListObjects
                If Left(lo.Name, 2) = "t_" Then
                    Select Case lo.Name
                        Case "t_cable_patch201", "t_ltech_patch201", "t_zpbo_patch201", "t_cab_cond", "t_cond_chem", "t_love"
                            'Debug.Print "Impossible de trier " & lo.Name
                        Case Else
                            targetColName = gracode_Dico(lo.Name) & "_code"
                            
                            '校验目标列是否存在，避免新错误
                            On Error Resume Next
                            Set targetCol = lo.ListColumns(targetColName)
                            On Error GoTo 0
                            If targetCol Is Nothing Then
                                Debug.Print lo.Name & " 中不存在目标列：" & targetColName
                                GoTo NextLO '跳过当前ListObject
                            End If
                            
                            '直接使用ListColumn的Range作为排序键
                            With lo.Sort
                                .SortFields.Clear
                                .SortFields.Add2 Key:=targetCol.Range, _
                                    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                                .Header = xlYes
                                .MatchCase = False
                                .Orientation = xlTopToBottom
                                .SortMethod = xlPinYin
                                .Apply
                            End With
                    End Select
                End If
NextLO:
            Next lo
        End If
    Next
End Function

额外优化点 #

• 新增列存在性校验，避免因字典返回无效列名导致的新错误
• 拆分长代码行，提升可读性
• 去掉依赖当前激活工作表的风险，所有对象引用均明确指向父级

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