vba 使用VBA检测计算机使用的十进制符号

Using VBA to detect which decimal sign the computer is using

提问by Wai Wong

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

是否可以使用 VBA 来检测计算机上正在使用哪个十进制符号？

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.

我有一个宏脚本，可以向 Excel 工作表添加条件格式。问题是目标计算机可能同时使用两个十进制符号。所以我想让脚本适用于所有计算机。

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

采纳答案by GSerg

I didn't actually know the Formulas in FormatConditionsaccept localized formulas. In other places you have a choice between Formulaand FormulaLocal.

我实际上并不知道接受本地化公式中的Formulas FormatConditions。在其他地方，您可以在Formula和之间进行选择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.DecimalSeparatorand Application.International(xlDecimalSeparator)actually behave.

请注意：
事实证明，这部分过于简单到了错误的地步。请参阅另一个答案（实际上应该是已接受的答案）以了解行为方式Application.DecimalSeparator和Application.International(xlDecimalSeparator)实际行为。

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

要简单地回答问题，您可以使用Application.International(xlDecimalSeparator)或 简单Application.DecimalSeparator地知道分隔符。

---

---

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

但是对于非平凡的公式，将基于英语语言环境的不变公式分配给Formula隐藏单元格的属性可能更容易，然后FormulaLocal从该单元格中读取并将其用于FormatConditions. Excel 将为您完成所有转换。

回答by alfromFR29

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

关于上面的答案，重要的是要知道Application.DecimalSeparator并且Application.International(xlDecimalSeparator)不要以相同的方式行事：

• Application.DecimalSeparatorwill 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)

• Application.DecimalSeparator即使 Excel 被告知使用系统分隔符（来自 Windows 区域设置），也将始终输出在 Excel 选项中选择的小数点分隔符
• Application.International(xlDecimalSeparator)将输出 Excel 使用的实际小数点分隔符，无论它来自 Windows 设置（when Application.UseSystemSeparators = True）还是来自 Excel 选项（when Application.UseSystemSeparators = False）

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

因此，我强烈建议始终使用Application.International(xlDecimalSeparator).

回答by SBI

You can use the DecimalSeparatorproperty.

您可以使用DecimalSeparator属性。

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

Application.DecimalSeparator然后返回由运行 excel 的语言环境定义的小数点分隔符。

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

附带说明：即使有可能，也建议不要更改这一点，而是根据您的需要利用它。

回答by Erik A

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

对于 Excel 以外的应用程序，已接受答案中的解决方案不可用。

Instead, you can use Formatto retrieve the decimal separator: an unescaped dot in Formatgets replaced by the current decimal separator.

相反，您可以使用Format来检索小数点分隔符：未转义的点 inFormat被当前的小数点分隔符替换。

DecimalSeparator = Format(0, ".")

You can also look up the decimal separator from the registry

您还可以从注册表中查找小数点分隔符

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

回答by Marcelo Scofano

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:

我的 2 美分在这里混合了 Excel 答案和来自 Erik A. 的令人敬畏的注册表技巧。但我想在这个游戏中加入 Word，因为我经常使用 Word/Outlook 自动化：

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

回答by Elivagner

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.

您还可以将计算得出的已知数字转换为已知字符串，然后提取分隔符。在此示例中，1/2 计算结果为三个字符的字符串“0.5”或“0,5”。Mid 函数从字符串中的位置 2 中提取 1 个字符。

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