Overflow - why?

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,270
Office Version
365
Platform
Windows
I'm a bit stumped: why does VBA behave like this? See the Sub below: when I simply Debug.Print 60*24*2*60, I get an Overflow error. So apparently VBA treats that multiplication as an integer and therefore fails as it's too big. But why do the tests after that fail and why do the last two succeed? I mean: I'm happy my code works again, but I don't get why the one Overflows and the other doesn't.
Anybody a clue?
Thanks,
Koen

Code:
Sub TestOverflow()

Dim LimitTime As Double
Dim AddTime As Double

On Error Resume Next
'Test 1
Debug.Print 60 * 24 * 2 * 60
Debug.Print 1, Err.Number, Err.Description
Err.Clear
'Overflow, apparently the Debug.Print can only handle an integer (-32k to +32k)

'Test 2
AddTime = (60 * 24 * 2 * 60)
Debug.Print AddTime
Debug.Print 2, Err.Number, Err.Description
Err.Clear
'Overflow, although AddTime is declared

'Test 3
AddTime = CDbl(60 * 24 * 2 * 60)
Debug.Print AddTime
Debug.Print 3, Err.Number, Err.Description
Err.Clear
'Overflow, although forcing a double, also seen as integer

'Test 4
LimitTime = 1234567890
LimitTime = LimitTime - ((60 * 24) * 2 * 60)
Debug.Print LimitTime
Debug.Print 4, Err.Number, Err.Description
Err.Clear
'Overflow, although there is a value again the logic seems to transform the multiplication into an integer

'Test 5
bla = 60
Debug.Print bla * 24 * 2 * 60
Debug.Print 5, Err.Number, Err.Description
Err.Clear
'NO ERROR, why?

'Test 6
blaTwo = 2
Debug.Print 1234567890 - 60 * 24 * blaTwo * 60
Debug.Print 6, Err.Number, Err.Description 'NO ERROR
Err.Clear
'NO ERROR, why?

On Error GoTo 0

End Sub
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
The default type of such small numbers is Integer, so all of the multiplication is performed as type Integer, which has a positive limit of 32,767.

60*24*2*60 is 172,800, which exceeds that limit.

Simplest work-around: write 60& * 24 * 2 * 60. The "&" suffix forces 60 to be type Long, which then converts the rest of the arithmetic to be performed as type Long.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,937
Office Version
2010
Platform
Windows
Simplest work-around: write 60& * 24 * 2 * 60. The "&" suffix forces 60 to be type Long, which then converts the rest of the arithmetic to be performed as type Long.
Your explanation is correct and that should help the OP avoid this problem in the future; however, since all the values are constants, a simpler workaround is to use 172800 directly instead of making VBA calculate it (the OP can always put a remark at the end of the code line if he needs a reminder where the number came from).
 
Last edited:

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
But why do the tests after that fail and why do the last two succeed?
Sorry, my first response was incomplete.

Test 2 also fails because the type of the right-hand side arithmetic is determined by the types of values on the right-hand side. The conversion to type Double does not occur until the right-hand value is stored.

Test 3 fails because the subexpression 60 * 24 * 2 * 60 is still evaluated as type Integer. It is not converted to type Double until CDbl processes its parameter. If you had written CDbl(60) * 24 * 2 * 60 or more simply 60# * 24 * 2 * 60, all arithmetic would have been performed as type Double (overkill).

Test 4 fails because, again, the subexpression ((60 * 24) * 2 * 60) is evaluated as type Integer. If you had written LimitTime - (60 * 24) * 2 * 60 without the outer parentheses, you still get an integer overflow because the multiplication has precence, so again the subexpression (60 * 24) * 2 * 60 is evaluated as type Integer.

Test 5 works because variable "bla" is type Variant by default. Initially, bla=60 results in a Variant subtype Integer. Likewise for bla*24*2, because 2880 fits in an Integer. But when that is multiplied by 60, VBA automatically converts bla*24*2 to a the next "larger" type (Long) to fit the result.

Test 6 works because VBA recognizes that the large constant 1234567890 requires type Long. The rest of the calculation is then converted to type Long.
 

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,270
Office Version
365
Platform
Windows
Hi Joeu,
thanks, that clears up a lot! So in case of a multiplication, VBA uses the "biggest/largest" value of the multiplication as the end type of that multiplication. E.g. this also errors out, because it's greater than a long (the biggest item in the multiplication): Debug.Print 60000 * 24 * 2 * 60000 .
Thanks again,
Koen
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
thanks, that clears up a lot! So in case of a multiplication, VBA uses the "biggest/largest" value of the multiplication as the end type of that multiplication.
You're welcome. But this is issue is not specific to multiplication. And the data type of any arithmetic operation is determined in a left-to-right evaluation, subject to operator precedence and subexpression parenthesization, not the largest value in the overall expression.

For some details, see the help pages in VBA for "operator precedence", "+ operator", "* operator", etc.

For example, 60& * 24 * 2 * 6000 works (17280000) because type Long 60& appears first, so 60&*24*2 is type Long and the last multiplication is perform as type Long.

But 24 * 2 * 6000 * 60& overflows because 24*2*6000 is performed as type Integer (largest implicit type so far, left-to-right) and 288000 exceeds type Integer, even though 60& is type Long.

Likewise 60& + 24*2*6000 overflows even though 60& is type Long and it appears first, because the subexpression 24*2*6000 is performed as type Integer due to operator precedence. That is, the multiplications are done before the addition.

It can be very complicated.

And of course, we encounter the same problems with variables, where it is not feasible to suggest that we do the arithmetic ourself to avoid overflows.

For example, the following overflows, just like its constant equivalent.

Code:
Sub doit()
Dim a As Long, b As Long, c As Long, d As Long
a = 60000
b = 24
c = 2
d = 60000
Debug.Print a * b * c * d    ' overflows
End Sub
Changing d to CDbl(d) or changing d to type Double works around the problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,684
Messages
5,470,118
Members
406,681
Latest member
sachinmasurkar

This Week's Hot Topics

Top