Get an 2 dimension array with 0 and 1 combination

Question

My English is not good, so please forgive me if what I describle is not clear for you.

I want to create 2 dimension Array with 0 and 1 when I input n, it should create: Array01(1 to 2^n as long, n as long), and 0 and 1 is combination like this:

n = 1 ==> Arr (2 rows x 1 column)
0 |
1 |

n = 2 ==> Arr (4 rows x 2 columns)
0 0 |
0 1 |
1 0 |
1 1 |

n = 3 ==> Array (8 rows x 3 columns)
0 0 0 |
0 0 1 |
0 1 0 |
1 0 0 |
1 1 0 |
1 0 1 |
0 1 1 |
1 1 1 |

Answer 1

You can use a function like below

Option Explicit

Public Function CreateMatrix(ByVal n As Long) As Variant
    Dim Matrix() As Long
    ReDim Matrix(1 To 2 ^ n, 1 To n)
    
    Dim i As Long
    For i = 0 To 2 ^ n - 1
        Dim BinaryString As String
        BinaryString = DecToBin(i, n)
        
        Dim c As Long
        For c = 1 To n
            Matrix(i + 1, c) = CLng(Mid$(BinaryString, c, 1))
        Next c
    Next i
    
    CreateMatrix = Matrix
End Function


Public Function DecToBin(ByVal DecimalIn As Variant, Optional ByVal NumberOfBits As Variant) As String
    Dim Result As String
    
    DecimalIn = CDec(DecimalIn)
    
    Do While DecimalIn <> 0
        Result = Trim$(Str$(DecimalIn - 2 * Int(DecimalIn / 2))) & Result
        DecimalIn = Int(DecimalIn / 2)
    Loop
    
    If Not IsMissing(NumberOfBits) Then
        If Len(Result) > NumberOfBits Then
            Result = "Error - Number too large for bit size"
        Else
            Result = Right$(String$(NumberOfBits, "0") & Result, NumberOfBits)
        End If
    End If
    
    DecToBin = Result
End Function

and call it like

' generate the matrix
Dim MyMatrix() As Long
MyMatrix = CreateMatrix(n:=3)

' and write it to a sheet
Worksheets("Sheet1").Range("A1").Resize(UBound(MyMatrix, 1), UBound(MyMatrix, 2)).Value = MyMatrix

---

How does this work?

If we look at the matrix below we can see each row as a binary number that can be converted into a decimal number. So binary 000 is decimal 0, then binary 001 is decimal 1 and binary 010 is decimal 2 and so on:

0 0 0 |  'decimal 0
0 0 1 |  'decimal 1
0 1 0 |  'decimal 2
1 0 0 |  'decimal 3
1 1 0 |  'decimal 4
1 0 1 |  'decimal 5
0 1 1 |  'decimal 6
1 1 1 |  'decimal 7

So we know if we want to create that matrix we need to convert the decimal numbers 1 to 7 into binary numbers. Each of this binary numbers then represents one row of the matrix.

Since the only number to define the martix is n (in the example n = 3) we can use that to calculate the dimensions of the matrix:

• rows: 2 ^ n (in the example 2^3 = 8)
• columns: n

So we define a matrix of that size ReDim Matrix(1 To 2 ^ n, 1 To n).

Then we need to generatate the decimal numbers from 1 to 7 to be able to convert them into binaries. We do that with a loop: For i = 0 To 2 ^ n - 1 (in the example this means For i = 0 To 7).

In that loop we convert each decimal number i into a binary string of the length n. We do that using BinaryString = DecToBin(i, n).

Finally we just need to split that string into the columns of our matrix. Therefore we use another loop that loops through the characters of that BinaryString For c = 1 To n (which means start with character 1 until character n). And fill the matrix:

Matrix(i + 1, c) = CLng(Mid$(BinaryString, c, 1))

Here Mid$(BinaryString, c, 1) picks the character out of the string and CLng converts it into a Long number so it is numeric and writes it into the correct position of the matrix Matrix(i + 1, c).

Fanally we return that matix as result of our function CreateMatrix = Matrix.

Answer 2

This post is 10 months old. But I saw a new post by the OP which led me here, so I thought I'd share another solution.

This can be solved by formula instead of script.

Suppose in cell A1 of some sheet you place the number for n.

In some other cell (say, A3 or C1), you could use the following formula to generate the list in question:

=FILTER(TEXT(SEQUENCE(10^A1,1,0),REPT("0",A1)),NOT(REGEXMATCH(SEQUENCE(10^A1,1,0)&"","[2-9]")))

Essentially, this formula creates a SEQUENCE of all possible numbers between 0 and 10 to the nth, formatted to contain n digits; then it FILTERs out any elements of that sequence that contain any digits from 2 to 9 (i.e., anything other than elements containing only 1s and 0s).