Strange behavior when assigning a VBA array to formulas of an excel range

Question

This is strange... Someone has some knowledge or can figure out why is this happening?

In the past, I was able to assign an array of formulas to an Excel range (Example of fast assignment: Range("A1:A1000") = ArrayOfFormulas ). It was working fine and FAST (very FAST) compared to assign cell by cell (Example cell by cell: Range("A" & i).Formula=ArrayOfFormula(i) inside a loop. Is slow!).

I'm working with Excel 2013 now, and when trying to assign the formulas stored in an array of strings to an excel range it doesn't work (The excel shows the formulation but not the calculation as per left part of the picture below) an unfortunately I cannot retrieve old code to compare. For example the following code shows "=1+2" instead of "3" in the range A1 to A1000.

Sub AssignFormulas_1()
    Dim i as Long
    Dim FORML_ARRAY(1 To 1000, 1 To 1) As String
    For i = 1 To 1000
         FORML_ARRAY(i, 1) = "=1+2"
    Next i
    Range("A1:A1000").Formula = FORML_ARRAY  '<- Don't work as formula
                                             '   It put the value!
End Sub

However, when I don't use the array of formulas it work fine (all cells shows "3" instead of "=1+2" as per the right part of the picture below). Here the code:

Sub AssignFormulas_2()
    Dim i as Long
    Dim FORML_SINGLE As String
    FORML_SINGLE = "=1+2"
    Range("A1:A1000").Formula = FORML_SINGLE '<- works ok, not practical for my
                                             '   real life case as I need 
                                             '   different formula for each cell.
End Sub

The following code work also well (In this case I assign formulas cell by cell, so is more flexible but sloooowly for large formulas).

Sub AssignFormulas_3()
    Dim i as Long
    Dim FORML_ARRAY(1 To 1000, 1 To 1) As String
    For i = 1 To 1000
         FORML_ARRAY(i, 1) = "=1+2"
         Range("A1:A" & i).Formula = FORML_ARRAY(i, 1) '<- works ok, but slowly
    Next i
End Sub

The pictures with the output of all above code:

---

Note: The cells were properly formatted as number, not text!

Answer 1

your first code should work.
Instead of declaring it as string, declare it as variant.

So change your declaration from this:

Dim FORML_ARRAY(1 To 1000, 1 To 1) As String

to this:

Dim FORML_ARRAY(1 To 1000, 1 To 1) As Variant

I have no way of trying it on Excel 2013, but it should work.
Hope it does.

Answer 2

Interesting question. I've never used this functionality before.

Your first one has the same result for me in both 2010 and 2013 - a column containing the string "=1+2". I changed FORML_ARRAY from a string to a Variant and it works as expected. For good measure I changed the formula to include i and it still fills the range with formulas:

Sub AssignFormulas_1()
    Dim i As Long
    Dim FORML_ARRAY(1 To 1000, 1 To 1) As Variant 'changed to Variant
    For i = 1 To 1000
         FORML_ARRAY(i, 1) = "=" & i & "+2" 'changed 1 to i - still works
    Next i
    Range("A1:A1000").Formula = FORML_ARRAY
End Sub

Answer 3

Just to add another option, cause as you said you would rather dim your array correctly, With this you can, it only adds 1/250th of a second to the OVERALL Sub (not per loop), but will allow you to dim your array as a string.

Sub AssignFormulas_2()
    Dim i As Long

    Dim FORML_ARRAY(1 To 1000, 1 To 1) As String

    For i = 1 To 1000
         FORML_ARRAY(i, 1) = "= " & i & " + 2"
    Next i

    Range("A1:A1000").Formula = FORML_ARRAY  '<- Don't work as formula
                                             '   It put the value!

    Range("A1:A1000").Value = Range("A1:A1000").Value
End Sub

Now, I am not sure why excel reads the strings as constants, But if you run your original formula and go to Evaluate Formula, it gives the error that the cell contains a constant, This really just seems to be a bug, but If i figure something else out I will edit my answer also.

NOTE: This answer is not intended to be a BETTER answer as the accepted answer, it is a slower answer and is more of an alternative; to answer the request that you wanted to dim the array a string.

UPDATE: This method will split the difference between the accepted answer and the above method. This will only add 1/500th of a second to the sub's run time.

Sub AssignFormulas_Transpose()
    Dim i As Long

    Dim FORML_ARRAY(1 To 1000) As String
    For i = 1 To 1000
         FORML_ARRAY(i) = "= " & i & " + 2"
    Next i

    Range("A1:A1000").Formula = Application.Transpose(FORML_ARRAY)  '<- Don't work as formula
                                                                    '   It put the value!
End Sub