Struggling with ranges vs arrays in a custom function

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
330
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?

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

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,101
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
For simplicity, I'd suggest separate functions for each of the two situations and one master function that calls the appropriate one:

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

Active Member
Joined
Oct 26, 2010
Messages
330
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! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,850
Messages
5,598,457
Members
414,239
Latest member
xnanx

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
Top