A disturbing behaviour of DIM (for variable declaration)

[davidm]

I have been wracking my brains on what happens when we declare a
variable, in spite of what programming literature says. The code below
declares X as integer and should accordingly handle all values within
the bounds -32763 to + 32673.
But the code generates an overflow error after p=30,000 and when x =3.

Sub Declaratn2()

Dim x As Integer
Dim p As Long
For x = 1 To 1000
p = x * 10000
MsgBox p
Next

End Sub

Is it correct to say that EXCEL is evaluating x against the product
x*1000? I am confused.

David.

 

[Kalpesh]

Hi David,

The reason being - when an integer is multiplied with another integer,
it will return an integer
So, 4 * 10000 (in your case) will make it 40000 & it will try to put
that result in a temporary integer variable (before assigning it to p
- which is long)

So, change your expression to p = clng(x) * 10000
Here, you are telling that multiply a long by an integer

Alternatively, you could write x * 10000.0 (this will make it a double
data type)

HTH
Kalpesh

 

[Rowan Drummond]

Which is not that far off the answer Simon gave back in August <g>.

 

[Dave Peterson]

And another reason never to use "As Integer"

I have been wracking my brains on what happens when we declare a
variable, in spite of what programming literature says. The code below
declares X as integer and should accordingly handle all values within
the bounds -32763 to + 32673.
But the code generates an overflow error after p=30,000 and when x =3.

Sub Declaratn2()

Dim x As Integer
Dim p As Long
For x = 1 To 1000
p = x * 10000
MsgBox p
Next

End Sub

Is it correct to say that EXCEL is evaluating x against the product
x*1000? I am confused.

David.

 

[Gary Keramidas]

you have a great memory, rowan<g>

--


Gary

Which is not that far off the answer Simon gave back in August <g>.