arrays - Defining an `n` length nested loop in VBA -

building portfolio asset allocator, assets a, b, c, d, , want expand n assets. 4 assets, can run 4 nested loops. issue create n nested loops, , apply constraint, detailed below, , middle nest, allocates array current asset distribution. constraint (a + b + c + ... + n) <> 100 i.e. 100% allocation across assets.

the asset step sizes arbitrary (5 or 1) there minimum , maximum bounds on each n assets.

the asset sum 100 constraint knocks out many of iterations.

what interesting in question, allocarray requires position of each {a, b, c, ... , nasset} i.e. location of each nested loop. think makes exciting puzzle.

below nassets = 4 i.e. {a,b,c,d}:

minmaxstepparam = range("a1:c4")    min max step    5 100 5 b    5 100 5 c    5 100 5 d    5 100 5   dim sim long: sim = 1  dim allocarray() variant redim allocarray(1 nsim, 1 nasset + 1)  dim double dim b double dim c double dim d double  = amin amax step astep     b = bmin bmax step bstep     'if (a + b) > 100 goto endb ' not required middle nest bound catches         c = cmin cmax step cstep         'if (a + b + c) > 100 goto endc ' ditto             d = dmin dmax step dstep                         'constraints                         if (a + b + c + d) <> 100 goto endd                         debug.print sim; a; b; c; d                         allocarray(sim, 1) = sim                         allocarray(sim, 2) =                         allocarray(sim, 3) = b                         allocarray(sim, 4) = c                         allocarray(sim, 5) = d                         sim = sim + 1 endd:             next d endc:         next c endb:     next b next  printarray allocarray, activeworkbook.worksheets("output").[a1]  sub printarray(data variant, cl range)     cl.resize(ubound(data, 1), ubound(data, 2)) = data end sub 

i have produced dynamic loop, there error. can vba handle using array locations min, max , step of loop? there alternative?

sub configurearraydylan()  dim param() variant: param = range("m3:o6")  ' 4x3 with...min max step... {5 100 5; 5 100 5; 5 100 5; 5 100 5}. dim nasset long: nasset = ubound(param) ' count {a, b, ... , d } = 4 debug.print nasset  dim asset double dim value double dim sim long: sim = 1  dim nsim long: nsim = 1 dim nsimstep long = 1 nasset step 1     nsimstep = (1 + (param(i, 2) - param(i, 1)) / param(i, 3))  '(1 + (amax - amin) / astep) *     debug.print i; nsimstep     nsim = nsim * nsimstep next debug.print nsim  dim allocarray() variant redim allocarray(1 nsim, 1 nasset + 1)  asset = 1 nassets step 1     value = param(asset, 1) param(asset, 2) step param(asset, 3)         debug.print value; ' failure here         allocarray(sim, asset) = value         sim = sim + 1     next value next asset debug.print sim;  printarray allocarray, activeworkbook.worksheets("test").[l18] 'range("d30").resize(numrows, numcols).value = allocarray  end sub