Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum part of range passed to UDF
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum part of range passed to UDF
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 wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum part of range passed to UDF
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 -- __________________________________ HTH Bob wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum part of range passed to UDF
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum part of range passed to UDF
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 wrote in message ... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum part of range passed to UDF
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify range variable passed through Function | Excel Programming | |||
ClearContents method on a passed range | New Users to Excel | |||
PrintOut macro from ?passed range.addrsess | Excel Programming | |||
segregating passed range by rows and columns | Excel Programming | |||
Translate range name passed as string to a custom function to range addresses! | Excel Programming |