VBA按CSV第二个数值排序数组失败问题排查

我尝试按CSV格式元素的第二个数值对数组进行降序排序，期望"1,7"排在首位。通过循环拆分字符串提取第二个值进行比较，理论上应把大值前置，但实际排序结果不符合预期。附上VBA代码及调试输出，请求排查错误。

原VBA代码 #

Public Function SortArray(ByRef arr() As Variant) As Variant

Dim temp As String, temparr As Variant
Dim i As Long, j As Long
Dim tempspi As String, tempspj As String

ReDim arr(5)

arr = Array("0,0", "1,7", "2,0", "3,1", "4,3", "5,1")

For i = LBound(arr) To UBound(arr) - 1
tempspi = Split(arr(i), ",")(1)
    For j = i + 1 To UBound(arr)
    tempspj = Split(arr(j), ",")(1)
        If CLng(tempspi) > CLng(tempspj) Then
            temp = arr(i)
            arr(i) = arr(j)
            arr(j) = temp
        End If
    Next j
Next i

For Each Item In arr
    Debug.Print Item
Next

SortArray = arr

End Function

调试结果 #

0,0
5,1
4,3
3,1
2,0
1,7

错误原因分析 #

1. 比较值未实时更新：外层循环开始时就固定了tempspi的值，但内层循环中arr(i)可能会被交换修改，后续比较仍使用初始值，导致逻辑混乱。
2. 排序方向颠倒：条件CLng(tempspi) > CLng(tempspj)实现的是升序排序（小值前置），但需求是降序排序，需要反向判断。

修正后的代码 #

Public Function SortArray(ByRef arr() As Variant) As Variant
    Dim temp As String
    Dim i As Long, j As Long
    Dim vali As Long, valj As Long

    arr = Array("0,0", "1,7", "2,0", "3,1", "4,3", "5,1")

    For i = LBound(arr) To UBound(arr) - 1
        ' 每次循环前重新提取当前arr(i)的第二个数值
        vali = CLng(Split(arr(i), ",")(1))
        For j = i + 1 To UBound(arr)
            valj = CLng(Split(arr(j), ",")(1))
            ' 降序逻辑：若当前i位置值小于j位置值，交换两者
            If vali < valj Then
                temp = arr(i)
                arr(i) = arr(j)
                arr(j) = temp
                ' 交换后更新vali，确保后续比较基于最新的arr(i)值
                vali = valj
            End If
        Next j
    Next i

    For Each Item In arr
        Debug.Print Item
    Next

    SortArray = arr
End Function

修正后调试输出 #

1,7
4,3
5,1
3,1
0,0
2,0

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