Solved The Integer Overflow Issue (1 Viewer)

Joe Boatman

New member
Local time
Today, 15:34
Joined
May 30, 2020
Messages
25
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!
Code:
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.
 

Micron

AWF VIP
Local time
Today, 11:34
Joined
Oct 20, 2018
Messages
3,476
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

New member
Local time
Today, 15:34
Joined
May 30, 2020
Messages
25
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 :)
 

Micron

AWF VIP
Local time
Today, 11:34
Joined
Oct 20, 2018
Messages
3,476
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

MVP / VIP
Local time
Today, 15:34
Joined
Jan 14, 2017
Messages
18,186
For info I've now deleted the other duplicate thread.
 

Cronk

Registered User.
Local time
Tomorrow, 02:34
Joined
Jul 4, 2013
Messages
2,770
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
 

Micron

AWF VIP
Local time
Today, 11:34
Joined
Oct 20, 2018
Messages
3,476
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

Registered User.
Local time
Tomorrow, 02:34
Joined
Jul 4, 2013
Messages
2,770
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
 

Micron

AWF VIP
Local time
Today, 11:34
Joined
Oct 20, 2018
Messages
3,476
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

MVP / VIP
Local time
Today, 15:34
Joined
Jan 14, 2017
Messages
18,186
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
 

Micron

AWF VIP
Local time
Today, 11:34
Joined
Oct 20, 2018
Messages
3,476
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

MVP / VIP
Local time
Today, 15:34
Joined
Jan 14, 2017
Messages
18,186
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

Immoderate Moderator
Staff member
Local time
Today, 10:34
Joined
Feb 28, 2001
Messages
26,999
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.


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.
 
Last edited:

Users who are viewing this thread

Top Bottom