Forum Discussion
International nightmare with boolean inputs
Update: The investigation gets weirder by the minute. I skipped a few details in the post above, one being that the flag was also passed thru some VBA code. I could not understand why the VBA code did not puke. And I just ran a test on a German computer:
Public Function haveFun(flag as Variant) as Variant
if flag then
haveFun = "Hello"
else
haveFun = "world"
End Function
I can call =haveFun(WAHR) or =haveFun(TRUE) and get "Hello" in both cases. That is, VBA is indifferent to the input language used.
Do the same thing in native Excel - =if(C5;"Hello";"world") - and we get #WERT! when C5 contains TRUE.
Extraordinary.
I am looking at multiplying the VBA input *1 to assure symmetric behaviour. TRUE*1 blows up in German Excel.
And reflecting on this some more, it is actually the return value from a VBA function that popped up as "TRUE" in the German Excel and prompted a #WERT! in a downstream WENN. There is nothing I can do to turn that into WAHR. The VBA code handles a Boolean throughout. So I might guess that there is a bug in the German Excel which does not translate a Boolean into WAHR/FALSCH but merrily returns TRUE/FALSE.
So, while there appear to be unrelated problems of translation of boolean values, the bigger problem with VBA I cannot solve. My best solution will likely be to steer clear of TRUE/FALSE altogether use only 0 and 1 going forward. Ouch.
Reflected on yesterday's disaster overnight and believe to understand the origins of the problem. Let's bear in mind a few truths about Excel:
- In Excel, it is impossible to tell a difference between a cell containing the word TRUE and the word WAHR. All cells are Variants (in VBA parlance) and can handle any type of input.
- But upon assignment, a Variant will attempt to classify its value as String/Boolean/Long/Double/... And it will retain this classification until reassigned.
- Whether Excel will classify the words TRUE or WAHR as a String or a Boolean depends entirely on the locale of the Excel in which the assignment was first performed. My English Excel will recognize TRUE as a Boolean / WAHR as a String. A German Excel the other way around.
- A Boolean is not stored as TRUE|FALSE but as 1|0 and must be rendered whenever displayed. That is, Excel never translates between locales, it just renders Booleans differently in English and German.
- This complete absence of translation affected my Data Validation. I now believe that all the entries which fail to translate from English into the local language were originally created in a non-English locale. To meet my Data Validation, the foreign user could only choose between TRUE and FALSE. Neither word could however create a Variant/Boolean in their Excel. So the entry was created as a Variant/String.
- Whenever a Variant/String is introduced into a Boolean context, the fuzzy type-handling will attempt a type-cast. In English Excel, the foreign Variant/Strings "TRUE" were hence without consequence to me; the type-cast from "TRUE" to 1 is possible. In German Excel, that same type-cast fails.
This analysis explains the odd records that will not translate. No records are ever translated. It is just that most records hold a Variant/Boolean that must be rendered (in the local language), while others are Variant/String (that are displayed as stored).
PS: None of this explains why a VBA function that returns a Variant/Boolean renders to "TRUE" in a German Excel. Or why a German VBA is happy to type-cast "TRUE" into a Variant/Boolean.