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

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!

## Solutions/Answers:

### 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:**