Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow error occurs when multiplying integer constants in excel
An overflow error occurs when 2 integer constants (< 32768) are multiplied
that results in a value that is higher than what an integer can hold - see code below. This happens all the excel versions I tested. Anyone out that aware of this problem ? Sub Mult_Of_2_IntegerConstants_that_result_in_a_LongIn t() Dim i1 As Long i1 = 1 * 32767 ' This works since the result is less than 32768 i1 = 2 * 32767 ' This results in an OVERFLOW (the answer is = 32768) i1 = 2 * 32768 ' This works since one of the operands is = 32768 End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow error occurs when multiplying integer constants in excel
Anyone out that aware of this problem ?
if you check help for overflow error 6 then you'll see why, try i1 = 2 * CLng(32767) Mike "Abe Thomas" wrote: An overflow error occurs when 2 integer constants (< 32768) are multiplied that results in a value that is higher than what an integer can hold - see code below. This happens all the excel versions I tested. Anyone out that aware of this problem ? Sub Mult_Of_2_IntegerConstants_that_result_in_a_LongIn t() Dim i1 As Long i1 = 1 * 32767 ' This works since the result is less than 32768 i1 = 2 * 32767 ' This results in an OVERFLOW (the answer is = 32768) i1 = 2 * 32768 ' This works since one of the operands is = 32768 End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow error occurs when multiplying integer constants in excel
32,767 is the largest positive number you can have not 31768.
You are dealing with 16 bit signed arithmetic using 2's compliment. The MSB bit is the sign so your range of numbers are When you use twos compliment you invert evry bit and then add 1 the range of numbers are 7FFF (largest postive number) to 8000 + 1 = 8001 (largest negative number) 8000 is not used it is usually refered to Negative Zero. 7FFF (hex) = 32767 "Abe Thomas" wrote: An overflow error occurs when 2 integer constants (< 32768) are multiplied that results in a value that is higher than what an integer can hold - see code below. This happens all the excel versions I tested. Anyone out that aware of this problem ? Sub Mult_Of_2_IntegerConstants_that_result_in_a_LongIn t() Dim i1 As Long i1 = 1 * 32767 ' This works since the result is less than 32768 i1 = 2 * 32767 ' This results in an OVERFLOW (the answer is = 32768) i1 = 2 * 32768 ' This works since one of the operands is = 32768 End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow error occurs when multiplying integer constants in excel
You need to use the #
i1 = 2# * 32767# ' This results in an OVERFLOW (the answer is = 32768) Excel is using integer type for 2 and 32767 and the multiplication of these two numbers is an overflow. The assignment to I1 occurs later. "Abe Thomas" wrote: An overflow error occurs when 2 integer constants (< 32768) are multiplied that results in a value that is higher than what an integer can hold - see code below. This happens all the excel versions I tested. Anyone out that aware of this problem ? Sub Mult_Of_2_IntegerConstants_that_result_in_a_LongIn t() Dim i1 As Long i1 = 1 * 32767 ' This works since the result is less than 32768 i1 = 2 * 32767 ' This results in an OVERFLOW (the answer is = 32768) i1 = 2 * 32768 ' This works since one of the operands is = 32768 End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow error occurs when multiplying integer constants in ex
Yes that works, but that just confirms the problem.
It appears to me that the temporary holding value in excel this case is just another integer. Should probably be a at least a long int ? Note that it works when larger numbers are multiplied. I think its a bug ... with an easy workaround. "Mike H" wrote: Anyone out that aware of this problem ? if you check help for overflow error 6 then you'll see why, try i1 = 2 * CLng(32767) Mike "Abe Thomas" wrote: An overflow error occurs when 2 integer constants (< 32768) are multiplied that results in a value that is higher than what an integer can hold - see code below. This happens all the excel versions I tested. Anyone out that aware of this problem ? Sub Mult_Of_2_IntegerConstants_that_result_in_a_LongIn t() Dim i1 As Long i1 = 1 * 32767 ' This works since the result is less than 32768 i1 = 2 * 32767 ' This results in an OVERFLOW (the answer is = 32768) i1 = 2 * 32768 ' This works since one of the operands is = 32768 End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow error occurs when multiplying integer constants in ex
OK got it ... Thanks Joel & Mike for the replies.
"joel" wrote: You need to use the # i1 = 2# * 32767# ' This results in an OVERFLOW (the answer is = 32768) Excel is using integer type for 2 and 32767 and the multiplication of these two numbers is an overflow. The assignment to I1 occurs later. "Abe Thomas" wrote: An overflow error occurs when 2 integer constants (< 32768) are multiplied that results in a value that is higher than what an integer can hold - see code below. This happens all the excel versions I tested. Anyone out that aware of this problem ? Sub Mult_Of_2_IntegerConstants_that_result_in_a_LongIn t() Dim i1 As Long i1 = 1 * 32767 ' This works since the result is less than 32768 i1 = 2 * 32767 ' This results in an OVERFLOW (the answer is = 32768) i1 = 2 * 32768 ' This works since one of the operands is = 32768 End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow error occurs when multiplying integer constants in excel
VB is strange this way. If **all** the numbers you are calculating with are
Integers (that is, a VB Integer... a number between -32768 to 32767), then VB will try to put the answer into an Integer... if any part of the calculation (not the final answer, but the sub-calculations that go into making the final calculation) exceeds what an Integer can hold, then an overflow error is generated. Multiplication and Division have the same order of precedence and, in that case, calculations take place from left to right for the operations with equal precedence. Consider this example... MsgBox 300 * 110 / 200 The 300 gets multiplied by the (111 - 1), which is 110, before the division by 200 takes place... 300 * 110 equals 33000 which is larger than an Integer can hold, hence, an overflow is generated before the division has a chance to reduce the calculation to a number that will fit in an Integer. Now, if **any** one number is made into a numeric data type other than an Integer (this applies to hard coded numbers as well as Dim'med variables), then the problem is avoided (because **all** the numbers are not Integers). You can use the CLng function to force VB to consider a number that would have been an Integer to be a Long instead... MsgBox CLng(300) * (111 - 1) / 200 -- Rick (MVP - Excel) "Abe Thomas" <Abe wrote in message ... An overflow error occurs when 2 integer constants (< 32768) are multiplied that results in a value that is higher than what an integer can hold - see code below. This happens all the excel versions I tested. Anyone out that aware of this problem ? Sub Mult_Of_2_IntegerConstants_that_result_in_a_LongIn t() Dim i1 As Long i1 = 1 * 32767 ' This works since the result is less than 32768 i1 = 2 * 32767 ' This results in an OVERFLOW (the answer is = 32768) i1 = 2 * 32768 ' This works since one of the operands is = 32768 End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run-time error '6' - overflow - once I declare a pre-used variable name as integer | Excel Programming | |||
Overflow error, need help | Excel Programming | |||
Overflow Error | Excel Programming | |||
Overflow error | Excel Programming | |||
overflow error | Excel Programming |