VBA:显示带点而不是逗号的小数 [英] Vba: display decimals with point and not coma

问题描述

我想在宏中用点而不是逗号显示所有数字

I would like to have all numbers in a macro displayed with point and not coma

对于实例，该显示为"0,03"，但我希望显示为"0.03":

For Instance This display "0,03" but I would like "0.03":

Dim MyNumber As Single
MyNumber = 0.03
MsgBox (MyNumber)

我尝试了一组 不 有效的代码:

I have tried a set of codes that does not work:

• 这仍然显示昏迷:

• 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, ",", "."))

谢谢！

推荐答案

第一步，必须声明Kernel32动态链接库中的以下三个函数:

As a first step, the three functions bellows from Kernel32 Dynamic-link library have to be declared:

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

• 第一个将获得用户默认ID
• 第二个将有助于获取当地人的价值(并因此恢复)
• 第三个将有助于设置当地人的价值

小数的本地类型值为14(有些喜欢写& HE-十六进制E-)

The local type value for the decimals is 14 (some likes to write &HE - hexadecimal 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

这篇关于VBA:显示带点而不是逗号的小数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！