Excel VBA双加法错误 [英] Excel VBA Double Addition Error

问题描述

我很难解释这个。以下功能被用作工作表公式。值空仅仅意味着单元格为空，因此没有值。给定值为空（空，空，0.8，0.2），以下函数有时候返回墙值，如5.55111512312578E-17。在调试器中，在ParamArray（在这种情况下为0.2）的最后一个值被处理之前，它看起来就像一切都正确。任何想法？

 私有函数getOvertimeEP（ParamArray epAllocations（）As Variant）
 Dim overtimeEP As Double 
 overtimeEP = -1 
 
对于每个nextVal在epAllocations 
 overtimeEP = overtimeEP + nextVal 
下一个
 
如果overtimeEP< 0然后
 overtimeEP = 0 
如果
 
 getOvertimeEP = overtimeEP 
结束函数
  

解决方案

该错误是浮点精度问题。即使你的前两个值是0和0，它仍然会有相同的结果。 {0.1，0.2，0.3，0.4}。

将其舍入到一些合理的小数位数后再返回并称为一天。

I'm having a hard time explaining this one. The following function is being used as a worksheet formula. A value of "empty" simply means that the cell was empty and, therefore, there is no value. Given the values {empty, empty, 0.8, 0.2}, the following function is sometimes returning off the wall values like 5.55111512312578E-17. In the debugger, it looks like everything is correct until the last value in the ParamArray (in this case 0.2) is processed. Any thoughts?

   Private Function getOvertimeEP(ParamArray epAllocations() As Variant)
        Dim overtimeEP As Double
        overtimeEP = -1

        For Each nextVal In epAllocations
            overtimeEP = overtimeEP + nextVal
        Next

        If overtimeEP < 0 Then
            overtimeEP = 0
        End If

        getOvertimeEP = overtimeEP
    End Function

解决方案

That error is the result of floating point accuracy problems. Even if your first two values are 0 and 0, it will still have the same result. So will {0.1, 0.2, 0.3, 0.4}.

Round it to some reasonable number of decimal places before returning it and call it a day.

这篇关于Excel VBA双加法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！