Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A curious OVERFLOW problem
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 -- Myle ----------------------------------------------------------------------- Myles's Profile: http://www.excelforum.com/member.php...fo&userid=2874 View this thread: http://www.excelforum.com/showthread.php?threadid=49208 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A curious OVERFLOW problem
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 he http://tinyurl.com/ctlgv Hope this helps Rowan Myles wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A curious OVERFLOW problem
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& In article , Myles wrote: What's happening ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A curious OVERFLOW problem
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 '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''' Myles wrote: 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...o&userid=28746 View this thread: http://www.excelforum.com/showthread...hreadid=492084 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
A curious OVERFLOW problem
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 Myles wrote: : 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...o&userid=28746 : View this thread: http://www.excelforum.com/showthread...hreadid=492084 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
A curious OVERFLOW problem
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 -- Myle ----------------------------------------------------------------------- Myles's Profile: http://www.excelforum.com/member.php...fo&userid=2874 View this thread: http://www.excelforum.com/showthread.php?threadid=49208 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Just curious | Excel Discussion (Misc queries) | |||
Problem with overflow | Excel Programming | |||
Just curious | Excel Discussion (Misc queries) | |||
Overflow Problem | Excel Programming |