1

(* As Excel JavaScript APIs evolve fast, I would like to re-ask a critical question. *)

How could we evaluate a formula in JavaScript API as Application.Evaluate does in VBA?

If there is no dedicated API, what are the workarounds today?

PS: previous workarounds were, for instance,

  • by using named ranges as described here, where we need to create a named range for the formula. We may also need to adjust the formula (e.g., convert range references to absolute references with the name of the active worksheet). Additionally, although adding named ranges on Excel Online by JavaScript API is permitted, loading their value by JavaScript API returns #BLOCKED!.
  • write the formula to a cell in the workbook, let Excel evaluate that, and read the value. The disadvantage is that borrowing a cell from the workbook is not neat.
  • worksheet functions as described here. But I guess worksheet functions don't work with dynamic arrays yet?
SoftTimur
  • 5,630
  • 38
  • 140
  • 292

1 Answers1

0

Thank you for reporting! Sorry that there is no dedicated API yet.

As for workaround, just as you have said, you could use 'named item' to evaluate. Adding named item though is not permitted on Excel online from UI, it's permitted on Excel online using Javascript API.

You might want to consider going to Ideas Forum to request a new feature.

Please let me know if you have any other questions. Thank you very much!

dingjin_ms
  • 16