How to declare as decimal in VBA

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.

Excel VBA – Floating Point Numbers in Excel – Not Exact

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
    
End Sub

Think twice before working with doubles, those decimals are just around the corner, helping you keep your sanity.

🙂