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, Apr 26 '18 at 08: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, Apr 26 '18 at 08:49
And read why you can [always use `Long` instead of `Integer`](https://stackoverflow.com/a/26409520/3219613) in VBA because there is no benefit in using `Integer` at all. — Pᴇʜ, Apr 26 '18 at 08:50
Possible duplicate of [Overflow when multiplying Integers and assigning to Long](https://stackoverflow.com/questions/17315650/overflow-when-multiplying-integers-and-assigning-to-long) — Pᴇʜ, Apr 26 '18 at 08:58

Bathsheba · Accepted Answer · 2018-04-26T09:10:05.943

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 '18 at 09:10

answered Apr 26 '18 at 08:58

Bathsheba

231,907
34
361
483

VBA overflow behaviour

1 Answers1