Why cells(1,1) = 500 * 100 causes overflow but 50000*100 doesn't?

Question

I just created a simple sub and it gives an overflow error. However, I don't see anything wrong with the code, and it is really weird since 50000*100 is much bigger 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

Evidently VBA was picking a default type of Integer for the first expression because that type is large enough to hold the literals on the right hand side. 50000 is too big for an Integer so it interprets it as a Long. CLng explicitly triggers a promotion to Long.

Answer 2

The maximim value for a Integer is 32767 and since 50000 is more it is cast as a Long type. Thus the results fits in Long just fine. But in the first case everything is done with Integer types and it overflows.

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

Answer 3

This is because of how VBA evaluates mathematical expressions. The return type of expression will be the type of first operand in the expression or its nearest numeric type equivalent. However, the final result may not fit in that return type and throw overflow error.

When you do 500*100 , return type is integer. While when you do 50000*100 the return type of this expression is Long.

To avoid overflow error you can do an explicit cast to let it know your intentions

CLng(500) * 100

Answer 4

i got the answer from the following link: Link from microsoft

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