Hi,
I implemented a simple (Newton’s method) equation solver using the Macros plugin (Javascript) in a desktop workbook. After many trial/error iterations suspecting that the Api.RecalculateAllFormulas() method was not working as expected, I found out that my test code below is in fact working fine in a new workbook, until I close the workbook and open it again.
Before doing this, the expected (correct) behavior is that the Macro will iteratively modify the “variable” cell (B7) until the “image” cell (B15) becomes equal or very close to “target” cell (C15). The final variable value is then copied to “result” cell (D15). Attached snapshot shows that difference between “image” and “target” becomes null.
But after saving to file, closing the workbook and opening it again, the only thing the Macro will succeed to do is copying the value from “variable” cell to “result” cell. It actually behaves as this if the “image” cell would not change when the “variable” cell is modified (i.e. constant image value, instead of a function of the variable). This is why it made me believe that my Api.RecalculateAllFormulas() call was failing in the first place. And it may be the case, but only after closing and opening the workbook again.
What’s going on?
PS1: Attached you can find snapshots of before/after successfully running the macro. You also find the saved workbook file. EDIT: Sorry, can’t upload (new user).
PS2: Running ONLYOFFICE Desktop Editors version 7.5.1.23 (x64 msi) in Windows 11 Pro, 10.0.22631 Build 22631. French language, hence decimal separator is a coma (not a point).
PS3: The “solver” test code below.
Thanks for help,
hlipot
(function()
{
const cVARIABLE = "B7";
const cIMAGE = "B15";
const cTARGET = "C15";
const cRESULT = "D15";// + Api.GetSelection().GetRow();
var s = Api.GetActiveSheet();
function writeAsNumber(c, x) {
s.GetRange(c).SetValue( x.toString().replace(".", ",") );
Api.RecalculateAllFormulas();
}
function readAsNumber(c) {
return Number.parseFloat( s.GetRange(c).GetValue() );
}
var xini = readAsNumber(cVARIABLE);
if ( Number.isNaN(xini) ) writeAsNumber(cVARIABLE, 1.0);
const Target = readAsNumber(cTARGET);
function setAndReadResult(x) {
writeAsNumber(cVARIABLE, x);
Api.RecalculateAllFormulas();
var y = Target - readAsNumber(cIMAGE);
return y;
}
function writeAsItIs(c, x) {
s.GetRange(c).SetValue(x);
}
/*
var xa = readAsNumber("F5");
writeAsNumber("AA7", xa);
var dx = xa * 0.01;
writeAsNumber("AA8", dx);
var xb = xa + dx;
writeAsNumber("AA9", xb);
var ya = setAndReadResult(xa);
writeAsNumber("AA10", ya);
var yb = setAndReadResult(xb);
writeAsNumber("AA11", yb);
var a = (ya - yb)/(xa - xb);
writeAsNumber("AA12", a);
var b = ya - a * xa;
writeAsNumber("AA13", b);
if (a === 0) return;
writeAsNumber("AA14", (-b / a));
*/
function intersection(xa) {
var xb = xa + 0.1;
var ya = setAndReadResult(xa);
var yb = setAndReadResult(xb);
var a = (ya - yb)/(xa - xb);
var b = ya - a * xa;
if (a === 0) writeAsNumber(cVARIABLE, xa);
else writeAsNumber(cVARIABLE, (-b / a));
Api.RecalculateAllFormulas();
}
for (var i = 0; i < 10; i++) {
var y = readAsNumber(cIMAGE);
//if (Math.abs(y) <= 0.1) return;
intersection( readAsNumber(cVARIABLE) );
}
writeAsNumber(cRESULT, readAsNumber(cVARIABLE));
})();
