Sorting worksheets in Excel with VBA using names as dates

Question

I am writing a custom sorting procedure for my Excel spreadsheet that has at least 3 worksheets. On first position I put the worksheet called "Summary", on second goes "Data" and the rest are worksheets whose names are dates ex "17.03.2011", "20.03.2011" etc. Those need to be sorted chronologically.

Here is what I have so far, the script stops with an "Object Required" error on line with the DateDiff() and I have no idea why: After correcting the code below I am still having trouble in making the thing sort in the right order. Can anyone suggest a way to compare and move around the sheets?

Public Sub ssort()
sSummary.Move before:=Worksheets.Item(1)
sData.Move after:=sSummary
Dim i, n As Integer
Dim diff As Long
Dim current, other As Worksheet

For i = 1 To Worksheets.Count
    Set current = Worksheets.Item(i)
    If current.Name <> sData.Name And current.Name <> sSummary.Name Then

        For n = i + 1 To Worksheets.Count
            Set other = Worksheets.Item(n)
            diff = DateDiff(DateInterval.day, Format(current.Name, "dd.mm.yyyy"), Format(other.Name, "dd.mm.yyyy"))
            If diff > 0 Then
                current.Move before:=other
                Debug.Print "Moving " & current.Name & " before " & other.Name
            ElseIf diff < 0 Then
                current.Move after:=other
                Debug.Print "Moving " & current.Name & " after " & other.Name
            End If
        Next n

    End If
Next i
End Sub

~~I think I either don't understand DateDiff() or Format(), could anyone please shed some light on this?~~

After modifying code from an online example here http://www.vbaexpress.com/kb/getarticle.php?kb_id=72 to use the datediff for comparison, I came up with this solution which works as intended:

Sub sort2()
sSummary.Move before:=Worksheets.Item(1)
sData.Move after:=sSummary
Dim n As Integer
Dim M As Integer
Dim dsEnd, lowest As Integer
Dim dCurrent() As String
Dim dOther() As String
Dim diff As Long
dsStart = 3
dsEnd = Worksheets.Count

For M = dsStart To dsEnd
    For n = M To dsEnd
        If Worksheets(n).Name <> "Summary" And Worksheets(n).Name <> "Data" And Worksheets(M).Name <> "Summary" And Worksheets(M).Name <> "Data" Then
            dCurrent = Split(CStr(Worksheets(n).Name), ".")
            dOther = Split(CStr(Worksheets(M).Name), ".")
            diff = DateDiff("d", DateSerial(dCurrent(2), dCurrent(1), dCurrent(0)), DateSerial(dOther(2), dOther(1), dOther(0)))
            If diff > 0 Then
                Worksheets(n).Move before:=Worksheets(M)
            End If
        End If
    Next n
Next M

End Sub

Hi! Post the complete code. What is sSummary at the beginning? — Hossein
– Hossein, Commented Mar 25, 2011 at 17:01
Its a part of a larger codebase, posting all would be too much. sSummary and sData are global variables providing access to "Summary" and "Data" worksheets. — Dreen
– Dreen, Commented Mar 29, 2011 at 10:27

Jean-François Corbett · Accepted Answer · 2011-03-25 20:14:53Z

1

The DateDiff function requires the two date arguments to be of Variant (Date) type. Instead you're giving it two String arguments, which is what the Format function returns.

You need to convert each of the Strings to Variant (Date). This can be done like this:

strDate = current.Name ' String: "20.03.2011"
aintDateElements = Split(strDate, ".") ' Array: {2001, 03, 20}
varDate = DateSerial(aintDateElements(2), aintDateElements(1), 
    aintDateElements(0)) ' Variant (Date)

There are other ways of doing this conversion, but I find that this is the way that least often gives unexpected results!

edited Mar 25, 2011 at 20:14

answered Mar 25, 2011 at 19:45

Jean-François Corbett

38.7k30 gold badges145 silver badges192 bronze badges

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

1 Comment

Dreen Over a year ago

Thanks, this solves the problem of comparing dates and getting an integer difference.

Lance Roberts · Accepted Answer · 2011-03-25 17:53:20Z

1

If you took this code off of the web, be aware that DateInterval isn't a native Excel object or a VBA object, it's a .Net object. You could just substitute "d" for "DateInterval.day".

diff = DateDiff("d", Format(current.Name, "dd.mm.yyyy"), _
                Format(other.Name, "dd.mm.yyyy"))

edited Mar 25, 2011 at 17:53

answered Mar 25, 2011 at 17:47

Lance Roberts

22.9k32 gold badges115 silver badges132 bronze badges

Comments

Tiago Cardoso · Accepted Answer · 2011-03-25 17:00:23Z

0

If you're getting error messages in Format/Datediff calls, try split them into separated statements. You'll see where the problem lies.

Example:

        dtStart = CDate(Format(current.Name, "dd.mm.yyyy"))
        dtEnd = CDate(Format(other.Name, "dd.mm.yyyy"))

        diff = DateDiff("d", dtStart, dtEnd)

answered Mar 25, 2011 at 17:00

Tiago Cardoso

2,1061 gold badge16 silver badges34 bronze badges

Collectives™ on Stack Overflow

Sorting worksheets in Excel with VBA using names as dates

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related