Rounding Errors in VBA algorithms

[PLdeNeef]

It has been twice now in a short period that one of my algorithms crashed because of rounding errors when using VB for my excel file. In performing simulation analysis I have to calculate the square root of a-b, where a and b can take any value on [0,1]. It can happen that both a and b are equal (in my case they will most likely be 1 then). The program throws an error since it calculates 1 - 1 = -1.2..*10^-16. The input was read from an excel sheet. Is there anything else I can do about this than simply using the round function with ten digits or so in every formula where i can't have negative numbers?

Same kind of problem surfaces with stopping criteria. I have to stop when for example a = b. But even when a = b in excel, VB might think it is smarter than that and will return a false when testing a = b. I currently check if the absolute difference is smaller than a very small number.

All these things make the code less clear to read and that is a very important aspect for me.

Hope you have some usefull tips for me.

Patrick

[fenrisW0lf]

Consider these two routines in excel vba:

Public Function VBRoundError(numberOfLoops As Long, Optional Round As Boolean) As Double
    Dim i As Long
    Dim sum As Double
    
    For i = 0 To numberOfLoops
        If Round Then
            sum = RoundIt(sum + 0.01, 7)
        Else
            sum = sum + 0.01
        End If
    Next
    
    VBRoundError = sum
End Function



Public Function RoundIt(ByVal aNumberToRound As Double, Optional ByVal aDecimalPlaces As Double = 0) As Double

Dim nFactor As Double
Dim nTemp As Double

nFactor = 10 ^ aDecimalPlaces
nTemp = (aNumberToRound * nFactor) + 0.5
RoundIt = Int(CDec(nTemp)) / nFactor
End Function

Once you place these routines into a module you can access the VBRoundError function by typing this "=VBRoundError(99)" into a cell.

If I type these into cells within excel:

=VBRoundError(99) excel displays 1.000000000000000000
=VBRoundError(999) excel displays  9.999999999999830000
=VBRoundError(9999) excel displays 100.000000000014000000

You can clearly see that there is rounding error taking place. This is just the way that VB/A behaves.

If I use this:

=VBRoundError(99,true) excel displays 1.000000000000000000
=VBRoundError(999,true) excel displays  10.000000000000000000
=VBRoundError(9999,true) excel displays 100.000000000000000000

I get nice even values. There really is no way around this because of the way VB/A works.

When working with double or single precision numbers, you should always test using a tolerance or delta value instead of directly comparing two doubles. I would use something like:

if (a-b) < 0.00001 then

If you are concerned with readability (as you should be) you can create a function that returns a boolean:

public function ToleranceTest(num1 as double, num2 as double, tolerance as double) as boolean

if (num1 - num2) < tolerance then
    ToleranceTest = true
else
   ToleranceTest  = false
endif

end function

[PLdeNeef]

ok, I was already afraid there would be no way around it. Thanks for the answer and code examples.

Cheers,
Patrick