Using Arrays when Working with Excel Worksheets

[vba_corner]

Whenever you need to work with very many cells in Excel, check or manipulate cell values, it can considerably speed up your code to use arrays instead of working directly with the cells.

Here're a few examples to show how it works:

How to read cell values from a worksheet into an array

Sub ReadRange()
'declare the array as Variant!
Dim myArray() As Variant

'fill array with values from range
myArray() = Range("A1:C6").Value
'success message
MsgBox "Array with " & UBound(myArray, 1) & " rows and " & _
UBound(myArray, 2) & " columns filled."
End Sub
The array receiving the cell values must be declared with data type Variant and without presetting the size!
The row index and the column index of the resulting array always begin with 1, no matter if and how Option Base is set.
If you read from a range that doesn't start in cell A1, the row and column index of the array still begin with 1, they don't match the real row and column number of the according cells.

If your array for instance contains the cell values from range "C4:F7", then you can convert your array indexes to cell row/column numbers as follows:
myCellRow = Range("C4:F7").Rows(myArrayRow).Row
myCellColumn = Range("C4:F7").Columns(myArrayColumn).Column

How to write the array back to a worksheet

Sub WriteRange()
Dim myArray() As String
Dim myRow As Integer
Dim myColumn As Integer

'fill array with values
ReDim myArray(1 To 6, 1 To 3)
For myRow = 1 To 6
For myColumn = 1 To 3
myArray(myRow, myColumn) = myRow & "," & myColumn
Next
Next
'write array to worksheet
Range("A1:C6").Value = myArray()
End Sub
As you can see, the array containing the values doesn't have to be of data type Variant, it can also be another data type. But usually it is, because in most cases, you'll read the values from the worksheet first (and then it must be Variant) and write them back to the worksheet after the changes to the value are done.

Performance Test

Last, but not least, an example to show the differences in how long it takes to work with each cell individually as opposed to using an array. (This example uses 100.000 cells.)

'This program counts all cells in a range that have
'a four character long value and contain a "1"
'It does it four times, using four different approaches,
'and shows the time needed for each approach
Sub CountCells()
Dim myRange As Range 'range to work with
Dim myPattern As String 'pattern to check
Dim myLen As Integer 'length to check
Dim myTimer(1 To 4) As Single 'timers for each approach
Dim myCell As Range 'current cell in loop
Dim myRow As Integer 'row index
Dim myColumn As Integer 'column index
Dim myValue As Variant 'current value
Dim myValues() As Variant 'array of cell values
Dim myCount As Long 'counter for cells that match
'the condition

Set myRange = Range("A1:CV1000")
myPattern = "*1*"
myLen = 4

''''first approach: work with cells directly
''''using For Each loop
myCount = 0
'get time
myTimer(1) = Timer
'evaluate each cell
For Each myCell In myRange
With myCell
If (.Value Like myPattern) And (Len(.Value) = myLen) Then
myCount = myCount + 1
End If
End With
Next
'get time
myTimer(1) = Timer - myTimer(1)

''''second approach: work with cells directly
''''with loop over cell indexes
myCount = 0
'get time
myTimer(2) = Timer
'evaluate each cell
For myRow = 1 To myRange.Rows.Count
For myColumn = 1 To myRange.Columns.Count
With Cells(myRow, myColumn)
If (.Value Like myPattern) And (Len(.Value) = myLen) Then
myCount = myCount + 1
End If
End With
Next
Next
'get time
myTimer(2) = Timer - myTimer(2)

''''third approach: work with array
''''using For Each loop
myCount = 0
'get time
myTimer(3) = Timer
'fill array
myValues() = myRange.Value
'evaluate each value
For Each myValue In myValues
If (myValue Like myPattern) And (Len(myValue) = myLen) Then
myCount = myCount + 1
End If
Next
'get time
myTimer(3) = Timer - myTimer(3)

''''fourth variant: work with array
''''with loop over indexes
myCount = 0
'get time
myTimer(4) = Timer
'fill array
myValues() = myRange.Value
'evaluate each value
For myRow = 1 To UBound(myValues, 1)
For myColumn = 1 To UBound(myValues, 2)
If (myValues(myRow, myColumn) Like myPattern) And _
(Len(myValues(myRow, myColumn)) = myLen) Then
myCount = myCount + 1
End If
Next
Next
'get time
myTimer(4) = Timer - myTimer(4)

'show result
MsgBox "Working with cells directly" & vbCr & _
"For Each loop: " & Format(myTimer(1), "0.00") & vbCr & _
"Index loop: " & Format(myTimer(2), "0.00") & vbCr & _
vbCr & "Working with array" & vbCr & _
"For Each loop: " & Format(myTimer(3), "0.00") & vbCr & _
"Index loop: " & Format(myTimer(4), "0.00")

End Sub

For detailed info about working with arrays look here.