|
The computer stores everything in zeros and ones. It can store whole numbers exactly. For example, the binary representation of the value 13 is 1101. On the other hand, the computer cannot store all fractional values exactly. Sometimes that can cause a great deal of confusion. [This issue is described in a little more detail in my book Bug Proofing Visual Basic.]
Differences between a number's theoretically exact value and its floating point representation can sometimes be confusing. For example, the following code adds the value 10/17 to itself 17 times. The result should be the value 10. If you run this code, however, the program displays the message "9.999999 <> 10" because the result is not exactly 10.
Dim x As Single
Dim i As Integer
x = 0
For i = 1 To 17
x = x + 10 / 17
Next i
If x = 10 Then
MsgBox x & " = 10"
Else
MsgBox x & " <> 10"
End If
|
In fact, the result isn't really 9.999999 either. That's just a reasonable decimal version of the true binary value stored in the computer. It's what the computer gets when it converts the binary representation into the Double data type for display.
If you try to examine the value using the Debug.Print statement, you'll the same result. Even if you use this statement to make extra digits appear, you'll still see what appears to be exactly 10.
Debug.Print Format$(x, "0.0000000000000")
However, if you subtract 10 from the result as in this statement:
Debug.Print Format$(10 - x, "0.0000000000000")
you'll get this result:
0.0000009536743
Don't count on that value being correct either. The important thing is to realize that floating point values are not exact.
The situation can be even more confusing with the Double data type. If you make the variable x a Double in the previous code, the program displays the confusing message "10 <> 10." The value is not exactly 10 so the test x = 10 is false. When the program displays x in the MsgBox statement, it comes up with the representation 10 so you get the confusing statement "10 <> 10."
Once again, if you use a Debug.Print statement to examine x, you'll see the value 10. Even if you use the previous statement subtracting x from 10 you'll see the value 10.
The problem is this value is closer to 10 than the Single data type used in the previous example so you need to examine more digits in the Debug.Print statement. The following statement:
Debug.Print Format$(10 - x, _
"0.00000000000000000000000000000")
gives this result:
0.00000000000000177635683940025
There are several ways this sort of approximation can create errors.
For Loops. If you use a floating point number to control a For loop (For x = 0 To ...), the loop may stop earlier or later than you expect. To avoid this, use an Integer to control the loop and calculate the value for the floating point number independantly so you can use it in calculations.
Equality Testing. If you compare two floating point numbers for equality (If x = y Then ...), the binary representations of the numbers may not be exactly equal even though you know they should be. Rather than testing for equality, see how big the difference between the numbers is (If Abs(x - y) < 0.0001 Then ...).
Loop Stopping. Similarly if you use an equality or inequality test to stop a While loop, the loop may stop earlier or later than you expect. For example, consider the following code.
x = 0
Do While x < 10
...
Loop
Here the intent is probably to stop the loop when x equals 10. However, suppose at some point x becomes 9.99999924165. This loop will execute one more time although you probably want it to stop now. You can create similar problems with statements using x = 10, x <> 10, and x > 10.
To fix this problem, compare x to a number slightly larger or smaller than the limit you want to reach. A better version of this code would be:
x = 0
Do While x < (10 - 0.01)
...
Loop
Inexact floating point representations can lead to some confusing bugs. You can stare at the code all day and it will look correct because it would be if the numbers were stored exactly. Even worse, you can use Debug.Print statements to examine the values and verify that they really are correct. This leads to situations where Debug.Print says a value is 10 but the statement If x = 10 Then ... is false.
To avoid these problems, use Integers to control loops. Rather than looking for exact values in floating point variables, look for a small difference between the variable and the value you want. If you can make the computer spout nonsense like "10 <> 10," you probably have this kind of problem.
| 
