VBA: Unexpected Behaviour of Variant Data Type

Question

As per MSDN, for Variant data types:

“Numeric data can be any integer or real number value ranging 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 though all final values after computation fall well 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 points out:

” However, if an arithmetic operation is performed on a Variant containing a Byte, an Integer, a Long, or a Single, and the result exceeds the normal range for the original data type, the result is promoted within the Variant to the next larger data type. A Byte is promoted to an Integer, an Integer is promoted to a Long, and a Long and a Single are promoted to a Double.”

The documentation states that the type should be promoted when an arithmetic operation exceeds the normal range for the original data type. Why isn’t v4 promoted to Double?

Answer 1

You're working with numeric literals, which aren't Variants. They're interpreted by the compiler as the smallest necessary type to accommodate the literal value, although Byte values will default to type Integer.

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

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

As @dazedandconfused pointed out, you can coerce the literal values to a more suitable type, and the expression will evaluate, and the variant can be assigned.

In order to get the behavior that Microsoft documents for the Variant type, you need to coerce the literal values to variant, before using them in an expression. Both Variants will contain Longs, but you'll get the auto re-typing that the documentation asserts.

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

Answer 2

From https://support.microsoft.com/en-us/kb/199809 - See bolded statement

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

In order to work around this behavior, it is important to always explicitly type numbers when using them in numeric calculations or when defining constants. If the preceding expression is changed to the following:

So changing it to...

v4 = 1132747441# + 1788441323#

...does the trick, but it sure does smell.