Run Time Error '6': Overflow [XL03/XL10] on simple formula

[elifillmore]

While writing some more complex code I encountered a run-time error that has me stumped. I isolated the code to just a few lines and then self contained the code. I've included three different ways of coding the same formula but 2 of the example generate error and 1 works just fine. My question is why? Does anyone know the logic behind the what is generating the overflow errors? Has anyone seen examples like this before?

I tested this on 2 different computers and got the same results.
1) Excel 2003 w XP SP3 (3G/4GB RAM)
2) Excel 2010 w XP SP3 (3G/2GB RAM)

Thanks in advance!

'EXAMPLE 1: (This one works just fine = Msgbox with "900000")

Sub Example1()
Dim N As Long, L As Long
N = 15
L = N * 1000 * 60
MsgBox L
End Sub

'EXAMPLE 2: (Results in a Run Time Error 6: Overflow error. I replaced the N in L with 15 as opposed to the variable)

Sub Example2()
Dim N As Long, L As Long
N = 15
L = 15 * 1000 * 60
MsgBox L
End Sub

'EXAMPLE 3 (Results in a Run Time Error 6: Overflow error. Moved the N variable from the first to the end of the calculation)

Sub Example3()
Dim N As Long, L As Long
N = 15
L = 1000 * 60 * N
MsgBox L
End Sub

 

[RoryA]

Your multiplication is being done using implicit integer values, which are then converted to a Long at the end. If you use:

L = 15& * 1000& * 60&

the & character forces the values to be treated as Longs (you could also use # for Double) so that there is no overflow.

 

[elifillmore]

Interesting... I learn something new every day. Forcing as long with "&" worked great! Thanks for the quick response and expertise!

 

[elmer007]

Your multiplication is being done using implicit integer values, which are then converted to a Long at the end. If you use:

L = 15& * 1000& * 60&

the & character forces the values to be treated as Longs (you could also use # for Double) so that there is no overflow.

Hi Rory, would you mind explaining this a little bit? I'm confused as to why 15*1000*60 wouldn't work as integer, since they appear to be (and their product, also) well within the bounds of -2,147,483,648 through 2,147,483,647. In addition, why does the order matter for N*1000*60 vs 1000*60*N ?

I'm looking to help answer another post: http://www.mrexcel.com/forum/excel-...es-visual-basic-applications.html#post4395084

 

[RoryA]

In VBA an Integer can only be from -32768 to 32767. A Long (which means Long Integer) spans the values you mentioned.

The order matters because of the order of operations and the effective type of the binary operator.
If the left operand is an Integer and the right operand is also an integer, the effective type of the operator is also an integer.
If either operand is a Long, the effective type is a Long.

(the full tables are available in the VBA language specification document)

So when the order is 1000 * 60 * N, both 1000 and 60 are Integers, so the result of the first operation needs to fit into an Integer, which it won't, hence the overflow.

If the first operation is N * 1000 or 1000 * N, the effective type of the operator is a Long, and the result will fit. This also means that the effective type of the next operation is also a long since one of its operands is a Long.

You can also use parentheses to change the outcome: 1000 * (60 * N)
That causes the (60 * N) to be the first operation and since N is a Long, the result is a Long, and the result of the next operation is also a Long.

Equally using N * (60 * 1000) would fail, because the (60 * 1000) part will be done first and return an overflow due to trying to return 60000 as an Integer.

 

[elmer007]

I see, thank you very much! I was working off of this page https://msdn.microsoft.com/en-us/library/06bkb8w2.aspx , but I guess it's a difference between VB and VBA as some other sites reference the -32,768 to 32,767 range for Excel topics...