In this article I will explain some of the main concepts of working with arrays. Using arrays is a great way of working with lists or groups of variables.

Jump To:

• Arrays, Static Vs Dynamic
• Declaring Static Arrays
  • 1 Dimensional Static Arrays
  • 2 Dimensional Static Arrays
  • Common Errors When Declaring Static Arrays
  • Declaring Dynamic Arrays
  • Dynamic Arrays, ReDim Statement
• Working With Arrays
  • Assigning and Reading Values to and From the Array
  • Getting the Array Size
  • Getting the Array Size for a 2 Dimensional Array

Arrays, Static Vs Dynamic:

There are 2 main types of arrays you can use in VBA, Static and Dynamic. Static arrays have a fixed size. you will not be able to change the size of the array later in your code. On the other hand the size of a dynamic array can change. The downside towards using dynamic arrays is the extra resource consumption when resizing the array.

Declaring Static Arrays:

1 Dimensional Static Arrays:

The code below declares 1 dimensional static array of the Variant data type. Note that the indices start from 1 and go up to 10:

'Indices:1, 2, 3, 4, 5, 6, 7, 8, 9, 10
Dim arrVariant(1 To 10) As Variant


The code below declares 1 dimensional static array of the Integer data type. Note that the indices start from 0 and go up to 4:

'Indices: 0, 1, 2, 3, 4
Dim arrInteger(0 To 4) As Integer


Without using the Start To End syntax the starting index will be “0”. For example the code below declares a 1 dimensional static array of the double data type starting from the index 0 and going up to 7:

'Indices: 0, 1, 2, 3, 4, 5, 6, 7
Dim arrDouble(7) As Double

You could also use constants in the array declaration. For example the previous declaration could also have been written in the following format:

Const Value0 As Integer = 0
Const Value1 As Integer = 1
Const Value4 As Integer = 4
Const value7 As Integer = 7
Const value10 As Integer = 10


'Indices: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
Dim arrVariant(Value1 To value10) As Variant

‘Indices: 0, 1, 2, 3, 4
Dim arrInteger(Value0 To Value4) As Integer

'Indices: 0, 1, 2, 3, 4, 5, 6, 7
Dim arrDouble(value7) As Double


2 Dimensional Static Arrays:

The following codes will declare 2 dimensional static arrays:

'10X3 2 dimensional static array of Variant data type
Dim arrVariant(1 To 10, 1 To 3) As Variant
'5X2 Zero based index,  2 dimensional static array of integer data type
Dim arrInteger(0 To 4, 0 To 1) As Integer
'8X3 Zero based index, 2 dimensional static array of double data type
Dim arrDouble(7, 2) As Double


Also similar to the 1 dimensional case you can declare the using constant variables:

Const Value1 As Integer = 1
Const Value3 As Integer = 3
Const Value10 As Integer = 10

'declaring array size using constant values
Dim arrVariant(Value1 To Value10, Value1 To Value3) As Variant


Common Errors When Declaring Static Arrays:

One of the common errors when declaring static arrays is using non constant variables to declare the array size. The following code will generate a Compile Error, Constant Expression Required error:

Dim Value0 As Integer
Dim Value1 As Integer
Dim Value4 As Integer
Dim value7 As Integer
Dim value10 As Integer

Value0 = 0
Value1 = 1
Value4 = 4
value7 = 7
value10 = 10

'Indices: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
Dim arrVariant(Value1 To value10) As Variant
'Indices: 0, 1, 2, 3, 4
Dim arrInteger(Value0 To Value4) As Integer
'Indices: 0, 1, 2, 3, 4, 5, 6, 7
Dim arrDouble(value7) As Double


Declaring Dynamic Arrays:

Whether you are declaring a 1 dimensional dynamic array or a 2 dimensional dynamic array the declaration part is the same. The code below declares dynamic arrays:

'dynamic array of variant data type
Dim arrVariant() As Variant
'dynamic array of integer data type
Dim arrInteger() As Integer
'dynamic array of double data type
Dim arrDouble() As Double 

In order to assign a size to the dynamic array you would have to use the ReDim statement.

Dynamic Arrays, the ReDim and ReDim Preserve Statements:

Unlike static arrays where the size of the array is set in the declaration, dynamic arrays aren’t assigned a size in their declaration. Instead a ReDim statement is used later in the code to set their size. Choosing the size of the dynamic array using the ReDim statement is similar to the static array case. The code below creates dynamic arrays and assigns a size to them:

Dim arrVariant() As Variant
'a 1 dimensional dynamic array with the indices: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
ReDim arrVariant(1 To 10) As Variant

Dim arrInteger() As Integer
'a 1 dimensional dynamic array with the indices: 0, 1, 2, 3, 4
ReDim arrInteger(4)

Dim arrDouble() As Double
'8X3 Zero based index, 2 dimensional dynamic array
ReDim arrDouble(7, 2)

Const Value1 As Integer = 1
Const Value3 As Integer = 3
Const Value10 As Integer = 10
Dim arrString() As String
'10X3 2 dimensional dynamic array
ReDim arrVariant(Value1 To Value10, Value1 To Value3)


Note that upon using the ReDim statement all the previous data will be lost. By using the ReDim Preserve statement, the array will be resized, but the previous data will be preserved. The code below creates a dynamic array. Assigns values to it, resizes it and prints the results in column A:

Sub Example1()
Dim arrInteger() As Integer
Dim i As Integer

ReDim arrInteger(1 To 10)
'assign values to the array indices
For i = 1 To 10
    arrInteger(i) = i
Next i
'resize the array
ReDim Preserve arrInteger(1 To 12)
'print the data to column A
For i = 1 To UBound(arrInteger)
    Cells(i, 1) = arrInteger(i)
Next i
End Sub


The result:

Had we not used the Redim Preserve statement, the results would be:

Sub Example2()
Dim arrInteger() As Integer
Dim i As Integer

ReDim arrInteger(1 To 10)
'assign values to the array indices
For i = 1 To 10
    arrInteger(i) = i
Next i
'resize the array without using the preserve statment
ReDim arrInteger(1 To 12)
'print the data to column A
For i = 1 To UBound(arrInteger)
    Cells(i, 1) = arrInteger(i)
Next i
End Sub


Had we decided to resize the array to something smaller than its original size while using the ReDim Preserve statement, the extra rows in the original array would have been lost:

Sub Example3()
Dim arrInteger() As Integer
Dim i As Integer

ReDim arrInteger(1 To 10)
'assign values to the array indices
For i = 1 To 10
    arrInteger(i) = i
Next i
'resize the array to something smaller
ReDim Preserve arrInteger(1 To 5)
'print the data to column A
For i = 1 To UBound(arrInteger)
    Cells(i, 1) = arrInteger(i)
Next i
End Sub


The result:

Working With Arrays:

Assigning and Reading Values to and From the Array:

The code below creates a 1 dimensional static array and assigns the values “1”, “2”, “3”, …., “10” to it:

Sub Example4()
Dim i As Integer
Dim arrInteger(1 To 10) As Integer

For i = 1 To 10
    arrInteger(i) = i
Next i
End Sub


The code below creates a zero based, 1 dimensional dynamic array, assigns the values “Data1”, “Data2”, “Data3”, …. “Data10” to it, then prints the data to column A:

Sub Example5()
Dim i As Integer
Dim arrInteger() As String

ReDim arrInteger(9)
For i = 1 To 10
    arrInteger(i - 1) = "Data" + Strings.Trim(Str(i))
Next i
'prints the array data to column A
For i = 1 To 10
    Cells(i, 1) = arrInteger(i - 1)
Next i
End Sub


Results:

The code below creates a 1 dimensional dynamic array, reads the data from column A of the previous example. Then a 10X10 2 dimensional static array is populated with that data. In the end the results are printed on the same sheet. For more information about reading and writing data to and from arrays and sheets please see Excel VBA, Arrays and Ranges:

Sub Example6()
Dim i As Integer
Dim j As Integer
'the 1D dynamic array
Dim arrColumn() As Variant
'the 2D static array. Note that the rows have the indices: 1, 2, 3, ...., 10.
'but the columns have the indices: 0, 1, 2,...., 9
Dim arrOutput(1 To 10, 9) As String

'reads the data from the cells
arrColumn = Range("A1:A10")

For i = 1 To 10
    For j = 0 To 9
        arrOutput(i, j) = arrColumn(i, 1)
    Next j
Next i
'prints the data back to the sheet
Range(Cells(1, 1), Cells(10, 10)) = arrOutput

End Sub


Result:

Getting the Array Size:

To get the size of the array you could use the UBound() function. UBound returns the highest index of the array.  The result of using the UBound() function on the array below js “10”:

Sub Example7()
Dim arrInteger(1 To 10) As Integer
Dim intSize As Integer

intSize = UBound(arrInteger)
MsgBox (intSize)
End Sub


The result of using UBound() on the array below would be 9:

Sub Example8()
'zero based array. Indices= 0,1, 2, 3, 4, 5, 6, 7, 8, 9
Dim arrInteger(9) As Integer
Dim intSize As Integer

intSize = UBound(arrInteger)
MsgBox (intSize)
End Sub


You can also use this function to check if an array is empty as long as you use some error handling.

Getting the Array Size for a 2 Dimensional Array:

The highest row index of a 2 dimensional array can be acquired similar to a 1 dimensional array. The code below will return the value 4 which is the highest row index for the 2 dimensional array:

Sub Example9()
Dim arrInteger(1 To 4, 1 To 3) As Integer
Dim intSize As Integer

intSize = UBound(arrInteger)
MsgBox (intSize)
End Sub


In order to get the highest column index the code below can be used. The result will be 3, which is the highest column index:

Sub Example10()
Dim arrInteger(1 To 4, 1 To 3) As Integer
Dim intSize As Integer

intSize = UBound(arrInteger, 2)
MsgBox (intSize)
End Sub 

See Also:

•  Excel VBA, Arrays and Ranges
• Error: Can’t Assign to Array
• ReDim Multidimensional Arrays

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website www.software-solutions-online.com