A curious OVERFLOW problem

[Myles]

Has anyone attempted to perform 182*182 operation in VBA? The followin
code generates an ovwerflow error in spite of the declarations.

Sub test()
Dim x as Long

x= 182*182 'overflow error on this line
Msgbox x

End sub

The problem persists even with a DOUBLE declaration for x. Treating
as a variant either by default (no declaration) or explicitly (b
declaration) doesn't help either.

As 181*181=32761, which reminds one of the magical figure 2 ^15 ther
must be some connection here.


What's happening ?


Myle

 

[Rowan Drummond]

It has to do with the way vba does the calculation. Because 182 is an
integer the result which is stored temporarily before being assigned to
the variable x is expected to be an integer. You can get around this by
specifying 182 as a long first either with:

Sub test()
Dim x As Long
Dim num As Long
num = 182
x = num * num
MsgBox x

End Sub

or

Sub test()
Dim x as Long
x= clng(182)*clng(182) 'overflow error on this line
Msgbox x
End sub

See a similar query here: http://tinyurl.com/ctlgv

Hope this helps
Rowan

 

[JE McGimpsey]

VBA will attempt to use the smallest common data type when performing
operations on untyped constants.

So since 182 will fit in an integer, the internal result of the
multiplication will be placed in an integer, even if it will be assigned
to a long integer variable. And yes, an integer can be up to 32767
(2^15-1).

If you declare at least one constant to be of type long, the result will
be calculated and placed in a temporary long internal variable, then
coerced if necessary during assignment to your variable:

a = 182& * 182&

 

[Jay Petrulis]

Don't know the details of exactly why this occurs, but when you get the
error, choose Help from the popup. The last part indicates that the
numbers are being coerced into an Integer, not Long (Integers). Thus,
182 is seen as an integer and the multiplication overflows the limit
for (signed) integers. The Long declaration is of no consequence.
VB[A] sees (short) ints only.

All of the following work, so you might want to restructure your
calculations. My preference would be for test3, but a workaround is
definitely needed.

'''''''''''''''''''''''''''''''''''''''
Sub test()
Dim x As Long

x = CLng(182) * 182
MsgBox x
End Sub

Sub test2()
Dim x As Long

x = 182
MsgBox x * x
End Sub

Sub test3()
Dim x As Long
Const kVal As Long = 182

x = kVal * kVal
MsgBox x
End Sub

Sub test4()
Dim x As Long

x = 182 ^ 2
MsgBox x
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

[vandenberg p]

Put a decimal point after the 182.0 and it will work just fine.
Note VBA will put a # sign in place of .0.

You can also make it work with the following:

y1 = 182
Y2 = 182
x = y1 * Y2

I believe the fact that you enter an integer in the expression
over-rides the declaration so the 182*182 is done as integer
and the limit is 32,767. By using the above the conversion takes
place either to specified data type or the default
and then is multiplied, which avoids overflow.

There will problably be other posts with a more lucid explantion.


Pieter Vandenberg


: Has anyone attempted to perform 182*182 operation in VBA? The following
: code generates an ovwerflow error in spite of the declarations.

: Sub test()
: Dim x as Long

: x= 182*182 'overflow error on this line
: Msgbox x

: End sub

: The problem persists even with a DOUBLE declaration for x. Treating x
: as a variant either by default (no declaration) or explicitly (by
: declaration) doesn't help either.

: As 181*181=32761, which reminds one of the magical figure 2 ^15 there
: must be some connection here.


: What's happening ?


: Myles


: --
: Myles
: ------------------------------------------------------------------------
: Myles's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28746
: View this thread: http://www.excelforum.com/showthread.php?threadid=492084

 

[Myles]

Thanks all who attempted an explanation. The arguments are all plausibl
although it must be said that there is more than meets the eye with th
way EXCEL handles (or fails to handle) variable declarations. W
indeed need to be wary and circumspect when at first blush no dange
seems to lurk. The message is that I can't have EXCEL simpl
calculate x=182*182 without having to change the character of one o
both of the 182's! Thanks to the integer limit!


Myle