Array formulas are those entered in Excel with Ctrl + Shift + Enter.
With Excel API, when I read an array formula (Range.formulas
property), I get something like [["{=SUM(IF(A1:A10>50,1))}"]]
.
When I try to write this formula back to the worksheet, it enters a constant string (because of the brackets). If I delete the brackets and enter the formula, it will be entered as a normal formula, not as an array.
How can I enter an array formula?
In VBA, when you read an array formula, it returns the formula without the brackets. When you want to enter an array formula, you write range.FormulaArray = =SUM(IF(A1:A10>50,1))
(again, without the brackets).
It would be good to keep both plataforms (JS and VBA) consistent and also provide properties like Range.FormulaArray
, Range.HasArray
and Range.CurrentArray
.
=countif(a1:a10, ">50")
– user4039065