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

Question

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.

Answer 1

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=" ... "

Answer 2

Hello Tony Wilson,

Description: I am experiencing the issue that you original posted. I have an Add-In where the Add-In queries the data and spits out the results into Excel. Basically, when I manually do it in Excel, I have to open up the Add-In interface (for the first time to get the formula), put in my parameters (tagname, starttime and endtime), I click OK, it spits out all of the data for me based on the tagname,starttime and endtime. Basically, my results consist of two columns (datetime and value).

I can modify the array formula within Excel manually, by selecting all of the results (basically all rows and columns that the array formula returns) , hit F2 or clicking on the Formula Bar, then the editing of the array formula appears. I can change the name of the tagname, starttime and endtime and hit CTRL+SHIFT+ENTER at the same time and it updates the results for me. What I also found out is that, let say the original array formula returns 10rows and two columns of data, I have to select all ten rows and 2 columns of data in order to modify the array formula. I can't just select 5 rows and 2 columns. Ought! But I guess it has to be like that.

Question: Now, that I have the array formula that I like to use/automate in my C# application, so anytime the user selects a different tagname,starttime and endtime, my application will modify the array formula tagname, starttime and endtime in the array formula and return the results from the new tagname, starttime and endtime into a new Excel worksheet.

Could you please send me your code how you solve this issue? Do you need to use SendKeys.Send("^+{ENTER}") (= CTRL+SHIFT+ENTER) in order tell Excel to return the results. I know that you pasted the code snippet above but it didn't really give me a clear direction of where to go and how to modify the array formula. If you could post code to to retrieve the formula from excel,to modifying and to generate the results, that would be greatly appreciated.

This is really urgent, your post is the only post that is exactly what I need other ones are just about excel and nothing to do with C# application.

Best Regards,

HS2