Generating permutations in VBA

Question

This question has been asked before, but I can't find an answer that is easily applicable to Excel VBA.

Basically I want to do exactly what this poster has asked, but in VBA. I want to create an array, n x 2^n, where each line represents a different permutation of n variables which can be either 0 or 1.

I've played around with this for ages, and it's easy enough to do for a set n with loads of loops, but for a variable n I can't find anything that works.

Any code or just suggestions of ways of going about this would be much appreciated!

This is exactly equivalent to the bit patterns of the n-bit integers from 0 to 2^(n-1). — beaker, Aug 01 '14 at 15:27
Since this is a lot easier in Python and it as already been implemented, you might want to check the [ExcelPython](https://github.com/ericremoreynolds/excelpython) add-in. Basically it wraps Python functions using COM technology and you can call them from VBA. I have not used it in a production environment yet, but it might worth a shot. — Ioannis, Aug 01 '14 at 15:31
@beaker that's all very well, but the point is I don't know how to generate them... — ajor, Aug 01 '14 at 15:32

Gary's Student · Accepted Answer · 2014-08-01T15:51:04.520

This will list the value in column A

Sub EasyAsCounting()
    Dim N As Long, M As Long, K As Long
    N = Application.InputBox(Prompt:="Enter N", Type:=1)
    M = 2 ^ N - 1
    For K = 0 To M
        Cells(K + 1, 1) = "'" & Application.WorksheetFunction.Dec2Bin(K, N)
    Next K
End Sub

EDIT#1

This stores the array in VBA only:

Sub EasyAsCounting()
    Dim N As Long, M As Long, K As Long, ary, s As String
    Dim J As Long
    N = Application.InputBox(Prompt:="Enter N", Type:=1)
    M = 2 ^ N - 1
    ReDim ary(1 To M + 1, 1 To N)
    For K = 0 To M
        s = Application.WorksheetFunction.Dec2Bin(K, N)
        For J = 1 To N
            ary(K + 1, J) = Mid(s, J, 1)
        Next J
    Next K
    '
    'display the array
    '
    msg = ""
    For K = 1 To M + 1
        For J = 1 To N
            msg = msg & " " & ary(K, J)
        Next J
        msg = msg & vbCrLf
    Next K
    MsgBox msg
End Sub

Thanks, that's useful. I wasn't aware of that formula. However, I want to keep the values in an array to manipulate further, rather than to populate a worksheet. Can I split the result into seaprate array elements? — ajor, Aug 01 '14 at 15:35
Actually, I probably don't need that. Thanks for your answer! — ajor, Aug 01 '14 at 15:47

score 1 · Answer 2 · answered Aug 01 '14 at 15:50

Here's one if you're not in Excel and don't have access to the functions. Or if you have a number greater than 511.

Sub MakePerms()

    Dim i As Long, j As Long
    Dim n As Long
    Dim aPerms() As Byte
    Dim lCnt As Long
    Dim sOutput As String

    Const lVar As Long = 4

    ReDim aPerms(1 To 2 ^ lVar, 1 To lVar)

    For i = 0 To UBound(aPerms, 1) - 1
        n = i
        lCnt = lVar
        aPerms(i + 1, lCnt) = CByte(n Mod 2)
        n = n \ 2
        Do While n > 0
            lCnt = lCnt - 1
            aPerms(i + 1, lCnt) = CByte(n Mod 2)
            n = n \ 2
        Loop
    Next i

    For i = LBound(aPerms, 1) To UBound(aPerms, 1)
        sOutput = vbNullString
        For j = LBound(aPerms, 2) To UBound(aPerms, 2)
            sOutput = sOutput & Space(1) & aPerms(i, j)
        Next j
        Debug.Print sOutput
    Next i

End Sub

Generating permutations in VBA

2 Answers2