# Thread: A Factorial List

1. I have been trying for some time to figure out what VB code is needed to produce a list a result of a factorial calculation For instance how many different ways can you arrange 5 books. The answer of course the factorial of 5 which is 120. My problem is I then want to produce the list of all of those arrangements. The actual names would be cumbersome but an alphabet or numeric arrangement would work.

From what I have seen on the web it looks as if a Knutf shuffle may be the way to go but so far have not been able to interpret what I have found so I can write into a code module on a spreadsheet. Any suggestions greatly appreciated.

Thanks

Peter

2. See the reply by "iliace" in Permutations/Combinations Problem in VBA - CodeGuru Forums.

Note: the last line of the permuts procedure should be End Sub instead of End Function, otherwise it works OK.

3. Hi Hans

Thanks once again that was a great referral works perfectly. However I must be asleep but it only works to 7 numbers or (alpha characters) . Past that I get a run time error where the results are being transposed into the worksheet. Are there some bounding limits I am missing?

Thanks

Peter

4. You can get a little bit further by changing all instances of "As Integer" in the code to "As Long".

But keep in mind that factorial numbers increase extremely rapidly: 9! is larger than the number of available rows in Excel 97-2003, and 10! is larger than the number of rows in Excel 2007.

5. Originally Posted by HansV
You can get a little bit further by changing all instances of "As Integer" in the code to "As Long".

But keep in mind that factorial numbers increase extremely rapidly: 9! is larger than the number of available rows in Excel 97-2003, and 10! is larger than the number of rows in Excel 2007.

Hi Hans

Thank you , I tried that but the code will not let me change the array variable myArr to long. I get the message " Compile error Expected Array". This occurs on the array redim statement

ReDim myArr(0 To Len(myStr) - 1).

I am working on an allocation problem which has a max of 8 numbers so a factorial of 40320 well within the number of rows.

Really appreciate your help

Thanks

Peter

6. It works for me in the attached workbook...

[attachment=86862:Perm.xls]

7. Originally Posted by HansV
It works for me in the attached workbook...

[attachment=86862:Perm.xls]
Hans

Thanks, again it did not work but I suspected it was because I was running Excel 2000 so switched to 2003 and it worked so thank you once again.

Peter

#### Posting Permissions

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