1

The following evaluates to 32760, an integer:

Debug.Print (20 * 1638 - 1)

But this raises an overflow error:

Dim t as Integer
t = 1639
Debug.Print (20 * t - 1)

It seems like this is implicitly expecting an Integer return value, because if I do either of the following, the error is avoided.

Dim t as Long OR Debug.print (20 * CLng(t) - 1) OR Debug.Print (20# * t - 1)

Is this behavior documented?

Is my assumption accurate? Namely, that arithmetic of integers implies an integer return value, and that simply introducing one Long or Double value in to the equation will avoid the error?

Improve this question
6
  • 1
    stackoverflow.com/questions/3301733/vba-what-is-97-45-1 Commented Feb 25, 2015 at 18:30
  • 1
    That's an excellent answer you linked to, thanks @TimWilliams. Commented Feb 25, 2015 at 18:38
  • Useful for me too... Commented Feb 25, 2015 at 18:40
  • It may also be worth noting that VBA converts all Integer values to Long type, so there isn't any real benefits (which I can think of) in using Integer over Long. msdn.microsoft.com/en-us/library/office/… Commented Feb 26, 2015 at 11:07
  • 1
    @SOofWXLS AFAIK it's an internal conversion to long data type, if you try to put that result in to a typed variable as Integer, an overflow results. For my OP here, when doing math operations on all integer type data, VBA assumes the implied result is also Integer. It is quirky in this regard. Commented Apr 21, 2015 at 13:53

1 Answer 1

Reset to default
1

If my logic is correct, Dim or 'Dimension' is a way of telling the application that you expect use a variable of a certain type, and that type pertains to a certain amount of 'bits' (of memory).

This reserves a section of the system's memory, which has been allocated a certain amount of bits dependant on the variable type that you have instructed in your code. These bits then define how many (If you're familiar with C++ or similar then you will probably already know all this...)

An Integer is 16 bits in VBA and is a signed integer which means we can store negative values too, so the limit is 32,767 because this is the biggest number we can achieve with 16 bits:

(generally a variable can hold 2^n where n = number of bits)

unsigned 16 bits =         0 - 65,536    (2^16)
signed 16 bits   =   -32,768 - 32,767
32,767           =    111111111111111    (Binary)
32,768           =    1000000000000000 <--- note the extra bit

This extra bit is what causes the "overflow" error - because the amount of bits required to produce the number overflows the amount of bits that the memory has to store the number safely.

I don't think the method of the calculation is documented to this extent, however your code snippet:

Dim t as Integer
t = 1639
Debug.Print (20 * t - 1)

would require t to be first be multiplied by 20, resulting in a figure of 32,780:

20 * t = 20 * 1639 = 32,780
32,780 = 1000000000001100 (Binary)

which overflows the bit limit for the Integer data type. At this point the system throws an error before it has the chance to proceed with the rest of the calculation because it tries to multiply t whilst still in it's allocated memory address, for which only 16 bits of memory have been reserved.

Also, not declaring t as a type will force VBA to default to type Variant which will assess that t needs to have more memory allocated when the calculation runs and push it into the Long boundary automatically.

Update: It would appear that VBA will only permit the highest amount of bits held by a variable within the equation for the return value, as can be seen in this example:

Sub SO()

Dim t As Integer, c As Long

t = 1639
c = 20

Debug.Print (20 * (t - 1))  '// No Error
Debug.Print (c * (t - 1))   '// No Error
Debug.Print ((c * t) - 1)   '// No Error

c = (20 * t - 1)            '// Error
Debug.Print (20 * t - 1)    '// Error

End Sub

Although I don't believe this is documented anywhere, it would lead one to believe that VBA limits memory usage to the highest amount of bits being used by a variable at any one time.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

@David Zemens I fully appreciate with a rep of 21k+ you will already know most of this - it's mainly for the benefit of the VBA community that aren't as 'clued up' on memory allocation and OOP.
+1 all very good information! It doesn't answer the question per se (i know why a long causes an overflow in an integer, but wasnt sure why certain arithmetic would cause an overflow independent of a type container, i.e., multiplying an integer x integer implies an integer output even if assigning the result to a property capable of accepting a Long!) but should still be useful for others with similar q's in the future. Thanks!
@DavidZemens I've added some more onto my answer after playing around some more, I think ultimately without any official documentation all we can do is make 'educated guesses' at what the reason is. VERY interesting though so I hope some more information comes to light - good question!
You've got too many parentheses in the Debug statements or, you've put parentheses in such a way that changes the order of operations: (20 * t - 1) == ((20 * t) - 1)., (20 * t - 1) will throw an error, because of the implied output type as discussed here. The issue is that the product of an expression consisting only of integers can potentially cause an overflow even if assigning to a Long/Double container, because the implied output will be of type Integer -- try it: Dim val as Long: val = (20 * 1639 * 1).
Yeah, I understand that - I think I just misunderstood the question and went on a bit of a tangent, oops!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.