More efficient way to calculate values

[Marw]

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

[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.

--
Kind regards,

Niek Otten

"Marw" <Marw.20qvhy_1135764001.5149@excelforum-nospam.com> wrote in message
news:Marw.20qvhy_1135764001.5149@excelforum-nospam.com...
>
> 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
>

[Marw]

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