excel - IsNumeric 在 excel/vba 中针对不同的区域设置给出了意想不到的结果

Question

我的印象是IsNumeric(MyString)将显示 Val(MyString)预计会失败与否。根据区域设置，我发现了意想不到的差异。
示例 1，瑞典地区设置(使用 , 作为小数分隔符):

IsNumeric("1.1") => 真

Val("1.1") => 1.1

IsNumeric("1,1") => 真

Val("1,1") => 1

示例 2，爱沙尼亚地区设置(也使用 , 作为小数分隔符):

IsNumeric("1.1") => 错误

Val("1.1") => 1.1

IsNumeric("1,1") => 真

Val("1,1") => 1

我将字符串转换为数字的典型通用代码是:

Function ConvertToNumber(MyNumber as String) as Double 
    If IsNumeric(MyNumber) then
        ConvertToNumber = Val(Replace(MyNumber, ",", "."))
    Else
        MsgBox "Invalid format!"
    End If
End Function

但这在爱沙尼亚的地区环境中出人意料地失败了。知道这是否是预期的行为吗？微软对 IsNumber 在做什么的解释也有点模糊。你建议改用什么？ If Val(MyNumber & "1")<>0 ?这对于某些特殊情况会失败，例如 0E+0 .还可以考虑从以下位置捕获任何错误: CDbl(Replace(MyString, ".", Application.International(xlDecimalSeparator))我可以使用正则表达式，但必须有更好的方法。
我将不胜感激这方面的意见。
/乔纳斯

Answer 1

问题是使用 Val function哪个

Returns the numbers contained in a string as a numeric value of appropriate type.

所以它不是你所表达的，因为它不是 转换 将字符串转换为数字，但 摘录字符串中包含的数字，直到第一个不是空格的非数字字符。
这意味着

Val("    1615 198th Street N.E.")

将返回 1615198作为两倍。
那么 Val解释为小数的总是 .在所有本地化中。这意味着它将始终考虑 Val("1.1")作为一个数字，但在 Val("1,1")逗号是第一个非数字、非空白字符，因此它停在那里并返回 1只要。
您正在寻找的是 CDbl这实际上是 转换 使用系统的十进制分隔符将字符串转换为数字。

Option Explicit

Function ConvertToNumber(ByVal MyNumber As String) As Double
    Dim RegionalNumber As String
    RegionalNumber = MyNumber
    
    ' if you want the user to be able to enter dots as well as commas
    ' make sure all dots and commas are converted to the decimal seperator of your system
    ' otherwise go without the conversion
    RegionalNumber = Replace$(RegionalNumber, ".", Application.DecimalSeparator)
    RegionalNumber = Replace$(RegionalNumber, ",", Application.DecimalSeparator)
    
    If IsNumeric(RegionalNumber) Then
        ConvertToNumber = CDbl(RegionalNumber)
    Else
        MsgBox "Invalid format!"
    End If
End Function

如果你用

Sub test() 
    Debug.Print ConvertToNumber("1.1")
    Debug.Print ConvertToNumber("1,1")
End Sub

在逗号是十进制分隔符的系统上，它应该两次返回 1,1作为一个数字。

解释为什么您的测试返回了他们返回的结果
示例 1，瑞典语区域设置(使用 ',' 作为小数分隔符):

IsNumeric("1.1") => 真
因为.被视为日期分隔符，因此它是一个有效数字

Val("1.1") => 1.1
因为 val 始终将点视为小数分隔符

IsNumeric("1,1") => 真
因为,被视为 十进制 分隔符

Val("1,1") => 1
因为 val 始终将逗号视为非数字字符

示例 2，爱沙尼亚地区设置(也使用 ',' 作为小数分隔符):

IsNumeric("1.1") => 错误
因为爱沙尼亚语中的点是 不是 视为日期分隔符，这不是有效数字

Val("1.1") => 1.1
因为 val 始终将点视为小数分隔符

IsNumeric("1,1") => 真
因为,被视为十进制分隔符

Val("1,1") => 1
因为 val 始终将逗号视为非数字字符

请注意 IsNumeric接受千位分隔符、日期分隔符和小数分隔符以及货币符号。