Posts 1 - 10 of 13
First | Prev. | 1 2 | Next | Last
|
|
|
|
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 https://docs.microsoft.com/en-us/office/client-developer/excel/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 https://groups.google.com/g/exceldna/c/F-xBsTfrdjU/m/vZOa-R5hHgAJ 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: 18821
Joined: 2006-05-11
|
|
|
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: 18821
Joined: 2006-05-11
|
|
|
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
|
|
|
Posted 24 Aug, 2021 09:22:33
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18821
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
|
|
|
Posts 1 - 10 of 13
First | Prev. | 1 2 | Next | Last
|
Forums
Topics
Search
Help
Login
Register