Overflow - why?

[Rijnsent]

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

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

 

[joeu2004]

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]

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).

 

[joeu2004]

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]

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]

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.

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.