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

 

[RoryA]

For Power Pivot you have to have the right Office version. For 365 subscribers, that is a ProPlus or E3,E4 or E5 level. It's also in the Pro Retail version for 2016. Standalone Excel has it too, but I'm not sure how often it gets updated.

All very confusing and a decision that has been loudly criticised by many!

 

[Rick Rothstein]

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.

Thanks for the heads up. Actually, though, it is not my intent to upgrade to the subscription version as I am opposed to subscription software... I just do not like the concept at all.

 

[pgc01]

Thank you Rory

I know that now, my problem is that I did not know that when I subscribed.
I did not even imagine that such a big functionality would be taken out of the product without making it very clear to the general public that wanted to upgrade.

I don't even understand it now, and I've read a lot about it. Lots of comments about this (mostly angry) in the web. Interesting enough, not even one giving a good explanation of the why.

I'm trying to get the company excel. I understand that you'll have the power pivot in the company office.

It's just the general user that does not get to use it.


On another level, not having the help in my disk is annoying.

 

[pgc01]

Thanks for the heads up. Actually, though, it is not my intent to upgrade to the subscription version as I am opposed to subscription software... I just do not like the concept at all.

I used to agree with you but I thought I'd give it a try. Not sure it was a good decision.

 

[RoryA]

Yes, the whole thing was, IMO, very badly thought out. I've heard many reports that even customer support at MS can't figure out which versions do or don't have the various BI bits and pieces.

On another level, not having the help in my disk is annoying.

I couldn't agree more. You can download the 2013 offline help files, but I haven't yet figured out how to integrate them.

 

[petereddy]

Hi Joeu,

On the Microsoft site (Long Data Type (Visual Basic) | Microsoft Docs), they say that Long variables can go up to 9.2E18, but what you're saying is that it's more like 2.1E9. Why the discrepancy?

Thanks, everyone else for your suggestions.

Peter

 

[RoryA]

Visual Basic is not the same as VBA. VBA was based on Visual Basic 6, but that is long gone and those docs relate to VB.Net

 

[petereddy]

Okay. Thanks for clearing up my misunderstanding, Rory.

 

[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.

Hi shg

I don't understand. Can you explain?

Brain cramp, sorry. The value can't be zero if x is zero, and here x is a constant.