VBA Sorting Comma Separated Array - Not sorting-CodePudding

I'm trying to sort this array by the second number in the CSV. The result should be "1,7" first.

I have it split by comma in the for loop, so it should be comparing 0 to 7 and listing that first.

I'm not very talented with VBA. Does anyone know what I'm doing wrong here?

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

DEBUG RESULT:

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

CodePudding user response：

The flagged optimization is outside of the j loop, so if a swap occurs it will not update to reflect the swapped-in value at index i

For i = LBound(arr) To UBound(arr) - 1
    tempspi = Split(arr(i), ",")(1)       '<<< fixes the `arr(i)` value
    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

You should move that inside of the j loop so it always reads the current value from arr(i):

For i = LBound(arr) To UBound(arr) - 1
    For j = i   1 To UBound(arr)
        tempspi = Split(arr(i), ",")(1)       '<< move here
        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