ARRAYS

Excel Visual Basic arrays are structures which are used to stored several related variables, of the same type. Each of the entries in the array can be accessed by an index number.

For example, if you had 20 members of a team and you wanted to store all the names for use in your VBA code. You could declare 20 variables to hold the team member names, as follows:
Dim Team_Member1 As String
Dim Team_Member2 As String
.
.
.
Dim Team_Member3 As String

Alternatively, you could use the much simpler and more organised method of storing the Team members in an array of 20 String variables :
Dim Team_Members(1 To 20) As String

Following the array declaration, each entry of the array is accessed by indexing as follows:
Team_Members(1) = "John Smith"

A further advantage of storing your data in an array, rather than in individual variables is if you want to perform the same action on member of the list. If your team member names are stored in 20 individual variables, you will need 20 lines of code to carry out a specific action on each name. However, if you have stored your names in an array, you can use a simple loop to carry out the action for each entry in the array.

This is shown in the example below, which prints out each name in the Team_Members array to a cell in Column A of Sheet1 of the current Excel Workbook:
For i = 1 To 20
Cells(i, 1).Value = Team_Members(i)
Next i

It is clear from the above that the handling of an array of 20 names is much less cumbersome and more organised than handling 20 individual variables, but imagine if you had 1,000 names to store! And imagine that you wanted to store Surnames separately from Forenames! It is clear that it would soon become impossible to handle this amount of data without the use of Arrays in your VBA code.

Multi-Dimensional Excel Visual Basic Arrays

The Visual Basic Arrays discussed above are one-dimensional, in that they refer to one list of Names of team members. However, arrays can have multiple dimensions. An array having two dimensions acts as a grid of values. For example, imagine that you want to store daily sales figures for the month of January, for 5 different sales teams. You would need a 2-dimensional array, consisting of 5 sets of figures over 31 days. You would then declare the array as follows:
Dim Jan_Sales_Figures(1 To 31, 1 To 5) As Currency

In order to access the entries in the array 'Jan_Sales_Figures', you need to use two indices, refering to the day of the month and the team number. For example, the sales figures for Team2 on January 15th would be referenced as:
Jan_Sales_Figures(15, 2)

You can declare arrays with 3 or more dimensions in the same way - ie. by adding further dimensions into the declaration and using a further index to reference the array entries.

Declaring Excel Visual Basic Arrays

The above sections have already given some examples of Visual Basic Array declarations, but it is worth discussing this further. As seen above, a one-dimensional array can be declared as follows:
Dim Team_Members(1 To 20) As String

This declaration tells the VBA compiler that the array 'Team_Members' has 20 variables, which are referenced by indices 1 to 20. However, we could also decide to number our array variables from 0 to 19, in which case the declaration would be:
Dim Team_Members(0 To 19) As String

In fact, the default form of array indexing is to start at 0, so if you omit the start index from the declaration, and simply declare the array as:
Dim Team_Members(19) As String

Then the VBA compiler will understand this to be an array of 20 variables, which are indexed from 0 to 19.

The same rules are applied to declarations of multi-dimensional Visual Basic arrays. As shown in the previouse example, a two-dimensional array is declared by separating the dimension indices by a comma:
Dim Jan_Sales_Figures(1 To 31, 1 To 5) As Currency

However, if we omit the start indices from both dimensions, as follows:
Dim Jan_Sales_Figures(31, 5) As Currency

this is understood to be a two-dimensional array in which the first dimension has 32 entries, indexed from 0 to 31 and the second dimension has 6 entries, indexed from 0 to 5.

Dynamic Arrays

In the above examples, the arrays all have fixed dimensions. However, in many cases, we don't know how big an array is going to be before runtime. We could solve this be declaring a huge array, in an attempt to cover the maximum possible size needed, but this would use up an unnecessarily large amount of memory and could slow down your program. A better option would be to use a Dynamic array, which is an array that can be sized and re-sized as many times as you like, during the execution of a macro.

A dynamic array is declared with empty parentheses, as follows:
Dim Team_Members() As String

You then need to declare the dimension of the array during the execution of the code, using the ReDim statement:
ReDim Team_Members(1 To 20)

If, during the execution of the code, you need to extend the size of your array, you can use ReDim again:
If Team_Size > 20 Then
ReDim Team_Members(1 To Team_Size)
End If

It should be noted that resizing a dynamic array in this way will result in the loss of all the values that had previously been stored in the array. If you want to avoid this loss, and keep the values that had previously been assigned to the array, you need to use the "Preserve" keyword, as shown below:
If Team_Size > 20 Then
ReDim Preserve Team_Members(1 To Team_Size)
End If

The disadvantage of using the "Preserve" keyword when resizing Visual Basic Arrays is that you can only change the upper bound of an array, not the lower bound. Also, if you have a multi-dimensional array, the use of the "Preserve" keyword limits you to changing only the last dimension of the array.