PDA

View Full Version : [SOLVED] Thousands and Decimal Separator Check



Tovarocks
11-02-2016, 09:44 AM
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
11-02-2016, 10:04 AM
Hello Tovarocks,

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

Tovarocks
11-02-2016, 10:47 AM
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
11-02-2016, 11:08 AM
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
11-02-2016, 11:09 AM
Thanks you very much for your time bro, I really appreciate it!

Leith Ross
11-02-2016, 12:10 PM
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
11-02-2016, 12:15 PM
Yeah! It works perfectly, thanks you very much Leith, you should be working for NASA.

Leith Ross
11-02-2016, 12:21 PM
Hello Tovarocks,

De nada. glad I could help.

ielucero
09-25-2019, 08:12 PM
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/office/vba/api/excel.xlapplicationinternational

beta-L-user
12-16-2021, 11:56 AM
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/office/vba/api/excel.xlapplicationinternational

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