Solved The Integer Overflow Issue

[Joe Boatman]

Two Integers multiplied or added together must not exceed 32,767, even if assigned to a Long variable, or run-time error 8 occurs.

In the following code n1 and n2 are Integer types - they can hold numbers from -32,768 to +32,767. So if 1 is added to an integer variable that is set to 32767 it should produce error code 8 in MS Office VBA: Overflow.

What if the sum of 32676 + 1 is put into a Long variable which can hold numbers up to 2,147,483,647? Although each integer variable has been assigned a number less than the maximum permitted for Integer types, VBA is unable to put the result into a Long variable type. This is daft!

It gets worse! See the line of code that adds 1 to 32767? It fails with error 8 again. Edit that line to add 1 to 32768 - this succeeds because VBA must be realising that 32768 is too big to be an integer!

Private Sub Integer_TEST()
'Use F8 to step through this code
'Show Locals Window (View menu) to view the values in the variables
    Dim n1 As Integer, n2 As Integer    'numbers ranging in value from -32,768 to 32,767
    Dim nLong As Long   'numbers ranging in value from -2,147,483,648 to 2,147,483,647
    n1 = 32767
    n2 = 1
    nLong = n1 + n2     'Err 8: Overflow
    nLong = 32767 + 1   'Also Err 8
    nLong = 32767 * 1   'Is OK
    nLong = 32767 * 2   'Also Err 8
End Sub

There's no error if n1 and n2 are initially declared as Long types. So if you are dealing with large values like pixels and twips, declare variables with type Long or Single.

 

[Deleted member 147267]

So if you are dealing with large values like pixels and twips, declare variables with type Long or Single.

In your example, you declared a type as Long and got an error anyway? > nLong = 32767 + 1 so I don't understand the meaning of that comment.

It would seem that if you assign a value to a variable typed as long, and that value is 32767 or less, the value is being cast as an integer. I'll bet if you Clng(32767) first or use 32770 instead, it won't error - not that anyone should have to worry about that. If Access is really treating a Long variable as an Integer just because I input a number less than the max for integer then that's bizarre and news to many of us I bet.

 

[Joe Boatman]

I confirm: it's bizarre!

I changed the code as below and still get error 8:
nLong = CLng(32767 + 1) 'Also Err 8

This works: nLong = CLng(32768)

Fortunately we don't often type numbers into code.

I look forward to your comments after you paste my code into an Access VBE module and step though it. Tell me what you think

 

[Deleted member 147267]

I changed the code as below and still get error 8:
nLong = CLng(32767 + 1) 'Also Err 8

Likely you left this in before: nLong = n1 + n2
By passing two integers into nLong first, the problem remains. Would be nice if someone could shed some light on this because after dabbling in Access since v2.0 I can say I have never even read about this, let alone been impacted by it. Perhaps that is because as you say, we seldom hard code values. Would be interesting to know what happens if you say, DLookup 32767 or query from a table Long field and use that.

 

[isladogs]

For info I've now deleted the other duplicate thread.

 

[Cronk]

Not bizarre if you work through the sequence. Two integers are being added before the sum is assigned to the long. The addition is what causes the overload.

This would cause no problem
nLong = n1 nLong = nLong + n2

Nor would
nLong = 32767# + 1

 

[Deleted member 147267]

Not bizarre if you work through the sequence.

Well, I for one disagree with respect to whether or not it is odd/bizarre/whatever. I believe you are saying that this is no problem:
nLong = 32767
nLong = nLong + 1
and I would agree. However, as you say, this is a problem if nLong is 0 (as it will be in the beginning)
nLong = 32767 + 1

nLong is typed as Long - adding two values that are not even variables should not raise the error IMO. It is as if nLong is being treated as Integer in spite of the fact that it was specifically typed as Long.

 

[Cronk]

Do you agree that the addition occurs before the resulting sum is assigned to nLong?

If so, then
? (varType(32767)= vbInteger) True ? (varType(32767)= vblong) False

There are 2 bytes assigned to store the value 32767. Use 32767# and 4 bytes are assigned

The overflow happens before there is any assignment to nLong

 

[Deleted member 147267]

Do you agree that the addition occurs before the resulting sum is assigned to nLong?

Yep and I think I showed that. Still think that 2 numeric values added together without assigning them to a variable and them defaulting to integer type is odd. You shouldn't have to 32767# or Clng(32767) to add them. If you just happened to use 32770 instead, the values are cast as something else and will sum no problem. That's just nuts and just an opinion.

 

[isladogs]

Agree with previous responses.
What you describe isn't bizarre behaviour at all.
This does not create an error as the conversion is done before the sum
nLong = CLng(32767) + 1

 

[Deleted member 147267]

Colin, that's my point. If no one else can see the absurdity of having to convert an number (not a variable) before adding another number to it lest you raise an error, and if that number is larger than the maximum integer value there is no problem but not if it's less, then in the words of Forrest Gump, I have no more to say about that.

 

[isladogs]

Access will always assume the most appropriate number datatype based on the values supplied.
This is (usually) sensible as it ensures the smallest possible number of bytes are used.
So 32767 is treated as integer and 1 as byte.
Summing those will be treated as integer resulting in an overflow error.

Whereas 32768 + 1 will work as the larger number cannot be integer datatype

 

[The_Doc_Man]

Referring to the general issue of VBA syntax, I found the language specification. You can browse through this using the left-hand panel as a navigation device to find paragraphs. It is not for the faint of heart.

[MS-VBAL]: VBA Language Specification

Specifies the VBA Language, which defines the implementation-independent and operating system-independent programming

docs.microsoft.com

It says very clearly in section 2.1, Data Values and Data Types that the number 32767 will be treated as a 16-bit integer. So will 1. In fact, 1 COULD be treated as a byte integer except that it appears on the same line with a 16-bit number so may be coerced (to the next size up from BYTE) statically by the compiler itself.

Section 5.5, Implicit Coercion, doesn't seem to directly address the issue. Or perhaps it actually DOES, but by implication.

It comes down to this: Things on the right hand side of the expression aren't necessarily destinations. The destination (left-hand-side of the expression) is LONG so the compiler will probably have code to up-type the result of the expression. However, in the case named by Joe, both 32767 and 1 fit into 16-bit "implied" containers as selected by rules under section 2.1 and therefore do not trigger an up-type "coercion" before evaluation. I.e. it looks ahead of time like it fits. The compiler does not attempt to predict an overflow. If any OTHER element of the expression was LONG (including use of the # marker on either constant), however, that would trigger static up-type coercion before the run-time action.

EDIT/ADDENDUM: In essence, asking this question asks the compiler to predict the result of an operation at compile time. Even though in the specific case, you could make the prediction, in the general case you cannot. Given that the compiler is producing P-code, not true Intel binary code, it cannot even reliably perform "keyhole optimization" (a way to shorten certain long expressions), mostly because that kind of optimization is done with Boolean values, not integers. You cannot expect the compiler to test whether the sum of a set of constants would overflow. That is why runtime errors exist and have predictable behavior.

I have to say this doesn't surprise me.

 

[Joe Boatman]

Phew!