---

VBA provides plenty of methods for traversing Excel workbooks using Cell and Range objects. It's relatively straightforward to write code that cycles through a worksheet to process cells. However, when you're dealing with very large worksheets with thousands of rows and columns, then you soon see that while the code is easy to write, the performance can slow to a crawl. And it's not just VBA that starts to slow things down - large worksheets that are heavily studded with array formulas and look-ups can also be painfully slow to perform.

The good news is that VBA provides the tools and objects to massively speed things up. The key to this is to use variant arrays to grab all the data from a woksheet or range and then to work on this array. The performance improvements can be dramatic - something that takes hours can be done in minutes.

The starting point is simple - we grab the values from a range and assign them to a variant array:

Sub test()
Dim vData() As Variant

    vData = ActiveSheet.UsedRange.Value
    Debug.Print "Rows: " & UBound(vData, 1) & " Columns: " & UBound(vData, 2)

End Sub

The important thing to note is that the variant array is two-dimensional, with the first dimension mapping to rows and the second to columns. So, to extend the code so that we sum every cell in the range E2:M36 of the active sheet, we could write code as follows:

Sub test2()
Dim vData() As Variant
Dim r As Integer
Dim c As Integer
Dim rTotal As Variant

    vData = ActiveSheet.Range("E2:M36").Value
    
    rTotal = 0
    For r = 1 To UBound(vData, 1)
        For c = 1 To UBound(vData, 2)
            rTotal = rTotal + vData(r, c)
        Next
    Next
    
    Debug.Print rTotal
    
End Sub

As you can see traversing the range is very simple. But there are some caveats to keep in mind. Firstly, we are only accessing the data values in this code. Other attributes of the cells, font information, comment text etc, is dropped. If you need to operate on cells based on font colour, content of comments, style or other attribute then using a variant array doesn't gain you much.

Another thing to keep in mind is that having all the data in an array doesn't mean that you have to do all the heavy lifting in code. So, in the above example the code cycles through every cell to get to calculate the sum. An alternative is to use a worksheet function directly on the array:

Sub test3()
Dim vData() As Variant

    vData = ActiveSheet.Range("E2:M36").Value
    Debug.Print Application.WorksheetFunction.Sum(vData)
    
End Sub

And it's just not for reading data that the variant array comes in useful. You can also use it to write data back to a worksheet. In the following example, the code reads in data from a range, doubles it and then writes the variant array of values back to the worksheet:

Sub test4()
Dim vData() As Variant
Dim r As Integer
Dim c As Integer


    vData = ActiveSheet.Range("A1:C4").Value
    Debug.Print "Start " & Application.WorksheetFunction.Sum(vData)

    For r = 1 To UBound(vData, 1)
        For c = 1 To UBound(vData, 2)
            vData(r, c) = vData(r, c) * 2
        Next
    Next
    
    Debug.Print "End " & Application.WorksheetFunction.Sum(vData)
    
    ActiveSheet.Range("A1:C4").Value = vData
    
End Sub

So, if you want to speed up the processing of your VBA code on data intensive worksheets, then the use of variant arrays is definitely your starting point.

Return to home page