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:
[TABLE="width: 224"]
<tbody>[TR]
[TD="width: 56"]var1[/TD]
[TD="width: 56"]var2[/TD]
[TD="width: 56"]var3[/TD]
[TD="width: 56"]var4[/TD]
[/TR]
[TR]
[TD]value 1[/TD]
[TD="align: right"]1[/TD]
[TD]a[/TD]
[TD]City 1[/TD]
[/TR]
[TR]
[TD]value 2[/TD]
[TD="align: right"]3[/TD]
[TD]b[/TD]
[TD]City 2[/TD]
[/TR]
[TR]
[TD]value 3[/TD]
[TD="align: right"]5[/TD]
[TD]c[/TD]
[TD]City 3[/TD]
[/TR]
[TR]
[TD]value 4[/TD]
[TD][/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]value 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thank you in advance!
- Store the variables and their values into an array (done)
- Go through each combination of the variable values and perform actions, e.g. record the NPV for the combination (partially done)
[TABLE="width: 224"]
<tbody>[TR]
[TD="width: 56"]var1[/TD]
[TD="width: 56"]var2[/TD]
[TD="width: 56"]var3[/TD]
[TD="width: 56"]var4[/TD]
[/TR]
[TR]
[TD]value 1[/TD]
[TD="align: right"]1[/TD]
[TD]a[/TD]
[TD]City 1[/TD]
[/TR]
[TR]
[TD]value 2[/TD]
[TD="align: right"]3[/TD]
[TD]b[/TD]
[TD]City 2[/TD]
[/TR]
[TR]
[TD]value 3[/TD]
[TD="align: right"]5[/TD]
[TD]c[/TD]
[TD]City 3[/TD]
[/TR]
[TR]
[TD]value 4[/TD]
[TD][/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]value 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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!