I don't code in VBA, but I need to debug a code written in it. When I run the code, I get an Overflow error, which I looked at online. I used breakpoints in the debugger to find the line of code that is giving me the error.
This is the line:
lngInt = nIntA * nIntB
The debugger says that nIntA (an integer) = 4,851, nIntB (another integer) = 10. lngInt is a long integer, if I'm reading this line correctly:
dim lngInt As Long
nIntA and nIntB are initialized with:
Public nIntA As Integer
Public nIntB As Integer
If it helps, both are set from an Access form.
Nothing about this seems like it should be giving me an issue, so I'm assuming it's a quirk in VBA. What could be causing the error?
Thank you in advance!
The Integer type is obsolete. You can declare and use a 16-bit integer all you want, and the VBA runtime will enforce its limits, but internally it's (silently) converted to a 32-bit integer anyway, so why not just use Long (32-bit) integers in the first place?
That said, this has nothing to do with a "VBA quirk", and everything to do with the basics:
foo = [expression]
Before the runtime can perform the assignment of foo, the [expression] needs to be evaluated. The "quirk" is that VBA doesn't care about the type of foo until after the [expression] is evaluated, because it's going to have to (implicitly) convert it to that type anyway:
Dim foo As Double
foo = 2 + 2
Should 2 + 2 evaluate to a Double on its own? It doesn't, and doesn't need to either. 2 + 2 evaluates to 4, and since both operands are Integer, the result will be an Integer and so the assignment will be:
Dim foo As Double
foo = CDbl((2 + 2))
Now if you have two Integer values multiplied together, and that the total overflows the type, per the specs you get that overflow error.
Dim foo As Long
foo = 32000 * 2
Because that's still:
Dim foo As Long
foo = CLng((32000 * 2))
Notice the inner parentheses: (32000 * 2) is evaluated as an Integer before the result can be converted to a Long and the result of that conversion assigned to foo.
If one of the operands were a Long, the result would be a Long - because both operands are Integer, the result will be Integer.
If you want funky expressions and mind-boggling implicit conversions, try Javascript. VBA isn't being "quirky" at all here.