如何避免Excel VBA Double类型数学运算的内部计算误差

问题本质 #

你遇到的计算偏差不是代码逻辑错误，而是VBA里Double类型的固有特性：Double是双精度二进制浮点数，无法精确表示所有十进制小数。0.8、0.6、0.2这类十进制小数，本身在二进制下就是无限循环的，存储的时候就已经存在截断误差，后续运算会把误差持续放大，才会出现0.800000000000001这类结果。

解决方案 #

方案1：改用Decimal类型做精确运算 #

Decimal是VBA支持的定点十进制类型，能精确存储你需要的十进制小数，不会出现二进制浮点数的误差问题。把变量声明为Variant，然后用CDec转换赋值即可，同时可以加余数重复检测逻辑直接识别周期循环，修改后的代码如下：

Sub DTBfraction() ' Decimal to Binary
    Dim myFraction As Variant
    Dim tmpDEC As Variant, tmpPRO As Variant, tmpREM As Variant
    Dim i As Long
    Dim tmpSTRING As String, tmpBIT As String
    ' 用CDec转换为Decimal类型精确存储
    myFraction = CDec(0.8)
    tmpDEC = myFraction
    ' 存储已经出现过的余数，用来识别循环
    Dim existedRem As Object
    Set existedRem = CreateObject("Scripting.Dictionary")
    
    Do
        i = i + 1
        tmpDEC = tmpDEC * 2
        tmpPRO = Int(tmpDEC)
        tmpREM = tmpDEC - tmpPRO
        tmpBIT = IIf(tmpPRO >= 1, "1", "0")
        tmpSTRING = tmpSTRING & tmpBIT
        
        ' 检测到余数重复，说明进入循环，直接终止
        If existedRem.Exists(tmpREM) Then
            tmpSTRING = Left(tmpSTRING, existedRem(tmpREM)) & "(" & Mid(tmpSTRING, existedRem(tmpREM) + 1) & ")"
            Exit Do
        End If
        existedRem.Add tmpREM, i
        
        ' 防止极端情况的兜底终止
        If i > 2000 Then Stop
        
        Debug.Print i, myFraction, tmpDEC, tmpPRO, tmpREM
        tmpDEC = tmpREM
    Loop Until tmpREM <= 0
    
    Debug.Print "0b0." & tmpSTRING
End Sub

运行该代码后余数会稳定在0.6、0.2、0.4、0.8循环，检测到重复后会直接输出0b0.(1100)，完全符合预期的周期循环结果，不会出现浮点误差导致的异常值。

方案2：设置精度阈值终止循环 #

如果不想修改变量类型，可以放弃"余数等于0才终止"的判断逻辑，改成当余数小于设定的精度阈值（比如1e-12）就终止，也能避免无效循环：
把循环终止条件修改为：

Loop Until tmpREM <= 0 Or tmpREM < 1e-12

同时判断两个值是否相等时，不用=运算符，改用差值小于阈值的逻辑即可：

' 不要使用 If tmpREM = 0.6 Then 的判断
If Abs(tmpREM - 0.6) < 1e-12 Then

内容的提问来源于stack exchange，提问作者Fred Cailloux