Strange behavior of Excel when trying to insert array formula via VBA

[Tchesko]

Hello there,

I've been encountering some strange behavior of Excel when I try to insert an array formula using VBA.
I'll try to explain it with an example :

I have the following data in the cells A3:B7:

5 A
3 B
6 C
3 D
4 E

I also have an array formula that I use to perform dynamic data sorting. For instance, when I apply the formula to the above data, it yields the same lines but in decreasing order:

6 C
5 A
4 E
3 B
3 D

The array formula is the following:

=INDEX(INDEX($A$3:$B$7,,COLUMN($A$3:$B$7)-COLUMN(INDEX($A$3:$B$7,,1))+1),MATCH(LARGE($A$3:$A$7-ROW($A$3:$A$7)/10^10,ROW($A$3:$A$7)-ROW(INDEX($A$3:$A$7,1))+1),$A$3:$A$7-ROW($A$3:$A$7)/10^10,0))

It is initially intended for tables larger than the above example so some parts of the formula may seem superfluous in the present situation. However, it works fine when I enter it directly from the Excel sheet (via Ctrl+Shift+Enter).

Now the problem occurs when I try to automate the formula insertion by using the following code:

<code>Sub Test()
Range("H9:I13").FormulaArray = "=INDEX(INDEX(R3C1:R7C2,,COLUMN(R3C1:R7C2)-COLUMN(INDEX(R3C1:R7C2,,1))+1),MATCH(LARGE(R3C1:R7C1-ROW(R3C1:R7C1)/10^10,ROW(R3C1:R7C1)-ROW(INDEX(R3C1:R7C1,1))+1),R3C1:R7C1-ROW(R3C1:R7C1)/10^10,0))"
End Sub</code>

This is what I get as a result in H9:I13:

#REF! #REF!
5 5
#REF! #REF!
A A
#REF! #REF!

However, it suffices to select the range H9:I13, press F2 and then Ctrl+Shift+Enter (without changing anything in the formula) and... miracle: the correct result appears!

Could anyone explain why this happens and how to do it correctly?