CODEWITHSUNDEEP
vbaexcelinteger-overflow
user3516422
user3516422

Reputation: 115

VBA overflow behaviour

I have some vba behaviour that I do not understand. I'm running vba in a macro of excel 2016.

Sub alpha()

Dim a As Integer, b As Long
a = 750
b = 50 * a

Stop

End Sub

Running this sub results in an overflow error. Why?

Variable a is an integer and therefore cannot hold the number 750*50 because that is too high. But variable b is dimensionalised as a long - so it should be able to hold that number.

Strangely - If I change the type of variable a to Long then the code runs through.

Upvotes: 1

Views: 273

Answers (1)

Bathsheba
Bathsheba

Reputation: 234665

The maximum value for an Integer in VBA is 32,767.

You are overflowing that with 50 * a. It's not relevant that you want to assign that to a Long as conceptually the multiplication happens prior to the assignment, and by then, the damage has been done.

Use a Long instead for a or write

b = 50& * a

to force type promotion. Either are good up to 2,147,483,647

Upvotes: 2

Related Questions