vba - Writing into Range.FormulaArray gives me a #NAME! error

Question

Nothing really fancy here, altough I need to split the formula in 2 because it is longer than 255 characters (sources: http://www.dicks-blog.com/archives/2005/01/10/entering-long-array-formulas-in-vba/ http://support.microsoft.com/kb/213181).

    f1 = "=SI(1=1,SOMME(SI(mySheet!$R:$R = ""something"",SI(mySheet!$AQ:$AQ = AM$1, NBCAR(Incidents!$AP:$AP)-NBCAR(SUBSTITUE(mySheet!$AP:$AP,$B2,"""")))))/NBCAR($B2)-X_X_X())"
    f2 = "SOMME(SI(mySheet!$R:$R = """",SI(mySheet!$AQ:$AQ = AM$1, SI(mySheet!$AM:$AM = $A2,SI(DROITE(mySheet!$AP:$AP,6) = $B2,1))))),"""""
    .Range("AM2").FormulaArray = f1
    .Range("AM2").Replace "X_X_X()", f2

The formulas are in French, which shouldn't really matter. The formula is correctly returned in the cell AM2, so here is the result:

={SOMME(SI(1=1;SI(mySheet!$R:$R = "something";SI(mySheet!$AQ:$AQ = AM$1; NBCAR(mySheet!$AP:$AP)-NBCAR(SUBSTITUE(mySheet!$AP:$AP;$B2;"")))))/NBCAR($B2)-SOMME(SI(mySheet!$R:$R = "something";SI(mySheet!$AQ:$AQ = AM$1; SI(mySheet!$AM:$AM = $A2;SI(DROITE(mySheet!$AP:$AP;6) = $B2;1)))));"")}

However, I get a #NAME! error. If I look into the formula debug, the error comes from my first function, Excel isn't able to interpret SI() (which is the French for IF()). I tried completely removing that IF() clause but then Excel can't interpret the first function which is now SOMME() (SUM()).

If I just click into the formula bar and press CTRL+Shift+Enter (not changing anything), it returns the correct value.

Why doesn't Excel give me the right number after VBA sets the array formula? Why does it work if I just resubmit the formula in Excel? And why is it always having trouble with the first function? I guess there is some other kind of limitation that I am not aware of.

I found another similar issue while browsing the Web but no solution.

Answer 1

Because Excel has .Formula for formulas in English and .FormulaLocal for formulas in the language of the Excel interface.

You need the local version, but there isn't one for arrays. You have to provide array formulas in English.

You could e.g. set this French formula to .FormulaLocal of a temporary (hidden) range, then read back .Formula from the same range and set that as .FormulaArray on the desired range. But you shouldn't, because your code won't work on any Excel version other than French. The most portable thing is to always use English formulas in your code.