CODEWITHSUNDEEP
excelvba
Wai Wong
Wai Wong

Reputation: 2843

Using VBA to detect which decimal sign the computer is using

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

Upvotes: 19

Views: 37149

Answers (6)

Elivagner
Elivagner

Reputation: 27

You can also convert a known number derived from a calculation to a known string and thenextract the separator. In this example, 1/2 evaluates to a three-character string "0.5" or "0,5". The Mid function extracts 1 character from positon 2 in the string.

Public Function DecimalSeparator() As String
    DecimalSeparator = Mid$(1 / 2, 2, 1)
End Function

Upvotes: 0

Marcelo Scofano Diniz
Marcelo Scofano Diniz

Reputation: 669

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

Upvotes: 0

GSerg
GSerg

Reputation: 78185

I didn't actually know the Formulas in FormatConditions accept localized formulas. In other places you have a choice between Formula and FormulaLocal.

Please note:
This part turned out to be oversimplified to the point of being wrong. Please refer to the other answer (which should really have been the accepted one) for how Application.DecimalSeparator and Application.International(xlDecimalSeparator) actually behave.

To simply answer the question, you can use Application.International(xlDecimalSeparator) or simply Application.DecimalSeparator to know the separator.

But for non-trivial formulas it might be easier to assign the invariant English-locale based formula to the Formula property of a hidden cell and then read FormulaLocal from that cell and use that for FormatConditions. Excel will do all the conversions for you.

Upvotes: 17

Erik A
Erik A

Reputation: 32642

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

Upvotes: 10

alfromFR29
alfromFR29

Reputation: 346

Regarding the answer above, it is important to know that Application.DecimalSeparator and Application.International(xlDecimalSeparator) do not behave the same way:

  • Application.DecimalSeparator will ALWAYS output the decimal separator chosen in Excel options even when Excel is told to use System Separators (from Windows regional settings)
  • Application.International(xlDecimalSeparator) will output whatever is the actual decimal separator used by Excel whether it comes from Windows settings (when Application.UseSystemSeparators = True) or from Excel options (when Application.UseSystemSeparators = False)

I therefore strongly recommend to always use Application.International(xlDecimalSeparator).

Upvotes: 32

SBI
SBI

Reputation: 2322

You can use the DecimalSeparator property.

Application.DecimalSeparator then returns the decimal separator defined by the locale that excel is being run with.

On a side note: It's advisable, even though it's possible, to not change this and instead leverage it to your needs.

Upvotes: 9

Related Questions