Thousands and Decimal Separator Check

[Tovarocks]

Hi, I need to check via VBA which is the thousands and decimals separator of the computer or excel, because I got a macro but Im not the only person that use it, I have specific setting and my coworkers has other, so I need something like this.

Sub Checkseparator ()

Dim Separator1 as String
Dim Separator2 as String

Separator1 = CheckComputer.decimalSeparator
Separator2 = CheckComputer.ThousandsSeparator

If Separator1 = "," and Separator2 = "." then

Do something

Else

Do This

Else If

End Sub

This is posible?

Thanks in advance guys.

[Leith Ross]

Hello Tovarocks,

You can use Application.DecimalSeparator And Application.ThousandsSeparator to return or change the current characters.

[Tovarocks]

I just did it, but it gives me the same decimal and thousands separator in my computer and computers of my coworkers, and we have different configuration of that.

When I do this

Dim Separator As String    
    Separator = Application.decimalSeparator

Its return the same " , " on my computer and the same in the computer of my coworkers, and I have for decimal separatos " . " and my coworkers has " , ".

What can I do.

[Leith Ross]

Hello Tovarocks,

Okay, you will need the system settings for these. Application programs can change these based on the language being used.

I will need to write a macro using API calls to get these values for you.

[Tovarocks]

Thanks you very much for your time bro, I really appreciate it!

[Leith Ross]

Hello Tovarocks,

Add a new VBA module to your workbook. Copy and paste this code into it. It returns the System settings for the decimal and thousands separators based on your locale.

Private Declare Function GetUserDefaultLCID Lib "Kernel32.dll" () As Long


Private Declare 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


Sub CheckSeparators()


    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
    
        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)


    ' Add your code from this point on ...
       
End Sub

[Tovarocks]

Yeah! It works perfectly, thanks you very much Leith, you should be working for NASA.

[Leith Ross]

Hello Tovarocks,

De nada. glad I could help.

[ielucero]

Hello. I had a problem with decimal and thousand separator and I didn't find info I needed here. Investigated and I can add some clarification here:

Application.DecimalSeparator: Decimal separator in EXCEL settings
Application.ThousandsSeparator: Thousands separator in EXCEL settings

Application.UseSystemSeparators: Here is the tricky part (at least for me) if =TRUE then the above doesn't apply to VBA variables or excel cell formats. BUT the above do not change value to be equal to system separators. So I needed other way to get system level separators, solution:

Application.International(3): Decimal separator.
Application.International(4): Zero or thousands separator.
Application.International(5): List separator.

In example:
Excel advanced settings:
Application.DecimalSeparator=","
Application.ThousandsSeparator="."

Windows Language configuration (control panel)
Decimal separator="." Application.International(3)="."
thousands separator="," Application.International(4)=","

The above 4 variables will remain un changed regardles of Application.UseSystemSeparators value.


Reference: https://docs.microsoft.com/en-us/off...ninternational

[beta-L-user]

Hello. I had a problem with decimal and thousand separator and I didn't find info I needed here. Investigated and I can add some clarification here:

Application.DecimalSeparator: Decimal separator in EXCEL settings
Application.ThousandsSeparator: Thousands separator in EXCEL settings

Application.UseSystemSeparators: Here is the tricky part (at least for me) if =TRUE then the above doesn't apply to VBA variables or excel cell formats. BUT the above do not change value to be equal to system separators. So I needed other way to get system level separators, solution:

Application.International(3): Decimal separator.
Application.International(4): Zero or thousands separator.
Application.International(5): List separator.

In example:
Excel advanced settings:
Application.DecimalSeparator=","
Application.ThousandsSeparator="."

Windows Language configuration (control panel)
Decimal separator="." Application.International(3)="."
thousands separator="," Application.International(4)=","

The above 4 variables will remain un changed regardles of Application.UseSystemSeparators value.


Reference: https://docs.microsoft.com/en-us/off...ninternational

Here is the (simple) function that converts a dot-decimal to the format which is NOW used in Excel (even you have a dot used)

Function Dot2Dec(NumberAsString As String)

System = Application.UseSystemSeparators
If System = True Then
DecSep = Application.International(xlDecimalSeparator)
ThousendSep = Application.International(xlThousandsSeparator) 'not used;for information only
Else
DecSep = Application.DecimalSeparator
ThousendSep = Application.ThousandsSeparator 'not used;for information only
End If

Dot2Dec = Replace(NumberAsString, ".", DecSep)

End Function

If you further insert replacing the thousandsSeparator take care that if "," is thousandsSeparator not to change to "." before replacing the "." as DecimalSeparator. You need to split the "Replace" or take a temporary thousandsSeparator.
Enjoy

beta-L-user