+ Reply to Thread
Results 1 to 14 of 14

Run-time error 6: Overflow - Trying to generate list of combinations

  1. #1
    Registered User
    Join Date
    04-24-2008
    Posts
    85

    Run-time error 6: Overflow - Trying to generate list of combinations

    Hello, I'm running into a problem and am wondering if there's an easy way to get around this.
    I have three columns of information and I'm trying to get every possible combination. This works when the lists are small, however when I use my actual data - I get an overflow error.
    These are the lengths of my list:
    Column1 = 6
    Column2 = 90
    Column3 = 389

    This is my code:
    Please Login or Register  to view this content.
    Is there any easy way around this?
    I get this error after it generates about 32,767 rows..

    Column2 is also dependant on Column1... ie: if Column1 value was A, column2 selection can be reduced to a much smaller amount -- this could potentially help, but I'm unsure of how to code this in - I adapted this code that I'm using from doing some google searching and extensive trial & error.

    Thanks for any suggestions.
    Adam
    Last edited by AdamParker; 05-22-2013 at 09:27 AM. Reason: Solved

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Run-time error 6: Overflow - Trying to generate list of combinations

    Change Integer to Long
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    04-24-2008
    Posts
    85

    Re: Run-time error 6: Overflow - Trying to generate list of combinations

    Thanks for the suggestion - unfortunately that did not affect the outcome.
    Same error after the same amount of results were populated.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Run-time error 6: Overflow - Trying to generate list of combinations

    Post a workbook that demonstrates the error.

  5. #5
    Forum Contributor
    Join Date
    10-13-2012
    Location
    Southern California
    MS-Off Ver
    Excel 2007
    Posts
    401

    Re: Run-time error 6: Overflow - Trying to generate list of combinations

    This code worked for me. It generated 210,060 different permutations. (6x90x389 = 210,060)

    (trivia: Since the order matters, it's permutations, not combinations. A combination lock should really be called a permutation lock.)

    http://www.mathsisfun.com/combinator...mutations.html

    Please Login or Register  to view this content.
    Last edited by Ed_Collins; 05-21-2013 at 07:53 PM. Reason: Give link to permutation / combination description

  6. #6
    Forum Contributor
    Join Date
    10-13-2012
    Location
    Southern California
    MS-Off Ver
    Excel 2007
    Posts
    401

    Re: Run-time error 6: Overflow - Trying to generate list of combinations

    Note: It takes about 30 seconds to generate the list on my laptop. But then again, I have a very slow work laptop. At home I'll bet my PC can generate the list is much less time. Be sure to include Application.ScreenUpdating = False (and then True), or it will even take longer to run.

  7. #7
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: Run-time error 6: Overflow - Trying to generate list of combinations

    Quote Originally Posted by Ed_Collins View Post
    Note: It takes about 30 seconds to generate the list on my laptop. But then again, I have a very slow work laptop. At home I'll bet my PC can generate the list is much less time. Be sure to include Application.ScreenUpdating = False (and then True), or it will even take longer to run.
    Ed,
    Could I suggest you do the job with memory arrays rather than worksheet cells.
    I think you'd find it a lot faster. And you wouldn't need screen updating.
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    10-13-2012
    Location
    Southern California
    MS-Off Ver
    Excel 2007
    Posts
    401

    Re: Run-time error 6: Overflow - Trying to generate list of combinations

    Very, very nice.

    I've never, ever used that resize property before. Wasn't even aware of it. I'm going to have to take a look at that. Thanks.

  9. #9
    Forum Contributor
    Join Date
    10-13-2012
    Location
    Southern California
    MS-Off Ver
    Excel 2007
    Posts
    401

    Re: Run-time error 6: Overflow - Trying to generate list of combinations

    Okay, I'm still not sure what is going on or why that works.

    I understand storing the x, y, and z values in a two dimensional array called a. That much is easy and clear.

    But then you reference the array when done, without specifying which element. That I've never done with an array before. And you use this resize property, using the variable i, (which after the three FOR NEXT loops are finished, should be equal 210061) And after doing so then suddenly all cells are filled in, instantly, albeit down a row from where it was before.

    So what's all going on?
    Last edited by Ed_Collins; 05-22-2013 at 12:24 AM. Reason: typo

  10. #10
    Forum Contributor
    Join Date
    10-13-2012
    Location
    Southern California
    MS-Off Ver
    Excel 2007
    Posts
    401

    Re: Run-time error 6: Overflow - Trying to generate list of combinations

    Never mind. I finally played around with it and broke it all down and figured it all out.

    Again, I've never used resize before. Pretty slick. Thanks.

    This might have made a tiny, tiny bit more sense to me, initially.

    Please Login or Register  to view this content.
    Notice I dimension the array only as large as intended. (It confused me, for a minute, as to why the array was dimensioned the way that it was.) I also reference the entire array this way: a() which I thought was required. (Never seen it referenced without the parenthesis before. Finally, if the user wanted the permutations to begin in row 2, the above code does that.

  11. #11
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: Run-time error 6: Overflow - Trying to generate list of combinations

    Quote Originally Posted by Ed_Collins View Post
    Never mind. I finally played around with it and broke it all down and figured it all out.

    Again, I've never used resize before. Pretty slick. Thanks.

    This might have made a tiny, tiny bit more sense to me, initially.

    ...
    ...

    Notice I dimension the array only as large as intended. (It confused me, for a minute, as to why the array was dimensioned the way that it was.) I also reference the entire array this way: a() which I thought was required. (Never seen it referenced without the parenthesis before. Finally, if the user wanted the permutations to begin in row 2, the above code does that.
    @Ed,

    Glad that you, and hopefully the OP, found the demo interesting and maybe useful.

    My 1 million row dimension was only because I didn't bother to multiply 6*90*389. However sometimes the minimum array size isn't known in advance, so this must be handled however best (say by arbitrary allocation, by ReDim Preserve, etc.)

    The 10^6 did set aside somewhat more computer memory than the minimum needed for this problem, but with the abundant memory on computers these days it mattered but little unless maybe part of a larger code. It made negligible or no difference to the speed of executing this code.

    Screen updating disabled if anything slows this type of code slightly. Only if you're using worksheet cells or worksheet arrays is it useful.

    Dim a(of whatever dimensions) tells the VBA that you're using a memory array of whatever dimensions.

    But, once declared (or dimensioned), the VBA knows what you mean by a , so the parentheses become redundant as far as the entire array is concerned. Use them or not, just as you like. Parts of it though do need to be specified, such as a(i,3).

    Also, my modification of your code above to use memory array rather than cells actually required Dim a(1 to 210061, 1 to 3) because you started with i=2 (I tried to modify your code as little as possible). Your later version didn't do this so 210060 is OK.

  12. #12
    Forum Contributor
    Join Date
    10-13-2012
    Location
    Southern California
    MS-Off Ver
    Excel 2007
    Posts
    401

    Re: Run-time error 6: Overflow - Trying to generate list of combinations

    Understood.

    I learned something today. Thanks again.

  13. #13
    Registered User
    Join Date
    04-24-2008
    Posts
    85

    Re: Run-time error 6: Overflow - Trying to generate list of combinations

    Thanks for all the help and the additional techniques...
    For my purposes, the Application.ScreenUpdating = False stopped the Overflow, however - it does take about 30 seconds to run.
    I'll play around with this second set of code to see if I can speed things up. Thanks for your help everyone, very much appreciated.

  14. #14
    Forum Contributor
    Join Date
    10-13-2012
    Location
    Southern California
    MS-Off Ver
    Excel 2007
    Posts
    401

    Re: Run-time error 6: Overflow - Trying to generate list of combinations

    kalak's code populates the cells instantly on my machine - there is no delay whatsoever. Prior to this my prior code took 15 seconds to run on my PC here at home. (I knew my home PC was much faster than my laptop.)

    However, I don't understand why Application.ScreenUpdating = False stopped your overflow message. It shouldn't have.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1