How to improve the performance of a looping UDF

Hi all,

I have a public function that divides the 1st cell in a range by the 2nd cell, rounds the value up, then divides to the 3rd and 4th cells and rounds up, then divides the 6th and 7th cells... until the end of the range.

Each rounded up value is added together and the function returns the total.

I'm looping through the range which I think is why it seems quite slow. Slow is a problem because I'm going to use this function in about 450 cells and each range will have up to 100 cells. Also, it really needs it to be a volatile function.

So I'm wondering if perhaps there is a better way to perform the required calculation. Perhaps manipulating the range via an array rather than a loop? But I'm not sure how to do that.

Any advice or suggestions would be appreciated.

Here's the existing code:

Please Login or Register  to view this content.

Wow, thanks guys - I had almost given up on getting feedback on this one when it slipped to page 2 of the new postings lists.

I'll give them all a test today.

Thanks again!

Thanks RS, that certainly helped get me closer to what I needed, however when I ran your code as is, it errored out.

I had to change the calculation line to:

Please Login or Register  to view this content.

I think the varData = rngTest.Value caused problems when trying to resolve varData(iCntr). I'm guessing it resolves = rngTest.Value(1) when the what is really needed is rngTest(1).Value

But I ran into one other problem after I ran the code, it works fine for a column of numbers, but it seems to finish after one calculation when rngTest is a row of cells (e.g. A22:O22).

What do I need to change to make the function work with a row of cells?

Thanks in advance,

JC

Got it figured out - thanks all for your help.

Once I learned from this clever camper that Range.Value (or .Value2) returns a 2 dimensional array even if it is given a 1 dimensional array, all I had to do was modify the code to handle 2 dimensions and my issues were resolved.

Was all this worth it? Not in terms of speed. For my requirements, I'll have at most 450 FCroundups in a workbook, each with a range of 99 cells. Both the looping and array approaches take about 2 seconds to process that many calculations.

When I tried 1000 FCroundups each with a range of 3500 cells, the array process took 48 seconds, the looping 68 seconds. About 30% faster.

Was a great learning process though. And for that - thanks again Mordred and RomperStomper for your help and contributions.

For reference, here's the code in working form.

Please Login or Register  to view this content.