# Go through all combinations of n number of variables

#### antti

##### New Member
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>

Code:
``````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!

### Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Replies
0
Views
60
Replies
17
Views
408
Replies
1
Views
163
Replies
0
Views
156
Replies
3
Views
248

1,203,071
Messages
6,053,369
Members
444,658
Latest member
lhollingsworth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back