VBA overflow behaviour

Question

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.

the language does not know (yet) that the result will end up in b, all it has is the "50" so it chooses an integer to begin with. — S Meaden
– S Meaden, Commented Apr 26, 2018 at 8:45
Or b = 50 * cLng(a): VBA is not doing an implicit conversion from Integer to Long until after it has done the calculation. — Chronocidal
– Chronocidal, Commented Apr 26, 2018 at 8:49
And read why you can always use Long instead of Integer in VBA because there is no benefit in using Integer at all. — Pᴇʜ
– Pᴇʜ, Commented Apr 26, 2018 at 8:50
Possible duplicate of Overflow when multiplying Integers and assigning to Long — Pᴇʜ
– Pᴇʜ, Commented Apr 26, 2018 at 8:58

Bathsheba · Accepted Answer · 2018-04-26 09:10:05Z

2

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

edited Apr 26, 2018 at 9:10

answered Apr 26, 2018 at 8:58

Bathsheba

235k35 gold badges376 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA overflow behaviour

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related