Re: Problem comparing double values

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

"Chris Eden" <ec2208@xxxxxxxxxxx> wrote in message news:%239934aVgFHA.3936@xxxxxxxxxxxxxxxxxxxxxxx
> Hello all, I have a strange problem that I would appreciate any help
> with. I have an application that involves a comparison of two numbers,
> one of which is made up of the sum of the values in an array. The code
> is as follows:
>
> Private Sub CompareNumbers()
> Dim intIndex As Integer
> Dim arNumbers(5) As Double
> Dim dblTotal As Double
> Dim dblComparison As Double
>
> arNumbers(0) = 16843.2
> arNumbers(1) = 40348.41
> arNumbers(2) = 371910.18
> arNumbers(3) = 148183.27
> arNumbers(4) = 29183.96
> arNumbers(5) = 143530.98
>
> For intIndex = 0 To 5
> dblTotal = dblTotal + arNumbers(intIndex)
> Next
>
> dblComparison = 750000
> If dblComparison = dblTotal Then
> MsgBox "Array Total:" & Chr$(9) & dblTotal & vbCr & "Comparison:" &
> Chr$(9) & dblComparison & vbCr & "Numbers match"
> Else
> MsgBox "Array Total:" & Chr$(9) & dblTotal & vbCr & "Comparison:" &
> Chr$(9) & dblComparison & vbCr & "No match"
> End If
> End Sub
>
> Notice the oddity? The numbers are apparently identical and yet
> according to the code, they do not match. What is even more bizzare is
> that if I reduce the total of the numbers in the array to 597000, made
> up of the following:
> '' arNumbers(0) = 57843.2
> '' arNumbers(1) = 44348.41
> '' arNumbers(2) = 371910.18
> '' arNumbers(3) = 50183.27
> '' arNumbers(4) = 29183.96
> '' arNumbers(5) = 43530.98
> the comparison suceeded.
>
> I don't think I have ever come up against anything so strange or so
> frustrating; if anyone can shed any light on this it would be greatly
> appreciated.
>
> Thanks in advance
>
> Chris
>
> *** Sent via Developersdex http://www.developersdex.com ***

The problem is that they are not the same due to inherent rounding errors and the internal representation of floating point numbers.

?dblComparison-dblTotal
1.16415321826935E-10

The following will work. It uses variants and the decimal subtype.

Private CompareNumbers()
Dim intIndex As Integer
Dim arNumbers(5) As Double
Dim dblTotal As Variant
Dim dblComparison As Variant

arNumbers(0) = CDec(16843.2)
arNumbers(1) = CDec(40348.41)
arNumbers(2) = CDec(371910.18)
arNumbers(3) = CDec(148183.27)
arNumbers(4) = CDec(29183.96)
arNumbers(5) = CDec(143530.98)

For intIndex = 0 To 5
dblTotal = dblTotal + arNumbers(intIndex)
Next

dblComparison = CDec(750000)
If dblComparison = dblTotal Then
MsgBox "Array Total:" & Chr$(9) & dblTotal & vbCr & "Comparison:" & _
Chr$(9) & dblComparison & vbCr & "Numbers match"
Else
MsgBox "Array Total:" & Chr$(9) & dblTotal & vbCr & "Comparison:" & _
Chr$(9) & dblComparison & vbCr & "No match"
End If
End Sub

--

Al Reid


.

Relevant Pages