More efficient way to calculate values
 

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

--
Mar
-----------------------------------------------------------------------
Marw's Profile: http://www.excelforum.com/member.php...fo&userid=2993
View this thread: http://www.excelforum.com/showthread.php?threadid=49635

More efficient way to calculate values
 

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.

--
Kind regards,

Niek Otten

"Marw" wrote in message
...

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

Here is what I have done, but this function seems to be very
unefficient when I'm using that in many cells to calculate quite large
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 Function


--
Marw
------------------------------------------------------------------------
Marw's Profile:
http://www.excelforum.com/member.php...o&userid=29934
View this thread: http://www.excelforum.com/showthread...hreadid=496352

More efficient way to calculate values
 

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


--
Marw
------------------------------------------------------------------------
Marw's Profile: http://www.excelforum.com/member.php...o&userid=29934
View this thread: http://www.excelforum.com/showthread...hreadid=496352