none
Excel unexpected behavior: My non-volatile UDF is called when a row/column is deleted RRS feed

  • Question

  • Hello,

    Consider the following VBA UDF; it simply returns the current time

    Option Explicit
    
    Public Function MyUDF()
        MyUDF = DateTime.Now
    End Function
    

    You can create it in this way:

    1.    Start Excel and create a new workbook.
    2.    Press Alt+F11 to open the VBA IDE
    3.    In the Project window, find the project corresponding for your workbook, right-click the project and choose Insert | Module
    4.    Paste the code above into the newly-added module (replace all existing code, if any) and choose Debug | Compile {project name}

    The UDF is ready, let's create a test formula:

    1.    Switch to the Excel UI and enter this formula in a cell: =MyUDF()
    2.    Right-click the cell and choose Format Cells on the context menu
    3.    On the Number tab select the Time category, choose any format displaying seconds and click OK

    You can check that the formula isn't volatile: changing any cell, adding a row or column doesn't trigger the formula. This is expected. Nevertheless, if you delete a row, column or cell(s), the formula is triggered. This isn't expected as we know that the formula doesn't depend on any of the cells, columns, rows deleted. Is there an explanation to this behavior? I can't find such an explanation in Excel Recalculation.

    I see the issue in Office 2016 Insider but I was reported that the issue is reproducible on a variety of Excel 2016 builds as well as in Excel 2010 and 2013.

    Do you reproduce the issue? My goal is to confirm or disprove that the issue exists, to create a reference point and to collect more details (if any). I'm going to report this issue to the Excel team but I'd like to get any kind of confirmation first. Thank you in advance.


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader

    Please mark answers and useful posts to help other developers use the forums efficiently.

    Tuesday, May 15, 2018 8:59 AM

All replies

  • Hello Andrei Smolin,

    To be honest, this is my first time to hear volatile and non-volatile function. From the page you shared, I get the definition of volatile. However, there is no detail description of a non-volatile function and so I have no idea that if a non-volatile function should change its value while user trigger recalculation.

    Anyway, I could reproduce the phenomenon as you said. You could try to feedback the issue if you think it is an issue.

    Best Regards,

    Terry


    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.

    Wednesday, May 16, 2018 10:23 AM
  • I confirm your finding in Excel 2013; the UDF is not flagged as volatile but is flagged as dirty when a column is deleted (but not when a column is added). the position of the column (before or after the cell containing the UDF) does not affect this behavior.

    This also happens if the UDF does not refer to Now() or is not formatted as Time.

    I do not have an explanation for this peculiarity of the Excel calculation method.


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

    Wednesday, May 16, 2018 12:44 PM
  • From documentation page Excel Recalculation:

    --

    When a structural change is made to a workbook, for example, when a new formula is entered, Excel reconstructs the dependency tree and calculation chain. When new data or new formulas are entered, Excel marks all the cells that depend on that new data as needing recalculation. Cells that are marked in this way are known as dirty. All direct and indirect dependents are marked as dirty so that if B1 depends on A1, and C1 depends on B1, when A1 is changed, both B1 and C1 are marked as dirty.

    After marking cells as dirty, when a recalculation is next done, Excel reevaluates the contents of each dirty cell in the order dictated by the calculation chain. In the example given earlier, this means B1 is first, and then C1. This recalculation occurs immediately after Excel finishes marking cells as dirty if the recalculation mode is automatic; otherwise, it occurs later.

    --

    From the documentation, non-volatile UDFs should only be recalculated when an input to the UDF changes. This is not the behavior I have been experiencing. This can be a pretty big problem when the UDF does expensive processing.




    Wednesday, May 16, 2018 1:38 PM