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.

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.

Originally Posted by Ed_Collins

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.

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.

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.

Originally Posted by Ed_Collins

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.

Understood.

I learned something today. Thanks again.

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.

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.