Arrays and range formulas in Excel VBA
On the page Arrays and ranges I described how you can get considerably faster code (for some tasks) if you copy a range to an array, manipulate the data in the array and then paste the array as a table in one swift operation.
A range an be copied to an array like this:
rMyArray() = rMyRange.Value
However this method only copies the values, not the formulas. What do you do if you need the formulas, because they must be inserted somewhere else?  You write:
rMyArray() = rMyRange.Formula
I used this method the other day, when I had to make a new table from a lot of data, and I needed to keep the original formulas and cell references.
First of all arrays are fast, when you need to manipulate data, and compared to other ways of copying you don't need to worry about keeping the original cell references.
You know, if for instance you copy B1 to D2, and B1 has the formula "=A1", then D2 will get the formula "=C2", unless the original formula used dollar signs: "=$A$1".
The following piece of code is rather useless, but it shows the difference between ".Value" and ".Formula". You can highlight the code using the mouse and copy it to a VBA module (CTRL+C and paste: CTRL+V).
If you view this page on a small device, some of the code lines may break/wrap, but they will be OK, when you paste into a VBA module.
The example requires values (anything) in the range A1:A10, and B1:B10 must use the formula "=A1", "=A2" etc.
First we copy B1:B10 to an array using ".Value". The values are then copied in reverse order to a new array, which is inserted in range E2:E11. Then we repeat the exercise using ".Formula" and insert into G2:G11.
You will see that the cells in column E contain values only, and that the cells in column G have the original formulas.
Sub FormulasToArray()
Dim vInArray()
Dim vOutArray()
Dim rMyRange As Range
Dim lCount As Long
Dim lRow As Long
On Error GoTo ErrorHandle
Set rMyRange = Range("B1:B10")
vInArray() = rMyRange.Value
ReDim vOutArray(1 To UBound(vInArray), 1 To 1)
For lCount = UBound(vInArray) To 1 Step 1
lRow = lRow + 1
vOutArray(lRow, 1) = vInArray(lCount, 1)
Next
vInArray() = rMyRange.Formula
Set rMyRange = Range("E2:E11")
rMyRange.Value = vOutArray()
lRow = 0
For lCount = UBound(vInArray) To 1 Step 1
lRow = lRow + 1
vOutArray(lRow, 1) = vInArray(lCount, 1)
Next
Set rMyRange = Range("G2:G11")
rMyRange.Value = vOutArray
Range("E1").Value = "Values"
Range("G1").Value = "Formulas"
BeforeExit:
Set rMyRange = Nothing
Erase vInArray
Erase vOutArray
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub
For smaller tasks this method can be overkill, but it is really smart and fast if you need to handpick in or manipulate big sets of data to make a new table that must have the original formulas and cell references.
Related:
