VBA: difference between Variant/Double and Double

Question

I am using Excel 2013. In the following code fragment, VBA calculates 40 for damage:

Dim attack As Variant, defense As Variant, damage As Long
attack = 152 * 0.784637
defense = 133 * 0.784637
damage = Int(0.5 * attack / defense * 70)

If the data types are changed to Double, VBA calculates 39 for damage:

Dim attack As Double, defense As Double, damage As Long
attack = 152 * 0.784637
defense = 133 * 0.784637
damage = Int(0.5 * attack / defense * 70)

In the debugger, the Variant/Double and Double values appear the same. However, the Variant/Double seems to have more precision.

Can anyone explain this behavior?

Answer 1

tldr; If you need more precision than a Double, don't use a Double.

The answer lies in the timing of when the result is coerced into a Double from a Variant. A Double is an IEEE 754 floating-point number, and per the IEEE specification reversibility is guaranteed to 15 significant digits. Your value flirts with that limit:

0.5 * (152 * .784637) / (133 * .784637) * 70 = 39.99999999999997 (16 sig. digits)

VBA will round anything beyond 15 significant digits when it is coerced into a double:

Debug.Print CDbl("39.99999999999997") '<--Prints 40

In fact, you can watch this behavior in the VBE. Type or copy the following code:

Dim x As Double
x = 39.99999999999997

The VBE "auto-corrects" the literal value by casting it to a Double, which gives you:

Dim x As Double
x = 40#

OK, so by now you're probably asking what that has to do with the difference between the 2 expressions. VBA evaluates mathematical expressions using the "highest order" variable type that it can.

In your second Sub where you have all of the variable declared as Double on the right hand side, the operation is evaluated with the high order of Double, then the result is implicitly cast to a Variant before being passed as the parameter for Int().

In your first Sub where you have Variant declarations, the implicit cast to Variant isn't performed before passing to Int - the highest order in the mathematical expression is Variant, so no implicit cast is performed before passing the result to Int() - the Variant still contains the raw IEEE 754 float.

Per the documentation of Int:

Both Int and Fix remove the fractional part of number and return the resulting integer value.

No rounding is performed. The top code calls Int(39.99999999999997). The bottom code calls Int(40). The "answer" depends on what level of floating point error you want to round at. If 15 works, then 40 is the "correct" answer. If you want to floor anything up to 16 or more significant digits, then 39 is the "correct" answer. The solution is to use Round and specify the level of precision you're looking for explicitly. For example, if you care about the full 15 digits:

Int(Round((0.5 * attack / defense * 70), 15))

Keep in mind that the highest precision you use anywhere in the inputs is 6 digits, so that would be a logical rounding cut-off:

Int(Round((0.5 * attack / defense * 70), 6))