Vba：显示小数点而不是昏迷
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42532857/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Vba: display decimals with point and not coma
提问by Jonathan
I would like to have all numbers in a macro displayed with point and not coma
我想让宏中的所有数字都显示为点而不是昏迷
For Instance This display "0,03" but I would like "0.03":
例如此显示“0,03”但我想要“0.03”：
Dim MyNumber As Single
MyNumber = 0.03
MsgBox (MyNumber)
I have tried a set of codes that does notwork:
我已经尝试了一组，它的代码无法正常工作：
this still displays the Coma:
Application.DecimalSeparator = "."this still displays the Coma and does not apply to the whole macro
MyNumber = Format(MyNumber, "##0.00")this displays dot instead of coma but does not apply to the whole macro
MsgBox (Replace(MyNumber, ",", "."))
这仍然显示昏迷：
Application.DecimalSeparator = "."这仍然显示昏迷并且不适用于整个宏
MyNumber = Format(MyNumber, "##0.00")这显示点而不是昏迷，但不适用于整个宏
MsgBox (Replace(MyNumber, ",", "."))
Thank you!
谢谢！
采纳答案by Jonathan
As a first step, the three functions bellows from Kernel32 Dynamic-link library have to be declared:
作为第一步，必须声明来自 Kernel32 动态链接库的三个函数：
Private Declare Function GetUserDefaultLCID% Lib "kernel32" ()
Private Declare Function GetLocaleInfoA Lib "kernel32" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long
Private Declare Function SetLocaleInfoA Lib "kernel32" ( ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String) As Boolean
- The first will get the user default id
- The second will help to get the value of the locals (and therefore restore)
- The third will help to set the value of the locals
- 第一个将获得用户默认 id
- 第二个将有助于获得当地人的价值（并因此恢复）
- 第三个将有助于设置当地人的价值
The local type value for the decimals is 14 (some likes to write &HE - hexadecimal E -)
小数的本地类型值为 14（有些人喜欢写 &HE - 十六进制 E -）
Then the program has to look like this:
然后程序必须是这样的：
' record the settings in the variable LocalSettingsDecimal
Dim LocalSettingsDecimal As String
Dim Buffer As String
Buffer = String(256, 0)
Dim le As Integer
le = GetLocaleInfoA(GetUserDefaultLCID(), 14, Buffer, Len(Buffer))
LocalSettingsDecimal = Left(Buffer, le - 1)
' force decimal settings to '.'
Call SetLocaleInfoA(GetUserDefaultLCID(), 14, ".")
' body
Dim MyNumber As Single
MyNumber = 0.03
MsgBox (MyNumber)
' set back the decimal settings
Call SetLocaleInfoA(GetUserDefaultLCID(), 14, LocalSettingsDecimal)
Let's note that, unfortunately, In case the program body fail, the settings are not restored... but this still answer the issue
请注意，不幸的是，如果程序主体失败，则不会恢复设置……但这仍然可以解决问题
回答by Ron Rosenfeld
This is easier said than done. In the VBA editor, the decimal separator is the dot. However, the MsgBox function (and the Format function) will use the windows regional settings, and not the Excel settings, to format its results.
这说起来容易做起来难。在 VBA 编辑器中，小数点分隔符是点。但是，MsgBox 函数（和 Format 函数）将使用 Windows 区域设置而不是 Excel 设置来格式化其结果。
In order to have the MsgBox display a number using format settings of your choice, you need to create a string that has the value formatted as you want.
为了让 MsgBox 使用您选择的格式设置显示数字，您需要创建一个字符串，该字符串的值按您的需要格式化。
Here is one way of doing that:
这是这样做的一种方法：
Option Explicit
Sub dural()
Dim S As String
Dim D As Double
Const myDecSep As String = "."
D = 1234.56
S = Format(D, "0.00") 'will format using the system separator
S = Replace(S, Application.DecimalSeparator, myDecSep)
MsgBox S
End Sub
Note that if you want to use both decimal and thousands separators, and if you are interchanging, for example, the comma and the dot, you need to do this twice so as not to replace all your commas with dots, or vice-versa
请注意，如果您想同时使用小数分隔符和千位分隔符，并且如果您要互换，例如逗号和点，则需要执行此操作两次以免将所有逗号替换为点，反之亦然
Option Explicit
Sub dural()
Dim S As String
Dim D As Double
Const myDecSep As String = "."
Const myThousSep As String = ","
D = 1234.56
S = Format(D, "#,##0.00")
S = Replace(S, Application.DecimalSeparator, Chr(1))
S = Replace(S, Application.ThousandsSeparator, Chr(2))
S = Replace(S, Chr(1), myDecSep)
S = Replace(S, Chr(2), myThousSep)
MsgBox S
End Sub
回答by Crayons
Most likely your Excel settings are causing a conflict.
很可能是您的 Excel 设置导致了冲突。
1. On the File tab, click the Options button
2. Click the Advanced tab
3. Clear/Uncheck the "Use system separators" option.
Then try your code.
然后试试你的代码。
