VBA: difference between option / double and double

Question

I am using Excel 2013. In the following code snippet, 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 computes 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 ​​are displayed the same. However, the Variant/Double has higher accuracy.

Can anyone explain this behavior?

Answer 1

TL; DR; If you need higher precision than Double , do not use Double .

The answer lies in the time when the result is forcibly entered into Double from a Variant . A Double is an IEEE 754 floating point number, and 15 significant digits are guaranteed for the IEEE feedback specification. Your value flirts with this limit:

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

VBA will cover anything that does not exceed 15 significant digits when it is forcibly inserted into double:

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

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

 Dim x As Double x = 39.99999999999997 

VBE "automatically corrects" the literal value, dropping it to Double , which gives you:

 Dim x As Double x = 40# 

Okay, so now you are probably asking what this means with the difference between the two expressions. VBA evaluates math expressions using the variable type "highest order" that it can.

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

In your first Sub , where you have Variant declarations, an implicit conversion in Variant not performed before going to Int - the highest order in the mathematical expression is Variant , therefore , an implicit conversion is not performed before passing the result to Int() - Variant by - still contains the untreated float IEEE 754.

Int documentation :

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

Rounding is not performed. The top code calls Int(39.99999999999997) . The lower code calls Int(40) . The “answer” depends on what level of floating point error you want to round. If 15 works, then 40 is the “correct” answer. If you want to draw up to 16 or more significant digits, then 39 is the “correct” answer. The solution is to use Round and specify the level of accuracy you are looking for explicitly. For example, if you need the full 15 digits:

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

Keep in mind that the maximum accuracy you use anywhere in the inputs is 6 digits, so this will be a logical rounding:

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

Answer 2

If you get rid of the Int () function on both lines where the damage is calculated, both end the same. You should not use Int, as this creates erroneous behavior, you should use CLng when converting to a long variable, or if the damage was Int, you should use CInt.

Int and CInt behave differently. Int is always rounded to the next lower integer, while CInt is rounded up or down using Banker rounding. You will usually see this behavior for numbers that have a 0.5 mantissa.

Regarding options and double differences, if you use TypeName for MsgBox for the 1st block of code, you will find that both the attack and the protection, after the values ​​were assigned, were converted to double, despite the fact that they were declared as an option,