Long Variable Type Overflow

[petereddy]

Hi Everyone,

I'm getting the VBA run-time error 6, "Overflow."

I've defined what I think are the applicable variables as long, and I think that should be enough for my purposes.

I have

Dim dArray(0 to 9), mySum As Long

and the problem is at the line where two 10-digit numbers are being added together:

            mySum = mySum + dArray(0) * 1000000000 + dArray(1) * _
                100000000 + dArray(2) * 10000000 + dArray(3) * 1000000 _
                + dArray(4) * 100000 + dArray(5) * 10000 + dArray(6) * _
                1000 + dArray(7) * 100 + dArray(8) * 10 + dArray(9)

This is when I get the overflow error.

How can I fix my code so that it doesn't crash? Thanks for your help.

Peter

Here's the complete code:

Sub generateList()
    Dim myArray(0 To 9), dArray(0 To 9), digitsArray(0 To 9) As Long
    Dim digitPosition As Long
    Dim accumulatedValue, myFactorialArray(0 To 9), mySum As Long
    myFactorialArray(0) = 1
    digitsArray(0) = 0
    mySum = 0
    For j = 1 To 9
        myFactorialArray(j) = j * myFactorialArray(j - 1)
    Next j
    For i = 0 To 3628799
        For j = 0 To 9
            digitsArray(j) = j
        Next j
        accumulatedValue = 0
        For j = 0 To 9
            digitPosition = Int((i - accumulatedValue) / _
                myFactorialArray(9 - j))
            dArray(j) = digitsArray(digitPosition)
            accumulatedValue = accumulatedValue + digitPosition * _
                myFactorialArray(9 - j)
            For k = digitPosition To 8 - j
                digitsArray(k) = digitsArray(k + 1)
            Next k
        Next j
        If (((dArray(1) * 100 + dArray(2) * 10 + dArray(3)) Mod 2) = 0 _
            ) And (((dArray(2) * 100 + dArray(3) * 10 + dArray(4)) Mod _
            3) = 0) And (((dArray(3) * 100 + dArray(4) * 10 + dArray(5 _
            )) Mod 5) = 0) And (((dArray(4) * 100 + dArray(5) * 10 + _
            dArray(6)) Mod 7) = 0) And (((dArray(5) * 100 + dArray(6) _
            * 10 + dArray(7)) Mod 11) = 0) And (((dArray(6) * 100 + _
            dArray(7) * 10 + dArray(8)) Mod 13) = 0) And (((dArray(7) _
            * 100 + dArray(8) * 10 + dArray(9)) Mod 17) = 0) Then
            mySum = mySum + dArray(0) * 1000000000 + dArray(1) * _
                100000000 + dArray(2) * 10000000 + dArray(3) * 1000000 _
                + dArray(4) * 100000 + dArray(5) * 10000 + dArray(6) * _
                1000 + dArray(7) * 100 + dArray(8) * 10 + dArray(9)
        End If
        If (i Mod 10000) = 0 Then
            Cells(1, 10) = i
        End If
    Next i
    Cells(1, 1) = mySum
End Sub

 

[mole999]

if you remove the As Long a let excel determine what to use as the normal for it, then step through your code and look at what excel uses in Locals > VBA Data Types | Using Variables, Constants, and Data Types in Access | InformIT

 

[joeu2004]

Dim myArray(0 To 9), dArray(0 To 9), digitsArray(0 To 9) As Long
Dim digitPosition As Long
Dim accumulatedValue, myFactorialArray(0 To 9), mySum As Long

Aside.... The form of the declarations is incorrect if your intent is for myArray and dArray to be type Long as well as digitsArray. Similarly for accumulatedValue and myFactorialArray.

The correct syntax is:

Dim myArray(0 To 9) as Long, dArray(0 To 9) as Long, digitsArray(0 To 9) As Long
Dim digitPosition As Long
Dim accumulatedValue as Long, myFactorialArray(0 To 9) as Long, mySum As Long

Without an "as type<type>" specification, the default type is Variant. Although that is the most flexible type (VBA will change the subtype as needed), it is less efficient in memory and performance. Also, sometimes VBA interprets Variant types in special ways. IMHO, it is best to declare a specific type wheneven VBA permits. (There are some instances where VBA requires a Variant type.)

I'm getting the VBA run-time error 6, "Overflow." I've defined what I think are the applicable variables as long, and I think that should be enough for my purposes. [....] the problem is at the line where two 10-digit numbers are being added together

Your supposition is correct: type Long can accommodate only values between -2,147,483,648 to 2,147,483,647, as explained in the VBA Long Data Type help page.

For larger integers, use type Double (exact integers up to 2^-53; approximate for larger integers), type Decimal (fixed-point value up to 28 digits and some 29-digit values divided between integer and decimal fraction parts); or type Currency (exact fixed-point values between -922,337,203,685,477.5808 to 922,337,203,685,477.5807).

Note that type Decimal can only be used as a Variant subtype. It requires the use of Cdec() to convert other types.

Of the three options, I think type Double is easiest to use and to apply to your current implementation. Type Currency might be my second choice, if the 4-decimal-place limitation does not impact calculations adversely.

Caveat: But beware of storing type Currency values into Excel cells that are formatted as Currency, Accounting or any Custom format with a currency symbol. Use the Value2 property instead of the (default) Value property.</type>

 

[pgc01]

I have

Dim dArray(0 to 9), mySum As Long

and the problem is at the line where two 10-digit numbers are being added together:

            mySum = mySum + dArray(0) * 1000000000 + dArray(1) * _
                100000000 + dArray(2) * 10000000 + dArray(3) * 1000000 _
                + dArray(4) * 100000 + dArray(5) * 10000 + dArray(6) * _
                1000 + dArray(7) * 100 + dArray(8) * 10 + dArray(9)

Hi

On another note, you could replace that statement with:

mysum = mysum + Application.SeriesSum(10, 9, -1, dArray)

This is a simple example that you can test

Sub test()
Dim dArray As Variant
Dim mysum As Variant

dArray = Array(5, 4, 3, 2, 1, 0, 9, 8, 7, 6)

mysum = mysum + Application.SeriesSum(10, 9, -1, dArray)

MsgBox mysum

End Sub

Notice that you still have to review your type declarations.

 

[Rick Rothstein]

mysum = mysum + Application.SeriesSum(10, 9, -1, dArray)

SeriesSum looks like it could be useful... in what version of Excel was it added?

(Looks like I will have to consider upgrading my XL2010 to a higher version.)

 

[MARK858]

SeriesSum looks like it could be useful... in what version of Excel was it added?

(Looks like I will have to consider upgrading my XL2010 to a higher version.)

SERIESSUM function



Applies To: Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac,

from...

SERIESSUM function - Office Support

 

[Rick Rothstein]

from...

SERIESSUM function - Office Support

Heh-heh, I fooled myself. I wasn't thinking about the Excel function when I saw pgc's use of Application.SeriesSum... when I typed Application followed by a dot, there was no SeriesSum offered by the intellisense popup; of course, that is because SeriesSum comes from the WorksheetFunction property which is, in turn, a property of the Application object.

 

[shg]

dArray = Array(5, 4, 3, 2, 1, 0, 9, 8, 7, 6)
mysum = mysum + Application.SeriesSum(10, 9, -1, dArray)

Not that dArray(9) can't be 0 when you use that method.

 

[pgc01]

dArray = Array(5, 4, 3, 2, 1, 0, 9, 8, 7, 6)
mysum = mysum + Application.SeriesSum(10, 9, -1, dArray)

Not that dArray(9) can't be 0 when you use that method.

Hi shg

I don't understand. Can you explain?

 

[pgc01]

(Looks like I will have to consider upgrading my XL2010 to a higher version.)

Hi Rick

Just a caveat, 2 things I was not expecting when I upgraded to the xl365

1 - no help off-line. The help will always go to the web in the help on-line. Every time, even if just to look up a parameter.
Not as funny as you may think it is.

2 - no power pivot. I had not seen this anywhere. You upgrade and you lose a (big) functionality.

Of course, this can also be my ignorance and everyone else knew it when they upgraded.