Results 1 to 7 of 7
Thread: A Factorial List

20091202, 10:22 #1
 Join Date
 Apr 2003
 Location
 Vail, Colorado, USA
 Posts
 194
 Thanks
 36
 Thanked 0 Times in 0 Posts
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

20091202, 10:34 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
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.

20091202, 20:27 #3
 Join Date
 Apr 2003
 Location
 Vail, Colorado, USA
 Posts
 194
 Thanks
 36
 Thanked 0 Times in 0 Posts
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

20091203, 00:45 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
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 972003, and 10! is larger than the number of rows in Excel 2007.

20091203, 06:53 #5
 Join Date
 Apr 2003
 Location
 Vail, Colorado, USA
 Posts
 194
 Thanks
 36
 Thanked 0 Times in 0 Posts
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

20091203, 08:30 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
It works for me in the attached workbook...
[attachment=86862:Perm.xls]

20091203, 15:21 #7
 Join Date
 Apr 2003
 Location
 Vail, Colorado, USA
 Posts
 194
 Thanks
 36
 Thanked 0 Times in 0 Posts