Trouble computing Excel array formulas programmatically

Question

I am writing values in an excel sheet using a MATLAB program. I am also writing values in cells using formulas (for e.g. the MATLAB program writes =AVERAGE(A1:A10) to a cell and this gets converted to appropriate value (i.e. when I open the sheet, I don't see the above formula text, rather the value).

However I am having trouble writing array formulas (ones with curly braces around). Usually a user enters them by pressing Ctrl+Shift+Enter combination, curly braces appear and appropriate value is computed. However when I write these formulas enclosed in curly braces from MATLAB program the value is not computed, I simply see the formula text (with curly braces around it).
It seems I am not able to simulate Ctrl+Shift+Enter effect by simply writing the array formula to a cell.

Is there any solution to this?

danielpiestrak danielpiestrak · Accepted Answer · 2012-09-21T19:27:49

Excel auto-converts values that start with = into .formulas. To save an array formula, you need to write directly to the FormulaArray property of the range.

More Details:

If you're using a normal formula then you have two options in VBA. You can set the cell's value or formula properties like this:

Range("A3").value = "=Sum(A1:A2)"
Range("A3").Formula= "=Sum(A1:A2)"

When using array formulas, you cant use either of these approaches, you need to store it into a different area of the cell:

Range("A3").FormulaArray = "=Sum(A1:A2)"

When the user pushed Ctrl+Shift+Enter they are telling excel to send the .value of the cell into the .FormulaArray section of the cell.

Otherwise by default when excel sees the = sign i assigns the .value into the .formula

Anyways, this is all relative the the excel object model even if it's in VBA.

Now that I've gone into that detail I believe that matlab's xlswrite function only writes to the value of cells, and cant write to this sub-property, I may be wrong.

Something liek this may work in MATLAB (untested)

mlrange='A3'; % or similar

Excel = actxserver ('Excel.Application');
Excel.Workbooks.Open(filename);
TargetSheet = get(Excel.sheets,'item','A');
TargetSheet.Activate
ran = Excel.Activesheet.get('Range',mlrange);
ran.FormulaArray = '=Sum(A1:A2)'

Trouble computing Excel array formulas programmatically

1 Answers