VBA Recursion

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,957
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHI
1
2246LevelTargetUDF
3361218166
4510203022222
5714284235252
6918365447676
757878
865454
Sheet5
Cell Formulas
RangeFormula
C3:E6C3=$B3*C$2
I3:I8I3=xVolve(B3:B6,C2:E2)
Dynamic array formulas.


VBA Code:
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
I think we need to add the level to the function.
Try the following approach:

VBA Code:
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
ABCDEFGHI
1
2246LevelTargetUDF
3361218166
4510203022222
5714284235252
6918365447676
757878
865454
Hoja14
Cell Formulas
RangeFormula
C3:E6C3=$B3*C$2
I3:I8I3=xvol(G3,$B$3:$B$6,$C$2:$E$2)
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
Solution
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.
 
Upvote 0
@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!
 
Upvote 0
In case either of you are interested at all, I did make a Lambda function that does the same thing.

Excel Formula:
=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)
                )
            )
        )
)
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top