vba 如何修复点作为excel中的小数点分隔符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13710548/
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
How to fix dot as the decimal separator in excel
提问by user1049518
Got a tiny problem, but I can't get it right. Perhaps someone could help me out. Much appriciated.
my Regional Settings for Decimal Separator is "." (Dot).
i have an excel file, which contains decimals places in some columns. while updating the data in excel template i turned off the usersystem setting then applied DOT as the separator. While converting it to text file it is showing the comma "," as the decimal separator.
I am turning of the the user settings by using below code
有一个小问题，但我无法解决。也许有人可以帮助我。很受用。
我的十进制分隔符的区域设置是“。” （点）。
我有一个 excel 文件，其中包含某些列中的小数位。在更新 excel 模板中的数据时，我关闭了用户系统设置，然后应用 DOT 作为分隔符。将其转换为文本文件时，它显示逗号“,”作为小数点分隔符。我正在使用以下代码关闭用户设置
With Application
StrDecimal = .DecimalSeparator
StrThousand = .ThousandsSeparator
使用应用程序
StrDecimal = .DecimalSeparator
StrThousand = .ThousandsSeparator
.DecimalSeparator = "."
.ThousandsSeparator = "'"
.UseSystemSeparators = False
End With
Need to check if the Decimal Separator is not eqaul to "." then we need to force to use "." aS Decimal separator.
需要检查十进制分隔符是否不等于“。” 那么我们需要强制使用“.” aS 十进制分隔符。
Please help how to achieve this by using VBA or by using Datavalidation
Thank you very much in advance!
请帮助如何通过使用 VBA 或使用 Datavalidation 来实现这一点，
非常感谢！
回答by CuberChase
So you've got your data and are writing it to a textfile. Assuming your data is in an array, I'd loop through the array and convert the data after converting it to a string, like so.
所以您已经获得了数据并将其写入文本文件。假设你的数据在一个数组中，我会遍历数组并在将数据转换为字符串后转换数据，就像这样。
Sub ChangeDecimalSeperator()
Dim vData As Variant, ii As Integer, jj As Integer
vData = ActiveSheet.Range("A1:B2")
For ii = LBound(vData, 1) To UBound(vData, 1)
For jj = LBound(vData) To UBound(vData, 2)
'Only convert if it's a number
If VBA.IsNumeric(vData(ii, jj)) Then
'You can also specify the format using VBA.Format$() here, ie force the decimal places
vData(ii, jj) = ReplaceCharacter(VBA.CStr(vData(ii, jj)), ".", ",")
End If
Next jj
Next ii
'Now output vData to your textfile
End Sub
Function ReplaceCharacter(sForString As String, sReplacingCharacter As String, sWithCharacter As String) As String
Dim sStringPrevious As String
'While there is a character that we still need replacing left, keep going. If we can't find one, we're done
Do
sStringPrevious = sForString
sForString = VBA.Replace(sForString, sReplacingCharacter, sWithCharacter)
Loop Until sStringPrevious = sForString
ReplaceCharacter = sForString
End Function
