+ Reply to Thread
Results 1 to 9 of 9

How to improve the performance of a looping UDF

  1. #1
    Registered User
    Join Date
    04-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    14

    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.

  2. #2
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: How to improve the performance of a looping UDF

    Hi johnnycanuck,
    Hopefully this is close enough for your purposes. Let me know!
    Attached Files Attached Files
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  3. #3
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: How to improve the performance of a looping UDF

    Hi johnnycanuck, I am having another go at the workbook again because I gave the code a range of approximately 110,000 rows and it took about 3 minutes to finish. This is partly due to the fact that the sheet has three cells for every calculation and they are all being filled with borders and colours. I'm going to strip those parts out and see how much better it runs but I'm thinking that there has to be a better approach to this.

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to improve the performance of a looping UDF

    The array version of the original would be something like:
    Please Login or Register  to view this content.
    Note: the function does not need to be made volatile as you are passing the required data to it as an argument.
    Last edited by romperstomper; 07-29-2011 at 03:00 AM. Reason: change int to long
    Remember what the dormouse said
    Feed your head

  5. #5
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: How to improve the performance of a looping UDF

    Hi RS, I just ran your code, before I go to sleep, and it comes up an overflow error at
    Please Login or Register  to view this content.
    on the first iteration.

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to improve the performance of a looping UDF

    Thanks - forgot to change the Integer to Long. Have amended my code accordingly.

  7. #7
    Registered User
    Join Date
    04-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: How to improve the performance of a looping UDF

    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!

  8. #8
    Registered User
    Join Date
    04-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: How to improve the performance of a looping UDF

    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

  9. #9
    Registered User
    Join Date
    04-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    14

    Thumbs up SOLVED Re: How to improve the performance of a looping UDF

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1