Visual Basic Arrays

Excel Visual Basic arrays are structures which are used to store a set of 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_Member20 As String

Alternatively, you could use the 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

Once you have declared the array as above, each entry of the array can be populated as follows:

Team_Members(1) = "John Smith" . . .

A further advantage of storing data in an array, rather than in individual variables, arises if you want to perform the same action on every member of the list. If the team member names were stored in 20 individual variables, you would need 20 lines of code to carry out a specific action on each name. However, if you have stored the 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 code below, which prints out each name in the Team_Members array to Column A of the current Excel Worksheet:

For i = 1 To 20 Cells(i, 1).Value = Team_Members(i) Next i

It is clear from the above that handling 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 you wanted to store Surnames separately from Forenames! It is clear that it would soon become almost 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. 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 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. I.e. 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 index the 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 previous 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, you may not know how big an array is going to be before run time. One way to solve this is to declare 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

It is then necessary to declare the dimension of the array during the execution of the code, using the ReDimstatement:

ReDim Team_Members(1 To 20)

If, during the execution of the code, you need to extend the size of an 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 have 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.

Click Here to  Main Menu