Excel 2007 VBA Calculations Wrong

Question

When you run a VBA macro that performs numeric calculations which result in a decimal value, the result that is returned may be incorrect.

Here are a couple of examples:

Dim me_wrong as Double
me_wrong = 1000 - 999.59

RESULT = 0.409999999999968

Dim me_wrong_too as Double
me_wrong_too = 301.84 - 301

RESULT = 0.839999999999975

I have never ever noticed this before. What on earth is going on???

I have seen the following article about Office 97 but can't find anything about a bug in Excel 2007: http://support.microsoft.com/default.aspx?scid=kb;en-us;165373

Plus it doesn't explain why I have never seen it before.

Please help!

Answer 1

The explanation for the problem from Office 97 and VBA is equally applicable going forward into Excel 2007. The core VBA system is largely unchanged despite the migration into later versions, hence the same kinds of accuracy gremlins that plague older VBA macros will persist.

The fundamental problem lies with the inherent inaccuracy in the representation of fractional numbers in binary, and how at least some effort to mitigate that inaccuracy was made with IEEE floating point representations. There is a very decent treatment of the subject of IEEE representation at this location.

*Edit: Just a minor bit of extra info for detail. *

For a very simple example that illustrates this issue in a trivial case, consider a situation in which decimals are represented as sums of inverse powers of two, eg 2^-1, 2^-2, 2^-3, and so on. That ends up looking like .5, .25, .125, and so on. If you're representing exactly those numbers, all is good. However, consider a number like .761; 2^-1+2^-2 gets you to .750, but now you need .011. 2^-3 (.125) is too big, but 2^-4 (.0625) is too small...so you keep going to smaller powers of two, realizing you'll never quite represent the number precisely.

The choice becomes where you stop resolving and accept the inherent inaccuracy as being "good enough" for the problem you're solving/modeling.

Answer 2

It is, unfortunately, not a bug.

Double representation follows a fixed point notation, where the mantissa is a number "1,x" with "1" being implicit. There's an exponent and a sign, which makes the full representation in Base 2.

The pertinent issue is Base=2 which makes "x" in "1,x" to be a finite-precision (53bits of it) fractional binary. Think x= a52*1/2+a51*1/4+a50*1/8+...+a*1**1/(2^52)+a0*1/(2^53), where a< i > are the bits in the mantissa.

Try attaining 1,4 with this representation, and you hit the precision wall... there is no finite decomposition of 0.4 in binary weights. So the norm specifies you should represent the number immediately before the real one, which leaves you with 0,39999..9997346 (or whatever the tail is).

The "good" news is, and I've just burned four "c" coding days last week on that subject, you can do without Doubles if you represent your number using a very small scale (say 10^-9), store then in very large variables (long64), and do your display functions using nothing but integers (mathematically slicing away integral and fractional parts through integer division and their remainders). A treat, I tell you... not.