I’ve written a macro to be used as cell formulas. The order in which the formulas execute is important. I figured that if I added a dummy parameter to function B that takes a cell, who calls function A, that it would force A to resolve before B does. But that isn’t the case. How can I control which order the cells execute their formulas in, if not by chaining them together?
would force A to resolve before B does. But that isn’t the case
Strange behavior. Can you show a small example that demonstrates this?
As you described, function B must be executed after A returns a new result. Perhaps the calculation of A does not lead to changes?
Cell B which takes cell A takes as a parameter to a macro function I made - the output is not affected by the cell. Maybe the parameter is being optimized out? I can’t make it depend on the value or it it would not result in a correct value. I just need to set them to resolve in a specified order.
Show the formula expression how you call it in the spreadsheet.
@fr_antic, You can only do this by macro, calculating outside the spreadsheet and entering the value in the cell.
Autocalcular will automatically execute, as many times as necessary, to eliminate calculation pending issues, alerting you when it enters circular mode (it will ask you what to do)
It depends… if the user defined spreadsheet function macro call in a formula expression is mymacro(A1) then A1 is calculated beforehand. That’s why I asked to show how it is called. Modifying cell content (“entering the value in the cell”) while a macro is calculating as part (i.e. spreadsheet function) of formula calculation is a no-go asking for trouble, that should only be done in a non-spreadsheet-function macro.
For example, I set cell A1 to call =ZeroCells( F1:F10 ) which sets them to 0. Then I set several other cells to =CalcPayment(A1, … , F1:F10 ) to update the cells F1:F10 and return a value that is not dependent on cell A1 at all, and doesn’t use the value in A1 whatsoever because it doesn’t need it, it’s just there to make sure A1 is evaluated - thus zeroing out the cells F1:F10 before it calculates.
Subsequent calls to CalcPayment will be passed the previous cell that called CalcPayment.
Example,
A1 = ZeroCells( F1:F10 )
C1 = CalcPayment( A1, … , F1:F10 )
C2 = CalcPayment( C1, … , F1:F10 )
C3 = CalcPayment( C2, … , F1:F10 )
C4 = CalcPayment( C3, … , F1:F10 )
Thereby making sure that each CalcPayment is modifying the values F1:F10 in the correct order, as CalcPayment requires the information from the previous CalcPayment call (which we put into F1:F10) in order to calculate what it needs to calculate.
The macro function is not yet finished but this is what it currently looks like: https://pastebin.com/P84As5y5
I am sorry to upset you, but your attempt is doomed to failure, you cannot make it work.
The techniques you are trying to use contradict the basic idea of spreadsheets - “the contents of the cell are the result of the calculations in this cell”. In other words, no function should change the values of other cells during calculations. And the LibreOffice developers put a lot of effort into this. Otherwise, the calculations would go on forever.
For example, such a line of code
a_cell.value = (a_cell.value + payment )
will not make any changes to a_cell. The fact is that at the time of recalculation of formulas, the sheet is blocked from changes.
So, it’s impossible
There are several options for other solutions to this problem, workarounds.
Try to solve the problem without macros, only using the built-in Calc functions. I am sure that you will not like this idea - you have already begun the implementation of the project with the help of BASIC and do not want to quit the task without completing it. But I should have called this option. It could really work - Calc very powerful computing device.
Try changing the logic of the macro - use the procedure instead of the functions in the cells. The trick is that the procedure is not subject to restrictions that are imposed on functions - the procedure during operation can change any cells on any sheet of the book. Yes, to start the procedure, you will need to use a button on the sheet, a hot key or a sheet event - but this will do the job.