VBA addition of doubles bug

[guyhjs]

Hi all,

I have a weird bug I cannot work through.


(3.1 + 0.1 = 3.2) = TRUE
(3.2 + 0.1 = 3.3) = FALSE

Please can someone explain why? This is messing with a while loop I have.

 

[CephasOz]

Hi @guyhjs - welcome. It would be helpful to be able to see the entire line(s) of code, and any Dim statements if variables are involved.

 

[CephasOz]

Okay, it's the old binary representation of decimal numbers thing. Is this any help?
(cdec(3.1) + cdec(0.1) = cdec(3.2)) = TRUE
(cdec(3.2) + cdec(0.1) = cdec(3.3)) = TRUE

 

[guyhjs]

Hi CephasOZ. Thanks for your help this worked. It was a floating point error.

 

[Joe4]

Hi CephasOZ. Thanks for your help this worked. It was a floating point error.

Yes, it is described in detail here: Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

A quick solution people often use is to use the ROUND function (as described in that article), i.e.

=ROUND(3.2,1) + ROUND(0.1,1) = ROUND(3.3,1)

 

[Alex Blakenburg]

@Joe4. The article uses the Excel rounding and not the VBA rounding. I am not sure if bankers rounding doesn't come into play if you use VBA rounding. WorksheetFunction.Round would be safer.

 

[Joe4]

@Joe4. The article uses the Excel rounding and not the VBA rounding. I am not sure if bankers rounding doesn't come into play if you use VBA rounding. WorksheetFunction.Round would be safer.

Ah, good catch! I was focusing on the question, and hadn't noticed the "VBA" in the title.