VBA Corner (vba_corner) wrote,
VBA Corner
vba_corner

Using Arrays when Working with Excel Worksheets

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.

Tags: array, arrays, cells, excel, performance, range, timer, vba
Subscribe
  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

  • 0 comments