Macro not accessing updated cell value

Plugins
1

Hello everyone!

I am trying to write a macro to implement a basic goal seek to solve this issue. Despite many years of VB macros and some experience with js, I am struggling with even the most basic things and would need some help. I have boiled down my current issue to the fact that the macro doesn’t seem to “see” updated cell values while it runs. It actually looks like the macro runs entirely, then updates the sheet.

For example, if I have a sheet with an input value in A1 and an output in A2 which is set as a formula as being =10xA1. if I run this simple code for instance :

(function()
{
    var oWorksheet = Api.GetActiveSheet();
    //var ActiveCell = oWorksheet.ActiveCell;

    console.log("starting macro...");
    
    var input = Number(oWorksheet.GetRange("A1").GetValue());
    console.log("original input:"+input);

    var output = Number(oWorksheet.GetRange("A2").GetValue());
    console.log("original output:"+output);
    
    let n = 0;
    while (n < 3) {
        n++;
        input = input + 1;
        console.log("-----");
        //update input
        oWorksheet.GetRange("A1").SetValue(input);
        console.log("input:"+input);
        console.log("A1 input:"+oWorksheet.GetRange("A1").GetValue());   	

        Api.asc_calculate(Asc.c_oAscCalculateType.All);
        //read updated value
        output = Number(oWorksheet.GetRange("A2").GetValue());
        console.log("A2 output:"+output);
    	
    }
    Api.Save();
    console.log("A2 output (after save):"+oWorksheet.GetRange("A2").GetValue());
    
})();

I get this:

starting macro...
VM379:11 original input:53
VM379:14 original output:530
VM379:20 -----
VM379:23 input:54
VM379:24 A1 input:54
VM379:29 A2 output:530
VM379:20 -----
VM379:23 input:55
VM379:24 A1 input:55
VM379:29 A2 output:530
VM379:20 -----
VM379:23 input:56
VM379:24 A1 input:56
VM379:29 A2 output:530
VM379:33 -----
VM379:34 A2 output (after save):530

This shows that the original output is 530 and while the macro updates the cell value in A1, it doesn’t “see” that the output value in A2 has changed based on the formula. It always stays at 530 as if A2 wasn’t being recalcuated until the macro is completed and the sheet then calculates the new value…

I have added the asc_calculate piece as I thought that perhaps the macro doesn’t recalculate values on each iteration by default but it makes no difference. the other interesting thing is that I can see the values being updated in A1 and A2 before the spreadsheet is saved. However, the output of the A2 value after save it still the old one.

Am I doing something wrong? Ideally I need to run the while loop on the output value and stop iterating once it has reached a defined target but right now, the output doesn’t change so the loop runs forever…

version: ONLYOFFICE Desktop Editors version 7.2.1.36 (flatpak package)
OS: Ubuntu 22.10

On a side note, oWorksheet.GetRangeByNumber starting at 0,0, for A1 is pretty mind boggling. I get that lists normally start at 0 but this is pretty counter intuituve in my view, especially as it is described as GetRangeByNumber(nRow,nCol)…

2

Hello @Batwam

You can use attachEvent method in combination with onWorksheetChange event to make macro that is capable of tracking the cell data changes.

3

Hello and thanks for your response. I had a look at these function but I don’t believe that this is what I am looking for. Presumably these are used to trigger custom action when a user updates certain cells. In my case, I don’t need to trigger a custom action, I just need the sheet to do its normal job and re-calculate the results in A2 when A1 is updated.

I don’t really understand why it even requires any change in the code. In Excel, it would re-calculate by default every formula in the spreadsheet everytime a cell is modified and I would have to specifically disable Autocalculation to prevent this. I get that this isn’t VBA but is there even a reason for the value in A2 not to update the result as the macro iterates? I can even see the value update before the files saves but GetRange("A2").GetValue() continues to return the old value…

if it doesn’t do it by default, is there a way to trigger recalculation of the entire spreadsheet with a command I would include in the code after I update the input?

4

Apologies for the misunderstanding.
To trigger calculation of all formulas on the current workbook you can use RecalculateAllFormulas method.

It seems that the desired result can be achieved like that:

(function()
{
    var oWorksheet = Api.GetActiveSheet();
    //var ActiveCell = oWorksheet.ActiveCell;

    console.log("starting macro...");
    
    var input = Number(oWorksheet.GetRange("A1").GetValue());
    console.log("original input:"+input);

    var output = Number(oWorksheet.GetRange("A2").GetValue());
    console.log("original output:"+output);
    
    let n = 1;
    while (n < 3) {
        n++;
        input = input + 1;
        console.log("-----");
        //update input
        oWorksheet.GetRange("A1").SetValue(input);
        console.log("input:"+input);
        console.log("A1 input:"+oWorksheet.GetRange("A1").GetValue());   	

        Api.RecalculateAllFormulas();
        //read updated value
        output = Number(oWorksheet.GetRange("A2").GetValue());
        console.log("A2 output:"+output);
    	
    }
    console.log("A2 output (after save):"+oWorksheet.GetRange("A2").GetValue());
    
})();
5

This is exactly what I was looking for, merci Constantine!

Now I can progress to the next step

1 Like
6

Feel free to contact us if you face any other difficulties.