Denne side på dansk Home > Excel VBA > Arrays and range formulas

RSS

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()
'Shows the difference between copying values
'and formulas from a range to an array.
Dim vInArray()          'Input array
Dim vOutArray()         'Output array
Dim rMyRange As Range   'Range variable
Dim lCount As Long      'Counter
Dim lRow As Long        'Counter

On Error GoTo ErrorHandle

Set rMyRange = Range("B1:B10")

'Copy the range to the array. The array
'will automatically get the same dimensions
'as the range (rows and columns).
'Arrays made this way will always have
'1 as base. This means that you point to
'the first row with vInArray(1,X)
'and not vInArray(0,X)
vInArray() = rMyRange.Value

'Redimension the other array
ReDim vOutArray(1 To UBound(vInArray), 1 To 1)

'Loop the first array backwards and add the
'values to the second array in reversed order.
For lCount = UBound(vInArray) To 1 Step -1
   lRow = lRow + 1
   vOutArray(lRow, 1) = vInArray(lCount, 1)
Next

'Copy the range once more, but this time with formulas:
vInArray() = rMyRange.Formula

'Redefine rMyRange to E2:E11
Set rMyRange = Range("E2:E11")

'Insert the values in the new range
rMyRange.Value = vOutArray()

'Reset the counter lRow
lRow = 0

'Copy the formulas to vOutArray in reversed order:
For lCount = UBound(vInArray) To 1 Step -1
   lRow = lRow + 1
   vOutArray(lRow, 1) = vInArray(lCount, 1)
Next

'Redefine rMyRange to G2:G11
Set rMyRange = Range("G2:G11")

'Insert the array
rMyRange.Value = vOutArray

'And now just two headlines:
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: