Why are cells (1,1) = 500 * 100 causing overflow, but 50,000 * 100 are not?

Question

Well, I just created a simple sub and gave an overflow error. However, I see nothing wrong with the code, and this is really strange, since 50,000 * 100 is much more than 500 * 100.

sub add() 'This will cause an overflow error cells(1,1) = 500 * 100 'But this won't cells(2,2) = 50000 * 100 end sub 

Answer 1

Consider:

 Sub add() 'This works: Cells(1, 1) = CLng(500) * 100 'as does this: Cells(2, 2) = 50000 * 100 End Sub 

Obviously, VBA chose the default Integer type for the first expression, because this type is large enough to hold literals on the right side. 50,000 is too large for Integer , so he interprets it as Long . CLng clearly launching a promotion to Long .

Answer 2

The maximum value for a Integer is 32767 , and since 50000 larger, it is cast as a Long type. Thus, the results are great for Long . But in the first case, everything is done with Integer types and overflowed.

 (Integer=500) * (Integer=100) = (Integer=50000) 'Overflow (Long=50000) * (Integer=100) = (Long=5000000) 'Ok 

Answer 3

This is due to how VBA evaluates math expressions. The return type of the expression will be the type of the first operand in the expression or its closest equivalent to a numeric type. However, the end result may not correspond to this type of return and throw overflow error.

When you make 500 * 100, the return type is integer. If you make 50,000 * 100, the return type of this expression is longer.

To avoid an overflow error, you can make an explicit cast so that it knows your intentions.

CLng(500) * 100

Answer 4

I got the answer at the following link: Link from Microsoft

It seems that even I did not assign the type to the number, excel automatically assigns it to it based on its length. Thus, 500 is defined as an integer, and the result of 50,000 is too large for an integer type. That's why.