VBA Recursion

[lrobbo314]

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.

RECURSION
	A	B	C	D	E	F	G	H	I
1
2			2	4	6		Level	Target	UDF
3		3	6	12	18		1	6	6
4		5	10	20	30		2	22	22
5		7	14	28	42		3	52	52
6		9	18	36	54		4	76	76
7							5	78	78
8							6	54	54
Sheet5

Cell Formulas
Range	Formula
C3:E6	C3	=$B3*C$2
I3:I8	I3	=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]

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 Amor
	A	B	C	D	E	F	G	H	I
1
2			2	4	6		Level	Target	UDF
3		3	6	12	18		1	6	6
4		5	10	20	30		2	22	22
5		7	14	28	42		3	52	52
6		9	18	36	54		4	76	76
7							5	78	78
8							6	54	54
Hoja14

Cell Formulas
Range	Formula
C3:E6	C3	=$B3*C$2
I3:I8	I3	=xvol(G3,$B$3:$B$6,$C$2:$E$2)

 

[pgc01]

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]

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]

@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]

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)
                )
            )
        )
)