Sum part of range passed to UDF

[cwrm4]

I am passing a range, along with some other parameters, to a UDF that
calculates a safety stock requirement.

Based on the other parameters I send to the UDF, the UDF needs to
return the sum of a certain number of sequential elements in the
range. For example, if the range contains (2, 2, 2, 3, 3, 3, 4, 4, 4),
and I need 4 weeks of safety stock, the UDF should sum 2 + 2 + 2 + 3.

Would there be a way to sum with a single command the first four
values the above range? Currently I am looping through the range and
adding the "next" value on each loop through the range. However, I am
hitting some performance issues (this UDF is several hundred times in
the workbook) and am looking for a faster method.

 

[Rick Rothstein \(MVP - VB\)]

It is always a good idea to post any code you have so we can see exactly
what your are doing and make suggestions about it where applicable. Without
knowing what the "other parameters" you are sending to your UDF, I will note
that the summation you are doing can probably be done using much faster
built-in worksheet formulas. For example, if your column of sequential
elements are in B2:B500 and the number of weeks of safety stock is in S2,
then the sum you are looking for can be found with this formula...

=SUMPRODUCT((ROW(B2:B500)<=S2)*B2:B500)

Change the value in S2 and the summation changes accordingly. Whether your
"other parameters" can be easily folded into this formula or not depends on
what they are.

Rick

 

[Bob Phillips]

Do you mean

Function GetSum(rng As Range)
GetSum = rng.Cells(1, 1) + rng.Cells(2, 1) + rng.Cells(3, 1) +
rng.Cells(4, 1)
End Function

 

[cwrm4]

The other parameters I mentioned are for a separate calculation to
determine how many weeks of demand to calculate.

This code is a simplified version of what I have:

Public Function StockTarget(WeeksTotalDemand As Integer, rngDemand
As Range)

Dim ThisWeek As Integer

For ThisWeek = 1 To WeeksTotalDemand
StockTarget = StockTarget + rngDemand(ThisWeek)
Next ThisWeek

End Function

Is there a way to replace the for-next loop with a single sum
function?

 

[Rick Rothstein \(MVP - VB\)]

You might be able to reconstruct your ranges so you can use the
WorksheetFunction.Sum function, but I doubt if that would be faster than
your simple loop. I still think your slow down is the UDF itself. If you can
reduce the calculation for the weeks of demand to a worksheet formula and
put that formula in my sample suggested location of S2, you could use the
formula I posted for the summation and not use any UDF (which are much
slower than worksheet formulas). The only thing that might have to be
addressed with the summation formula is absolute versus relative address
depending on whether your formulas would be copied down or across.

Rick

 

[cwrm4]

Thanks Rick...the problem is that this UDF is used in literally
hundreds of places in the spreadsheet. Over time I will probably
change how the calculation works inside the UDF (it is much more
complicated than my sample code), therefore I am reluctant to change
the UDF to a worksheet function, due to maintenance issues.