在VBA中更改小数分隔符（不仅在Excel中） [英] Changing decimal separator in VBA (not only in Excel)

问题描述

输入A1单元格 = 1/10

运行此宏：

Sub ChangeDecimalSeparator()
    Application.DecimalSeparator = "#"
    Application.UseSystemSeparators = False
    MsgBox Range("A1").Value
End Sub

显然现在在单元格A1中可以看到十进制分隔符已更改为新的字符＃。为什么在地球VBA仍然将它看作你的默认分隔符，虽然你已经改变了（我的意思是MsgBox）。

Apparently now in cell A1 you can see that the decimal separator has been changed to new character #. Why on earth VBA still sees it as your default seperator, although you have changed it (I mean the MsgBox).

为什么我需要它？我的默认分隔符是（（逗号））。我想将数据插入到SQL中使用。 （点）。

Why I need it? My default separator is , (comma). I want to insert data to SQL which used . (dot).

推荐答案

我不能说100％的确定性，但我相信虽然 Application.DecimalSeparator 影响单元格中显示的内容，似乎 MsgBox 使用Windows区域设置默认值。

I cannot speak with 100% certainty, but I believe that while Application.DecimalSeparator affects what is displayed in the cell, it appears that MsgBox uses the Windows locale default.

如果您尝试在Windows级别更改会发生什么？此外，您确定您的SQL插入不正确解释它吗？

What happens if you try changing it at the Windows level? Also, are you certain that your SQL inserts are not interpreting it correctly?

如果需要，潜在的解决方法可能会尝试： p>

If needed, a potential workaround kludge might be try:

Dim x as Integer
Dim xs as String
x = 1.5
xs = replace(cstr(x),".", ",")
SQLString = Stuff & xs & MoreStuff

这篇关于在VBA中更改小数分隔符（不仅在Excel中）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！