Array's cumulative sum

If you want to achieve a cumulative array array like Array(a,a+b,a+b+c) from Array(a,b,c), then this is the function to achieve it, if you want to pass start and end parameters:

Public Sub TestMe()    Dim outputArray     As Variant    Dim inputArray      As Variant    Dim counter         As Long    inputArray = Array(1, 2, 4, 8, 16, 32, 64)    outputArray = generateCumulativeArray(inputArray, 1, 4)    For counter = LBound(outputArray) To UBound(outputArray)        Debug.Print outputArray(counter)    Next counter    outputArray = generateCumulativeArray(inputArray, toValue:=4)    For counter = LBound(outputArray) To UBound(outputArray)        Debug.Print outputArray(counter)    Next counterEnd SubPublic Function generateCumulativeArray(dataInput As Variant, _                                        Optional fromValue As Long = 0, _                                        Optional toValue As Long = 0) As Variant    Dim i                   As Long    Dim dataReturn          As Variant    ReDim dataReturn(0)    dataReturn(0) = dataInput(fromValue)    For i = 1 To toValue - fromValue        ReDim Preserve dataReturn(i)        dataReturn(i) = dataReturn(i - 1) + dataInput(fromValue + i)    Next i    generateCumulativeArray = dataReturnEnd Function

Concerning just summing an array, this is the way to do it:You can use the WorksheetFunction. and you can pass the array as an argument. Thus, you get all the functions, e.g. Average, Min, Max etc:

Option ExplicitPublic Sub TestMe()    Dim k As Variant    k = Array(2, 10, 200)    Debug.Print WorksheetFunction.Sum(k)    Debug.Print WorksheetFunction.Average(k)End Sub

If you want the sum from a given start to a given end, the easiest way is probably to make a new array and to sum it completely. In Python this is called slicing, in VBA this could be done a bit manually:

Public Sub TestMe()    Dim varArr          As Variant    Dim colSample       As New Collection    varArr = Array(1, 2, 4, 8, 16, 32, 64)    colSample.Add (1)    colSample.Add (2)    colSample.Add (4)    colSample.Add (8)    Debug.Print WorksheetFunction.Sum(generateArray(varArr, 2, 4))    Debug.Print WorksheetFunction.Sum(generateArray(colSample, 2, 4))End SubPublic Function generateArray(data As Variant, _                              fromValue As Long, _                              toValue As Long) As Variant    Dim i                   As Long    Dim dataInternal        As Variant    Dim size                As Long    size = toValue - fromValue    ReDim dataInternal(size)    For i = LBound(dataInternal) To UBound(dataInternal)        dataInternal(i) = data(i + fromValue)    Next i    generateArray = dataInternalEnd Function

The idea is that the generateArray function returns a new array. Thus, its complete sum is what you need. It works also with collections, not only with arrays. Be careful, when using collections, they start with index 1, while arrays (usually) start with 0. If you want to use the same indexing for Arrays and Collections, then change the generateArray function to this one:

Public Function generateArray(data As Variant, _                              fromValue As Long, _                              toValue As Long) As Variant    Dim i                   As Long    Dim dataInternal        As Variant    Dim size                As Long    size = toValue - fromValue    ReDim dataInternal(size)    If IsArray(data) Then        For i = LBound(dataInternal) To UBound(dataInternal)            dataInternal(i) = data(i + fromValue)        Next i    Else        For i = LBound(dataInternal) To UBound(dataInternal)            dataInternal(i) = data(i + fromValue + 1)        Next i    End If    generateArray = dataInternalEnd Function

Or write Option Base 1 on top and the array will start from 1 (not advised!).

For cumulative sum try the following

Function CumulativeSum(Data() As Integer, k As Integer) As Integer    Dim tempArr    tempArr = Data    ReDim Preserve temp(0 To k - 1)    CumulativeSum = WorksheetFunction.Sum(tempArr)End Function

EDIT :

Sub Demo()    Dim MyArray    Dim i As Long    MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)    Debug.Print MyArray(LBound(MyArray))    For i = LBound(MyArray) + 1 To UBound(MyArray)        MyArray(i) = MyArray(i - 1) + MyArray(i)        Debug.Print MyArray(i)    Next iEnd Sub

Above code updates array arr from
1, 2, 3, 4, 5, 6, 7, 8, 9
to
1, 3, 6, 10, 15, 21, 28, 36, 45

Try this:

Sub test()Dim arr As Variantarr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)Dim mySum As Long, k As LongDim wsf As WorksheetFunctionSet wsf = Application.WorksheetFunctionk = 6'operative line belowmySum = wsf.Sum(wsf.Index(arr, 1, Evaluate("ROW(1:" & k & ")")))MsgBox mySumEnd Sub