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

 

[Kelvin Stott]

PS. Even the Long format can overflow:

? 1000000 * 1000000 -> Overflow

Whereas:

? 1000000 * 1000000.0 = 1000000000000 (no problem)

 

[Jonmo1]

Using a higher datatype than is necessary can effect performance.
Double takes up more space in memory than Integer.
And it takes longer to read the double than to read the integer.

Kinda the whole reason for having so many different datatypes.
You can control how much memory is occupied by your variables depending on the context of your code.

Otherwise, MS could have just created 1 datatype for all types of numbers (double)

 

[shg]

? 1000000# * 1000000#

The # is the type declaration character for a Double.

Table 5.31 of Microsoft's VBA Language Specification (http://msdn.microsoft.com/en-us/library/dd361851(v=prot.10).aspx) explains Let coercion in arithmetic expressions.

 

[Kelvin Stott]

So it's important to strike a balance between option 1 (performance) and option 2 (reliability)...

PS. This has been a great discussion, thanks. I hope it will be useful to others.

I'll leave you alone now.