VBA - write all permutations of numbers to an array

Question

Currently i am manually entering the values into array, sign_array(a, b, c). Where c is the level number. So see my output below, where c = 1 there are only two inputs to the array. When c = 2 (or level 2) there are 4 inputs. Level 3 = 8 inputs, level 4 there are 16 inputs. But writing these all out is getting very tiresome.

I am really struggling with this. I need to write to an array all combinations of 1 or 2, and looking for an output of:

Please see below how far I got, but have no idea what to do, to distinguish between a 1 or a 2. Any help would be greatly appreciated. At the moment i am manually putting in the combinations myself, but I its getting far to big the more dimensions I have.

I don't mind have a for loop for each level creation.

levels_to_use = 4
for i = 1 to levels_to_use  ^ 2
    for j = 1 to levels_to_use 
        ' in here how to chose between 1 or 2
        sign_array(i, j, levels_to_use) = 1
    next
next

How many "Levels" do you need to cover? or do you need that to be dynamic? — Zac
– Zac, Commented Feb 13, 2019 at 11:58
Ideally dynamic :-( I can't seem to edit the main post, my output numbers are wrong. level 2 = 1, 1 - 1, 2 - 2, 1 - 2, 2 e.g. — Surreall
– Surreall, Commented Feb 13, 2019 at 12:03
You could loop 2^x where x is the levels, then use a Decimal to Binary converter and replace 0's to 2's? Outputs like so 0000=0,0001=1,0010=2... — Nathan_Sav
– Nathan_Sav, Commented Feb 13, 2019 at 12:25
Just FYI, what you want is called permutations. If you google on it you can find some solutions like this one. — AutomatedChaos
– AutomatedChaos, Commented Feb 13, 2019 at 12:38

Vityata · Accepted Answer · 2019-02-13 12:34:19Z

2

Some time ago, I did not have anything better to do and I wrote exactly the code you are looking for - https://www.vitoshacademy.com/vba-nested-loops-with-recursion/

Thus, if you change it a bit, avoiding the _ and putting the size variable into a loop, it prints the desired result:

If you change c = Array(1, 2) to c = Array(1, 2, 3) it would add a third element to the system.

Sub Main()

    Static size         As Long
    Static c            As Variant
    Static arr          As Variant
    Static n            As Long

    c = Array(1, 2)
    n = UBound(c) + 1
    For size = 1 To 4
        ReDim arr(size - 1)
        EmbeddedLoops 0, size, c, n, arr
        Debug.Print "---------"
    Next size

End Sub

Function EmbeddedLoops(index, k, c, n, arr)

    Dim i                   As Variant
    If index >= k Then
        PrintArrayInOneLine arr
    Else
        For Each i In c
            arr(index) = i
            EmbeddedLoops index + 1, k, c, n, arr
        Next i
    End If

End Function

Public Sub PrintArrayInOneLine(myArray As Variant)

    Dim counter     As Long
    Dim sArray      As String
    For counter = LBound(myArray) To UBound(myArray)
        sArray = sArray & myArray(counter)
    Next counter
    Debug.Print sArray

End Sub

edited Feb 13, 2019 at 12:34

answered Feb 13, 2019 at 12:28

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Surreall Over a year ago

You genius! So i could use the sArray and loop over each character in the string?

Vityata Over a year ago

@Surreall - Welcome. Concerning the sArray and looping over its characters - it is better to avoid writing to a string, but simply looping through myArray.

Surreall Over a year ago

Worked a treat, thank you! This was driving me crazy.

Collectives™ on Stack Overflow

VBA - write all permutations of numbers to an array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related