Integer Multiplication Overflow and Long Assignment

Question

If I enter the following into the next window, I get a Runtime error '6': Overflow.

MsgBox 24 * 60 * 60 

Why is this?

This also fails:

 Dim giveTime As Long giveTime = 24 * 60 * 60 

Why is this? giveTime declared as long, so 24 × 60 × 60 = 86400 should fit comfortably.

Answer 1

This is a really strange VBA quirk. I am amazed that I have never come across this.

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

So, it seems that the * and + operators return Integer in the first two examples. Of course, in the help file for the * operator (similar to the + operator):

result = number1 * number2

[...]

The data type of the result usually matches the type of the most accurate expression.

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

However, the letter 32,768 in the third example above is by default of type Long (since it is too large to be an integer), and therefore + returns Long; no overflow.

The help file also says the following:

If the result data type [...] is an integer version that overflows its legal range [...], then the result [...] is converted to a long version.

Accent . Now this little rule sounds like common sense, and anyone reasonably suggested that it applies in your case. But your numbers are of type Integer, not Variant / Integer, so VBA does not apply this rule! It makes no sense to me, but how it is and what the documentation says.

Solution: make one of the arguments of your operator * more accurate than Integer (e.g. Long), and the problem will disappear.

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

Actually, and probably why I never came across this quirk, I'm used to declaring all my Integer variables as Long instead, unless there is a particular concern that using Longs instead of Integer will cause memory usage problems or runtime (which almost never happens). Of course, this will not help in cases when you work with literals less than 32,768, because they are of type Integer by default.

---

You are asking in a comment what Variant / Integer is. A variant is basically a container type for any other data type. In the particular case, when you do it, it contains 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 simple old Integer triggers an overflow error:

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

Answer 2

After each number, put #. He defines each number as a double. Think about it, since each number is stored in memory for calculation as a kind of temporary variable. If you define each number, this will allow enough room for calculations.

eg:

Dim x as long

x = 24 # * 60 # * 60 #

or 24 & 'stands for long