Sorting a one dimensional array of dates

Question

I want to sort an array containing only date values.

I want 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 before sorting:

Expecting "12.04.2022" to be sorted to the upper bound of the array:

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:

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".

The only idea I had 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 transferring the values back to another array and reformat it again in the desired target date format. This seems inefficient.

Result of the sorted temp array would look like this:

Is there another way to achieve this?

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

I am not sure I get your point right, but as you can see in the variable observing window that the values are formatted as "Date". So Excel knows those are date values. — smartini
– smartini, Commented Nov 11, 2022 at 9:36
I did not look to the window... Then, only eliminate UCase... — FaneDuru
– FaneDuru, Commented Nov 11, 2022 at 9:38
you were right about the UCase thing. Haven't looked at the function I found in detail. Very interesting thing regarding the UCase thing! Appreciate your help. — smartini
– smartini, Commented Nov 11, 2022 at 9:41
DOn't write your own sort code. Pick one from here vbforums.com/… — freeflow
– freeflow, Commented Nov 11, 2022 at 10:58

FunThomas · Accepted Answer · 2022-11-11 09:43:30Z

2

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

answered Nov 11, 2022 at 9:43

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

smartini Over a year ago

exactly, @FaneDuru also commented on this topic to my question. Thank you.

Collectives™ on Stack Overflow

Sorting a one dimensional array of dates

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related