Apply boolean autofilter criteria to different language

Question

I have a table which I filter based on a Boolean value.

Whenever the value is TRUE, I want it to show.

I use an autofilter and the following working VBA code:

lCol = tbl.ListColumns("xFilter").Index
With tbl
    If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
    .Range.AutoFilter Field:=lCol, Criteria1:="TRUE"
End With

I am working on an English instance of Excel. When I try this on an instance in Dutch I have to manually set the Criteria to WAAR (Dutch equivalent of TRUE).

I can add multiple criteria and state:

Criteria1:="WAAR", Operator:=xlOr, Criteria2:="TRUE"

However, if I would then go to Germany and Spain I would need to write:

Criteria1:="WAAR", Operator:=xlOr, Criteria2:="TRUE", Operator:=xlOr, Criteria3:="WAHR", Operator:=xlOr, Criteria4:="VERDADERO"

Is there a way to have Criteria1:="TRUE" work in any language?

Answer 1

You can try use the CBool function to avoid using a string or language-dependent value for TRUE when setting the AutoFilter.

CBool(1) should be 'true' in any locale.

Option Explicit

Sub TestLanguageIndependentBooleanForAutoFilter()

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim lCol As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set tbl = ws.ListObjects(1)

    lCol = tbl.ListColumns("xFilter").Index
    With tbl
        If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
        .Range.AutoFilter Field:=lCol, Criteria1:=CBool(1)
    End With

End Sub

Works in an Australian English locale: