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.