Create permutations sets using VBA

[kato01]

Hello,

I have the following Table

Name	Min	Max	Nvals
A	-5​	6.7​	6​
B	-10​	23​	4​
C	2​	15​	3​

I am looking for a code that generates all the sets of [A B C] that include all the possible variatiosn as defined in the Table
The cath is that the code has to be able to cope with an apriori unknown number of variables , so nested loopes option seems to me unusable

Thank you

 

[ranman256]

Is A supposed to run from -5 to 6.7?
then whats the Nval for?

 

[kato01]

Is A supposed to run from -5 to 6.7?
then whats the Nval for?

yes, nval is the number of values between MIN and MAX. each variable might have its own number of values.

 

[ranman256]

not sure if this is it, but this loads the parameters,
then generates random value between Min & Max then produces the permutation:

Public Sub GenParams()
Dim a, b, c
Dim iAMax, iBMax, iCMax
Dim iAMin, iBMin, iCMin
Dim iANval As Integer, iBNval As Integer, iCNval As Integer
Dim lCol As Long
Dim colA As New Collection, colB As New Collection, colC As New Collection


iAMin = Range("I2").Value
iBMin = Range("i3").Value
iCMin = Range("i4").Value

iAMax = Range("j2").Value
iBMax = Range("j3").Value
iCMax = Range("j4").Value

iANval = Range("k2").Value
iBNval = Range("k3").Value
iCNval = Range("k4").Value

'gab N random numbers
AddRand iAMax, iAMin, iANval, colA
AddRand iBMax, iBMin, iBNval, colB
AddRand iCMax, iCMin, iCNval, colC

   'display results
Range("A1").Select
For a = 1 To colA.Count
    For b = 1 To colB.Count
        For c = 1 To colC.Count
            ActiveCell.Offset(0, 0).Value = colA(a)
            ActiveCell.Offset(0, 1).Value = colB(b)
            ActiveCell.Offset(0, 2).Value = colC(c)

            ActiveCell.Offset(1, 0).Select  'next row
        Next
    Next
Next
Set colA = Nothing
Set colB = Nothing
Set colC = Nothing
End Sub


private Sub AddRand(pvMax, pvMin, piNvarLimit As Integer, pcol As Collection)
Dim sKey As String
On Error Resume Next

While pcol.Count < piNvarLimit
  sKey = Int((pvMax * Rnd) + pvMin)   ' Generate random value between Top and Btm
  pcol.Add sKey, sKey
Wend
End Sub

 

[kato01]

not sure if this is it, but this loads the parameters,
then generates random value between Min & Max then produces the permutation:

Public Sub GenParams()
Dim a, b, c
Dim iAMax, iBMax, iCMax
Dim iAMin, iBMin, iCMin
Dim iANval As Integer, iBNval As Integer, iCNval As Integer
Dim lCol As Long
Dim colA As New Collection, colB As New Collection, colC As New Collection


iAMin = Range("I2").Value
iBMin = Range("i3").Value
iCMin = Range("i4").Value

iAMax = Range("j2").Value
iBMax = Range("j3").Value
iCMax = Range("j4").Value

iANval = Range("k2").Value
iBNval = Range("k3").Value
iCNval = Range("k4").Value

'gab N random numbers
AddRand iAMax, iAMin, iANval, colA
AddRand iBMax, iBMin, iBNval, colB
AddRand iCMax, iCMin, iCNval, colC

   'display results
Range("A1").Select
For a = 1 To colA.Count
    For b = 1 To colB.Count
        For c = 1 To colC.Count
            ActiveCell.Offset(0, 0).Value = colA(a)
            ActiveCell.Offset(0, 1).Value = colB(b)
            ActiveCell.Offset(0, 2).Value = colC(c)

            ActiveCell.Offset(1, 0).Select  'next row
        Next
    Next
Next
Set colA = Nothing
Set colB = Nothing
Set colC = Nothing
End Sub


private Sub AddRand(pvMax, pvMin, piNvarLimit As Integer, pcol As Collection)
Dim sKey As String
On Error Resume Next

While pcol.Count < piNvarLimit
  sKey = Int((pvMax * Rnd) + pvMin)   ' Generate random value between Top and Btm
  pcol.Add sKey, sKey
Wend
End Sub

Thanks, will try