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?

Use `Criteria1:=TRUE` instead of `Criteria1:="TRUE"`? (AFAIK, VBA uses English for its constants, methods, properties, etc, no matter what language version is being used. But if you are having to cast a `String` to a `Boolean` within Excel, it will use the language applicable to that version of Excel.) — YowE3K, May 16 '17 at 08:29
That did not appear to work. I've tried `Criteria1:=TRUE` which did not work. I thought it could be that VBA thinks TRUE would be a variable name. So then I set a variable `Tr=TRUE` followed by `Criteria1:=Tr`, but that also did not work — Robin Kramer-ten Have, May 16 '17 at 08:34
If there is a formula for column `lCol` then perhaps you can make it return a `1` or `0` outcome (e.g. `=IF(foo,1,0)`) as a workaround? — Robin Mackenzie, May 16 '17 at 08:39
Do the values in the cells you are filtering on containing a boolean value (True/False) or do they contain a text version of True/False? To find out, try entering a formula of `=A1*1` (if the value you want to check is in A1) - if it says `1` then the value is a boolean `TRUE` - if it says `#VALUE!` then the value is text. — YowE3K, May 16 '17 at 08:39
@YowE3K Boolean values idd. 1*Cell returned 1. Could it have something to do with the notation of the autofilter? Many criteria need to be written between `"..."` — Robin Kramer-ten Have, May 16 '17 at 08:43
Actually, I just tried a filter and it didn't care less whether the values were text or boolean, so it appears the filter is converting the criteria to text before filtering anyway. Probably best to go with the suggestion from @RobinMackenzie. — YowE3K, May 16 '17 at 08:45

score 3 · Accepted Answer · answered May 16 '17 at 09:08

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:

score 0 · Answer 2 · answered Aug 07 '19 at 20:03

0

Try this:

Criteria1:=CStr(True)

answered Aug 07 '19 at 20:03

Dean Meyer

31
1

score 0 · Answer 3 · edited Jun 06 '20 at 17:27

The other answers did not work for me in Russian Excel, where I was trying to do some coding independent of language. In the end, I had to utilize a set of two cells where I manually put TRUE and FALSE values (ИСТИНА and ЛОЖЬ in Russian) and I reference their .Text (this is important!) instead of .Value2 in the Criteria. It seems like the issue is that Criteria uses .Text from cells in the filter which may not be equal to the text True and False.

This solution is far from perfect, obviously, but, so far it's the only way I've found. It's good that Excel stores boolean values as 0 and 1: if you open the Workbook as a zip file, you can see files like sheet1.xml which will have entries like this for boolean cells:

<c r="B5" s="6" t="b">
<v>0</v>
</c>

t seems to represent the type of the value—in this case b, which implies boolean.

Apply boolean autofilter criteria to different language

3 Answers3