Evaluate a sub-formula of a formula

Question

After selecting a cell having a formula, we could select a part of its formula in Excel formula bar and calculate it (by pressing F9). I need to reproduce this evaluation of sub-formulas by JavaScript API.

For example, let's assume the values of Cell A1, B1, C1 are respectively 1, 2, 3, and Cell D1 contains a formula =A1+B1+C1. I would like to be able to evaluate quickly a sub-formula such as A1+B1, B1+C1, and get the result 3 and 5.

In VBA, under manual calculation mode, we could store the initial formula of D1 in a variable, and then assign a sub-formula (eg, =A1+B1) to D1 to get the result 3, and then restore back the initial formula to D1 like nothing happened; this evaluation does not raise calculation of any other cells that are descendants of D1 (thanks to the manual mode).

However, with JavaScript API, the re-calculation only works under automatic mode. If we assign a sub-formula (eg, =A1+B1) to D1, all the cells that are descendants of D1 are re-calculated by ctx.sync, which may be costly.

So is there a way or a workaround to optimise that?

One possible workaround is to find an isolated cell in the workbook that no cell depends on (eg, a cell outside usedRange of a worksheet, but we still need to make sure no cell depends on it, because of the nature of usedRange...), and then assign a sub-formula to that cell and get the value. The disadvantages of this approach are

1) it's still a hack, and modifies the area of a worksheet.

2) a User-Defined Function (if programmed badly) may rely on the dimension of a worksheet or the position of the cell holding it. In this case, evaluating a user-defined function in an isolated cell may lead to different result (or side effects) from the evaluation in the original cell.

Could anyone help?

Answer 1

I think the first approach that you list is probably the easiest. Namely, reead the formula of the cell and save it as a javascript variable (string). Next modify the formula in place, issue a load on the value, and then revert the formula back to the save string. The only true disadvantage of this approach is that you're messing with someone's formula, which means that if something goes wrong between step 1 and step 2, you leave the document in a dirty state. And, as you say, it may have downstream impacts (perf-wise) on calculations.

Alternatively, if you actually know what formulas are there, you could use the "worksheet functions" feature. For example, here you're calculating the sum of 20 + the sum of two worksheet ranges.

Excel.run(function (ctx) {
    var result = ctx.workbook.functions.sum(
        20,
        ctx.workbook.worksheets.getItem("Sheet1").getRange("F3:F5"),
        ctx.workbook.worksheets.getItem("Sheet1").getRange("H3:H5")
    ).load();

    return ctx.sync();
}).catch(function(e) {
    console.log(e);
});

This is the "cleanest" way to calculate in that it doesn't impact the worksheet state, and you can even chain the calculations:

Excel.run(function (ctx) {
    var range = ctx.workbook.worksheets.getItem("Sheet1").getRange("E2:H5");

    var sumOfTwoLookups = ctx.workbook.functions.sum(
        ctx.workbook.functions.vlookup("Wrench", range, 2, false),
        ctx.workbook.functions.vlookup("Wrench", range, 3, false)
        );
    sumOfTwoLookups.load();

    return ctx.sync();
}).catch(function(e) {
    console.log(e);
});

The downside is that this won't work with UDFs (and with array formulas, for that matter), and that you sort of need to know a-priori what sort of formula the cell consists of. But if you're talking about calculating a sub-formula of the formula, I assume you already have some information on the formula within, so the latter may not be a problem.