Search code examples
c#arraysexcelexcel-formulaexcel-addins

Getting Excel add ins to modify array formula parameters; or perform 'ctrl-shift-enter'

I am trying to make a C# Excel add in change the parameters of an array formula in-place; i.e. do the same as a user modifying an array formula and hitting ctrl-shift-enter.

Setting the activeCell.FormulaArray property does not achieve this; it throws a 'You cannot change part of an array' error.

Does anyone know how I can achieve this?

A solution that also works in VBA would be brilliant.

I've tried creating some logic that 'walks' to the perimeter of the array formula and deletes it first, but it doesn't account for adjacent array formulas and I believe this is unnecessarily drastic.

Solution

  • Sounds like you are looking for the CurrentArray property

    In VBA this will enter a multicell array formula into all the cells of the array formula one cell of which is Z99 

    Range("Z99").CurrentArray.FormulaArray=" ... "