使用CDbl()转换文本格式数字时丢失小数点问题求助

问题描述 #

编写了如下VBA函数，用于将工作表首个表格中的文本格式数字转换为Double类型：

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' **Occurence:**
' This function is used in multiple handlers.
'
' **Summary:**
' This function iterates over all the (a) rows & columns in the 1st table
' on the sheet. It checks non-empty values. If value is not formatted as
' to the table while *"numbers formated as text"* are converted to
' `Double` and added to the array. At the end this array is written back
' to the table while *"numbers formated as text"* are converted to `Double`.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Public Function remove_numbers_formated_as_text(ByVal sh As Worksheet)

    Dim r As Range
    Dim arr As Variant
    Dim i As Long '''' arr's rows
    Dim j As Long '''' arr's columns
    
    Dim s As String
    Dim b As Boolean
    
    Set r = sh.ListObjects(1).DataBodyRange
    arr = r.Formula2

    '''' Iterate over a whole row and then proceed to next column
    For i = 1 To UBound(arr)
        For j = 1 To UBound(arr, 2)
            If IsEmpty(arr(i, j)) = False Then
                
                '''' Check whether array mamber stores formula
                b = r(i, j).HasFormula
                If b = True Then GoTo A
                
                '''' If cell doesn't treat numbers as text and is a numeric value,
                '''' then this is definitely a measurement that somebody entered and
                '''' it is therefore converted to double.
                s = r(i, j).NumberFormat
                If IsNumeric(arr(i, j)) And s <> "@" Then
                    arr(i, j) = CDbl(arr(i, j))
                End If
            
            End If
A:
        Next
    Next

    r.Formula2 = arr
    
End Function

实际运行发现，原数组中的Variant/String类型值（如"0.544502556324005"）经CDbl()转换后丢失小数点，变成544502556324005，导致数据损坏。

原因分析 #

问题出在系统区域设置的小数分隔符差异：

• 如果Windows系统设置中，小数分隔符是逗号（,）而非点（.），CDbl()会将字符串中的点（.）识别为千位分隔符而非小数分隔符。
• 比如字符串"0.544502556324005"会被解析为0 544502556324005（千位分隔符被忽略），最终得到整数544502556324005。

修复方案 #

针对当前函数，只需将CDbl()替换为不受区域设置影响的Val()函数即可：

' 替换原CDbl行
arr(i, j) = Val(arr(i, j))

Val()函数始终以点（.）作为小数分隔符解析数字字符串，不会受系统区域设置干扰。

更优转换方法 #

如果需要更稳妥的文本转数值方案，可参考以下两种思路：

1. 直接利用Excel单元格的自动转换 #

无需遍历数组，直接让Excel自动处理文本格式数字，效率更高，尤其适合大表格：

Public Function remove_numbers_formated_as_text(ByVal sh As Worksheet)
    Dim tbl As ListObject
    Set tbl = sh.ListObjects(1)
    
    ' 利用TextToColumns快速转换文本格式数字
    With tbl.DataBodyRange
        .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
            FieldInfo:=Array(1, xlGeneralFormat)
    End With
End Function

2. 使用区域无关的自定义转换函数 #

自定义函数兼容不同区域设置的小数/千位分隔符：

Private Function ConvertToDouble(ByVal strNum As String) As Double
    ' 统一替换分隔符为系统默认格式
    strNum = Replace(strNum, ".", Application.DecimalSeparator)
    strNum = Replace(strNum, ",", Application.ThousandsSeparator)
    ConvertToDouble = CDbl(strNum)
End Function

在原函数中调用此函数即可：

arr(i, j) = ConvertToDouble(arr(i, j))

内容的提问来源于stack exchange，提问作者71GA