How to make For Each Cumulative Sum more efficient

[drpdrpdrp]

Hi All,
How can I make the following structure faster or simpler? Simple running cumulative sum of elements in an array returning an array of the same size. I've heard using For Each should make things faster but the lag I'm noticing with the structure below is noticeable on a few arrays per sheet (arrays are 100,000 rows each).


Function CumulativeSum(myArray As Variant) As Variant
Dim result() As Variant
Dim element As Variant
Dim i As Long

' Resize the result array to match the size of input array
ReDim result(1 To UBound(myArray))

' Initialize the cumulative sum variable
Dim sum As Double
sum = 0

i = 1

' Loop through each element in the array using For Each
For Each element In myArray
' Convert the element to a number (assuming the array contains numbers)
element = Val(element)

' Calculate cumulative sum
sum = sum + element

' Store the cumulative sum in the result array
result(i) = sum
i = i + 1
Next element

' Return the array of cumulative sums
CumulativeSum = result
End Function

 

[offthelip]

What is the type of the "variant" you are calling this array with, Are you using a range as the input? If so this could be your problem

 

[DanteAmor]

How can I make the following structure faster or simpler?

I did a test with a million values and the result is immediate.

Do you have other macros running or does your sheet have too many formulas?

 

[drpdrpdrp]

I did a test with a million values and the result is immediate.

Do you have other macros running or does your sheet have too many formulas?

It takes a few seconds on my 4GB RAM pc when I run it for a few columns on the same sheet. Wondering if I can improve that. Thanks for your reply Dante

 

[drpdrpdrp]

What is the type of the "variant" you are calling this array with, Are you using a range as the input? If so this could be your problem

Correct. My input is an array (usually long... 100k to 200k elements)

 

[DanteAmor]

It takes a few seconds on my 4GB RAM pc when I run it for a few columns on the same sheet. Wondering if I can improve that. Thanks for your reply Dante

If you have the CumulativeSum function several times in the sheet, each time you do an execution, all the functions will be executed, that is, if you have 10 functions, each with 100 thousand records, then the function will be executed 10 times.
It is best if you put all 10 executions in a single macro and it is executed only once.