none
Speeding up a UDF RRS feed

  • Question

  • Hi,

    Don't know why this rubish is on top, anyway.

    The code is not really necessary hence I do not paste it here.

    Public Function MostCommonVal(Data As Range, Optional Index

    I put Data in a dictionary and rebuild each time the dictionary when the formula is recalculated.

    I was thinking to add a static variable thet holds the data address and skip rebuilding the dictionary when the address is the same.

    But of course, there will be situations that the UDF is used in some cells with the same range and in some other cells with another range.

    Is this a good idea or is this worse for the performance. All ideas are welcome.



    • Edited by JP Ronse Saturday, May 5, 2018 12:27 PM
    Saturday, May 5, 2018 12:26 PM

Answers

  • Hi JP,

    Thanks for the detailed explanation. It depends on your scenario, you should build the dictionary based on the business requirements based on the two parameters of this function.

    As you mentioned you may use the static parameter to hold the history of range already be calculated to ignore the Index parameter to improve the performance. And in my option, we need to improve the performance only when we can't bear it. So if the function is rare slow for you, skip the building when the range is same should be considered.

    Hope it is helpful.

    Regards & Fei


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by JP Ronse Saturday, May 19, 2018 8:50 AM
    Tuesday, May 8, 2018 9:46 AM
    Moderator

All replies

  • Hi JP Ronse,

    By default, Excel assumes that VBA UDFs are not volatile. It means the function will not recalculate when the input range is not changed. 

    Here are some helpful documents about performance and recalculation in Excel:

    Excel performance: Improving calculation performance

    Excel Recalculation

    Please feel free to let me know if misunderstood.

    Regards & Fei



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, May 7, 2018 7:15 AM
    Moderator
  • Hi Fei,

    Thanks for the swift feedback. The function is not volatile. I'll try to explain better.

    Public Function MostCommonVal(Data As Range, Optional Index

    What it does is reading Data and putting it in a dictionary. Quite fast and useable. The slowness starts when I use f.i. ROW() as the Index and drag it down. In this situation the Data range is always the same and there is no need to refill the dictionary each time because Data is always the same. So I was thinking to skip filling the dictionary when it is already initialized.

    But of course? i can imagine situations like:

    =MostCommonVal(Data1 As Range, Optional Index

    =MostCommonVal(Data2 As Range, Optional Index

    As different ranges are used, it is also required to rebuild the dictionary.

    We could even go further and drag down above formulas with different index.

    Is there a reliable way to detect if the dictionary must be recreated or not.

    Regards,

    JP

     

    Monday, May 7, 2018 5:13 PM
  • Hi JP,

    Thanks for the detailed explanation. It depends on your scenario, you should build the dictionary based on the business requirements based on the two parameters of this function.

    As you mentioned you may use the static parameter to hold the history of range already be calculated to ignore the Index parameter to improve the performance. And in my option, we need to improve the performance only when we can't bear it. So if the function is rare slow for you, skip the building when the range is same should be considered.

    Hope it is helpful.

    Regards & Fei


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by JP Ronse Saturday, May 19, 2018 8:50 AM
    Tuesday, May 8, 2018 9:46 AM
    Moderator
  • Hi Fei,

    The more I think about the more I dislike the idea. The outcome of the UDF can be wrong when someone changes the contents in the data range. The UDF is part of a free add-in and although I know and can document the limitations, I have no control over the use by others. Therefore...

    In meantime, I made some progress by using QuickSort to sort the dictionary in ascending or descending order which eliminates the search of the given index.

    I have also to say that I tested on a huge range of 60,000 cells. When it remains too slow, I will think about using workbook events and set/reset a global variable to determine if the dictionary must be rebuild or not but this is maybe overkill for the purpose.

    I'll keep you posted.

    Regards,

    JP

    Tuesday, May 8, 2018 5:14 PM