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.