More efficient way to calculate values

[Marw]

Hi,
I'm trying to make a function that sums values from calls that have th
same outline level. Idea is to calculate only cells until the next uppe
level cell.

Here is what I have done, but this function seems to be ver
unefficient when I'm using that in many cells to calculate quite larg
amount of data.

Do you have any ideas how to make the same more efficient way?


Function OutlineLevelSum(iLevel As Integer, rSumRange As Range)
Dim rCell As Range
Dim vResult

For Each rCell In rSumRange
If Rows(rCell.Row).OutlineLevel = iLevel Then
vResult = vResult + rCell.Value
ElseIf Rows(rCell.Row).OutlineLevel < iLevel Then
Exit For
End If
Next rCell

OutlineLevelSum = vResult
End Functio

 

[Niek Otten]

Maybe declaring the function itself as Double helps a bit (Function
OLS(....) as Double)
It avoids one Variant addition per cycle.
I always use Long instead of Integer because that is the basic unit in
32-bit systems. But there may be leftover instructions for Integers, I don't
know. Just try.

 

[Marw]

Thanks for the idea. I did that but it doesn't seem to affect much.