Declaration problem

[davidm]

Can someone help clear this for me?
The declaration of the variable x as Integer works perfectly in:

Sub Declaratn()
Dim x as Integer

For x = 1 to 1000
Cells(x,1) = x
Next

End sub


...but produces overflow error at x=2 in:

Sub Declaratn2()

Dim x as Integer

For x = 1 to 1000
Cells(x,1) = x*30000
Next

End sub

My hunch is that Excel seems to evaluate x*30000 and allocates memory space to the product value. And since "integer" has an upper limit of 32767, the code crashes at x=2 (which produces 60,000). But shouldn't Excel be concerned with allocating memory space to x over the range 1-1000 in this instance? I would welcome an explanation.

TIA

[Simon Murphy]

David
I think you are right
As the formula is multiplying 2 ints VB assumes the result will be an int.
Fixes include:
Cells(x, 1).Value = x * CLng(30000)
Cells(x, 1).Value = x * (38000 - 8000)
Cells(x, 1).Value = clng(x) * 30000
etc

Basically it looks like you need to warn VB that this will overflow, or type x as a long.

cheers
Simon

[davidm]

Simon,

The thrust of my question is that if a variable is declared (as integer etc.), shouldn't its character be solely governed by the values assigned to it-1 to 1000 in the example? I had never thought of declaration affecting the calculations the variable is made to undertake, but that seems to be case. Strange!

[Simon Murphy]

David
sorry I should have been clearer
x is not overflowing, the result of your formula is overflowing. The issues isn't so much what x can and can't do, but how VB guesses the data type of a formula result. (hence why 38000-8000 fixes it)
effectively you are taking a short cut from this:

result = x*30000
Cells(x, 1).Value = result

The question is what is the type of result?
answer: most programming languages assume the largest data type of any of the arguments, which is why changing one to a long fixes it.

If you replace your short version with the 2 lines above and dim result as a long then the problem is still there. VB internally apparently can't calculate x*30000 where x is an int >1.

So your observation is right - the data type does have a big impact on what can be done with a variable, and part of that is how VB guesses the data type of a formula result, based on the data type of all the arguments

I rarely use ints anyway in VBA, I mainly use longs.

cheers
Simon

[davidm]

Simon,

Thanks for your elaborate explanation. I however cannot still get my head round "what a declaration does to a variable" in the light of the quirky behaviour we are analysing. In the sub

Sub OverflowError()

Dim x as integer
Dim result as long

For i = 1 to 10
result = x*30000
next

End sub

as you correctly pointed out, VBA cannot get past x=2, only because "VB internally apparently can't calculate x*30000 where x is an int >1". Given this,
I will driven to wonder when a variable x can be declared as integerin order to benefit from optimal memory location. In other words, when can one profitably go INTEGER and avoid the profligacy of going LONG or DOUBLE.

I know the problem in the above code (as in the original) can be fixed by avoiding INTEGER declaration (and using the default variant) or declaring LONG (or DOUBLE if you will) but these are options we might not want to take if the goal of programming speed and efficiency is pursued. The whole exercise seems a bit academic: nonetheless, I am looking at the greater picture of what can be done to fully harness the advantages of declarating data types appropriately.

David.

[Simon Murphy]

David
Longs are the native size and therefore more effecient than ints on 32 bit machines. Well (slightly) faster anyway, memory is the same I think.

You can keep it as an int if you do the 38000-8000 shuffle (which lets vb know one of the args is a long). This therefore does not affect your declaration of x.

You are right, it is odd, I'm sure I read about this a long time ago, no idea where though. It looks like the issue is still there in .net if thats any consolation.

cheers
Simon

[davidm]

...And Simon, as an interesting side relection, BYTE as a data type even behaves more queerly. Take:

Sub OverflowError2()
Dim x as Byte
Dim result as Long

For x = 1 to 255
result = x*250
Next

End Sub

If VBA or EXCEL were to be consistent in the oddity, the above code should crash at x=2, given that the upper bound of Byte range is 255 and 2*500 > 255 (in the same manner an integer variable fails to be computed over 32767). The surprise is that the above loop rolls on till "result" <= the "magical" 32767. In this example, the code crashes at x=132 when "result" of 32750 exceeds the 32767-barrier. This throws up the question, why should Byte have the same computing limit as Integer when their memory allocations are vastly different: byte has 1 byte, integer 4!

Like you, I seldom, if ever, go INTEGER, preferring LONG or DOUBLE as appropriate.

Finally, I tend to muse time and again over why anyone would want declare a variable as SINGLE? Computing is more about accuracy than speed, so why go SINGLE PRECISION rather than DOUBLE?


Many thanks for your sharing, Simon.