More than 3 years ago, I wrote an article about floating point numbers in Excel and a tiny problem with those, concerning all floating point numbers. This article is actually a nice one and it is worth reading – What Every Computer Scientist Should Know About Floating-Point Artihmetics.
Today, I was thinking simply to write an example, concerning the usage of these in VBA. It is pretty much the same problem and VBA has a silver bullet to solve it – the Decimal type. A variable cannot be declared explicitly decimal, but once it is decared as a Variant, it can be converted (or casted, I am playing too much AoE2 lately) to a Decimal with CDec().
This code is an example of what happens “under to hood” of VBA, in which it states that 0.10 + 0.01 is not 0.21 in a Double type, but it is ok, if the type is a Decimal.
Public Sub TestMe()
Dim preciseA As Variant: preciseA = CDec(0.1)
Dim preciseB As Variant: preciseB = CDec(0.11)
Dim notPreciseA As Double: notPreciseA = 0.1
Dim notPreciseB As Double: notPreciseB = 0.11
Dim precise As Variant: precise = preciseA + preciseB
Dim notPrecise As Variant: notPrecise = notPreciseA + notPreciseB
Debug.Print preciseA + preciseB = 0.21 'True
Debug.Print notPreciseA + notPreciseB = 0.21 'False
Think twice before working with doubles, those decimals are just around the corner, helping you keep your sanity.