excel - 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.