An
array is a series of variables referred to by the same name. You differentiate each element of the array by a unique index number, but all the elements must be of the same data type. Arrays help make coding more efficient. It's easy to loop through each element of an array, performing some process on each element. Arrays have a lower bound, which is zero by default, and an upper bound, and array elements must be contiguous.
The scope of an array can be Public, Module, or Local. As with other variables, this depends on where the array is declared and whether the Public, Private, or Dim keyword is used.
When declaring a
fixed array , you give VBA the upper bound and the type of data that it will contain. The following code creates an array that holds six string variables:
Dim astrNames(5) As String
Fixed means that you cannot alter this array's size at runtime. The following code gives an example of how you can loop through the array:
Sub FixedArray() 'Declare an array of six elements Dim astrNames(5) As String Dim intCounter As Integer 'Populate the first four elements of the array astrNames(0) = "Dan" astrNames(1) = "Alexis" astrNames(2) = "Brendan" astrNames(3) = "Zachary" 'Use a For...Next loop to loop through the 'elements of the array For intCounter = 0 To UBound(astrNames) Debug.Print astrNames(intCounter) Next intCounter End Sub
This code starts by storing values into the first four elements of a six-element array. It then loops through each element of the array, printing the contents. Notice that the For...Next loop starts at zero and goes until the upper bound of the array, which is (5). Because the array is made up of strings, the last two elements of the array contain zero-length strings. If the array was composed of integers, the last two elements would contain zeros.
Another way to traverse the array is to use the For Each...Next construct. Your code would look like this:
Sub ArrayWith() 'Declare an array of six elements Dim astrNames(5) As String Dim intCounter As Integer Dim vntAny As Variant 'Populate the first four elements of the array astrNames(0) = "Dan" astrNames(1) = "Alexis" astrNames(2) = "Brendan" astrNames(3) = "Zachary" 'Use a For...Each loop to loop through the 'elements of the array For Each vntAny In astrNames Debug.Print vntAny Next vntAny End Sub
This code declares a Variant variable called vntAny. Instead of using a loop with Ubound as the upper delimiter to traverse the array, the example uses the For Each...Next construct.
NOTE
Many people do not like the fact that, by default, the elements of an array are zero-based. Fortunately, the VBA language allows you to declare both the lower bound and the upper bound of any array. The syntax looks like this:
Dir astrNames(1 to 6)
Often, you don't know how many elements your array needs to contain. In this case, you should consider declaring a
dynamic array , which you can resize at runtime. Using this type of array can make your code more efficient because VBA pre-allocates memory for all elements of a fixed array, regardless of whether you store data in each of the elements. However, if you aren't sure how many elements your array will contain, pre-allocating a huge amount of memory can be quite inefficient.
To create a dynamic array, you declare it without assigning an upper bound. You do this by omitting the number between the parentheses when declaring the array, as shown in this example:
Sub DynamicArray() 'Declare a dynamic array Dim astrNames() As String Dim intCounter As Integer Dim vntAny As Variant 'Resize the array to hold two elements ReDim astrNames(1) 'Populate the two elements astrNames(0) = "Dan" astrNames(1) = "Alexis" 'Use a For...Each loop to loop through the 'elements of the array For Each vntAny In astrNames Debug.Print vntAny Next vntAny End Sub
However, there's a potential problem when you try to resize the array:
Sub ResizeDynamic() 'Declare a dynamic array Dim astrNames() As String Dim intCounter As Integer Dim vntAny As Variant 'Resize the array to hold two elements ReDim astrNames(1) 'Populate the two elements astrNames(0) = "Dan" astrNames(1) = "Alexis" 'Use a For...Each loop to loop through the 'elements of the array For Each vntAny In astrNames Debug.Print vntAny Next vntAny End Sub Sub ResizeDynamic() 'Declare a dynamic array Dim astrNames() As String Dim intCounter As Integer Dim vntAny As Variant 'Resize the array to hold two elements ReDim astrNames(1) 'Populate the two elements astrNames(0) = "Dan" astrNames(1) = "Alexis" 'Resize the array to hold four elements ReDim astrNames(3) 'Populate the last two elements astrNames(2) = "Brendan" astrNames(3) = "Zachary" 'Use a For..Each loop to loop through the 'elements of the array For Each vntAny In astrNames Debug.Print vntAny Next vntAny End Sub
You might expect that all four elements will contain data. Instead, the ReDim statement re-initializes all the elements, and only elements 2 and 3 contain values. You can avoid this problem by using the Preserve keyword. The following code behaves quite differently:
Sub ResizePreserve() 'Declare a dynamic array Dim astrNames() As String Dim intCounter As Integer Dim vntAny As Variant 'Resize the array to hold two elements ReDim astrNames(1) 'Populate the two elements astrNames(0) = "Dan" astrNames(1) = "Alexis" 'Resize the array to hold four elements ReDim Preserve astrNames(3) 'Populate the last two elements astrNames(2) = "Brendan" astrNames(3) = "Zachary" 'Use a For...Each loop to loop through the 'elements of the array For Each vntAny In astrNames Debug.Print vntAny Next vntAny End Sub
In this example, all values already stored in the array are preserved. The Preserve keyword brings its own difficulties, though. It can temporarily require huge volumes of memory because, during the ReDim process, VBA creates a copy of the original array. All the values from the original array are copied to a new array. The original array is removed from memory when the process is complete. The Preserve keyword can cause problems if you're dealing with very large arrays in a limited memory situation.
TIP
Each type of array complements the other's drawbacks. As a VBA developer, you have the flexibility of choosing the right type of array for each situation. Fixed arrays are the way to go when the number of elements doesn't vary widely. Dynamic arrays should be used when the number varies widely, and you're sure you have enough memory to resize even the largest possible arrays.
Many people are unaware that you can pass an array as a parameter to a function or subroutine. The following code provides an example:
Sub PassArray() 'Declare a six-element array Dim astrNames(5) As String Dim intCounter As Integer 'Call the FillNames function, passing a reference 'to the array Call FillNames(astrNames) 'Use a For...Next loop to loop through the 'elements of the array For intCounter = 0 To UBound(astrNames) Debug.Print astrNames(intCounter) Next intCounter End Sub
The code begins by declaring a fixed array called astrNames. The code calls the FillNames routine. It receives the array as a parameter and then populates all its elements. The PassArray routine is then able to loop through all the elements of the array that was passed, displaying information from each element. The FillNames routine looks like this:
Sub FillNames(varNameList As Variant) 'Populate the elements of the array varNameList(0) = "Alison" varNameList(1) = "Dan" varNameList(2) = "Alexis" varNameList(3) = "Brendan" varNameList(4) = "Zachary" varNameList(5) = "Sonia" End Sub
Notice that the routine receives the array as a variant variable. It then populates each element of the array.