Maximum length of addition line in VBA

user_6381427 0
Viewed 78

Is there a maximum length of a line when adding numbers in VBA? For example in MS Access:

 Dim L As Long

    ' this works
    L = 2696 + 2630 + 2860 + 2860 + 2860 + 2860 + 2795 + 2795 + 2630 + 2630 + 2630 + 263

    ' this gives an Overflow error
    L = 2696 + 2630 + 2860 + 2860 + 2860 + 2860 + 2795 + 2795 + 2630 + 2630 + 2630 + 2630

    Debug.Print L

I have never seen this documented. Also, it doesn't throw an overflow error in C# in Visual Studio.

ms-access vba
3 Answers

The type of an integer literal is an Integer (Max +32767):

?typename(2696)
Integer

The result of adding n Integers is also of type Integer:

?typename(2696 + 2630)
Integer

As soon as the accumulation exceeds the bounds of an Integer you will get an Overflow exception, it does not matter that the variable you are assigning to is a Long because the accumulation is performed before the assignment.

The way to fix this is to make one of the values in the addition a Long which you can easily do with the & Type Suffix:

L = 2696& + 2630 + ..
user_2463420

You need to use type literal:

Sub FFF()

    Dim L As Long
    Dim V As Variant

    ' this works
    L = 2696 + 2630 + 2860 + 2860 + 2860 + 2860 + 2795 + 2795 + 2630 + 2630 + 2630 + 263

    ' this DOES NOT give an Overflow error
    V = 2696& + 2630& + 2860& + 2860& + 2860& + 2860& + _
        2795& + 2795& + 2630& + 2630& + 2630& + 2630&

    MsgBox TypeName(V) '//Long

End Sub

UPDATE

It's interesting to note that if you declare your sum variable as Variant, then conversion to Long is done automatically. For instance, in the following code sum variable is Integer before the last addition - after that it has Long type:

Sub SSS()

    Dim sum As Variant, arr As Variant, num As Variant

    arr = Array(2696, 2630, 2860, 2860, 2860, 2860, _
                2795, 2795, 2630, 2630, 2630, 2630)

    For Each num In arr
        '// sum is Integer before last addition
        '// and Long - after it
        sum = sum + num
    Next

    MsgBox TypeName(sum) '//Long

End Sub
user_31870330

You need to specify the type of one of the elements in the expression, VBA should then use that type. I think literal whole numbers are implicitly Integers (16 bit), could be wrong.

So something like this

Dim L As Long
L = 2696& + 2630 + 2860 + 2860 + 2860 + 2860 + 2795 + 2795 + 2630 + 2630 + 2630 + 2630
Debug.Print L, TypeName(L)

Or this would work

Dim L As Long
L = CLng(2696) + 2630 + 2860 + 2860 + 2860 + 2860 + 2795 + 2795 + 2630 + 2630 + 2630 + 2630
Debug.Print L, TypeName(L)
user_48398270
Related