RTD and recalculation

Add-in Express™ Support Service
That's what is more important than anything else

RTD and recalculation
 
nwein




Posts: 577
Joined: 2011-03-28
From my tests I can see that my XLL UDF wrapper for RTD is being re-triggered whenever any of the Excel recalculation is being used (e.g. add new column/row, move a cell) regardless if the cell is a precedent/dependent of the UDF; In fact, my UDF has no parameters at all, yet it is being flagged as dirty by the recalculation logic when any (or some) of the aforementioned actions happen.
This is not the case for non-RTD UDFs. i.e. having a simple (non-volatile) UDF will not be re-triggered by adding a new column/row.
My RTD UDF is not volatile so that is not the reason why it is being called, and of course, I'm only talking about Excel being in Automatic calculation mode.

What I'm trying to find is some assurance of my finding. I just couldn't find any reference stating that RTD will always recalculate when user triggers such event. Only place I saw something like that was Govert's comment in a group discussion.

Do you have some more information and/or reassurance of the above assumption?
Posted 05 Aug, 2021 10:44:09 Top
Andrei Smolin


Add-in Express team


Posts: 17962
Joined: 2006-05-11
Hello Nir,

I've never heard of an XLL function becoming volatile if it wraps an RTD call.

You can use the "237" trick that makes your function non-volatile; see Excel XLL AcceptAsArray = False causes the function to be volatile.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 05 Aug, 2021 13:49:40 Top
nwein




Posts: 577
Joined: 2011-03-28
No, my RTD UDF is not volatile as I wrote. It is not being triggered every time the sheet/workbook calculates.
It is however, being triggered when triggering recalculation as a result of (from the link I've added above):
The user can trigger recalculation in Microsoft Excel in several ways, for example:

Entering new data (if Excel is in Automatic recalculation mode, described later in this topic).

Explicitly instructing Excel to recalculate all or part of a workbook.

Deleting or inserting a row or column.

Saving a workbook while the Recalculate before save option is set.

Performing certain Autofilter actions.

Double-clicking a row or column divider (in Automatic calculation mode).

Adding, editing, or deleting a defined name.

Renaming a worksheet.

Changing the position of a worksheet in relation to other worksheets.

Hiding or unhiding rows, but not columns.


However triggering a recalculation using the above actions does not cause all UDFs to recalculate, simple UDFs (especially ones without any parameters) will not be recalculated, yet RTD UDFs will.

Also, just to make sure it's not volatile (and it's not - I'm 100% sure), I did add the 237 there just to test and it didn't make a difference.
Posted 05 Aug, 2021 14:02:55 Top
nwein




Posts: 577
Joined: 2011-03-28
Actually, my tests shows that most of the actions specified above *do not* cause recalculation (either on regular UDFs or RTD), but the following actions will:

  • Add/Delete row/column (depending on which column/row this might trigger a recalc on "regular" UDFs but will always trigger RTD UDFs)
  • Move a cell around (i.e. with the mouse select the border of a cell and drag the cell anywhere. The cell can be empty, it doesn't matter) - this will casue an RTD re-calc but not "regular UDF recalc
  • Calling CalculateFullRebuild - this is expected though and I have no problem with it (will recalc both regular and RTD UDfs)
Posted 05 Aug, 2021 15:44:56 Top
Andrei Smolin


Add-in Express team


Posts: 17962
Joined: 2006-05-11
Hello Nir,

Oh, I'm sorry.

I've found a somewhat similar issue reported at https://www.excelforum.com/excel-programming-vba-macros/400449-problem-in-excel-rtd-and-manual-recalculation.html.

Also, I can't reproduce the issue using the project at https://temp.add-in-express.com/support/MyRTDServer1-nwein-forum.zip. I test it on a new workbook in Excel 365 Version 2108 (Build 14326.20046 Click-to-Run).

That is, if I delete a column or row, the calculation isn't triggered for me. Neither for an RTD topic nor for the XLL wrapper function (parametrless).

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 06 Aug, 2021 14:03:49 Top
nwein




Posts: 577
Joined: 2011-03-28
Thanks for looking into it, I've downloaded your sample and ran it and still see the same behaviour - whenever I delete/add a row/column, or move a cell around, the RtdWrapper1 method in the xll module is being called (i've placed a breakpoint at the beginning of the method). I've change the interval to be higher so that it's not interfering with the calls.
I'm using Excel for Microsoft 365 MSO (16.0.13127.21734) 64-bit Version 2008 (build 13127.21736 Click-to-Run) but I can say that this behaviour occurred in every Office version I've checked (from 2010 to this)
Posted 23 Aug, 2021 11:04:52 Top
nwein




Posts: 577
Joined: 2011-03-28
You can see a video of this behaviour in the project you created for me here
Posted 24 Aug, 2021 09:22:33 Top
Andrei Smolin


Add-in Express team


Posts: 17962
Joined: 2006-05-11
Hello Nir,

That is, in your case, deleting a row (etc.) causes the test add-in to return a new value, correct? In my case, the XLL wrapper function gets triggered, but the RTD itself isn't.

I've found out that the deletion (etc.) is reported to the XLL add-in first and to the COM add-in then.

Does the issue reproduce if you turn off al other extensions in Excel?

If yes, you'd need to invent a condition that would prevent your RTD function from recalculation too often.

Regards from Poland (CEST),

Andrei Smolin
Add-in Express Team Leader
Posted 25 Aug, 2021 06:38:51 Top
nwein




Posts: 577
Joined: 2011-03-28
You are right, the RTD itself is not called in those scenarios, however that is not the issue here. The problem occurs when another formula references such RTD cell - it will recalculate on those events because of it.
My question was more on the way of why the UDF is being called on those events, given that "normal" UDFs don't get called when doing such events.
Posted 25 Aug, 2021 10:03:49 Top
nwein




Posts: 577
Joined: 2011-03-28
After further investigation I can say that deletion of rows/column will always trigger re-calculation for any UDF, RTD or not. So my question is really about adding new row/column and moving a cell - those events only cause an RTD wrapper UDF to be called but not non-RTD UDFs.
Posted 25 Aug, 2021 10:31:37 Top