Display update incomplete after cell-invoked macro Function modifies multiple cells

[wrighch]

Disclaimer: I realize the question is rather vague and open-ended? So, if this is not the appropriate forum in which to ask such questions, please let me know and I’ll happily re-post elsewhere. Also, since the issue is complex by its very nature, providing a Simple Self-Contained Example is a contradiction in terms. I’m asking for thoughts and suggestions from others who understand the interactions between the calculation engine and the Basic runtime library better than I do. That said…

I have written a Basic macro Function MyFunction. Cell A2 contains the formula =MyFunction(A1). When I manually enter a new value in cell A1, MyFunction is invoked and MyFunction’s return value then appears in cell A2 as expected.

However, in addition to displaying the return value in the cell from which it is invoked, I would like MyFunction to make changes to multiple cells on multiple sheets. When I do that, the other cells are properly updated per the macro code as expected. But Calc sometimes (but not always) fails to display the changes to one or more of those other cells until I manually toggle to another sheet within the workbook and back. I am told this is because Calc cannot know which cells the macro code might modify and, therefore, cannot determine which other cells may need to be re-calculated or re-displayed and the correct order in which to perform the re-calculations. Invoking ThisComponent.calculateAll does not seem to help.

Is it possible to force Calc to refresh the display of all cells? Is what I’m trying to do impossible? Any suggestions would be appreciated.

[igorlius]

That sounds a bit like a common limitation of a macro function, which is:

with the exception being the cell from which the function was called.

(ref. Accessing cells directly - Apache OpenOffice Wiki)

A subroutine in contrast does not have this limitation, as far as i know.

Hope that helps.

[wrighch]

Thank you for the link. Based on my various attempts, it would seem that the referenced prohibition is more general and applies to any value on any sheet except the cell from which the function is called. The issue is present even if the Function is invoked from a sheet other than the Active Sheet (e.g. move the formula from Sheet1.A2 to Sheet2.A2.) And the impact extends to other aspects: Conditional Formats may or may not update to reflect modifications to other cells changed by the Function. And changes made by the Function to Control Properties (such as Button.Enabled or Button.Visible) are not always effected until subsequent manual changes to the Active Sheet.

Are there no other techniques that might be tried?

[igorlius]

That would be new information to me.

Especially since I already wrote a few functions, which modify other sheets.

For an example you can look at this answer of mine :

In Calc, is there a way to dynamically fill a cell with a color based on a hex code/RGB values/LO color code in an adjacent cell?

Hello, here is my try at a solution as a macro function (see end of this post) which can be called via =SETCOLOR($Sheet1.A3,"Sheet1","B3") the 1st argument is a reference to a cell containing a hex color value (e.g. “ff0000” without the quotes) the 2nd and 3rd argument are strings which specify the cell which shoult be colored Sadly i found that this solution has one major drawback: When a macro is called as a Calc function, the macro cannot modify any value in the sheet from which th…

But you might be right, that there is a kind of “limit” to how many changes or what kind of changes a function can evoke.

Can you post an reduced example so i / we can redpocue the issue?
Otherwise its hard to advice/debug.

[wrighch]

I have just begun trying to create an example. But my existing workbook is so extensive that it will take me some time to extract just enough pieces needed to reliably reproduce an issue that is intermittent to begin with. Sometimes, it seems that simply moving one or more Functions or Subroutines in my code from the top of the file to the bottom ‘resolves’ the problem until it reappears randomly after subsequent code modifications. Sometimes, saving, exiting, and reopening the workbook resolves it.

In the meanwhile, your comment above that a “subroutine in contrast does not have this limitation” led me to place a Text Box Control on top of my data entry cell (A1 in the original post) which is linked to cell A1 via ‘Linked Cell’ box on the Data tab of the Control Properties. The Text Box Control is configured to invoke a Subroutine macro to do the desired work when the text is changed. The initial result is: That does what I need to do.

More to follow as I do more testing.

[wrighch]

In my comment of 04 Oct, I promised more info. Here it is. I now believe my 04 Oct statement that the “prohibition is more general and applies to any value on any sheet except the cell from which the function is called” is incorrect and was merely a rather extreme manifestation of the restriction seen in spades when several different Functions, each called from different cell formulas, each violate the restriction that @igorlius references. Having completed the process of redesigning those Functions to comply with the restriction, and transforming several other Functions into subroutines that are now called via Button Controls, the problems I reported in the OP have disappeared. I will mark @igorlius’ response as the answer.