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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 End Sub |
Think twice before working with doubles, those decimals are just around the corner, helping you keep your sanity.
🙂