How to use arrays in Excel VBA

By using an array as a variable you can avoid declaring a lot of variables. Instead you collect them in one place, your array, and then you can look them up, when you need to read or write a value.

Every element in an array has an index number, and you can find the element by using the index number as a reference. You can also loop through arrays.

An important thing to remember is that (at least in older versions of Excel) the first element has 0 (zero) as its index number, and in that case element number 99 is in fact element number 100. If you find that confusing, you can declare otherwise by writing the following at the top of the VBA module:

Then 1 is standard instead of zero, but you can also do it, when you declare your array (see later) and that is recommended.

There are two types of arrays: The static and the dynamic. With a static array you know beforehand, how many elements it can contain, and then it is locked, while a dynamic array can be made smaller or bigger at runtime.

I'll get back to dynamic arrays later, and at the end of this page I explain the terms UBound and LBound. I will also show, how you can copy an array very fast to a range in the spreadsheet.

You can copy the examples by highlighting them with the mouse, copy by pressing CTRL+C and paste into a VBA module by pressing CTRL+V.

The static array

Where you say that it contains 100 elements of the data type Integer. Or (recommended):

Where you specify that the first element has index number one. This is good practice, because you avoid errors if you reuse the code later. You can also write:

This could be convenient if the array contains the years 2000 to 2010. Here is a small example of the use of a static array.

Sub ArrayTest()
'Our array with 100 String elements.
Dim MyArray(1 To 100) As String
Dim iCount As Integer 'Counter

'We fill the array with Strings
For iCount = 1 To 100
   MyArray(iCount) = "Number " & iCount + 1
Next

'Element nb. 100 has the text "Number 101",
because we added 1 to iCounts, when we made the string.
MsgBox MyArray(100)

'Now we write the array's elements to column
'A in the active sheet.
For iCount = 1 To 100
   Range("A1").Offset(iCount - 1, 0).Value = MyArray(iCount)
Next

End Sub

If you want an array for different data types, declare it as a Variant knowing full well that it will use more memory than "leaner" data types.

Arrays with more dimensions

Well, if it was just a one dimensional array (table) I needed, I would usually prefer using a Collection, because I find collections simpler and easier to use.

But unlike collections arrays can have more dimensions, and that is a smart thing! Here is a simple example of a two dimensional array:

Sub TwoDimensionalArray()
'We declare the array. It is just like 10 rows and 2 columns.
Dim MyArray(1 To 10, 1 To 2) As Integer
Dim iCount As Integer 'Counter

'We fill the array
For iCount = 1 To 10
   MyArray(iCount, 1) = iCount
   MyArray(iCount, 2) = iCount + 1
Next

'We loop through the array and write the values
'to column A and B in the active sheet.
'The values 1 to 10 will be in column A,
'and in column B it will be 2 to 11.
For iCount = 1 To 10
   Range("A1").Offset(iCount - 1, 0).Value = MyArray(iCount, 1)
   Range("B1").Offset(iCount - 1, 0).Value = MyArray(iCount, 2)
Next

End Sub

The procedure above could also be names and telephone numbers, and if you also wanted addresses, you could declare "1 To 3" instead of "1 To 2". With addresses in the third "column" you would have to declare the array as a Variant and not Integer.

It could be done as below, but before you copy the macro into a module and run it, you should write 10 names in cell A1 to A10, 10 numbers ("telephone numbers") in B1 to B10 and 10 somethings in cell C1 to C10.

Sub TwoDimArray()
'The array is declared as a Variant, so it can contain
'different data types like e.g. numbers and tekst.
Dim MyArray(1 To 10, 1 To 3) As Variant
Dim rRange As Range
Dim iCount As Integer
Dim iCount2 As Integer

'We set our range = cell A1:A10
Set rRange = Range("A1:A10")

'Values are read from cell A1:A10 and to the right.
For iCount = 1 To 10
   With rRange.Item(iCount)
      MyArray(iCount, 1) = .Value
      MyArray(iCount, 2) = .Offset(0, 1).Value
      MyArray(iCount, 3) = .Offset(0, 2).Value
   End With
Next

'We now change our range to A12:A22, before we write.
Set rRange = Range("A12:A22")

'We now write the values back to the spreadsheet, but in reversed order.
For iCount = 10 To 1 Step -1
   iCount2 = iCount2 + 1
   With rRange.Item(iCount2)
      .Value = MyArray(iCount, 1)
      .Offset(0, 1).Value = MyArray(iCount, 2)
      .Offset(0, 2).Value = MyArray(iCount, 3)
   End With
Next

Set rRange = Nothing
End Sub

You can add more dimensions to an array, much like Chinese boxes. A three-dimensional array can be visualised as a cube, but with more than three dimensions it becomes difficult to visualise - it will be like fractals.

Imagine the first elements as "tree trunks". Every trunk can have branches. Each branch has etc. etc.

Dynamic arrays

If you don't know how many elements you need in the array, you can declare a dynamic array and then change the size in runtime (add or remove).

When you get to the point, where you need to put values into your array, you can resize it with the command "ReDim". For instance:

You can redimension a dynamic array anytime with the ReDim command, but if you don't use the word "Preserve", any existing content will be erased.

Say for instance that you have an array with 100 elements, and you want to increase it to 200 and preserve the content, you write:

LBound and UBound

When you work with dynamic arrays, you may not know the number of elements - the array may have been rezised in runtime by one or more variables.

In that situation (e.g. when looping through the array) you can use LBound to find the "low boundary" and UBound to find the upper.

If for instance you have an array that has been dimensioned runtime from 1 to 99, just like:

UBound(DynArray, 1)
UBound(DynArray, 2)
LBound(DynArray, 1)
LBound(DynArray, 2)

Fast copying of an array to a range

In some of the examples above I have shown, how you can copy the contents of an array to a range in the spreadsheet by looping through the array and filling the cells one by one.

That works fine with small arrays, but if they get big, it is a slow method, and there is a much smarter and much faster way - provided it is a two dimensional array like a table.

See below how to do it. The point is to define a range and an array with the same dimensions - i.e. number of rows and columns. Once the array has been filled, it is fast as lightning to copy it to the range by writing:

As simple as that! Here is an example doing just that. Because a range with more columns is two dimensional by nature, our array must also be two dimensional.

Sub ArrayToRange()
Dim MyArray() As Integer
Dim rRange As Range
Dim iNumber As Integer
Dim iCount As Integer
Dim iCount2 As Integer

'We set our range to cell A1 to N30
Set rRange = Range("A1:N30")

'The array is dimensioned to the same size
'as rRange, that is 30 rows and
'14 columns in this case.
'If it was the other way round, we would have
'to find LBound and UBound of the array and
'then dimension the range accordingly.
With rRange
   ReDim MyArray(1 To .Rows.Count, 1 To .Columns.Count)
End With

'Switches screen updating off for speed
Application.ScreenUpdating = False

'Fill the array
iNumber = 0
For iCount = 1 To rRange.Rows.Count
   For iCount2 = 1 To rRange.Columns.Count
      MyArray(iCount, iCount2) = iNumber + 1
      iNumber = iNumber + 1
   Next
Next

'Copy MyArray to rRange
rRange.Value = MyArray

BeforeExit:
Set rRange = Nothing
'Switches screen updating back on
Application.ScreenUpdating = True

Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

You can find more information (and more advanced) about arrays at Pearson or Microsoft.

Arrays in Excel VBA

The static array

Arrays with more dimensions

Dynamic arrays

LBound and UBound

Fast copying of an array to a range