VBA recursive permutation of multi-dimensional array

Question

I am trying to create a recursive function in VBA to print out permutations of values in a spreadsheet. I saved all the values that need permutation into one x-dimensional array (array of arrays):

paramValues = [
    ['AA', 'BA', 'IA', '8A'],
    ['A', 'B', 'C'],
    ['1', '2']
]

Important: this is not a known array (in any size), so a recursive function will be needed.

Also, I have another array that tells me the dimension on each sub-array (how many values are in each array): paramControl = [4, 3, 2]

...

Now I'm trying to create something that looks like this:

Screenshot of desired result

but I'm stuck. I wrote three different codes, and I'm now trying to ignore the recursive aspect and spell out each individual For cycle.

This is what I have so far, but it's far from working properly - two main issues being: 1. Iteration through the arrays isn't working properly 2. Movements in the sheet are just... wrong!

For i = 1 To paramet

    For k = 1 To paramControl(i)

        ActiveCell.Value = paramValues(i, k)

            ' Print subsequent values
            For j = (i + 1) To paramet

                ActiveCell.Offset(0, 1).Range("A1").Select  ' Move right to fill second column

                For w = 1 To paramControl(j)

                    ActiveCell.Value = paramValues(j, w)

                    For y = (i + 2) To paramet

                        ActiveCell.Offset(0, 1).Range("A1").Select  ' Move right to fill second column

                        For p = 1 To paramControl(j)

                            ActiveCell.Value = paramValues(y, p)

                            ActiveCell.Offset(1, 0).Range("A1").Select ' Move down to fill second column


                        Next p

                    Next y

                    ActiveCell.Offset(0, -1).Range("A1").Select  ' Move left to fill second column

                Next w

                ActiveCell.Offset(0, -2).Range("A1").Select  ' Move left to fill second column


            Next j

        Cells(ActiveCell.Row, 1).Select

        ActiveCell.Offset(1, 0).Range("A1").Select ' Move down to fill second column

    Next k

    ActiveCell.Offset(1, 0).Range("A1").Select  ' Move down to fill second column

Next i

Can someone please help shade some light? I feel I'm getting stuck with high school problems! :)

I've also found this post which helped in understanding some different approach, but the underlying problem is slightly different. VBA recursive "For loops" Permutation?

Thanks

You don't need recursion for this - see e.g. stackoverflow.com/questions/19780016/… — Tim Williams
– Tim Williams, Commented Jun 28, 2018 at 16:39
@TimWilliams thanks, I am a novice with VBA so I can't say I fully understood the code in that answer, but I'm now adapting my code to work with it. This did the trick! :) — M Wasabi Z
– M Wasabi Z, Commented Jun 29, 2018 at 12:43

user4039065 · Accepted Answer · 2018-06-28 16:39:03Z

0

I'm not sure I follow all of that but will this help?

Dim paramValues  As Variant, i As Long, j As Long

paramValues = Array(Array("AA", "BA", "IA", "8A"), _
                    Array("A", "B", "C"), _
                    Array(1, 2))

For i = LBound(paramValues, 1) To UBound(paramValues, 1)
    For j = LBound(paramValues(i), 1) To UBound(paramValues(i), 1)
        Debug.Print paramValues(i)(j)
    Next j
Next i

'results in Immediate window
AA
BA
IA
8A
A
B
C
 1 
 2

You might also be interested in Expanding column cells for each column cell.

answered Jun 28, 2018 at 16:39

user4039065

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

1 Comment

M Wasabi Z Over a year ago

Hi Jeeped, thanks but this does not exactly do what I needed - I was looking at something more along the lines of this: i.sstatic.net/9Xfms.png

Collectives™ on Stack Overflow

VBA recursive permutation of multi-dimensional array

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related