Application.DecimalSeparator issue

Question

My region uses "," as decimal separator. When I run the following code in Excel 2013 to import a number that uses "." as decimal separator I get no errors.

Dim strGetResult As String
strGetResult = httpObject.responseText
strGetResult = Replace(strGetResult, ".", Application.DecimalSeparator)

But when I try this very same code in Excel 2010 that also uses "," as the decimal separator I don´t get the same result. Debugging shows that Application.DecimalSeparator = "." That makes no sense since numbers are input with "," separator in that Excel.

Any idea why this is happening?

Answer 1

I had the same issue and managed to find the answer in this thread.

Application.DecimalSeparator is only relevant if the user doesn't have the UseSystemSeparators option checked.

E.g., if Application.UseSystemSeparators = True, the value of Application.DecimalSeparator doesn't reflect what Excel actually uses. It would have been nice if this was described in the MSDN docs for Application.DecimalSeparator, but it isn't.

The mrexcel thread also contains an elegant solution; instead of calling Application.DecimalSeparator, create a function

Public Function GetDecimalSeparator()

    GetDecimalSeparator = Mid(Format(1000, "#,##0.00"), 6, 1)

End Function

and call that instead. I used this approach and it seems to work fine.

Answer 2

My region also uses "," as decimal separator and "." for thousands separator. I also had similar problems in the past. Unfortunately, I was unable to repeat the error now, but I can recall that changing the decimal separator of Excel, didn't fix the problem either.

The only workaround I was able to find (which I didn't really like) was to switch decimal and thousands separators by code at Workbook_Open and change them back to the original at Workbook_BeforeClose. The good part of it was that the workbook continued to show the original separators, while the code used the changed ones. This way the user wouldn't see any difference at all.

I guess you could use this technique to change regional settings to the ones used by httpObject, just for the import procedure and eliminate the Replace part of your code.

Original solution was found here: http://www.xtremevbtalk.com/archive/index.php/t-138511.html.

Put the code below in a standard module. Call InitLocale to set decimal separator to "." and thousands separator to ",". Call RestoreLocale to restore the original setting. Two named ranges (rDecimal and rThousand) are used to store the original settings, but you might not need to do this, if you change back to the originals within the same procedure.

Declare PtrSafe Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" _
(ByVal LOCALE As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long
Declare PtrSafe Function GetUserDefaultLCID% Lib "kernel32" ()
Declare PtrSafe Function SetLocaleInfo Lib "kernel32" Alias "SetLocaleInfoA" _
(ByVal LOCALE As Long, ByVal LCType As Long, ByVal lpLCData As String) As Long
Public Const LOCALE_SCURRENCY = &H14
Public Const LOCALE_SDECIMAL = &HE
Public Const LOCALE_STHOUSAND = &HF

Public SDECIMAL As String
Public STHOUSAND As String
Public LOCALE As Long
Public rDecimal As Range, rThousand As Range


Sub FixLocale()
    Set rDecimal = ActiveWorkbook.Names("sDecimal").RefersToRange
    Set rThousand = ActiveWorkbook.Names("sThousand").RefersToRange
    Call GetLocale
    rDecimal = SDECIMAL
    rThousand = STHOUSAND
    If SDECIMAL <> "." Or STHOUSAND <> "," Then
        Call SetLocale(".", ",")
    End If
End Sub

Sub RestoreLocale()
    Set rDecimal = ActiveWorkbook.Names("sDecimal").RefersToRange
    Set rThousand = ActiveWorkbook.Names("sThousand").RefersToRange
    Call GetLocale
    If SDECIMAL <> rDecimal Or STHOUSAND <> rThousand Then
        Call SetLocale(rDecimal.Value, rThousand.Value)
    End If
End Sub

Sub GetLocale()
    Dim Symbol As String
    Dim iRet1 As Long
    Dim iRet2 As Long
    Dim lpLCDataVar As String
    Dim Pos

    LOCALE = GetUserDefaultLCID()

    iRet1 = GetLocaleInfo(LOCALE, LOCALE_SDECIMAL, lpLCDataVar, 0)
    Symbol = String$(iRet1, 0)
    iRet2 = GetLocaleInfo(LOCALE, LOCALE_SDECIMAL, Symbol, iRet1)
    Pos = InStr(Symbol, Chr$(0))
    If Pos > 0 Then
        SDECIMAL = Left$(Symbol, Pos - 1)
    Else
        MsgBox ("Error geting LOCALE")
    End If

    iRet1 = GetLocaleInfo(LOCALE, LOCALE_STHOUSAND, lpLCDataVar, 0)
    Symbol = String$(iRet1, 0)
    iRet2 = GetLocaleInfo(LOCALE, LOCALE_STHOUSAND, Symbol, iRet1)
    Pos = InStr(Symbol, Chr$(0))
    If Pos > 0 Then
        STHOUSAND = Left$(Symbol, Pos - 1)
    Else
        MsgBox ("Error geting LOCALE")
    End If

End Sub

Sub SetLocale(SymbDecimal As String, SymbThousand As String)
    LOCALE = GetUserDefaultLCID()
    iRet1 = SetLocaleInfo(LOCALE, LOCALE_SDECIMAL, SymbDecimal)
    LOCALE = GetUserDefaultLCID()
    iRet1 = SetLocaleInfo(LOCALE, LOCALE_STHOUSAND, SymbThousand)
End Sub

Answer 3

As stated by GodSmith, if Application.UseSystemSeparators = True then Application.DecimalSeparator returns incorrect values. But you can use:

Application.International(xlDecimalSeparator)

to find the actual decimal separator.