VBA Double as String 带逗号而不是点
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27905412/
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 Double as String with comma and not point
提问by mrbela
I am searching for a solution to convert a double to a string, but the string should have a comma before the decimal place, not a point.
我正在寻找一种将 double 转换为字符串的解决方案，但该字符串的小数位前应该有一个逗号，而不是一个点。
"One and a half" should look that way 1,5 (german notation).
“一个半”应该看起来像 1,5（德语符号）。
Thanks for your help!!
谢谢你的帮助！！
回答by Bathsheba
Unfortunately in VBA, you can't easily write locale-independent code. That is, you can't specify a locale when you take a CStrcast.
不幸的是，在 VBA 中，您无法轻松编写与语言环境无关的代码。也就是说，您在进行CStr演员表时不能指定语言环境。
One work around is to convert a double like 0.5 to a string and see what you end up with. If you end up with 0,5then you're in German (etc.) locale, and you don't need to do anything else.
一种解决方法是将像 0.5 这样的 double 转换为字符串，然后看看你最终会得到什么。如果你结束了，0,5那么你就在德语（等）语言环境中，你不需要做任何其他事情。
If you end up with 0.5then you know you need to make a conversion. Then you just need to traverse your string, replacing dots with commas and vice versa (the vice versa bit is important in case your string has thousands delimiters). You can use Replacefor that.
如果你最终得到，0.5那么你知道你需要进行转换。然后你只需要遍历你的字符串，用逗号替换点，反之亦然（如果你的字符串有数千个分隔符，反之亦然位很重要）。你可以用Replace它。
回答by Jeanno
A combination of CStr and Replace will do the job.
CStr 和 Replace 的组合将完成这项工作。
Function Dbl2Str(dbl As Double) As String
Dbl2Str = Replace(CStr(dbl), ".", ",")
End Function
回答by user3479671
something like this then
这样的事情然后
Dim somestring As String
Dim someDec As Double
someDec = 1.5
somestring = CStr(someDec)
somestring = Replace(somestring, ".", ",")
MsgBox (somestring)
回答by fortuna
Following RubberDuck comment I ended up with this:
在RubberDuck评论之后，我最终得到了这个：
Function DblToStr(x As Double)
DblToStr = CStr(x)
If (Application.ThousandsSeparator = ".") Then
DblToStr = Replace(DblToStr, ".", "")
End If
If (Application.DecimalSeparator = ".") Then
DblToStr = Replace(DblToStr, ".", ",")
End If
End Function
回答by Top-Master
I checked the other answers but ended up writing my own solution to convert user inputs like 1500.5into 1,500.50, using below code:
我检查了其他的答案，但最终写我自己的解决方案，像转换用户输入1500.5到1,500.50，使用下面的代码：
'
' Separates real-numbers by "," and adds "." before decimals
'
Function FormatNumber(ByVal v As Double) As String
Dim s$, pos&
Dim r$, i&
' Find decimal point
s = CStr(v)
pos = InStrRev(s, ".")
If pos <= 0 Then
pos = InStrRev(s, ",")
If pos > 0 Then
Mid$(s, pos, 1) = "."
Else
pos = Len(s) + 1
End If
End If
' Separate numbers into "r"
On Error Resume Next
i = pos - 3
r = Mid$(s, i, 3)
For i = i - 3 To 1 Step -3
r = Mid$(s, i, 3) & "," & r
Next i
If i < 1 Then
r = Mid$(s, 1, 2 + i) & "," & r
End If
' Store dot and decimal numbers into "s"
s = Mid$(s, pos)
i = Len(s)
If i = 2 Then
s = s & "0"
ElseIf i <= 0 Then
s = ".00"
End If
' Append decimals and return
FormatNumber = r & s
End Function
回答by Gary's Student
Select the cells you wish to convert and run this small macro:
选择要转换的单元格并运行这个小宏：
Sub changeIT()
For Each r In Selection
t = r.Text
If InStr(1, r, ".") > 0 Then
r.Clear
r.NumberFormat = "@"
r.Value = Replace(t, ".", ",")
End If
Next r
End Sub
Only those cells with "."in them will change and they will be Stringsrather than Doubles
只有那些带有“.”的单元格 他们会改变，他们将是字符串而不是双打
