Improve number combination vba

Hello, Friends,

The following vba is used to combine "n" numbers (written in column A), picked "p" by "p" (written in cell "B1").

Please Login or Register  to view this content.

The Windows Task Manager shows that the memory usage is only 260MB !!!!

So, I would like to ask your help to improve this code in order to INCREASE THE MEMORY USAGE.

Thanks,

John

Pl upload sample data showing what is required result.

Pl upload sample file with data and also showing what is required result.

Why would like to "to INCREASE THE MEMORY USAGE."
Is it for speed reason?
If yes you could speed up your code, removing all .select and use

Please Login or Register  to view this content.

The memory size could be an issue because your code is prepared to have a lot of columns: Sheet added when the number of columns is reached

Are there any improvements to this macro?

Yes come back to my comments

Try next code

Please Login or Register  to view this content.

The purpose of the changes was not to modify the memory used but only to speed up the macro: Is there some improvements ?
It is possible to make the macro to go really faster rebuiling the code and using arrays which means memory ... this is the second step, but do you need it ?

I will see when I can do.
Do you know that just for a 4 digits set it takes 60 s for the first code and 13 for the second one, with just the modifications explained before ... !

Is needed some details ...!
You want to pick up p numbers out of n numbers.
The total number of sets ( combinations ) is given by the formula:
Fact(n) / Fact (p) / Fact(n-p)
Formula crosschecked with you file
in the file you sent n = 30 and p = 10 => Total = 30 045 015
What is the max value for n and p, just to see amount of rows and if memory will be large enought

If that number is correct and you don't have a supercomputer in your living room the only thing left to do for you is to call NASA and ask them to calculate that for you.

As one "worksheet" will not support the total number of rows (2 250 829 575 120), the combinations may be saved in several files.

Clear, this explains some part of your code but then I have some dout about Excel and your computer's capacity to do the job

Assuming total of rows = 2 250 829 575 120 and 1048576 rows per sheet this lead to 2146550 sheets ....!

Dear Friends,

Right now, and that's the reason why I have started this thread, I am concerned with a faster macro code for creating number combinations.

I have always thought that using ARRAYS will bring an expressive speed boost. PCI has confirmed my inicial thought, but I don't know how to write a new code for creating number combinations using ARRAYS.

But one thing I know for sure, that you guys here in this FORUM have a great knowledge and skills in writing macro codes, and are the ones that can help me rewrite this code.

Your assistance will be very much appreciated.

Regards,

John

using ARRAYS would bring an expressive speed boost
Yes and your need is clear now,( it is not to increase the memory used ) but versus the memory size needed for some case I am not sure it is possible while using sheet you can add anther when you reach the botom of the previous one with memory you cannot ...!

OKAY but for the first run I made I had memory issue, we will see what is the limits

my 2 cents/input...

First off, Excel is the wrong tool for this job. There are many reasons why this is the case.

1. VBA is single threaded, thus very slow to do something like this.
2. The results can easily surpass the available (and especially reasonable) space to store them available in Excel.
3. There are tools designed to do this which do it faster and better.

A scripting language/programming language like VBS, Java, C, etc are much better options if you want to make your own tool for this. They can be threaded, making better use of your CPU (and possibly your GPU) for parallel processing. The results are then typically dumped into a database like MySQL/SQL as they can be manipulated, viewed and stored in an efficient and usable way.

Next, when it comes to VBA (or programming/computers in general) using more RAM never makes things faster. To elaborate, using more RAM than actually needed is a waste. In something like this the RAM usage should be pretty low, as its not memory intensive but CPU/GPU intensive.

If you have macros using 11GB of RAM to process there are 2 basic possibilities as to why; A) you actually have that much data and it warrants the usage, B) regardless of the amount of data, your code's drastically inefficient. The first is extremely unlikely.

When programming, the aim is generally using the least resources needed with the fastest outcome. You dont make your code faster by using more resources than the task reasonably needs, you make it faster by using less code and/or more efficient code.

This loops back to my first point, VBA is single threaded. No matter how efficient you make the code, it can only handle 1 thread at a time. So as an example lets say your CPU can handle 8 threads at a time. No matter what you do in VBA you get 1 thread, where a language that can be threaded could use all 8 threads. That may not mean 8x faster in all cases, but generally speaking it would be significantly faster.

Don't want to let you hanging so I got into my archieves and found a code by Myrna Larson which does mainly what you are looking for.

I've adapted it a bit to suit your requirements. The results are put on second sheet column by column. The only restriction is the number of cells on 1 sheet. (rows x columns)

The combinations are comma-seperated.

On Sheet1 column A are the numbers, combinations in B1 then press button.

Tested it and it took under 2 minutes for over 3 million combinations.

Dear Bakerman2,

The code is very fast. I really appreciate your help.

I just would like to ask you one more question: which changes should be done in the code to format the numbers so that the number combinatios are displayed as 01,02,03,04,05,06,07,08,09,10,11,12,13,14,15

Make the numbers be displayed with 2 digits. For example: change "1" to "01"

Thank you very much,

John

Format Column A as Text.

Glad to help and thanks for rep+. Much appreciated.