vba - Excel 和 VBA : 'If value is less than 1' condition being triggered by a value that is exactly 1

Question

我在 Excel VBA 中遇到了一个令人困惑的行为，我正在尝试理解它，想知道是否有人可以指出我的解释方向？

背景 - 我继承了一个报告工具，用于计算每天是否有足够的剩余假期津贴以允许额外休假。我发现当剩余限额恰好为“1”时，它的行为会出乎意料。

下面是已经存在的 VBA(实际文件中的变量值是由其他查询设置的，但我已在此处手动设置它们以便复制问题)。使用此代码，即使 (Total * Allowance) - Taken 的结果恰好为 1，并且只有值小于 1

才满足“If”条件，也会触发消息框

Dim Total As Double
Dim Allowance As Double
Dim Taken As Double

Total = 20
Allowance = 0.15
Taken = 2

If (Total * Allowance) - Taken < 1 Then
    MsgBox "Not Enough Allowance Remaining"
End If

我尝试将代码更改为下面的代码，发现当“remaining”声明为“double”数据类型时，会出现同样的问题。但是，如果我将“remaining”的数据类型更改为“single”，代码将按预期运行并且不会显示消息框:

Dim Total As Double
Dim Allowance As Double
Dim Taken As Double
Dim Remaining As Double

Total = 20
Allowance = 0.15
Taken = 2
Remaining = (Total * Allowance) - Taken

If Remaining < 1 Then
     MsgBox "Not Enough Allowance Remaining"
End If

我认为这一定与 Excel/VBA 处理不同数据类型中的值“1”的方式有关，并且一些搜索出现了下面的文章，但我不确定我是否走在正确的道路上缺少一个更简单的答案 - 有什么想法吗？

Article 1
Article 2

谢谢

Answer 1

这是一个简单的舍入问题。这有效:

Sub dural()
Dim Total As Double
Dim Allowance As Double
Dim Taken As Double

Total = 20
Allowance = 0.15
Taken = 2
If ((Total * Allowance) - Taken) < 0.999999 Then
    MsgBox "Not Enough Allowance Remaining"
End If
End Sub

因为浮点运算不能精确地产生1
例如:

Sub dural()
    Dim Total As Double
    Dim Allowance As Double
    Dim Taken As Double

    Total = 20
    Allowance = 0.15
    Taken = 2
    If (Total * Allowance) - Taken < 0.999999 Then
        MsgBox "Not Enough Allowance Remaining"
    End If

    MsgBox 1 - ((Total * Allowance) - Taken)

End Sub

产品: