Declaration problem
 

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 memor
space to the product value. And since "integer" has an upper limit o
32767, the code crashes at x=2 (which produces 60,000). But shouldn'
Excel be concerned with allocating memory space to x over the rang
1-1000 in this instance? I would welcome an explanation.

TI

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=39800

Declaration problem
 

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


--
Simon Murphy
------------------------------------------------------------------------
Simon Murphy's Profile: http://www.excelforum.com/member.php...o&userid=26538
View this thread: http://www.excelforum.com/showthread...hreadid=398008

Declaration problem
 

Simon,

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

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=39800

Declaration problem
 

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


--
Simon Murphy
------------------------------------------------------------------------
Simon Murphy's Profile: http://www.excelforum.com/member.php...o&userid=26538
View this thread: http://www.excelforum.com/showthread...hreadid=398008

Declaration problem
 

Simon,

Thanks for your elaborate explanation. I however cannot still get m
head round "what a declaration does to a variable" in the light of th
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 becaus
*"VB internally apparently can't calculate x*30000 where x is an in
1". * Given this,
I will driven to wonder when a variable x can be declared as integeri
order to benefit from optimal memory location. In other words, when ca
one profitably go INTEGER and avoid the profligacy of going LONG o
DOUBLE.

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

David

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=39800

Declaration problem
 

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


--
Simon Murphy
------------------------------------------------------------------------
Simon Murphy's Profile: http://www.excelforum.com/member.php...o&userid=26538
View this thread: http://www.excelforum.com/showthread...hreadid=398008

Declaration problem
 

...And Simon, as an interesting side relection, BYTE as a data type eve
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 cod
should crash at x=2, given that the upper bound of Byte range is 25
and 2*500 255 (in the same manner an integer variable fails to b
computed over 32767). The surprise is that the above loop rolls on til
"result" *<=* the "magical" 32767. In this example, the code crashe
at x=132 when "result" of 32750 exceeds the 32767-barrier. This throw
up the question, why should Byte have the same computing limit a
Integer when their memory allocations are vastly different: byte has
byte, integer 4!

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

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


Many thanks for your sharing, Simon

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=39800