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]

Similar Calculations (* similar to /, + similar to-) are done from left to right.

So 328*100 is done first.

328*100 = 32800
32800 > 32767


Try it like
328 * (100 / 1000)

 

[Kelvin Stott]

Thanks, I guess that would work, but now I will have to check and rewrite all of my calculations to make sure this kind of scenario can never occur.

Still, it won't avoid problems with the following:

? 328 * 100

This is a real fundamental problem with Excel/VBA, if you ask me.

 

[Kelvin Stott]

Interestingly, either of the following do work:

? 328.0 * 100
? 328 * 100.0

But not this:

? 328 * 100

Is there a way (setting or command) to make sure that Excel/VBA can handle this?

 

[Jonmo1]

Interestingly, either of the following do work:

? 328.0 * 100
? 328 * 100.0

But not this:

? 328 * 100

When you don't declare a variable datatype, VBA uses Variant.
When Variant is used, you're asking VBA to make it's best guess at which datatype it is.

328 = Integer
328.0 = Double

If you attempt a math operation on 2 different datatypes (328.0 * 100), then VBA adjusts the datatype to the one that can handle either value (double).

Is there a way (setting or command) to make sure that Excel/VBA can handle this?

Always declare your variables.

Don't do math with straight numbers
Declare a Variable and it's datatype.
Assign values to the variables
Do the math with the variables.

 

[Kelvin Stott]

I do always declare my variables, but this is very helpful advice to understand what can go wrong and how, thank you!

 

[Jonmo1]

Glad to help..

Slightly off topic, but this seems pertinent to the subject.

When declaring multiple variables of the same type,
This doesn't do what you would expect

Dim x, y, z As Long

In that scenario, only z has been declared as Long, x and y are Variants.


You would have to do that like

Dim x As Long, y As Long, z As Long



Not sure if you were aware of that or not, but it is definately a little quirk that can cause issues/nightmares with Variable Types.

 

[Kelvin Stott]

Yes, I was aware of that, but thanks for checking just in case. The main problem seems to be in how Excel "decides" which data type to use half way through a calculation that combines several defined variables. It isn't exactly obvious, as in my original example:

dMax = Int(s * DPoints / SPoints), where all individual variables are defined as Integers within scope.

 

[Jonmo1]

If only Excel could go to colledge and get a PHD in Mathematics. LOL.

It's inherent to computers, they just don't "guess" very well.
Although it's not really guessing.

I think...
I think this is what it boils down to
When no datatype is declared, and it's any kind of numeric value, it chooses the lowest possible type it can.
Integer
Long
Single
Double

And when a math operation happens with 2 similar datatypes
328 = Integer
100 = Interer
It expects the result to be the same datatype = overflow.

And when a math operation happens with 2 different datatypes
328.0 = Double
100 = Interer
Then it expects the result to be the higher type = correct expected result

 

[Kelvin Stott]

...except that the following works fine:

? 100 / 7 = 14.2857142857143

Note: integer / integer -> double

So still not exactly obvious how Excel is "thinking".