Using VBA to detect which decimal sign the computer is using

user_290413 0
Viewed 30417

Is it possible to use VBA to detect which decimal sign is being used on the computer?

I have a macro script that adds a conditional formatting to an excel sheet. The problem is that the target computers might use both decimal signs. So I want to make the script work for all computers.

The code looks like this:

With range("D" & row)
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, Formula1:="=1,01*$C$" & row, Formula2:="=0,99*$C$" & row
    .FormatConditions(1).Font.ColorIndex = 3
End With
vba excel
6 Answers

For applications other than Excel, the solution in the accepted answer is not available.

Instead, you can use Format to retrieve the decimal separator: an unescaped dot in Format gets replaced by the current decimal separator.

DecimalSeparator = Format(0, ".")

You can also look up the decimal separator from the registry

DecimalSeparator = CreateObject("WScript.Shell").RegRead("HKCU\Control Panel\International\sDecimal")
user_72968930

My 2 cents here mixing the Excel answers here and the awesome registry trick from Erik A.; but I want to include Word in this game, because I use to automate Word/Outlook a lot:

Function CorrectDecimalSeparator() As String
Dim auxCorrectListSeparator As String
    If WordIsOpen Then
        CorrectDecimalSeparator= Word.Application.International(wdListSeparator)
    ElseIf ExcelIsOpen Then
        CorrectDecimalSeparator= Excel.Application.International(xlListSeparator)
    Else
        auxCorrectListSeparator = CreateObject("WScript.Shell").RegRead("HKCU\Control Panel\International\sDecimal")
    End If
End Function
Function WordIsOpen() As Boolean
    Dim oWord As Object
    On Error Resume Next
    Set oWord = GetObject(, "Word.Application")
    On Error GoTo 0
    WordIsOpen = Not oWord Is Nothing
    Set oWord = Nothing
End Function
Function ExcelIsOpen() As Boolean
    Dim oExcel As Object
    On Error Resume Next
    Set oExcel = GetObject(, "Excel.Application")
    On Error GoTo 0
    ExcelIsOpen = Not oExcel Is Nothing
    Set oExcel = Nothing
End Function
user_27523080
Related