Go through all combinations of n number of variables

[antti]

I'm performing multiple variable sensitivity analysis in an Excel model, and I'm now running into some obstacles when sketching the functionality in VBA. The code needs to do the following:

1. Store the variables and their values into an array (done)
2. Go through each combination of the variable values and perform actions, e.g. record the NPV for the combination (partially done)

Step number 2 is easily done with nested For-loops if the number of variables is fixed, and this I have already done. What is proving to be problematic is making the number of variables dynamic. I have tried recursion but I can't seem to get the loop to run correctly. My question is: how can I recreate the subprocedure Test below but with a dynamic amount of variables (i.e. number of For-loops)? Sample data and code can be found below.

var1	var2	var3	var4
value 1	1	a	City 1
value 2	3	b	City 2
value 3	5	c	City 3
value 4		d
value 5

<tbody>
</tbody>

Function getVariables()

Dim variables(), values()       As Variant
Dim iVariables, iValues, i, j   As Integer
Dim rStart, rEnd, rValues       As Range

iVariables = Range("A1").End(xlToRight).Column - Range("A1").Column + 1

ReDim variables(1 To iVariables)

For i = 1 To iVariables
    Set rStart = Range("A1").Offset(1, i - 1)
    Set rEnd = Range("A1").Offset(1, i - 1).End(xlDown)
    Set rValues = Range(rStart, rEnd)
    iValues = rValues.Count
    
    ReDim values(1 To iValues)
    
    For j = 1 To iValues
        values(j) = rValues.Cells(j, 1)
    Next j

    variables(i) = values
    
Next i

getVariables = variables

End Function

Sub Test()

Dim variables()     As Variant
Dim i1, i2, i3, i4  As Integer
Dim s               As String

variables = getVariables
s = ", "

For i4 = 1 To UBound(variables(4))
    For i3 = 1 To UBound(variables(3))
        For i2 = 1 To UBound(variables(2))
            For i1 = 1 To UBound(variables(1))
                ' Do stuff
                Debug.Print variables(1)(i1) & s &  variables(2)(i2) & s & variables(3)(i3) & s &  variables(4)(i4)
            Next i1
        Next i2
    Next i3
Next i4

End Sub

Thank you in advance!