Formula being stored as text, when assigned from an array in VBA

[luker]

I've written the following sub, but when i run it, it doesn't populate A1 and A2 with 2 as one would expect, but instead just places the formulas in the cells as text

Sub Test()
    Dim dummyArr(0 To 1, 1 To 1) As String
    dummyArr(0, 1) = "=1+1"
    dummyArr(1, 1) = "=1+1"

    Worksheets("Sheet1").Range("A1:A2").Formula = dummyArr
End Sub

I've checked all my cell formatting options, calculation mode (and tried recalculating at the end of the subprocedure) but none of this seems to work.

I found this solution on stack overflow which seems to work Evaluate text string as formula in VBA, which seems to be the only thing that works:

Sub Test()
    Dim dummyArr(0 To 1, 1 To 1) As String
    dummyArr(0, 1) = "=1+1"
    dummyArr(1, 1) = "=1+1"

    Worksheets("Sheet1").Range("A1:A2").Value= dummyArr
    Worksheets("Sheet1").Range("A1:A2").Formula = Worksheets("Sheet1").Range("A1:A2").Value
End Sub

Does anyone know what exactly is going on here, and what exactly is causing Excel to have this behaviour?

 

[RobP]

Well @RoryA , to be fair I did say "maybe"

correct on the first point - but it does get a result that I suppose he looks for somehow, on the second point I was not aware - I'm now off to change the second formula to prove your theory .. (which I'm sure is correct of course, I just hadn't seen it in my test of his data)

thanks
Rob

edit: Yep, does what you said it would