1

hi i have a c# program which gives the excel2007 range, its formulaarray as follows

   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 = "=(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))))))";

it gives me exception saying that formula is wrong... but if open excel-2007 and in a new sheet (let's say sheet5) select the range A1:HM232 and paste the above formula directly to the formula bar, and then press the Ctrl+Shift+Enter together it does everything fine,... plz can you tell me how to do the same with the c#?

i am aware that if i use formulaarray to use the R1C1 style, but if i use the

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

it gives me no exceptions and it performs it as if i did Ctrl+Shift+Enter, both from c# and excel directly

for the above two formulas i did change the A1:HM232 to R1C1:R232C221 again shorter one works fine from c# but the longer one does not!

Improve this question

9 Answers 9

Reset to default
2

I had the added complication that the people I am developing for use Excel in Spanish.
To get formulas to work in non-English versions of Excel you have to use a different set of functions, i.e. FormulaLocal instead of Formula, FormulaR1C1Local intead of FormulaR1C1 etc.
A very simple example like =RC[-1]+R4C4 does work for FormulaArray, but something like =SUMA(SI(FC(-2)=1;F8C(-3):F1000C(-3);0)) which contains excel function names and semicolons as argument markers does not - you get a runtime error.
The workaround I found was to do something like =SUM(IF(AC9=1,AB$8:AB$1000,0)), i.e. translated to English and using local formulas, not R1C1...and that works.

It is surprising because
a) the published advice is to use R1C1 for Array Formulas and
b) you would normally expect a runtime error if you use English function names.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

It looks like you need to modify the cell references. From the FormulaArray documentation:

If you use this property to enter an array formula, the formula must use the R1C1 reference style, not the A1 reference style.

More info on R1C1 reference style:

Improve this answer

1 Comment

=ROUND((IF(Sheet4!A1:HM232=1,0,IF(Sheet4!A1:HM232=0,1,Sheet4!A1:HM232))),0) but this one works fine without any exceptions
1

I think you need to specify the first cell for the formula, even though you are applying the formula over the entire selection:

range_res("A1").FormulaArray = "=(IF((IF(Sheet4!A1:HM232=1,0,"+ etc
Improve this answer

2 Comments

int c# you cannot write range_res("A1")... the range is already specified ... which A1:HM232 of the sheet ws
My bad - I don't have access to C# right now, but this is how it works in VBA.
1

Try using a sumproduct instead. It has the advatage of being an "implicit" array formula.

myrange.formula = "=sumproduct((logicalExpr1) * (logicalExpr2) * (logicalExpr3))

It will also make your formula more readable.

Improve this answer

Comments

1

The issue is that Formula Array doesn't seem to work with R1C1 referencing because now we have an RC column and it confuses Excel as to which formula style to apply to the formulaArray let property

=Sum(RC2:RC3) is both RC format and a A1 reference format now.

And they haven't added a new let property like formulaArrayR1C1

Improve this answer

Comments

0

Just off the top of my head, did you mean to apply the formula to the entire range?

EDIT: Try just setting the Formula property instead of the FormulaArray property.

Improve this answer

1 Comment

then it works like cell by cell, but that's what i don't want... i want it to work like matrix by matrix addition subtraction etc
0

yep I agree with Robert J. so you have to convert one of the formulas first (preferably to A1). luckily, there is a function to do that but then you have to run the macro every time, instead of just entering in the cell using Ctrl-Shift-Enter.

Improve this answer

Comments

0

You can still enter formulas in the R1C1 format. Try it this way:

Private Sub Tryformula()
  Range("B2").FormulaR1C1 = "=RC[-1]+R4C4"
End Sub
Improve this answer

Comments

0

A bit older thread, however my experience with FormulaArray (using Excel 2010, German):

As Richard mentioned, complex formulas in A1 reference style also did not work for me. However for me the R1C1 reference style was not applicable as I prefer to work with named references in my worksheets. The following workaround worked for me:

Microsoft.Office.Interop.Excel.Worksheet sheet = Globals.ThisAddIn.Application.ActiveSheet;
Microsoft.Office.Interop.Excel.Range range = sheet.Cells[1, 1];
range.Value2 = "=IFERROR(MATCH(INDEX(ForeignTable[ForeignTableField1],MATCH(1,(ForeignTable[ForeignTableField1]=LocalField1)*(ForeignTable[ForeignTableField2]=LocalField2),0)),ForeignTable[ForeignTableField1],0),MATCH(1,(ForeignTable[ForeignTableField3]>=LocalField3)*1,0))"
string formulaArrayString = range.FormulaArray;
range.FormulaArray = formulaArrayString;

When I output the formulaArrayString it contains the localized (German) version of the formula. When I use the localized formula I do not get an error. However by using the temporary formulaArrayString string I am not limited to localized Excel versions and I can always use english formulas.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.