1

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!

Erik A
  • 31,639
  • 12
  • 42
  • 67
Joubert Lucas
  • 153
  • 2
  • 12
  • 2
    `Integer * Integer` gives `Integer`, so `4851 * 10` gives `Overflow` before it implicitly converts it to a `Long` to store in `lngInt`. Use `lngInt = CLng(nIntA) * CLng(nIntB)` – YowE3K Oct 30 '17 at 21:13
  • 1
    or simply change the declarations`Public nIntA As Long` and `Public nIntB As Long` – Scott Craner Oct 30 '17 at 21:14
  • 1
    In any case your 16-bit `Integer` variables are internally being computed on 32-bit just like `Long` integers, so why not declare them `As Long` anyway? It's not a "VBA quirk", it's just how expression assignment works: the expression needs to get evaluated before its result can be assigned, and `Integer * Integer` is going to be `Integer`. Don't blame the language, VBA has far fewer "quirks" than people think - in any case it's much less flaky than, say, JavaScript math. – Mathieu Guindon Oct 30 '17 at 21:15

1 Answers1

2

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.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • @YowE3K [MSDN states it rather plainly](https://msdn.microsoft.com/en-us/library/office/aa164506(v=office.10).aspx) - see also [this answer](https://stackoverflow.com/a/26409520/1188513) on the subject. – Mathieu Guindon Oct 31 '17 at 00:42
  • So is what you're saying is that I should declare the two IntA and IntB as Long integers so that their product doesn't cause the overflow? – Joubert Lucas Oct 31 '17 at 12:29
  • @JoubertLucas an expression will evaluate to the widest type used by its operands, so one of the two needs to be a long for the expression to evaluate to a long, yes. – Mathieu Guindon Oct 31 '17 at 12:54