How can I sort dates in an array in vba?

Question

Hi I am new to programming and just started learning VBA for excel. I have a query regarding sorting of arrays. How do I sort an array containing dates? For example if I have an array containing the dates ("23-jul-13","11-jan-10","1-may-09","3-feb-04") how do I sort this array. I have searched all over the internet for answers but could only find code for sorting numbers. I have been racking my brains on this for 2 days but can't seem to get it.

Thanks

I have the code below which takes dates from a selected column but I am getting an error whenever I run it. I have been trying to figure out what's wrong with it for 2 days now. I didn't mention this code earlier as I though it would unnnecessarily add to the confusion. The sub GetUniqueAndCount works fine but it's the sort sub which is the problem as it doesn't accept the array passed to it as an argument.

Sub GetUniqueAndCount()
Dim d As Object, c As Range, k, tmp As String

  Set d = CreateObject("scripting.dictionary")
  'I will select the column of dates
 For Each c In Selection
  tmp = Trim(c.Value)
  If Len(tmp) > 0 Then
  If Year(DateValue(Format(tmp, "dd-mmm-yy"))) = 2013 Then
  d(tmp) = d(tmp) + 1
  End If
  End If
  Next c
  i = 0
  ReDim ThisArray(UBound(d.keys)) As Date
  For Each k In d.keys
  ThisArray(i) = DateValue(Format(k, "dd-mmm-yy"))
  i = i + 1

  Next k
  Sort (ThisArray)
End Sub


Sub Sort(arr() As Date)

  Dim Temp As Date
  Dim i As Long
  Dim j As Long

  For j = 2 To UBound(arr)

  Temp = arr(j)
  For i = j - 1 To 1 Step -1
  If (arr(i) <= Temp) Then GoTo 10
  arr(i + 1) = arr(i)

  Next i
  i = 0
10  arr(i + 1) = Temp


  Next j
  End Sub

Do you have an array of Dates or an array of strings which look like dates? If they're actual dates then they should be sortable just like numbers (since excel actually stores dates as numbers) — Tim Williams
– Tim Williams, Commented Dec 21, 2013 at 17:19
Could you provide a sample of the values before formatting, that is the values as they exist in the cell? — NoChance
– NoChance, Commented Dec 21, 2013 at 20:11
yes they are as follows: 23-jul-13","11-jan-10","1-may-09","3-feb-04 — user3125707
– user3125707, Commented Dec 21, 2013 at 20:51

Siddharth Rout · Accepted Answer · 2013-12-22 01:29:51Z

7

Your Sort(arr() As Date) works fine. The problem is with this line

Sort (ThisArray)

Change it to

Sort ThisArray

Also since you are storing Dates in ThisArray, I hope you have declared it as Date?

Example

Sub Sample()
    Dim ThisArray(1 To 5) As Date

    ThisArray(1) = #12/13/2013#
    ThisArray(2) = #12/13/2012#
    ThisArray(3) = #12/13/2015#
    ThisArray(4) = #12/13/2014#
    ThisArray(5) = #12/13/2016#

    SortAr ThisArray

    For i = 1 To 5
        Debug.Print ThisArray(i)
    Next i
End Sub

Sub SortAr(arr() As Date)
    Dim Temp As Date
    Dim i As Long, j As Long

    For j = 2 To UBound(arr)
        Temp = arr(j)
        For i = j - 1 To 1 Step -1
            If (arr(i) <= Temp) Then GoTo 10
                arr(i + 1) = arr(i)
        Next i
        i = 0
10:     arr(i + 1) = Temp
    Next j
End Sub

OUTPUT

answered Dec 22, 2013 at 1:29

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

2 Comments

user3125707 Over a year ago

Hi Siddharth Rout, Thanks I figured out what the problem was and your code helped me do it. It was a very simple error. I think the Sort method is a predefined VBA function and hence was not accepting any arguments. Also I chanaged syntax from Sort (ThisArray) to SortAr ThisArray. The code is working perfectly fine. I don't know how I can thank u or give u +1 as I need a rep of 15 to do so. So sorry...

user3125707 Over a year ago

I had not noticed ur comment above the code. yes u are right the problem was this line Sort (ThisArray) as u mentioned

Nikko · Accepted Answer · 2018-03-23 14:18:38Z

0

This might help. fell free to ask a follow up question.

Sub Sort()
Dim x As Long, y As Long, z As Long
    For x = Application.WorksheetFunction.Min(Columns("M")) To Application.WorksheetFunction.Max(Columns("M"))
        For y = 1 To Worksheets("Users Info").Cells(Rows.Count, 13).End(xlUp).Row
        If Worksheets("Users Info").Cells(y, 13).Value = i Then
            z = z + 1
            Worksheets("Users Info").Cells(z, 14).Value = i
        End If
        Next y
    Next x
End Sub

edited Mar 23, 2018 at 14:18

user9107868

answered Mar 23, 2018 at 12:53

Nikko

1

1 Comment

WhatsThePoint Over a year ago

Welcome to StackOverflow: if you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar or using Ctrl+K on your keyboard to nicely format and syntax highlight it!

kadrleyn · Accepted Answer · 2021-12-12 19:48:54Z

I used different two ways to fill dates to drop-down lists on sheet as uniquelly and the sorted oldest to newest values :

First way , using Adodb Connection.
Second way , using Scripting Dictionary object and a user-defined function.

Adodb connection codes that I used :

Dim con, rs As Object, sorgu As String
Set con = CreateObject("adodb.connection")
Sheets("Page1").ComboBox1.Clear
Sheets("Page1").ComboBox2.Clear
    
    #If VBA7 And Win64 Then
con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.FullName & ";extended properties=""excel 12.0;hdr=yes"""
    #Else
con.Open "provider=microsoft.jet.oledb.4.0;data source=" & ThisWorkbook.FullName & ";extended properties=""excel 8.0;hdr=yes"""
    #End If
          
Set rs = CreateObject("adodb.recordset")
sorgu = "select Date from [Page1$] group by Date"
rs.Open sorgu, con, 1, 1
While Not rs.EOF
Sheets("Page1").ComboBox1.AddItem Format(rs("Date").Value, "dd.mm.yyyy")
Sheets("Page1").ComboBox2.AddItem Format(rs("Date").Value, "dd.mm.yyyy")
rs.movenext
Wend
rs.Close
con.Close

Source

Collectives™ on Stack Overflow

How can I sort dates in an array in vba?

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related