Array in Excel VBA Macro | What is Array 2024 | How to Implement Array | Use of Array

Arrays in Visual Basic for Application are structures that typically store collections of related variables of the same type. The array records are accessed by their numeric 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 this:

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 the list of team member names in an array of 20 String variables :

Dim Team_Members (1 To 20) As String

In the line shown above, we have declared an array. Now let’s write the value into each of its elements, like this:

Team_Members (1) = "John Smith"

The 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, it would take 20 lines of code 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 this works is demonstrated below with a code example that displays the names of each team member sequentially in the cells of column A of the active Excel worksheet.

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

Obviously, working with an array containing 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, you want to keep separate surnames and patronymics ? Clearly, it will soon become completely impossible to cope with 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 keep a simple list of names. However, arrays can have many dimensions. For example, a two-dimensional array can be compared to a grid of values.

Let’s say you want to store daily January sales figures for 5 different teams. This would require a two-dimensional array of 5 metric sets for 31 days. Let’s declare an array like this:

Dim Jan_Sales_Figures (1 To 31, 1 To 5) As Currency

In order to access the elements of the Jan_Sales_Figures array , you need to use two indices indicating the day of the month and the team number. For example, the address of the item containing the sales figures for the 2nd team for January 15th would be written as follows:

Jan_Sales_Figures (15, 2)

In the same way, you can declare an array with 3 or more dimensions – it is enough to 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 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

This declaration tells the VBA compiler that the Team_Members array consists of 20 variables, which can be accessed at indices from 1 to 20. However, it may occur to us to number our array variables with numbers 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 starting index may not be specified at all, like this:

Dim Team_Members (19) As String

The VBA compiler interprets 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 the starting index for both dimensions of the array and declare it like this:

Dim Jan_Sales_Figures (31, 5) As Currency

then this record will be regarded 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. You can get out of the situation by declaring a huge array, the size of which will most likely be larger than what is needed 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, the size of which can be set and changed any number of times during the execution of the 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 ReDim expression :

ReDim Team_Members (1 To 20)

And if during the execution of the code you need to change the size of the array again, you can use the ReDim expression again:

If Team_Size> 20 Then
ReDim Team_Members (1 To Team_Size)
End If

It should be borne in mind that changing the size of a dynamic array in this way will lead to the loss of all values ​​stored in the array. In order to preserve the data already in 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

Unfortunately, the Preserve keyword can only be used to change the upper bound of an array dimension. The bottom of the array cannot be changed in this way. In addition, if the array has multiple dimensions, only the last dimension of the array can be resized using the Preserve keyword .