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?

Answers


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)'

Need Your Help

Can .NET load and parse a properties file equivalent to Java Properties class?

c# configuration file-io load

Is there an easy way in C# to read a properties file that has each property on a separate line followed by an equals sign and the value, such as the following:

JAMA Matrix performance

java performance matrix profiler jama

First of all, sorry for my bad English, but I need your help.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.