Generating Permutations
Category: General VBA | [Item URL]
A companion file is available: Click here to download
The number of permutations of a string is equal to the factorial of the length
of the string. For example, the word DOG has a length of three -- which means
that the letters can be rearranged in six different ways: DOG, DGO, ODG OGD, GDO,
and GOD. The number of permutations quickly gets unwieldy. The table below lists
the number of permutations for strings of various sizes.
Characters | Permutations |
1 | 1 |
2 | 2 |
3 | 6 |
4 | 24 |
5 | 120 |
6 | 720 |
7 | 5,040 |
8 | 40,320 |
9 | 362,880 |
10 | 3,628,800 |
11 | 39,916,800 |
12 | 479,001,600 |
This tip describes how to generate all permutations from a string. It uses a recursive subroutine to do the work. The source of this algorithm is not known (I was browsing through some old files on my hard drive and discovered it).
VBA Listing
The GetString subroutine prompts the user for a string. If the length of the string is greater than 1 and less than 8, the GetPermutations subroutine is called --which then calls itself. The permutations are stored in column A of the worksheet.
Dim CurrentRow Sub GetString() Dim InString As String InString = InputBox("Enter text to permute:") If Len(InString) < 2 Then Exit Sub If Len(InString) >= 8 Then MsgBox "Too many permutations!" Exit Sub Else ActiveSheet.Columns(1).Clear CurrentRow = 1 Call GetPermutation("", InString) End If End Sub Sub GetPermutation(x As String, y As String) ' The source of this algorithm is unknown Dim i As Integer, j As Integer j = Len(y) If j < 2 Then Cells(CurrentRow, 1) = x & y CurrentRow = CurrentRow + 1 Else For i = 1 To j Call GetPermutation(x + Mid(y, i, 1), _ Left(y, i - 1) + Right(y, j - i)) Next End If End Sub
Search for Tips
All Tips
Browse Tips by Category
Tip Books
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks
Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers