From Excel 2007, I came up with this VBA code when reading this website: Convert Decimal to Binary fraction
The main goal is to come up with a function that will convert fractional numbers into binary representation.
Here is my code:
Sub DTBfraction() ' Decimal to Binary
Dim myFraction As Double
Dim tmpDEC As Double, tmpPRO As Double, tmpREM As Double
Dim i As Long
Dim tmpSTRING As String, tmpBIT As String
myFraction = 0.8
tmpDEC = myFraction
Do
i = i + 1
tmpDEC = tmpDEC * 2
tmpPRO = Int(tmpDEC)
tmpREM = tmpDEC - tmpPRO
tmpBIT = IIf(tmpPRO >= 1, "1", "0")
tmpSTRING = tmpSTRING & tmpBIT
If i > 2000 Then Stop ' needed to prevent cases of infinite looping
Debug.Print i, myFraction, tmpDEC, tmpPRO, tmpREM
tmpDEC = tmpREM
Loop Until tmpREM <= 0
Debug.Print "0b0." & tmpSTRING
End Sub
Rem Debug.Print i myFraction tmpDEC tmpPRO tmpREM
Rem Debug.Print 1 0.8 1.6 1 0.6
Rem Debug.Print 2 0.8 1.2 1 0.2
Rem Debug.Print 3 0.8 0.4 0 0.4
Rem Debug.Print 4 0.8 0.800000000000001 0 0.800000000000001
Rem Debug.Print 5 0.8 1.6 1 0.600000000000001
Rem Debug.Print 6 0.8 1.2 1 0.200000000000003
Rem Debug.Print 7 0.8 0.400000000000006 0 0.400000000000006
' ... up to 52 in that particular case
Rem Debug.Print 0b0.1100110011001100110011001100110011001100110011001101
The problem encountered is that at the 4 looping, instead of getting 0.8 the code will calculate 0.800000000000001 which create an undesirable behavior of looping many times until the condition will terminate the loop until tmpREM attain a value so small that it is considered 0. In reality the loop should remain infinite, for, never will tmpREM become zero. It's a periodic situation.
After some experimentation and testings it was discovered that the first 3 tmpREM values of 0.6 0.2 0.4 are not. Instead they are a tiny bit higher but not showing on the Debug.Print line but could be detected if a test is performed such as tmpREM > 0.6 , in fact tmpREM = 0.6 will show a False status.
The goal would be to programmatically catch such situations so that the code stop spiting out 1's and 0's ad-infinitum, but my solution is one of an amateur and not functional. I was trying to multiply the result with 10^12 , do an INT() and devise by 10^12. Not a workable solution. Substraction dont work either, instead of ending up with 0.8 VBA spit out 0.799999999999.
I feel like the solution lies in preventing the calculation error from spitting out a value of 0.80000000000001 instead of 0.8
Once achieved, I would probably be able to come up with a solution to detect periodic calculations and stop the code from looping. At this point I am out of imagination. Hence the question:
How can I get rid of this computational error such that I get 0.8 instead of 0.800000000000001 ?
