VBA Arrays And Worksheet Ranges

This page describes how to transfer data between VBA arrays and worksheet ranges.

 

Introduction

Data transfer between worksheet cells and VBA variables is an expensive operation  that should be kept to a minimum. You can considerably increase the performance of your Excel application by passing arrays of data to the worksheet, and vice versa, in a single operation rather than one cell at a time. If you need to do extensive calculations on data in VBA, you should transfer all the values from the worksheet to an array, do the calculations on the array, and then, possibly, write the array back to the worksheet. This keeps the number of times data is transferred between the worksheet and VBA to a minimum. It is far more efficient to transfer one array of 100 values to the worksheet than to transfer 100 items at a time.

This page shows you how to transfer data between worksheet cells and VBA ararys.  You will find that with large amounts of data being transferred between the  worksheet and the array, working with the array is much faster than working  directly with worksheet cells.

Reading A Worksheet Range To A VBA Array

It is very simple to read a range on a worksheet and put it into an array in VBA. For example,

Dim Arr() As Variant ' declare an unallocated array.
Arr = Range("A1:C5")' Arr is now an allocated array

When you bring in data from a worksheet to a VBA array, the array is always 2 dimensional. The first dimension is the rows and the second dimension is the columns. So, in the example above,Arr is implicitly sized as Arr(1 To 5, 1 To 3) where 5 is the number of rows and 3 is the number of columns. A 2 dimensional array is created even if the worksheet data is in a single row or a single column (e.g,Arr(1 To 10, 1 To 1)).  The array into which the worksheet data is loaded always has an lower bound  (LBound) equal to 1, regardless of whatOption Base directive you may have in your module. You cannot change this behavior. For example,

Dim Arr() As Variant
Arr = Range("A1:A10")

Here, Arr is dimensioned automatically by VBA asArr(1 to 10,  1 To 1).

You can use code like the following to loop through the array of the worksheet values:

Dim Arr() As Variant
Arr = Range("A1:B10")
Dim R As Long
Dim C As Long
For R = 1 To UBound(Arr, 1) ' First array dimension is rows.
    For C = 1 To UBound(Arr, 2) ' Second array dimension is columns.
        Debug.Print Arr(R, C)
    Next C
Next R

There is a special case when the range on the worksheet is a single cell. Expanding on the code above, you should use the code below if it is possible that the range is a single cell:

Dim Arr() As Variant
Dim RangeName As String
Dim R As Long
Dim C As Long
Dim RR As Range

RangeName = "TheRange"
Set RR = Range(RangeName)
If RR.Cells.Count = 1 Then
    ReDim Arr(1 To 1, 1 To 1)
    Arr(1, 1) = RR.Value
Else
    Arr = Range(RangeName)
End If

Writing A One Dimensional VBA Array To The Worksheet

Once you have calculated an array with the appropriate values, you  can write it back to the worksheet. The array may be 1 or 2 dimensional.

To write a one dimensional array back to the worksheet, you must create a Range object, resize that range to the size of your  array, and then write to the range.

Suppose we have a one dimensional array and want to write that out to the worksheet starting at cellK1. The code must first resize the destination range. For example,

' one row spanning several columns
Dim Destination As Range
Set Destination = Range("K1")
Set Destination = Destination.Resize(1, UBound(Arr))
Destination.Value = Arr

This code will write the values of Arr to range that is  one row tall by UBound(Arr) columns wide, starting at  rangeK1. If you want the results passed to a range that is one column wide spanning several rows, use code like the following to resize the range and set the values.

Dim Destination As Range
Set Destination = Range("K1")
Set Destination = Destination.Resize(UBound(Arr), 1)
Destination.Value = Application.Transpose(Arr)

NOTE that the parameters toResize are reversed and that the arrayArr is transposed before being written to the worksheet.

Writing A Two Dimensional VBA Array To The Worksheet

If you have a 2 dimensional array, you need to useResize to resize the destination range to the proper size. The first dimension is the number of rows and the second dimension is the number of columns. The code below illustrates writing an arrayArr out to the worksheet starting at cellK1.

Dim Destination As Range
Set Destination = Range("K1")
Destination.Resize(UBound(Arr, 1), UBound(Arr, 2)).Value = Arr

You can transpose the array when writing to the worksheet:

Set Destination = Range("K1")
Destination.Resize(UBound(Arr, 2), UBound(Arr, 1)).Value = Application.Transpose(Arr)

Here, the parameters to Resize are reversed and the array Arr is transposed.

Array Sizing

When you read from a worksheet to an array variable, VBA will automatically size the array to hold the range on the worksheet. You don't have to concern yourself with sizing the array. However, when writing an array from VBA to the worksheet, you must resize the destination range to hold the array. We saw this earlier in  the examples. Basically, you use code like the following. 

Dim NumRows As Long
Dim NumCols As Long
NumRows = UBound(Arr,1) - LBound(Arr,1) + 1
NumCols = UBound(Arr,2) - LBound(Arr,2) + 1
Set Destination = Range("K1").Resize(NumRows, NumCols).Value = Arr

If the array being passed to the worksheet is smaller than the Range to which it is written, the unused cells get a#N/A error. If the array being passed is larger than the range to which it is written, the array is truncated on the right or bottom to fit the range.

As you've seen in the examples, passing array between the worksheet and VBA is really quite simple. Used correctly, the code snippets above can have a strong effect on increasing the performance of your VBA application.