Overflow when multiplying Integers and assigning to Long

Accepted answer +16 upvotes

This is a really odd VBA quirk. I’m amazed I’ve never bumped into this.

Dim x As Long
x = 24 * 60 * 60 ' Overflow
x = 32767 + 1 ' Overflow. 
x = 32768 + 1 ' Works fine!

So it looks like the * and + operators return an Integer in the first two examples. Sure enough, in the help file for the *operator (similar for the + operator):

result = number1 * number2

[…]

The data type of result is usually the same as that of the most precise expression.

Your literals 24, 60, and 60 are all of type Integer by default, so your * (or +) operator returns an Integer, which overflows because the result is greater than 32,767.

However, the literal 32,768 in the third example above defaults to Long type (since it is too large to be an Integer) and so the + returns a Long; no overflow.

The help file also says this:

If […] the data type of result is an Integer variant that overflows its legal range […] Then result is […] converted to a Long variant.

Emphasis mine. Now this little rule sounds like common sense, and anyone would reasonably assume that it applies in your case. But your numbers are of type Integer, not Variant/Integer, so VBA doesn’t apply this rule! Makes absolutely no sense to me, but that’s how it is, and that’s what the documentation says.

Solution: make one of the arguments of your * operator be of a type more precise than Integer (e.g. Long) and the problem will go away.

x = CLng(24) * 60 * 60 ' Result is Long, works fine.

In fact, and this is probably why I’ve never bumped into this quirk, I make a habit of declaring all of my Integer variables as Long instead, unless there is a specific concern that having Longs instead of Integers will cause problems with memory use or execution time (which is almost never the case). Granted, this won’t help in cases when you operate on literals smaller than 32,768, because they default to Integer type.

In your comment, you ask what a Variant/Integer is. Variant is basically a container type for any other data type. In the particular case where you make it contain an Integer:

Dim a As Variant ' a is now Empty
a = CInt(32767) ' a is now Variant/Integer
x = a + 1 ' works fine

But as noted above, a plain old Integer triggers the overflow error:

Dim b As Integer
b = 32767
x = b + 1 ' overflow