[RESOLVED] VBA Excel - Double Comparison failure

[Torc]

Hi,

I am trying to compare two values from different spreadsheets in a macro in Excel (Excel 2002, Visual Basic 6.3).

Most of the time the comparison works, but occasionally it decides two values are unequal, when they are equal
as far as I can see, and verify when stepping through the code in the Visual Basic Editor.

The code is as follows

CheckGloss = Workbooks(SecurityWBK).ActiveSheet.Cells(GlossRow, GlossCoupon).Value
            CheckSummit = SummitCouponRate
            If CheckGloss <> CheckSummit Then
                Script
            End If

CheckGloss and CheckSummit are declared as Doubles.
I resorted to using them as the code was failing on this comparison.

SummitCouponRate is a Function, as follows

Function SummitCouponRate() As Double
    If Trim(Workbooks(SummitWBK).ActiveSheet.Cells(SummitRow, SumFixFloat).Value) = "FIX" Then
        SummitCouponRate = Workbooks(SummitWBK).ActiveSheet.Cells(SummitRow, SumCouponorSpread).Value
    Else
        SummitCouponRate = (Workbooks(SummitWBK).ActiveSheet.Cells(SummitRow, SumCouponorSpread).Value / 100#) + Workbooks(SummitWBK).ActiveSheet.Cells(SummitRow, SumCurrentRate).Value
    End If
End Function

On most occasions where CheckGloss is equal to CheckSummit they are working fine, it is just a few where they are being taken to be not equal.

CheckGloss is equal to 2.688

Workbooks(SummitWBK).ActiveSheet.Cells(SummitRow, SumCouponorSpread).Value is 4.5
Workbooks(SummitWBK).ActiveSheet.Cells(SummitRow, SumCurrentRate).Value is 2.643

I have run the math through a calculator, and the fallback of pencil and paper and I still get 2.688 for CheckSummit.

Does anyone have any ideas as to why this is happening, also what the solution is?

[si_the_geek]

Welcome to VBForums!

The problem here is that you are using Doubles - unfortunately they are not accurate.

One way around this is to round the numbers to a level that you are happy with (as the lack of accuracy usually occurs "late" after the decimal point), eg:

If Round(CheckGloss,3) <> Round(CheckSummit,3) Then

[Torc]

Thanks,

I had not realised that Doubles had that limitation.

Unfortunately I need full accuracy on the numbers, and I cannot tell what precision it will be working to.

I tried changing the Declaration of CheckGloss and CheckSummit to Strings and the Macro now works fine.