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?

 

[Herakles]

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

View attachment 101081

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?

Try this:

Sub Test()
Dim dummyArr(0 To 1, 1 To 1) As String
Dim i As Integer

    dummyArr(0, 1) = "=1+1"
    
    dummyArr(1, 1) = "=1+1"

    For i = LBound(dummyArr) To UBound(dummyArr)
        Worksheets("Sheet2").Range("A" & i + 1).Formula2 = dummyArr(i, 1)
    Next i
        
End Sub

 

[luker]

Try this:

Sub Test()
Dim dummyArr(0 To 1, 1 To 1) As String
Dim i As Integer

    dummyArr(0, 1) = "=1+1"
   
    dummyArr(1, 1) = "=1+1"

    For i = LBound(dummyArr) To UBound(dummyArr)
        Worksheets("Sheet2").Range("A" & i + 1).Formula2 = dummyArr(i, 1)
    Next i
       
End Sub

I had this initially, but for my actual use case, it was doing 1000's of formulas, and doing the write call from VBA 1000's of times is significantly slower than calling it once and assigning an array as i am doing.

 

[Herakles]

I had this initially, but for my actual use case, it was doing 1000's of formulas, and doing the write call from VBA 1000's of times is significantly slower than calling it once and assigning an array as i am doing.

Give us some realistic information to go on.

What formulas, surely not just "=1+1", are you trying to apply, how do you assign them to the array and do they reference values in any of the cells.

 

[Alex Blakenburg]

In the first test sub in post #1, just change your arr data type to variant.

Sub Test_OP()
    Dim dummyArr(0 To 1, 1 To 1) As Variant
    dummyArr(0, 1) = "=1+1"
    dummyArr(1, 1) = "=1+1"

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

 

[luker]

In the first test sub in post #1, just change your arr data type to variant.

Sub Test_OP()
    Dim dummyArr(0 To 1, 1 To 1) As Variant
    dummyArr(0, 1) = "=1+1"
    dummyArr(1, 1) = "=1+1"

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

How peculiar, I wonder why this works? If I dim a variable as string and assign it to a cell, I dont get the issue, only when assigning the array as type string

 

[luker]

Give us some realistic information to go on.

What formulas, surely not just "=1+1", are you trying to apply, how do you assign them to the array and do they reference values in any of the cells.

Obviously my workload is slightly more complex, but given it breaks for this simple example, im pretty sure its not my formulas that im constructing. I only used 1+1 as an example to better highlight the issue I saw

 

[Alex Blakenburg]

If you define your array as a string when it writes it out to Excel it will treat it as string, which is exactly what you are experiencing.

just places the formulas in the cells as text

 

[RobP]

maybe this could help you:

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 = Evaluate(dummyArr)
    
End Sub

 

[RoryA]

maybe this could help you:

That won't put a formula into the cells, and it will only evaluate the first formula in the array, not all of them individually.