Overflow error occurs when multiplying integer constants in excel

[Abe Thomas]

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_LongInt()
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

 

[Mike H]

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

 

[joel]

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

 

[joel]

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]

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.

 

[Abe Thomas]

OK got it ... Thanks Joel & Mike for the replies.

 

[Rick Rothstein]

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