Defining an `n` length nested loop in VBA

Question

Building a portfolio asset allocator, for assets A, B, C, D, and want to expand it to n assets. With four assets, can run four nested loops. The issue is to create n nested loops, and then apply the constraint, detailed below, and the middle nest, which allocates an array to the current asset distribution. The constraint is (A + B + C + ... + n) <> 100 i.e. 100% allocation across all assets.

The asset step sizes are arbitrary (5 or 1) but there are minimum and maximum bounds on each n assets.

The asset sum to 100 constraint knocks out many of the iterations.

What is interesting in this question, is the AllocArray requires the position of each {A, B, C, ... , nAsset} i.e. the location of each nested loop. I think this makes an exciting puzzle.

Below is nAssets = 4 i.e. {A,B,C,D}:

MinMaxStepParam = Range("A1:C4")
   Min Max Step
A    5 100 5
B    5 100 5
C    5 100 5
D    5 100 5


Dim Sim As Long: Sim = 1

Dim AllocArray() As Variant
ReDim AllocArray(1 To nSim, 1 To nAsset + 1)

Dim A As Double
Dim B As Double
Dim C As Double
Dim D As Double

For A = AMin To AMax Step AStep
    For B = BMin To BMax Step BStep
    'If (A + B) > 100 Then GoTo endB ' not required as middle nest bound catches all
        For C = CMin To CMax Step CStep
        'If (A + B + C) > 100 Then GoTo endC ' ditto
            For D = DMin To DMax Step DStep
                        'Constraints
                        If (A + B + C + D) <> 100 Then GoTo endD
                        Debug.Print Sim; A; B; C; D
                        AllocArray(Sim, 1) = Sim
                        AllocArray(Sim, 2) = A
                        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 A

PrintArray AllocArray, ActiveWorkbook.Worksheets("Output").[A1]

Sub PrintArray(Data As Variant, Cl As Range)
    Cl.Resize(UBound(Data, 1), UBound(Data, 2)) = Data
End Sub

I have produced this as a dynamic loop, but there is an error. Can VBA handle using Array locations for the Min, Max and Step of a loop? Is there an alternative?

Sub ConfigureArrayDylan()

Dim Param() As Variant: Param = Range("M3:O6")  ' a 4x3 with...Min Max Step... {5 100 5; 5 100 5; 5 100 5; 5 100 5}.
Dim nAsset As Long: nAsset = UBound(Param) ' Count {A, B, ... , D } = 4
Debug.Print nAsset

Dim Asset As Double
Dim Value As Double
Dim Sim As Long: Sim = 1

Dim nSim As Long: nSim = 1
Dim nSimStep As Long
For i = 1 To 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 i
Debug.Print nSim

Dim AllocArray() As Variant
ReDim AllocArray(1 To nSim, 1 To nAsset + 1)

For Asset = 1 To nAssets Step 1
    For Value = Param(Asset, 1) To 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

`(A + B + C + D) <> 100` is potentially misleading if the variables are floats and, in any event, a hit-and-miss approach becomes exponentially bad as the number of terms increases (you are mostly missing, but wasting CPU cycles generating and testing those misses). You need to find a more direct way to iterate, one that doesn't require deeper nesting as the number of terms increases. — John Coleman, Nov 10 '16 at 15:47
This question would be more interesting if you showed enough code (and sample inputs) so that others could more easily test it & assist you with it... — David Zemens, Nov 10 '16 at 15:53
This is probably not really specific to VBA unless there is some Excel function that can be used here. There should be lots of examples how to implement a dynamic nested iteration in general. Your example could be improved by eliminating the last loop (set D = 100 - (A + B + ...)) and exiting the levels when the sum gets > 100 — arcadeprecinct, Nov 10 '16 at 16:03
The `Application.WorksheetFunction.Sum` can compute the sum across a vector array like this. Seems like that, along with some recursion might get you what you need. — David Zemens, Nov 10 '16 at 16:09
Your edit helps -- but why the `Double` types for `A,B,C,D`? It becomes much simpler if you could assume an integer type (perhaps rounding to the nearest integer). — John Coleman, Nov 10 '16 at 17:27
It absolutely is an integer. There's only a need for the precision to be that of Step size (third column of Param array). Take a look at earlier post for fully speced code in nAssets = 4 — rrg, Nov 10 '16 at 22:30

Defining an `n` length nested loop in VBA

0 Answers0