List all possible order combinations

[fb7894]

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]

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]

That works great for ABC. But when i change to Array("A", "B", "C", "D"), i get a subscript of out range error??

[fb7894]

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)