VBA Sort方法为何不使用我提供的自定义排序列表？

问题现象 #

已逐步检查全部代码，确认正确的CustomList索引已被使用，但数据行始终按字母排序，而非自定义列表指定的顺序。列B中即使是与自定义列表完全匹配的值，也未按预期排序。（注：列B存在部分值与列表格式差异，如“CDO Chief Development Officer”与列表中的“CDO - Chief Development Officer”不匹配，但此部分非问题重点）

原始自定义排序代码 #

Dim vCustom_Sort As Variant
    wsName.Sort.SortFields.Clear
    vCustom_Sort = Array("Direktor Gruppe/CEO", _
                    "CDO - Chief Development Officer", _
                    "CFO - Chief Financial Officer", _
                    "COO - Chief Operating Officer", _
                    "Assistenz CEO", _
                    "Holding Management Office", _
                    "Chauffeur", _
                    "Assistenz CFO", _
                    "Finanzen", _
                    "Bilanzierung", _
                    "Strategische IT", _
                    "Corporate Marketing & Communication", _
                    "Leitung Arbeitsschutz", _
                    "Arbeitsschutz", _
                    "Leitung Facility Management", _
                    "Facility Management", _
                    "Organisation, Prozesse & Privacy" _
                    )
    
    Dim customListIndex As Long
    customListIndex = Application.CustomListCount + 1
    Application.AddCustomList ListArray:=vCustom_Sort
    
    ' Find the index of the added custom list
    Dim tempList As Variant
    For i = 1 To Application.CustomListCount
        On Error Resume Next
        tempList = Application.GetCustomListContents(i)
        On Error GoTo 0
        If Not IsEmpty(tempList) Then
            If UBound(tempList) = UBound(vCustom_Sort) + 1 Then 'Here i had to add 1 to "UBound(vCustom_Sort)" since "vCustom_Sort" starts at 0 and "tempList" starts at 1
                Dim listMatch As Boolean
                listMatch = True
                For j = LBound(vCustom_Sort) To UBound(vCustom_Sort)
                    If tempList(j + 1) <> vCustom_Sort(j) Then 'Here again I had to add 1 to the variable "j" because "tempList" starts at 1 and "vCustom_Sort" starts at 0, and otherwise the loop would return an error since "j" starts at 0 and the "tempList" wouldn't have index 0
                        listMatch = False
                        Exit For
                    End If
                Next j
                If listMatch Then
                    customListIndex = i
                    Exit For
                End If
            End If
        End If
    Next i
    
    wsName.Sort.SortFields.Clear
    With wsName.Range("A3", wsName.Cells(lastRow(wsName), lastColumn(wsName)))
        .Cells.Sort Key1:=wsName.Range("B3:B" & lastRow(wsName)), Order1:=xlAscending, DataOption1:=xlSortNormal, _
                    Header:=xlYes, MatchCase:=False, _
                    OrderCustom:=customListIndex

    End With
    wsName.Sort.SortFields.Clear

已完成的检查项 #

• 确认Sort方法的Range范围正确
• 确认Key引用了正确的列和范围
• 确认lastRow和lastColumn函数返回值正确
• 确认wsName变量指向目标工作表，相关代码如下：

Set wb = Workbooks.Add
'Worksheets are added
wsName = wb.Sheets("Holding Management")

有效解决方案：辅助列排序 #

通过添加辅助列赋予自定义列表对应数值，再基于辅助列排序，最终实现预期顺序，代码如下：

' Add a helper column with numeric values based on the custom list
    Dim helperColumn As Long
    helperColumn = lastColumn(wsName) + 1 'lastColumn is a function that returns an integer, representing the last Column in that worksheet
    
    wsName.Cells(3, helperColumn).Value = "Helper Column"
    For j = LBound(customList) To UBound(customList)
        For i = 4 To lastRow(wsName)
            If LCase(wsName.Cells(i, "B").Value) = LCase(customList(j)) Then
                wsName.Cells(i, helperColumn).Value = j
            End If
        Next i
    Next j

    ' Sort the range based on the helper column
    With wsName.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=wsName.Range(wsName.Cells(3, helperColumn), wsName.Cells(lastRow(wsName), helperColumn)), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=Join(customList, Chr(1)), DataOption:=xlSortNormal
        .SetRange sortRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    ' Clean up: remove the helper column
    wsName.Columns(helperColumn).Clear

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