How can I make the Excel filtering macro use the date locale correctly?

Question

I found macro code (apologies to my source, I can't find where I got this from) to do some filtering and modified it to include a date range. Here is the code snippet:

Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim copyFrom As Range
Dim lRow As Long '<~~ Not Integer. Might give you error in higher versions of excel
Dim strSearch As String
Dim startDate As Date
Dim endDate As Date

Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("Data")

strSearch = Range("client").Value
startDate = Range("start_date").Value
endDate = Range("end_date").Value

With ws1
    '~~> Remove any filters
    .AutoFilterMode = False
    lRow = .Range("A" & .Rows.Count).End(xlUp).Row
    With .Range("A1:C" & lRow)
        .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
        .AutoFilter Field:=3, Criteria1:=">=" & startDate, Operator:=xlAnd, Criteria2:="<" & endDate
        Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
    End With
    '~~> Remove any filters
    '.AutoFilterMode = False
End With

The date range I'm trying to filter on is Feb 1 to Feb 28 of this year. When I single stepped through the code and moused over the 'start_date' variable, it showed the date to be the ~~2nd of January, where in the spreadsheet its,~~ the 1st of February (which is what is in the spread sheet. When I look at the filter it creates, it correctly puts in the end date, but the start date is the 2nd of January.

How can I make this work? My locale (sensibly) starts with the smallest date unit first, that is day, month, year. Excel is biased to month, day, year.

Gary's Student Gary's Student · Accepted Answer · 2014-03-07T18:15:34

After the line of code:

startDate = Range("start_date").Value

I would like you to insert:

MsgBox startDate & vbCrLf & Range("start_date").Value & vbCrLf & Range("start_date").Text

What do you see??

EDIT:

Based on our experiment, we can force VBA to interpret the date correctly. I place the text string:

28/2/2014

in cell D9 and run this:

Sub DoWhatsRight()
    Dim s As String, d As Date
    s = Range("D9").Text
    ary = Split(s, "/")
    d = DateSerial(ary(2), ary(1), ary(0))
    MsgBox d
End Sub

Basically, compelling the macro to interpret the string as I desire!

This is only a work-around..............I don't understand why VBA is miss-behaving.

How can I make the Excel filtering macro use the date locale correctly?

1 Answers