Struggling with ranges vs arrays in a custom function

[Kelvin Stott]

I have created the following custom function called CumArray(), which returns an ARRAY of running row and/or column totals from a RANGE of discrete values, which I then use within array functions in a worksheet, and it works very nicely, for example:

{=MyRange*CUMARRAY(MyRange)}

However, because it outputs an array rather than a range, I can't use the output of this function as the input into the same function, for example:

{=MyRange*CUMARRAY(CUMARRAY(MyRange))}

How can I revise my function so that it either takes in a range and gives out a range, or ideally, takes in an array and gives out an array, so that the output can be used as the input for the same function?

Function CumArray(MyRange, Optional Form As Integer = 0) As Variant
'Returns an Array of running row and/or column totals from a Range of discrete values
    On Error GoTo ExitFunction
    Set Fn = WorksheetFunction
    
    Dim x() As Variant
    Dim r, rMax As Integer
    Dim c, cMax As Integer
    
    rMax = MyRange.Rows.Count
    cMax = MyRange.Columns.Count
    
    ReDim x(1 To rMax, 1 To cMax)
    
    For r = 1 To rMax
        For c = 1 To cMax
            Select Case Form
                Case 0: x(r, c) = Fn.Sum(MyRange.Resize(r, c))
                Case 1: x(r, c) = Fn.Sum(MyRange.Resize(r, c).Columns(c))
                Case 2: x(r, c) = Fn.Sum(MyRange.Resize(r, c).Rows(r))
            End Select
        Next c
    Next r
    
    CumArray = x
ExitFunction:
End Function

 

[RoryA]

For simplicity, I'd suggest separate functions for each of the two situations and one master function that calls the appropriate one:

Function CumArray(MyRange, Optional Form As Integer = 0) As Variant
'Returns an Array of running row and/or column totals from a Range of discrete values
    On Error GoTo ExitFunction
    If TypeName(MyRange) = "Range" Then
        CumArray = CumArrayRange(MyRange, Form)
    Else
        CumArray = CumArrayArray(MyRange, Form)
    End If
ExitFunction:
End Function

Function CumArrayRange(MyRange, Optional Form As Integer = 0) As Variant
'Returns an Array of running row and/or column totals from a Range of discrete values
    
    Dim x() As Variant
    Dim r, rMax As Integer
    Dim c, cMax As Integer
    Dim Fn As WorksheetFunction
    
    On Error GoTo ExitFunction
    Set Fn = WorksheetFunction
    rMax = MyRange.Rows.Count
    cMax = MyRange.Columns.Count
    
    ReDim x(1 To rMax, 1 To cMax)
    
    For r = 1 To rMax
        For c = 1 To cMax
            Select Case Form
                Case 0: x(r, c) = Fn.Sum(MyRange.Resize(r, c))
                Case 1: x(r, c) = Fn.Sum(MyRange.Resize(r, c).Columns(c))
                Case 2: x(r, c) = Fn.Sum(MyRange.Resize(r, c).Rows(r))
            End Select
        Next c
    Next r
    
    CumArrayRange = x
ExitFunction:
End Function
Function CumArrayArray(MyRange, Optional Form As Integer = 0) As Variant
'Returns an Array of running row and/or column totals from a Range of discrete values
    Dim Fn As WorksheetFunction
    Dim x() As Variant
    Dim r, rMax As Integer
    Dim c, cMax As Integer
    On Error GoTo ExitFunction
    Set Fn = WorksheetFunction
    
    
    rMax = UBound(MyRange, 1)
    cMax = UBound(MyRange, 2)
    
    ReDim x(1 To rMax, 1 To cMax)
    
    For r = 1 To rMax
        For c = 1 To cMax
            Select Case Form
                Case 0: x(r, c) = Fn.Sum(Application.Index(MyRange, Evaluate("ROW(1:" & r & ")"), Evaluate("TRANSPOSE(ROW(1:" & c & "))")))
                Case 1: x(r, c) = Fn.Sum(Application.Index(MyRange, Evaluate("ROW(1:" & r & ")"), c))
                Case 2: x(r, c) = Fn.Sum(Application.Index(MyRange, r, Evaluate("TRANSPOSE(ROW(1:" & c & "))")))
            End Select
        Next c
    Next r
    
    CumArrayArray = x
ExitFunction:
End Function

 

[Kelvin Stott]

Hot-****, Rory, you're a star. Will take a while to figure out how you've done this, but will get back with any questions.

Thanks again!