Geek Answers Handbook

VBA: Unexpected Option Data Type Behavior

According to MSDN for Variant data types:

"Numeric data can be any integer or real numerical value from -1.797693134862315E308 to -4.94066E-324 for negative values ​​and from 4.94066E-324 to 1.797693134862315E308 for positive values.

However, the following code gives an error, even if all final values ​​after the calculation are within the acceptable range:

Sub Test() Dim v1, v2, v3, v4 v1 = 569847501 + 54678 ' OKAY v2 = 7784687414# + 98565821345# ' OKAY v3 = 7784687414# + 1132747441 ' OKAY v4 = 1132747441 + 1788441323 ' FAILS End Sub

MSDN also indicates:

“However, if the arithmetic operation is performed in a variant containing a byte, integer, long or single, and the result exceeds the normal range for the original data type, the result advances within the variant with the next larger data type. Byte advances to an integer, the integer increases to long, and long and solitary - to double.

The documentation states that the type must be raised if the arithmetic operation exceeds the normal range for the original data type. Why isnt v4 moving up to Double ?

+6
source share
2 answers

You work with numeric literals that are not variants. They are interpreted by the compiler as the smallest type needed to hold a literal value, although Byte values ​​will by default be of type Integer .

 Debug.Print TypeName(1132747441) 'Long Debug.Print TypeName(1788441323) 'Long

As @ComIntern points out, you assign the result of 2 longs in the expression, the expression overflows before it is assigned to Variant v4

As @dazedandconfused noted, you can force literal values ​​to a more appropriate type, and the expression will be evaluated, and a variant can be assigned.

To get the behavior that Microsoft documents for the Variant type, you need to force use literals for the version before using them in the expression. Both options will contain Long s, but you will get automatic retyping, which the documentation confirms.

 Sub Test() Dim v1, v2, v3, v4 Debug.Print TypeName(1132747441) 'Long Debug.Print TypeName(1788441323) 'Long Dim v5, v6 v5 = 1132747441 v6 = 1788441323 Debug.Print TypeName(v5) 'Long Debug.Print TypeName(v6) 'Long v4 = v5 + v6 'OKAY Debug.Print TypeName(v4) 'Double v4 = 0 Debug.Print TypeName(v4) 'Integer v4 = CVar(1132747441) + CVar(1788441323) ' OKAY Debug.Print TypeName(v4) 'Double v1 = 569847501 + 54678 ' OKAY v2 = 7784687414# + 98565821345# ' OKAY v3 = 7784687414# + 1132747441 ' OKAY v4 = 1132747441 + 1788441323 ' FAILS End Sub
+6
source

From https://support.microsoft.com/en-us/kb/199809 - see the bold operator

This statement generates an overflow error because 24 * 24 * 60 = 34560, which exceeds the maximum size of 2 byte integers (32767). Visual Basic does not evaluate all expressions to check the size of the result , but instead continues to use a 2-byte temporary space for calculation. The same overflow error occurs if you declare previous values ​​as constants and multiply constants.

To get around this behavior, it is important to always explicitly dial numbers when using them in numerical calculations or when defining constants. If the previous expression is changed to the following:

So change it to ...

 v4 = 1132747441# + 1788441323#

... does the trick, but it really smells.

+6
source

More articles:


All Articles