A disturbing behaviour of DIM (for variable declaration)

  • Thread starter Thread starter davidm
  • Start date Start date
D

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.
 
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
 
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.
Click to expand...
 
Back
Top