CLng() and CDbl() are dropping decimals

Question

If I run CLng("22.14") in my PC's Excel VBA Editor, I get the expected result, 22. If I do this on some international colleagues' PCs, it yields 2214.

CDbl is doing a similar thing. I get 22.14 and they get 2214.

My thoughts: I hypothesize some setting defines the decimal character (and mine is rightly "." while theirs is something else). My searching didn't yield any other ideas or solutions.

Answer 1

All of the numeric conversion functions in VBA are locale aware, so they will ignore both thousands separators and currency symbols. The IsNumeric function behaves the same way:

Public Sub Example()
    'en-US locale
    Debug.Print IsNumeric("$1,1,1,1,1,")    'True
    Debug.Print CLng("$1,1,1,1,1,")         '11111
End Sub

The only host agnostic work-around that I'm aware of for dealing with this (other than not storing numbers as String data) is to bypass the built in VBA casts entirely and call the underlying conversion functions in oleaut32.dll directly with a hard-coded locale ID. For example, to get a Double from an en-US localized string:

Public Declare PtrSafe Function VarR8FromStr Lib "oleaut32" _
    (ByVal strIn As LongPtr, ByVal lcid As Long, ByVal dwFlags As Long, ByRef pdblOut As Double) As Long

Public Const EN_US As Long = 1033

Public Function DoubleFromEnUsString(converting As String) As Double
    Dim converted As Double, result As Long
    result = VarR8FromStr(StrPtr(converting), EN_US, 0, converted)
    If (result = 0) Then
        DoubleFromEnUsString = converted
    Else
        Err.Raise 5
    End If
End Function

...or a Long:

Public Declare PtrSafe Function VarI4FromStr Lib "oleaut32" _
    (ByVal strIn As LongPtr, ByVal lcid As Long, ByVal dwFlags As Long, ByRef plOut As Long) As Long

Public Const EN_US As Long = 1033

Public Function LongFromEnUsString(converting As String) As Long
    Dim converted As Long, result As Long
    result = VarI4FromStr(StrPtr(converting), EN_US, 0, converted)
    If (result = 0) Then
        LongFromEnUsString = converted
    Else
        Err.Raise 5
    End If
End Function

Example usage:

Public Sub Sample()
    Debug.Print LongFromEnUsString("12.34")     '12
    Debug.Print DoubleFromEnUsString("12.34")   '12.34
End Sub