主页 / coding / 问题 / 79192736
Accepted
L0cked
Asked: 2024-11-15 22:11:00 +0800 CST

排序列表对象函数随机停止工作（错误 1004）

  • 772

我几年前编写的一个小函数遇到了麻烦，它用于简单地对列表对象中 A>Z 中的特定列进行排序。

它随机失败“对象的方法范围 - '全局'失败”但如果我在卡住时再次按 F5，它会正常继续（有时）。

我不明白如何调试这个，你能帮忙吗？

为了更好地理解：gracode_Dico 是一个全局词典，用于通过输入 lo 名称来获取代码的正确值。

代码在 .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（不喜欢它），没有任何变化。我尝试精确指定范围的父级，如 ws.Range(sRange) 或 lo.Range(sRange)，但没有成功。

excel

1 个回答

  1. Best Answer

    我不明白如何调试它。添加一些错误检查和/或诊断debug.print语句。例如

    Option Explicit

Private Sub GRA_Tri_By_Code(theworkbook As Workbook)

    Dim ws As Worksheet, lo As ListObject
    Dim n As Long, sColumn As String
    
    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
                
                    ' check dictionary
                    If gracode_Dico.exists(lo.Name) Then
                        sColumn = gracode_Dico(lo.Name)
                    Else
                        MsgBox "No entry for " & lo.Name & " in gracode_Dico", vbCritical
                        Exit Sub
                    End If
                
                    ' check column exists
                    Dim rngSort As Range
                    For n = 1 To lo.ListColumns.Count
                         'Debug.Print ws.Name, lo.Name, lo.ListColumns(n).Name, sColumn
                         If lo.ListColumns(n).Name = sColumn Then
                            Set rngSort = lo.ListColumns(n).Range
                            Exit For
                         End If
                    Next
                    
                    ' does not exist
                    If rngSort Is Nothing Then
                       MsgBox "Could not locate column '" & sColumn & _
                       "' in " & ws.Name & " " & lo.Name, vbCritical
                       Exit Sub
                    End If
                    
                    With lo.Sort
                        .SortFields.Clear
                        .SortFields.Add2 Key:=rngSort, 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 Sub
    • 2

相关问题