True False

[Kevin McCartney]

Hi,
How do I make my Excel application read a cell value which contains either
an Excel value True or False as 1 or 0. The reason being that in another
country the True or False is oftern another word e.g. in German it is Wahr or
Falsch so I can't just take the text, I need the interpreted value 1 or 0 so
that I can use it within my VB code.

I currently read the cell calue with

aryComboBox(X) = Application.Sheets(Y).Cells(rgeSearch.Row + 1 + X,
rgeSearch.column).Value

TIA
KM

 

[Iain King]

How do I make my Excel application read a cell value which contains
either

an Excel value True or False as 1 or 0. The reason being that in another
country the True or False is oftern another word e.g. in German it is Wahr or
Falsch so I can't just take the text, I need the interpreted value 1 or 0 so
that I can use it within my VB code.

the CBool(exp) function convertes exp (any non-boolean variable) into a
boolean. testing it with strings, it takes "True" and "False" (case
insensitive), and throws an exception for anything else. Since the other
listed Cx functions take locale into account, I would guess it would
compensate for language - you would have to test it.

Iain King

 

[Norman Jones]

Hi Kevin,

Would something like the following work for you?

Sub Tester02()
Dim rng As Range, rng1 As Range
Dim myVal As Variant
Dim i As Long
Dim aryComboBox As Variant

Set rng = Selection
On Error Resume Next
Set rng1 = rng.SpecialCells(xlCellTypeConstants, xlLogical)
On Error GoTo 0
'Or Set rng1 = rng.SpecialCells(xlCellTypeFormulas, xlLogical)
'Or build a union of constants and formulae

i = rng.Count
ReDim aryComboBox(1 To i)

For i = 1 To rng.Count
If Not rng1 Is Nothing Then
If Not Intersect(rng1, rng(i)) Is Nothing Then
myVal = CLng(rng(i)) * -1
End If
Else
myVal = rng(i).Value
End If
aryComboBox(i) = myVal
Next i
End Sub