My Excel is set in English for everything (formula, interface, guide). My Windows is set with "," for decimal symbol and with "." for Digit grouping symbol in regional settings, as I live in Italy.
If I write this code:
Dim v As Variant
v = CDec("12345678901234567000,123456789")
v = v + 50
result is displayed as "12345678901234567050,123456789" (Variant/Decimal) in Locals window. Same if I msgbox it.
If I use "." instead than ",", result is 12345678901234567000123456839.
In VBA, when writing numbers (in form of numbers, not strings), I must use the english syntax i.e. "." for decimal symbol.
I believe that that code of my example would run wrongly by a Windows with English regional settings.
How can I (modify it to) make it run correctly with any regional settings?
The C* conversion functions (CInt, CLng, CStr etc) are all designed to work in the current locale of the computer. They will use the current decimal separator, so you are correct in assuming that CDec will fail to correctly process a hardcoded , on a system with a different decimal dot.
On contrary, Str and Val always work with English separators, but they do not support Decimal.
So one option coming to mind is to obtain the decimal dot at runtime:
Dim v As Variant
v = CDec("12345678901234567000" & Application.International(xlDecimalSeparator) & "123456789")
It should be noted however that if Application.UseSystemSeparators is False and Application.DecimalSeparator has been changed, then Application.International(xlDecimalSeparator) will return that changed separator, not the one coming from the computer locale. So do not use this method if you cannot guarantee that UseSystemSeparators is True.
Another option is to express the decimal position in the form of division by a power of ten, which is fine with the precise fixed-point Decimal data type:
Dim v As Variant
v = CDec("12345678901234567000123456789") / CDec("1000000000")
Yet another option is to have a custom "CDec" that explicitly works in a certain locale, and always hardcode the strings in that locale:
Option Explicit
#If VBA7 Then
Private Declare PtrSafe Function VarDecFromStr Lib "OleAut32.dll" (ByVal strIn As LongPtr, ByVal lcid As Long, ByVal dwFlags As Long, ByRef pdecOut As Variant) As Long
#Else
Private Declare Function VarDecFromStr Lib "OleAut32.dll" (ByVal strIn As Long, ByVal lcid As Long, ByVal dwFlags As Long, ByRef pdecOut As Variant) As Long
#End If
Private Const LOCALE_INVARIANT As Long = &H7F&
Private Const S_OK As Long = &H0
Public Function ParseDecimalFromEnUsString(ByVal s As String) As Variant
Dim hr As Long
hr = VarDecFromStr(StrPtr(s), LOCALE_INVARIANT, 0, ParseDecimalFromEnUsString)
If hr <> S_OK Then
Err.Raise 5, , "Cannot parse the string. Error " & Hex$(hr)
End If
End Function
? ParseDecimalFromEnUsString("12345678901234567000.123456789")
12345678901234567000,123456789
? TypeName(ParseDecimalFromEnUsString("12345678901234567000.123456789"))
Decimal
(For a version of this code with more control over what the string is allowed to contain, see revision 3 of this answer. The parameter that receives NUMPRS_STD is the one to change.)