# VBA Recursion



## lrobbo314 (Monday at 4:51 PM)

I have always struggled with recursion, but I am making an effort to learn it.

In the example below you will see that I have a column and a row of numbers (dark cells).  The colored cells are just the result of MMULT of the column and row.  I am looking to return the sum of each of the colored bands.

I've written the recursive function below, but I would like to hear what could be done better or more efficiently.  It was enough of a struggle just to get it to work.

TIA for any insight.

RECURSIONABCDEFGHI12246LevelTargetUDF3361218166451020302222257142842352526918365447676757878865454Sheet5Cell FormulasRangeFormulaC3:E6C3=$B3*C$2I3:I8I3=xVolve(B3:B6,C2:E2)Dynamic array formulas.


```
Function xVolve(a As Range, b As Range)
Dim AR As Variant:      AR = Application.WorksheetFunction.MMult(a, b)
Dim Total As Integer:   Total = a.Cells.Count + b.Cells.Count - 1
Dim Res() As Variant:   ReDim Res(1 To Total, 1 To 1)


xVolve = CX(AR, 1, 1, 0, True, Res, Total)
End Function

Function CX(AR As Variant, RI As Integer, CI As Integer, Total As Integer, b As Boolean, Res As Variant, Index As Integer)
If b Then
    If RI < UBound(AR) Then
        CX AR, RI + 1, CI, Total, b, Res, Index
    ElseIf CI < UBound(AR, 2) Then
        CX AR, RI, CI + 1, Total, b, Res, Index
    End If
End If
b = False

Total = Total + AR(RI, CI)
If CI < UBound(AR, 2) And RI > 1 Then
    CX AR, RI - 1, CI + 1, Total, b, Res, Index
Else
    Res(Index, 1) = Total
    Index = Index - 1
End If

Total = 0
CX = Res
End Function
```


----------



## DanteAmor (Monday at 7:02 PM)

I think we need to add the level to the function.
Try the following approach:


```
Function xVol(niv As Long, rng1 As Range, rng2 As Range) As Long
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  a = rng1.Value
  b = rng2.Value
  
  If niv > UBound(a, 1) Then
    j = niv - UBound(a, 1)
    niv = UBound(a, 1)
  End If
  For i = niv To 1 Step -1
    j = j + 1
    If j <= UBound(b, 2) Then
      xVol = xVol + (a(i, 1) * b(1, j))
    End If
  Next
End Function
```

Dante AmorABCDEFGHI12246LevelTargetUDF3361218166451020302222257142842352526918365447676757878865454Hoja14Cell FormulasRangeFormulaC3:E6C3=$B3*C$2I3:I8I3=xvol(G3,$B$3:$B$6,$C$2:$E$2)


----------



## pgc01 (Monday at 10:33 PM)

Hi

This could be done with a loop, but since the idea is to practice recursion this is an option.
I tried to change your first function as little as possible.


```
Function xVolve(a As Range, b As Range)
Dim ar As Variant:      ar = Application.WorksheetFunction.MMult(a, b)
Dim Total As Long:      Total = a.Cells.Count + b.Cells.Count - 1
Dim res() As Variant:    ReDim res(1 To Total, 1 To 1)

CX ar, res, UBound(ar, 1), UBound(ar, 2), True
xVolve = res
End Function

Sub CX(ar As Variant, res As Variant, ByVal la As Long, ByVal lb As Long, ByVal bB As Boolean)

    res(la + lb - 1, 1) = res(la + lb - 1, 1) + ar(la, lb)
    
    If la > 1 Then Call CX(ar, res, la - 1, lb, False)
    If lb > 1 And bB Then Call CX(ar, res, la, lb - 1, True)

End Sub
```

Please try


----------



## lrobbo314 (Tuesday at 10:37 AM)

Thanks Dante.  The data in column G was there for illustrative purposes and not meant to be incorporated into the solution.  But I appreciate your input.


----------



## lrobbo314 (Tuesday at 10:40 AM)

@pgc01 That is amazing!  Works totally differently than I would have thought.  I was thinking going through each starting cell along the left and bottom edges of the array and working up and to the right.  But this is very clever and definitely is a help in wrapping my head around recursion.
I'd love to hear what your process is for breaking down a problem like this and how to attack it.

Thanks!


----------



## lrobbo314 (Tuesday at 10:42 AM)

In case either of you are interested at all, I did make a Lambda function that does the same thing.


```
=LAMBDA(
    set_1,set_2,
    LET(
        a,set_1,
        b,set_2,
        ca,COUNTA(a),
        cb,COUNTA(b),
        m,MMULT(set_1,set_2),
        s,SEQUENCE(ca+cb-1),
        g,MAKEARRAY(
            ca,cb,
            LAMBDA(
                r,c,
                IF(OR(AND(r=1,c>1),AND(c=1,r>1)),
                    r*c,
                    r*c-((r-1)*(c-1))
                )
            )),
            MAP(
                s,
                LAMBDA(
                    x,
                        SUMPRODUCT((g=x)*m)
                )
            )
        )
)
```


----------

