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