none
UDFs are evaluated multiple times RRS feed

  • Question

  • We are having some issues with UDFs that return arrays - in some cases they seem to be calculated multiple times.

    Here is an example that uses that same UDF, but in one case the UDF is evaluated only once when the input data changes, but in the other one the UDF is calculated multiple times. Here is the UDF:

    Public Function ArrayUDF(Number As Variant) As Variant
        Dim vntTemp(2, 0) As Variant
        MsgBox "here"
        vntTemp(0, 0) = Number ^ 2
        vntTemp(1, 0) = Number ^ 3
        vntTemp(2, 0) = Number ^ 4
        ArrayUDF = vntTemp
    End Function


    As you can see it accepts a number and returns an array of three powers of the input number.

    1.  If you evaluate this UDF as an array formula (passing in some cell with a value), then define a name on these calculated values and use  the name for the values on some chart it works fine. If you change the value used for the calculation then the UDF is only calculated once.

    2. If you define a name, which does exactly the same thing as before, only that it calls the UDF directly, instead of being connected to the range with the UDF, and connect that name to the chart, then the UDF is calculated multiple times.

    Here is an example workbook. The first worksheet is the first scenario, if you change the value in A1 you will only get one message box, meaning that the UDF was called only once. However, if you do the same thing on the second worksheet, you will see the message box multiple times (usually 8 on my computer). Also, sometimes the chart is not even visible when switching to the second worksheet. It sometimes only appears after changing the value in A1.

    Since this impacts the performance of our addin significantly I'm wondering if there is any workaround? I would except these two scenarios to work exactly the same, but the second one is much worse performance wise.




    Tuesday, October 4, 2016 1:16 PM

Answers

  • Hello Deepak,

    We are currently doing optimisations of the calculations. This won't solve the problem with the multiple calls to the calculations, but we hope that the improvements will be good enough so that other (more significant) changes won't be necessary.

    Regards,

    Mitja

    Wednesday, October 19, 2016 8:29 AM

All replies

  • Hi Mitja Bezensek,

    I think it's because of using Series to assign the data to the chart.

    when you use the range directly in that it is assigning the data of array element separately to each range in one time.

    also you will step through it when you debug it.

    but it's like when you use series it does not even allow you to debug. if you had put a break point then also it will not stop on it.

    and it get repeat for each calculation.

    like in your above calculation it will execute about 8 times.

    and I also notice that it is executing randomly after some time and if you did not change the value or do nothing then also it is executing.

    so here I think your 1st method is appropriate and suitable here to assign the UDF data to chart.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 5, 2016 4:22 AM
    Moderator
  • I would expect both scenarios to work the same. Maybe even the second one should be faster, since it does not need the indirection via the Range. I guess there is some bug when Charts evaluate names which causes this duplication in calculations.

    Which is quite sad. We don't want to use the Ranges since the implementation without them is much cleaner. We don't need to write all over the worksheet just to produce a few charts.

    Is it possible to take a look at what might be causing this poor performance and if it can improved in the future versions?

    Wednesday, October 5, 2016 11:47 AM
  • We don't want to use the Ranges since the implementation without them is much cleaner. We don't need to write all over the worksheet just to produce a few charts.

    If that's your motivation, why do you not write the data directly into the chart?

    Andreas.

    Wednesday, October 5, 2016 2:11 PM
  • Because the chart then doesn't change when the input data changes. It just has fixed values.

    See the example picture below: the input data has two columns and the chart shows the absolute difference between those two columns. But the difference is calculated in the UDF and it is not visible anywhere on the worksheet. So when the data changes (imagine this input data coming from a Pivot table and the users changes some filter) the UDF calculates again and the chart updates. The only problem is that the calculation is done multiple times and with many charts it can cause quite slow performance.

    This is just a simple example, we have much more complex charts where putting the intermediate calculations on the worksheet is not really relevant for our customers.


    Wednesday, October 5, 2016 2:44 PM
  • Hi Mitja,

    you should check for the volatile method for UDF.

    if it is true then it will call the UDF every time when any calculation occur in the sheet.

    in your case it looks like it is creating a cycle and calculating the further value based on the value of current execution of UDF.

    please check the link mentioned below may help you.

    Excel and UDF Performance Stuff

    How to stop excel evaluating a UDF twice

    UDF not recalculating or always recalculating or calculating in an unexpected sequence.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 6, 2016 4:47 AM
    Moderator
  • Because the chart then doesn't change when the input data changes.

    The simplest stupid solution is to use this code in the code module of "From UDF" in your sample file:

    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim Data
      Dim i As Long
      Select Case Target.Address(0, 0)
        Case "A1"
          'Create a 1D array
          ReDim Data(2 To 4)
          'Fill in the values
          For i = LBound(Data) To UBound(Data)
            Data(i) = Target ^ i
          Next
          'Flush into the chart
          Me.ChartObjects(1).Chart.SeriesCollection(1).Values = Data
      End Select
    End Sub

    Means you don't need to create a name, you don't need a UDF, you can create any chart just on the fly from anywhere.

    And it's much faster as any UDF, even if the UDF is called only once! (I'm sure about that.)

    Andreas.

    Thursday, October 6, 2016 10:08 AM
  • Hello Deepak. That was the first thing I checked. As you can see the UDF tied to a range only calculates once when the input data changes. It does not calculate when data other than the one that is used as input for the UDF changes. Which means that the function is not volatile. It only seems to have problem with multiple recalculations when connected to a chart.

    I have searched the web extensively before opening the topic here but none of them deal with this particular case: connecting UDF to names and those names to charts. The links you posted did help with understanding how Excel calculations and UDFs work, but none of the suggestions helped in our particular case. Maybe I'm missing something in those links, what exactly do you think will help in our situation?

    I have tried changing multiple settings for our udfs (like if it is a macro, thread save,...), but the number of triggered calculations stayed the same. I also tried VBA macros (like the one in the example workbook) as well as writing the UDFs in an XLL addin. The result was the same.

    Also the multiple calculations are not the only problem. The charts somehow disappear from time to time as well. When I first open the example workbook and switch to the second worksheet the chart is not visible. I have to update the A1 cell in order for the chart to appear. This is clearly some bug.



    Thursday, October 6, 2016 12:07 PM
  • Hello Andreas. Our Addin allows the users to export the generated charts to SharePoint as well. We can achieve that via the Excel services support for UDF. At least for now your solution is not supported on SharePoint, so we can't really use it.

    Our current solution seems to fill all our needs, the only problem seems to be performance. I'm wondering what is causing this inefficient evaluation of UDFs. Is this a known issue? Is there a workaround (which also works on SharePoint)?

    Thursday, October 6, 2016 12:10 PM
  • Our current solution seems to fill all our needs, the only problem seems to be performance. I'm wondering what is causing this inefficient evaluation of UDFs.

    It's the combination of your design and how Excel works.

    We can talk a long time about that behavior, but we have no chance to change the behavior.
    I'm not from MS, but I bet my salary that nobody will change that.

    Open your mind for other solutions, so what about the Solver tool?
    Solver is also an AddIn, the issue is similar to yours... does the Solver needs an UDF? No.
    The Solver creates hidden names to store the informations how to handle the scenarios.

    You don't like that? Why not create a (hidden) data sheet and call the UDF from cells as usual?

    If you want more performance, you have to go other ways, IMHO there is nothing that you can do to stop the (of course unexpected) behavior. That's all I can say, sorry.

    Andreas.

    Thursday, October 6, 2016 2:47 PM
  • We are very open and aware of different possibilities and are currently considering at least 4 of them. One of them is the hidden data sheet you mentioned. It gets quite complicated though since you need to handle operations like copying the worksheets (otherwise the copied worksheet points to the same hidden sheet and then the changing of input data would not longer work on the copied worksheet), deleting the worksheets,... All this is complicated by the fact that there are no Excel events for these actions, which means manually keeping some sort of state in order to detect all the scenarios that we need to handle. And on top of that events in different versions of Excel work differently - one silly example is probably a bug in Excel 2010 which does not allow you to delete some of the names that were used on a just deleted worksheet (but you can delete those names after you reopen the workbook). There are many cases like this (that we know of) and most likely many that were don't. Which makes it hard for us just to abandon our current solution.

    This is why I was asking in this thread if the described behaviour is expected and if there are any known workarounds. For example we only tried the VBA and XLL UDF, but not UDFs written in C or some other detail that we might have missed. We know we can optimise some of our calculations, and we will first try to do that. But if we would manage to solve the problem with UDF running multiple times we would be able to drastically improve our performance. So for the time being we would like to focus on this particular case any possible solutions. And if there aren't any we will evaluate other options in more detail.

    I'm thankful for your suggestions, and for sure we will look at the proposed options if it comes to that.




    Thursday, October 6, 2016 3:44 PM
  • Definitely looks like a bug to me, and I don't see an easy bypass that keeps the Name approach.

    The fact that setting a debug breakpoint on the UDF never gets into debug is similar to what happens with UDFs in conditional formats.


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

    Thursday, October 6, 2016 6:27 PM
  • Yeah, we came to the same conclusions, now looking into alternatives.

    PS: Charles thanks for the articles on your blog, I probably read all of them :)

    Wednesday, October 12, 2016 8:02 AM
  • Hi Mitja ,

    As you mentioned in your last post you are trying to looking for an alternatives.

    did you find any suitable alternative that can able to solve your issue?

    if yes, please try to share something about that work around.

    if not please try to update the status of the thread.

    I will try to include some senior engineers to look in to this issue.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 17, 2016 8:19 AM
    Moderator
  • Hello Deepak,

    We are currently doing optimisations of the calculations. This won't solve the problem with the multiple calls to the calculations, but we hope that the improvements will be good enough so that other (more significant) changes won't be necessary.

    Regards,

    Mitja

    Wednesday, October 19, 2016 8:29 AM
  • Hi mitja,

    Thank you for updating the status of this thread.

    as you had mentioned you are doing some optimizations of calculation to improve the performance.

    so do you need any further assistance?

    Regards

    Deepak 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 20, 2016 8:23 AM
    Moderator
  • Hello Deepak,

    No, not really at the moment. But if you do find something out about this issue please let me know.

    Thursday, October 20, 2016 8:31 AM