Table can be filtered on date from the GUI, but not VBA

Question

I have a table, where one of the columns is filled with dates. I want to add dynamic sorting on this list every time it is opened, so I made a macro to trigger a date filter update on Workbook_Open.

The problem is that the filter does not show any results when entered through VBA, even if I manually verify in the GUI that the code has entered the right dates. What's more, if I simply press Enter after verifying the dates in the GUI (that is, not changing anything), suddenly the filter works.

Here is the code, the out-commented lines are things I have tried. The last line is the EXACT code the macro-recorder gave me when I recorded the GUI-process (the manual process displaying the desired results while the code for it does not):

Sub FixSortings
    tbl.Sort.SortFields.Clear
    tbl.Sort.SortFields.Add Key:=Range("Tabell2[[#All],[Ref dato]]"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

    With tbl.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    tbl.ShowAutoFilter = False

    'tbl.Range.AutoFilter Field:=9, Criteria1:=">=" & CStr(Format(DateAdd("m", -6, Now()), "dd.mm.yy")), Operator:=xlAnd, Criteria2:="<=" & CStr(Format(Now(), "dd.mm.yy"))
    'tbl.Range.AutoFilter Field:=9, Criteria1:=">=" & CStr(Format(DateAdd("m", -6, Now()), "mm.dd.yy")), Operator:=xlAnd, Criteria2:="<=" & CStr(Format(Now(), "mm.dd.yy"))
    'tbl.Range.AutoFilter Field:=9, Criteria1:=">=22.08.2016", Operator:=xlAnd, Criteria2:="<=22.02.2017"
    ActiveSheet.ListObjects("Tabell2").Range.AutoFilter Field:=9, Criteria1:=">=22.08.2016", Operator:=xlAnd, Criteria2:="<=22.02.2017"
End Sub

The tbl declaration is correct, and I know that for a fact because the sort is added as should.. and the filters are added as well in some way (since I can find the entered dates when I inspect the table from the dropdown buttons in the GUI), it just seems that while the criteria for the filter are added, it isn't executed... or something of the sort.

I have an idea that it may be related to the localization of date formats. You can see in my code that I am using a European dateformat (dd.mm.yy), and I have previously had issues with datestamps in VBA because it seems that VBA prefers US date format.

However, as you can also see in my code, I have tried applying the mm.dd.yy format - with no success.

Answer 1

The answer seems to be that VBA is really particular about its dateformats.

This code works, when none of the ones in the question does.

tbl.Range.AutoFilter Field:=9, _
    Criteria1:=">=" & CStr(Format(DateAdd("m", -6, Now()), "yyyy-mm-dd")), _ 
    Operator:=xlAnd, _
    Criteria2:="<=" & CStr(Format(Now(), "yyyy-mm-dd"))

As a closer, I can only conclude that I get no results using the previously tried code because VBA doesn't always play well with other dateformats, but it willingly entered the strings into the table anyway. When I then inspect the table's filter using the GUI and press Enter, Excel recognizes the dateformat because it's not as difficult as the VBA engine, and wham, filter applied successfully.