Unexplained problem with Overflow Error 6

[Kelvin Stott]

I have tracked down an overflow Error 6 to the following line:

dMax = Int(s * DPoints / SPoints)

Where all variables are defined as Integers. The error goes away when I define s as Long, but I don't understand why this should be required since none of the individual variables ever exceed the scope of an Integer (-32768 to 32767).

Strangeley, I get the same overflow problem when I type either of the following calculations in the immediate window:

? Int(328 * 100 / 1000)
? 328 * 100 / 1000
? 328 * 100

Note that no variables are defined, so why the overflow problem? Surely Excel/VBA should be able to work with this very basic calculation without any problem, by assigning the appropriate dimension, no?

Is this a known problem with Excel/VBA?

PS. I am using Windows 7, with Excel 2010 (updated from 2009).

Thanks for any help,

Kelvin

 

[Jonmo1]

The resulting value does not exceed the limit of the Integer type.

 

[Kelvin Stott]

True, but neither is it an integer.

And note the following *does* exceed the integer limit, but still works fine:

? 328 / (1/100) = 32800

i.e., integer / (integer / integer) -> Long

Very strange!

 

[Jonmo1]

It does 1/100 first because you put that in parens
1/100 = 0.01 = Double

328 / 0.01 = Integer/Double - different datatypes - results in Double Not long.

 

[Kelvin Stott]

... which explains your first reply very nicely, thanks.

 

[Jonmo1]

No problem, great thread.

I did forget to mention in my first reply about how parens will force the order of calculations.

 

[Kelvin Stott]

I guess it just handles addition, subtraction, multiplication and division of integers differently, as follows:

integer + integer -> integer (subject to overflow, e.g., 20000 + 20000)
integer - integer -> integer (subject to overflow, e.g., 20000 - -20000)
integer * integer -> integer (subject to overflow, e.g., 1000 * 1000)
integer / integer -> double (no potential problems)

 

[Jonmo1]

I guess it just handles addition, subtraction, multiplication and division of integers differently, as follows:

integer / integer -> double (no potential problems)

No potential problems because the resulting value of INT/INT will NEVER exceed the limit of the Integer datatype.
The resulting Number will always be either Equal to or Less than the larger of the 2 Integers.

Not sure why it converts to double when the resulting value is a whole number
100/10 = 10
10 is still a whole number, I would think it would still be an integer, but it is double for some reason.
That I'm not sure why.

 

[Kelvin Stott]

As per my last post, I think it assumes that one integer divided by another integer will always be a decimal (double), even if the actual answer happens to be another integer (whole number).

Whereas it assumes that one integer plus/times/minus another integer will always be another integer, which is true except for the fact that the scope of an integer can be exceeded in certain cases (causing the overflow in the examples I gave).

Make sense?

 

[Jonmo1]

As per my last post, I think it assumes that one integer divided by another integer will always be a decimal (double), even if the actual answer happens to be another integer (whole number).

Probably not so much that it assumes it will ALWAYS be a decimal..
But just that it knows it MIGHT be a decimal, therefor it uses the datatype that can handle either whole# or decimal.


But yeah, I think we've covered it pretty well now.

 

[Kelvin Stott]

...except for the implications, and I see only 2 sure-fire ways of avoiding these potential overflow errors:

1. Check through all code where integers are used (or assumed by excel as per above) and what scope of values they may take under all potential scenarios, at each incremental stage within every single calculation.
2. Define all numerical variables as long or double, just to be on the safe side.

Clearly option 1 is a pain in the behind.