Sorting worksheets in Excel with VBA using names as dates

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 anyo开发者_开发百科ne 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

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!

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

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)