Rounding Errors in VBA algorithms
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
Re: Rounding Errors in VBA algorithms
Consider these two routines in excel vba:
Code:
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:
Code:
=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:
Code:
=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:
Code:
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:
Code:
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
Re: Rounding Errors in VBA algorithms
ok, I was already afraid there would be no way around it. Thanks for the answer and code examples.
Cheers,
Patrick
