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.

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


Tagged with: , ,