vba 如何在Excel中将字符串转换为数字，反之亦然
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6168651/
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 convert string to number and vice versa in Excel
提问by bombardier
I have some XML files that contain decimal numbers stored with '.' as decimal separator. I created Excel workbook that is used for editing data in that XML. MSXML is used to read the data from XML that are then displayed in worksheets. Once data is being stored I modify DOM structure with new values read from tables and I save XML.
我有一些包含十进制数字的 XML 文件，这些数字以 '.' 存储。作为小数点分隔符。我创建了用于编辑该 XML 中数据的 Excel 工作簿。MSXML 用于从 XML 读取数据，然后显示在工作表中。存储数据后，我使用从表中读取的新值修改 DOM 结构并保存 XML。
The problem is that when I convert decimal numbers back to strings, to save them as values in XML, decimal separator that is specified by user in Windows regional settings is used.
问题是，当我将十进制数转换回字符串时，为了将它们保存为 XML 中的值，使用了用户在 Windows 区域设置中指定的十进制分隔符。
I need a way to force storing decimal numbers with '.' as decimal separator in all cases. Is this possible?
我需要一种方法来强制使用 '.' 存储十进制数。在所有情况下都作为小数点分隔符。这可能吗？
回答by ErikE
How do you modify the DOM structure with the new values--programmatically? How?
您如何以编程方式使用新值修改 DOM 结构？如何？
Is it possible for you to use Format(Cell.value, "#0.0")? Also try Format(CDbl(Cell.Value), "#0.0"). If that doesn't work, can you just throw in a Replace(Value, ",", ".")? This should be okay if you're not using thousands separators.
你可以使用Format(Cell.value, "#0.0")吗？也试试Format(CDbl(Cell.Value), "#0.0")。如果这不起作用，你可以扔进去Replace(Value, ",", ".")吗？如果您不使用千位分隔符，这应该没问题。
回答by Jean-Fran?ois Corbett
Use the Strfunction. It recognizes only the period .as a valid decimal separator.
使用该Str功能。它仅将句点识别.为有效的小数分隔符。
Dim d as Double
Dim s as String
d = 3.25
s = Str(d)
' s is now " 3.25"
Note that Strreserves a leading space for the sign of the number. If you don't want a leading space on your positive numbers, just Trimthe result:
请注意，Str为数字的符号保留前导空格。如果您不想在正数上使用前导空格，只需Trim结果：
s = Trim(Str(d))
' s is now "3.25"
回答by Raymond Chenon
@Jean-Francois since we're talking about i18n you should use CStr( for international applications ) function instead of Str( where . dot is separator for floating number , not much use in French where comma , is a separator ex : 7,54)
@Jean-Francois 因为我们在谈论 i18n，所以你应该使用CStr（用于国际应用）函数而不是Str（其中 . dot 是浮点数的分隔符，在法语中用处不大，其中逗号是分隔符，例如：7,54 )
Sub myFunction()
Dim num As Double
Dim str As String
num = 3.251
str = CStr(num) ' convert a number into a String
num = Val(str) ' convert a String into an integer
num = Trim(str) ' convert a String into a Variant
str = Trim(num) ' notice this works the other way around
End Sub
