lrobbo314
Well-known Member
- Joined
- Jul 14, 2008
- Messages
- 3,957
- Office Version
- 365
- Platform
- 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.
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. |
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