PDA

View Full Version : List all possible order combinations



fb7894
05-13-2017, 06:56 PM
I'm looking for a function or algorithm that will return a list of values in every possible order.

For example, say I have an array with 3 entries (A, B, C). There are 6 possible ways these values may be ordered.

ABC
BAC
ACB
CAB
CBA
BCA


Can someone point me to some VBA code that would return all 6 combinations above? Keep in mind that I could have more than 6 entries.

Thanks in advance.

mana
05-13-2017, 11:27 PM
I learned from gekkasuikou san


Option Explicit


Dim p() As Long
Dim s As Variant
Dim m As Long
Dim k As Long
Dim w()


Sub test_by_gekkasuikou_san()
Dim i As Long

k = 0
s = Array("A", "B", "C")
m = UBound(s) + 1
ReDim p(1 To m)
ReDim w(1 To m * (m - 1), 1 To m)

For i = 1 To m
p(i) = i - 1
Next
Perm 1

Worksheets.Add
Range("A1").Resize(k, m).Value = w

End Sub


Private Sub Perm(n As Long)
Dim i As Long

If n < m Then
For i = n To m
Swap p(n), p(i)
Perm n + 1
Swap p(n), p(i)
Next
Else
k = k + 1
For i = 1 To m
w(k, i) = s(p(i))
Next
End If

End Sub


Private Sub Swap(ByRef A As Long, ByRef B As Long)
Dim T As Long

T = A
A = B
B = T

End Sub

fb7894
05-14-2017, 06:08 AM
That works great for ABC. But when i change to Array("A", "B", "C", "D"), i get a subscript of out range error??

fb7894
05-14-2017, 06:39 AM
Update: I changed the following line of code. It is working now. Thanks for the help!



' ReDim w(1 To m * (m - 1), 1 To m)
ReDim w(1 To Application.WorksheetFunction.Fact(m), 1 To m)