Sorting an one dimensional array of dates not working reliable-CodePudding

I have issues sorting an array containing only date values.

I use this sorting function, giving me back the latest date in the upper bound of the array:

Function vba_sort_array_a_to_z(ByRef myArray)
    Dim i As Integer
    Dim j As Integer
    Dim Temp As String
    
    'sorting array from A to Z
    For i = LBound(myArray) To UBound(myArray)
        For j = i   1 To UBound(myArray)
            If UCase(myArray(i)) > UCase(myArray(j)) Then
                Temp = myArray(j)
                myArray(j) = myArray(i)
                myArray(i) = Temp
            End If
        Next j
    Next i
End Function

Dummy array looks like this, expecting "12.04.2022" to be sorted to the upper bound of the array:

Before sorting:

Sub sortThisArray()
    Dim vDate() As Date: ReDim vDate(0 To 2)
    vDate(0) = "25.03.2022"
    vDate(1) = "12.04.2022"
    vDate(2) = "14.02.2022"
    Call vba_sort_array_a_to_z(vDate)
    Stop
End Sub

After sorting is done:

I assume the sort algorithm is taking the first indicators into account, which would be the day of the date in this case. So "12", "14" and "25". But I need a sorting tool to look at the whole date, not only parts of it.

The only idea I had - and I am struggling being happy with it - is to manipulate the whole array containing the dates by creating a temp array (as long) and insert the date values in the format YYYYMMDD to the temp array, then sorting that array and transfering the values back to another array and reformat it again in the desired target date format. But this seems pretty unefficient to me. Result of the sorted temp array would look like this:

Is there a way to achieve this with another approach? Thre are many questions out there like this, but none solved this exact issue. Thanks for your help.

If this is the only solution, I am thinking of creating a new function out of it, which handles only the sorting of dates.

CodePudding user response：

Your problem is that you convert the dates into strings before comparing them in your sort routine. This routine was probably written to sort strings and is using the function UCase that converts a string into uppercases (so that string are sorted case-independently).
So what happens in the background is that the date (which is internally a number) is converted into a string (a series of bytes) and these strings are used when you compare the values. And the string 25.03.2022 is larger than the string 12.04.2022 (because the character 2 is larger than the character 1).

Quick fix would be to remove the Ucase-function:

If myArray(i) > myArray(j) Then

However, if you want to use the sorting routine also for strings, you could do something like

For i = LBound(myArray) To UBound(myArray)
    For j = i   1 To UBound(myArray)
        Dim isLarger As Boolean
        If VarType(myArray(i)) = vbString Then
            isLarger = UCase(myArray(i)) > UCase(myArray(j))
        Else
            isLarger = myArray(i) > myArray(j)
        End If
        
        If isLarger Then
            Temp = myArray(j)
            myArray(j) = myArray(i)
            myArray(i) = Temp
        End If
    Next j
Next i