Macro not working after closing workbook and opening it again

Plugins
, ,
1

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));

})();
2

Hello @hlipot
I have increased your account level, please try it again.
Also you can place these files to any external storage and provide us with a download link.

3

Hi,

Here are the three files:

  • Example test sheet (before running the macro):

1_before_running_macro_1

  • Example test sheet (after running the macro):

2_after_running_macro_1

  • Saved workbook (if you manually change the variable to 1 or any other non-root value, the macro won’t be able to work out the root value anymore - it will just end up copying the initial value into the result cell, as explained before):

workbook.xlsx (11.6 KB)

Thanks.
hlipot

4

Hi,

Just to make it clear, here is how to reproduce the problem, step by step.

  1. For successful operation, use the above workbook file as a template: open it, create a new workbook (but do not save it to disk yet), copy/paste the template sheet into the new workbook, then copy/paste the macro into the new workbook. Now your new workbook is an unsaved copy of my template workbook.

  2. In the new (unsaved) workbook, manually change the “variable” cell to a random number. You can see the “image” column cells update their values accordingly. Notice the light grey column on the right, telling you the difference between “image” and “target” on each line (target cell C9 can be set to any constant).

  3. Please first clear the D15 cell. Now run the macro. It should successfully adjust the “variable” cell B7 (yellow) so that “image” value on line 15 precisely matches that of “target” value, and copy the final “variable” value to “result” cell on line 15. You can see that the difference is now zero or very close to zero (cell E15).

  4. You can clear D15 cell again, modify the “variable” cell again, and run the macro again, and it will adjust variable cell correctly again so that image B15 matches target. The image formula (e.g. cell B9) can be modified also and the solver will work (as long as at least one value exists that allows your formula to match “target”).

  5. If you just save the workbook to file, without closing it, it will continue operating fine.

  6. But if you close the workbook and open it again, all it will be able to do is to copy your initial “variable” value B7 to “result” cell without adjusting it to the root value that matches image to target. It now behaves as if “image” formula was a constant (i.e. did not depend on “variable” cell)!

Hope this helps,
hlipot

5

Hello @hlipot
Thank you for the detailed description!
However, it seems that I ran it incorrectly. Could you please point me if there’re any mistakes in my test scenario?

I used the latest version of Desktop Editor (v.8.0.1), and I see that that B,C,E columns has changed when I rewrote ‘variable’ and ran the Macro.

6

Hi,

Thanks for testing and giving feedback so quick.

The test you recorded does exhibit the problem. As a matter of fact, the macro is able to find the “root” when you run it in the new sheet. But after you close the workbook and open it again, it fails.

The way I can see it is that the “difference” cell (E15) is not going back to zero as a result of running the macro (like it actually does before the closing/opening cycle). The value copied by the macro to the “result” cell D15 is not the root value, but instead the initial value you set by hand. Root value should have been computed and copied instead of your manually entered value.

What makes it difficult to see it in your test case is the fact that you choose an initial variable value too close and difficult to distinguish from the root value. Please set the B7 “variable” value to 1 or 5 for example.

When performing as expected, the algorithm will replace this value with the root value, and the “difference” cell (E15) will become 0.

PS1: I can see from your test that version 8 doesn’t seem to fix this problem.

PS2: On my desktop (with version 7.5), running the macro in a copy of the sheet within the original “template” workbook is not enough to get it working (as you seemingly could do). I really need to create a new workbook. I will use version 8 from now on to catch up with your configuration.

hlipot

8

Thank you for the clarification! I think I got it now. We are checking the described scenario, I will update this thread once we have something to share.

9

Great, thanks.

My understanding of the problem from a source code point of view is that after closing/opening the workbook, the “Api.RecalculateAllFormulas()” call silently fails for some reason.

As a result, the algorithm in my macro works as if the “image” cell B15 was a constant, because it always returns the same (initial) value. In this “no root!” situation, the algorithm eventually copies the initial value of “variable” cell B7 to “result” cell D15.

Just my 2 cents
hlipot