0
0 Comments

I have a set of parameters. Each parameters has a list of possible values, this list of possible values is identified by a minimum value, a maximum value and a step value.

Parameters Input Table

I have many parameters all with their own set of values. The simplified example above says that:

  • Param 1 will take possible values 8, 10, 12
  • Param 2 will take possible values 1200, 1800, 2400
  • Param 3 will take possible values 60, 75, 90, 105

The input parameters Table should be dynamic in the number of rows as the number of parameters and the possible values may evolve.

My goal is to create a table with all the possible combinations of these arrays (unsorted, i do want all permutations). The expected output Table is shown as follows.

Expected output table

I would like to achieve the same for dynamic amount of parameters and and dynamic parameter values.

So far i have managed to read the Parameter Table to make arrays of all the values i want to combine, but i am struggling to find out how to loop through arrays to combine all values.

My code looks like this for now, where the input "ParametersTest" is the table shown above:

Dim myParametersTable As ListObject
Dim myArray As Variant
Dim myTmpArray As Variant
Dim inputArray As Variant
Dim outputArray As Variant
Dim printArray As Variant
Dim x As Long
Dim Param_ID As String
Dim Min As Double
Dim Max As Double
Dim Step As Double
Dim Size As Long
Dim OSize As Long


'Set path for Table variable
  Set myParametersTable = ActiveSheet.ListObjects("ParametersTest")


' Input array is first dimension as a list of parameters
ReDim inputArray(myParametersTable.ListRows.Count)

'Loop Through Every Row in Table and create 2nd dimension array with a list of values for each parameter
OSize = 1
  For x = 1 To myParametersTable.ListRows.Count
    Param_ID = myParametersTable.DataBodyRange(x, 1)
    Min = myParametersTable.DataBodyRange(x, 3)
    Max = myParametersTable.DataBodyRange(x, 4)
    Step = myParametersTable.DataBodyRange(x, 5)
    'Debug.Print (Step)

    If Step = 0 Then
        Size = 1
    Else
        Size = (Max - Min) / Step + 1
    End If
        
    OSize = OSize * Size
    printArray = Array(Size, OSize)
    'Debug.Print Join(printArray, ";")
    
    ReDim myTmpArray(Size)
    For y = 1 To Size
        myTmpArray(y - 1) = Min + Step * (y - 1)
    Next y
    'Debug.Print Join(myTmpArray, ";")
  
    ' Populate the 1st dimension of the input array
    inputArray(x - 1) = myTmpArray
    
  Next x
 
 
ReDim outputArray(OSize)
ReDim myTmpArray(myParametersTable.ListRows.Count)
  
  For x = LBound(inputArray) To UBound(inputArray) - 1
  Debug.Print (UBound(inputArray(x)) - 1)
    For y = LBound(inputArray(x)) To UBound(inputArray(x)) - 1
        Debug.Print (inputArray(x)(y))
    Next y
  Next x

End Sub ```


 


  [1]: https://i.stack.imgur.com/szAco.png
  [2]: https://i.stack.imgur.com/8qXpG.png
Anonymous Asked question May 14, 2021