ms office - Evaluate a sub-formula of a formula -

after selecting cell having formula, select part of formula in excel formula bar , calculate (by pressing f9). need reproduce evaluation of sub-formulas javascript api.

for example, let's assume values of cell a1, b1, c1 respectively 1, 2, 3, , cell d1 contains formula =a1+b1+c1. able evaluate sub-formula such a1+b1, b1+c1, , result 3 , 5.

in vba, under manual calculation mode, store initial formula of d1 in variable, , assign sub-formula (eg, =a1+b1) d1 result 3, , restore initial formula d1 nothing happened; evaluation not raise calculation of other cells descendants of d1 (thanks manual mode).

however, javascript api, re-calculation works under automatic mode. if assign sub-formula (eg, =a1+b1) d1, cells descendants of d1 re-calculated ctx.sync, may costly.

so there way or workaround optimise that?

one possible workaround find isolated cell in workbook no cell depends on (eg, cell outside usedrange of worksheet, still need make sure no cell depends on it, because of nature of usedrange...), , assign sub-formula cell , value. disadvantages of approach

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

2) user-defined function (if programmed badly) may rely on dimension of worksheet or position of cell holding it. in case, evaluating user-defined function in isolated cell may lead different result (or side effects) evaluation in original cell.

could help?

i think first approach list easiest. namely, reead formula of cell , save javascript variable (string). next modify formula in place, issue load on value, , revert formula save string. true disadvantage of approach you're messing someone's formula, means if goes wrong between step 1 , step 2, leave document in dirty state. and, say, may have downstream impacts (perf-wise) on calculations.

alternatively, if know formulas there, use "worksheet functions" feature. example, here you're calculating sum of 20 + sum of 2 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 "cleanest" way calculate in doesn't impact worksheet state, , can chain 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 won't work udfs (and array formulas, matter), , sort of need know a-priori sort of formula cell consists of. if you're talking calculating sub-formula of formula, assume have information on formula within, latter may not problem.