Convert string to double independent on regional settings

string to double

Convert string to number regarless of decimal or thousands separtor.

05.11.2021, Goran Dolenc
Categories: Excel VBA
Tags: Conversion Double Local Regional Separator String

Put the following code in a separate module:

    Option Explicit

    Private Declare PtrSafe Function GetUserDefaultLCID Lib "Kernel32.dll" () As Long
    Private Declare PtrSafe Function GetLocaleInfo Lib "Kernel32.dll" Alias "GetLocaleInfoW" (ByVal Locale As Long, ByVal LCType As Long, ByRef lpLCData As Any, ByVal cchData As Long) As Long

    Public DecimalSeparator As String
    Public ThousandsSeparator As String

    Sub CheckSystemSeparators()
    ' VBA uses Window System separtors, not Excel separators (if Excel separators are set differently, this setting is ignored in VBA)

    Dim LCID As Long
    Dim ret As Long
    Dim sDecimal As String
    Dim sThousands As String

    Const LOCALE_SDECIMAL As Long = &HE
    Const LOCALE_STHOUSAND As Long = &HF

    ' Excel: File/Options/Advanced
    ' Windows: Settings/Region/Formats/Additional settings.../Numbers

        sDecimal = String(4, " ")
        sThousands = String(4, " ")
       
        LCID = GetUserDefaultLCID()
       
        ret = GetLocaleInfo(LCID, LOCALE_SDECIMAL, ByVal sDecimal, 4)
        sDecimal = Left(sDecimal, ret - 1)
       
        ret = GetLocaleInfo(LCID, LOCALE_STHOUSAND, ByVal sThousands, 4)
        sThousands = Left(sThousands, ret - 1)
       
    '    Debug.Print "Excel decimal separator: " & Application.DecimalSeparator
    '    Debug.Print "Excel thousands separator: " & Application.ThousandsSeparator
    '    '
    '    Debug.Print "Windows decimal separator: " & sDecimal
    '    Debug.Print "Windows thousands separator: " & sThousands
       
        DecimalSeparator = sDecimal
        ThousandsSeparator = sThousands

    End Sub


Than you can use it anywhere in your VBA procedure like this:

    ' data conversions, string do double
    If DecimalSeparator = "," Then
        dblStake = StringToDouble(strStake)
        dblWinAmount = StringToDouble(strWinAmount)
    ElseIf DecimalSeparator = "." Then
        dblStake = StringToDouble_1(strStake)
        dblWinAmount = StringToDouble_1(strWinAmount)
    End If


While having both conversion functions prepared like this:

    ' IF decimal separator , and thousand separator .
    Public Function StringToDouble(str As String) As Double
    ' convert string to double number
    ' example: 1,162.50 --> 1162,50
        str = Replace(str, ",", "")
        str = Replace(str, ".", ",")
        'Debug.Print str
        StringToDouble = CDbl(str)
    End Function


    ' IF decimal separator . and thousand separator ,
    Public Function StringToDouble_1(str As String) As Double
    ' convert string to double number
    ' example: 1,162.50 --> 1162.50
        str = Replace(str, ",", "")
        'Debug.Print str
        StringToDouble_1 = CDbl(str)
    End Function