C# .NET - c# excel range.FormulaArray - Asked By Salom Kalai on 26-Jun-09 12:32 PM

hi, can anyone tell me why this does not work

 Excel.Worksheet ws_res = (Excel.Worksheet)
                  wb
.Worksheets.Add(mis, mis, mis, mis);
 
Excel.Range range_res = (Excel.Range)ws_res.get_Range("A1","HM232");
 range_res
.FormulaArray =
           
"=ROUND((IF((IF(Sheet4!A1:HM232=1,0,"+
           
"IF(Sheet4!A1:HM232=0,1,Sheet4!A1:HM232)))=1,0,"+
           
"IF((IF(Sheet4!A1:HM232=1,0,"+
           
"IF(Sheet4!A1:HM232=0,1,Sheet4!A1:HM232)))=0,1,("+
           
"IF(Sheet4!A1:HM232=1,0,"+
           
"IF(Sheet4!A1:HM232=0,1,Sheet4!A1:HM232)))))),0)";

but this does

   Excel.Worksheet ws_res = (Excel.Worksheet)
                  wb
.Worksheets.Add(mis, mis, mis, mis);
 
Excel.Range range_res = (Excel.Range)ws_res.get_Range("A1","HM232");
  range_res
.FormulaArray =
 
"=ROUND((IF(Sheet4!A1:HM232=1,0,IF(Sheet4!A1:HM232=0,1,Sheet4!A1:HM232))),0)";

=======================================================

but if i copy and paste the above two formulas to the formula bar of excel-2007 and press Ctrl+Shift+Enter, both work perfectly!

=======================================================

i have also changed in the above two formulas A1:HM232 to R1C1:R232C221, again from c# the shorter one works fine, but the longer one still generates the exception!

The exception says: COMException was unhandled by user code System.Runtime.InteropServices.COMException was unhandled by user code HelpLink="C:\Program Files\Microsoft Office\Office12\1033\XLMAIN11.CHM" Message="The formula you typed contains an error. Try one of the following: \n\n• Make sure you've included all parentheses and required arguments.\n• To get assistance with using a function, click Function Wizard on the Formulas tab (in the Function Library group).\n• If you include a reference to another sheet or workbook, verify that the reference is correct.\n• If you are not trying to enter a formula, avoid using an equal sign (=) or minus sign (-), or precede it with a single quotation mark ( ' ).\n• For more information about common formula problems, click Help." Source="Microsoft Office Excel" ErrorCode=-2146827284 StackTrace: Server stack trace: Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at Microsoft.Office.Interop.Excel.Range.set_FormulaArray(Object ) at ExcelAddIn1.BO.button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\abcdef\My Documents\Visual Studio 2008\Projects\ExcelAddIn1\ExcelAddIn1\BO.cs:line 47 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) InnerException:

It seems to be due to a character limit - Rolf Jaeger replied to Salom Kalai on 29-Jun-09 10:27 PM

I have taken a closer look at your problem and have concluded that it might be related to an undocumented character limit. When I reduced your formula to:

"=ROUND((IF((IF(Sheet4!A1:H12=1,0,"

+ "IF(Sheet4!A1:H2=0,1,Sheet4!A1:H2)))=1,0,"

+ "IF((IF(Sheet4!A1:H2=1,0," + "IF(Sheet4!A1:H2=0,1,A1:H2)))=0,1,("

+ "IF(A1:H2=1,0,"

+ "IF(A1:H2=0,1,A1:H2)))))),0)"

it still worked (notice that I eliminated a few Sheet4! strings and HM232 to H2; so it's clearly no longer the formula you intended). The length of this formula is 170 characters. Now if I add even one more character (e.g. extend one of the ranges to A2:H12) the error reoccurs. Strange? YES!

Sorry I can't help any further. Maybe you should report this to Microsoft.

Best wishes,
Rolf