Contents
Arrays in Visual Basic for Application are structures that typically store sets of related variables of the same type. Array entries are accessed by their numerical index.
For example, there is a team of 20 people whose names need to be saved for later use in VBA code. One could simply declare 20 variables to hold each name, like so:
Dim Team_Member1 As String Dim Team_Member2 As String ... Dim Team_Member20 As String
But you can use a much simpler and more organized way – store a list of team member names in an array of 20 variables like String:
Dim Team_Members(1 To 20) As String
In the line shown above, we have declared an array. Now let’s write a value to each of its elements, like this:
Team_Members(1) = "John Smith"
An additional advantage of storing data in an array, compared to using separate variables, becomes apparent when it becomes necessary to perform the same action on each element of the array. If the names of the team members were stored in 20 separate variables, then it would take 20 lines of code to write each time to perform the same action on each of them. However, if the names are stored in an array, then you can perform the desired action with each of them using a simple loop.
How it works is demonstrated below with a code example that prints the names of each team member sequentially in the column cells. A active Excel worksheet.
For i = 1 To 20 Cells(i,1).Value = Team_Members(i) Next i
Obviously, working with an array that stores 20 names is much less cumbersome and more accurate than using 20 separate variables. But what if these names are not 20, but 1000? And if, in addition, it is required to keep the surnames and patronymics separately?! It is clear that it will soon become completely impossible to handle such a volume of data in VBA code without the help of an array.
Multidimensional arrays in Excel Visual Basic
The Visual Basic arrays discussed above are considered one-dimensional. This means that they store a simple list of names. However, arrays can have multiple dimensions. For example, a two-dimensional array can be compared to a grid of values.
Let’s say you want to save the daily sales figures for January for 5 different teams. This will require a two-dimensional array consisting of 5 sets of metrics for 31 days. Let’s declare an array like this:
Dim Jan_Sales_Figures(1 To 31, 1 To 5) As Currency
To access array elements Jan_Sales_Figures, you need to use two indexes indicating the day of the month and the command number. For example, the address of an element containing sales figures for 2-oh teams for 15 th January would be written like this:
Jan_Sales_Figures(15, 2)
In the same way, you can declare an array with 3 or more dimensions – just add additional dimensions to the array declaration and use additional indices to refer to the elements of this array.
Declaring Arrays in Excel Visual Basic
Earlier in this article, we already looked at several examples of declaring arrays in VBA, but this topic deserves a closer look. As shown, a one-dimensional array can be declared like this:
Dim Team_Members(1 To 20) As String
Such a declaration tells the VBA compiler that the array Team_Members consists of 20 variables that can be accessed at indices from 1 to 20. However, we might think of numbering our array variables from 0 to 19, in which case the array should be declared like this:
Dim Team_Members(0 To 19) As String
In fact, by default, the numbering of array elements starts from 0, and in the array declaration, the initial index may not be specified at all, like this:
Dim Team_Members(19) As String
The VBA compiler will treat such an entry as declaring an array of 20 elements with indices from 0 to 19.
The same rules apply when declaring multidimensional Visual Basic arrays. As already shown in one of the examples, when declaring a two-dimensional array, the indices of its dimensions are separated by a comma:
Dim Jan_Sales_Figures(1 To 31, 1 To 5) As Currency
However, if you do not specify a starting index for both dimensions of the array and declare it like this:
Dim Jan_Sales_Figures(31, 5) As Currency
then this entry will be treated as a two-dimensional array, the first dimension of which contains 32 elements with indices from 0 to 31, and the second dimension of the array contains 6 elements with indices from 0 to 5.
Dynamic arrays
All arrays in the above examples have a fixed number of dimensions. However, in many cases we do not know in advance what size our array should be. We can get out of the situation by declaring a huge array, the size of which will certainly be larger than necessary for our task. But such a solution will require a lot of extra memory and can slow down the program. There is a better solution. We can use a dynamic array – this is an array whose size can be set and changed any number of times during the execution of a macro.
A dynamic array is declared with empty parentheses, like this:
Dim Team_Members() As String
Next, you will need to declare the dimension of the array during code execution using the expression ReDim:
ReDim Team_Members(1 To 20)
And if during the execution of the code you need to change the size of the array again, then you can use the ReDim expression again:
If Team_Size > 20 Then ReDim Team_Members(1 To Team_Size) End If
Keep in mind that resizing a dynamic array in this way will result in the loss of all values stored in the array. In order to store data already in the array, you need to use the keyword Preserveas shown below:
If Team_Size > 20 Then ReDim Preserve Team_Members(1 To Team_Size) End If
Unfortunately the keyword Preserve can only be used to change the upper bound of an array dimension. The lower bound of an array cannot be changed in this way. Also, if the array has multiple dimensions, then using the keyword Preserve, only the last dimension of the array can be resized.