Trouble computing Excel array formulas programmatically
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?
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.
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)'