VBA Arrays

What Is VBA Arrays In Excel?

VBA Arrays can hold multiple values in a single variable. It is very useful when we have to run loops multiple times and assign different values to the same variable during every loop. This makes the code more compact and easy to run.

When we define a variable and store some value, it can hold only one value at any given point in time. To store more than one value in the variable, we must declare the variable as an array variable and assign numerous values of the same data type to it rather than creating different variables. For example, assume you have a 100-product list. Instead of creating 100 different variables of the same data type, we can create a single variable with the array method and assign all the 100 products to the same variable.

Properties Of VBA Arrays

The properties of VBA arrays are as follows:

• In an array variable, multiple values can be stored together with the same data type. This is simply a way of creating a separate memory unit that can retain data.
• There are two types of an array, i.e., One Dimensional and Two Dimensional.
• An array can be static or dynamic. In a static array, we define the limit of the array, whereas, with a dynamic array, we can store an infinite number of rows and columns.
• We cannot reset the size of the static array, but with a dynamic array, we can reset it while inside the code.

Declaring Arrays In VBA

Declaring an array variable is slightly different than declaring a regular variable. The Rule of Mathematics Matrix is applied while declaring the array variables.

For instance, if you want to declare an array to hold two values of the String data type, then we can declare the array variable as follows.

The letter “k” is the variable name, and inside the parenthesis, we have defined the limit of the array as 1.

In VBA, the array range starts from 0, so to assign two values, we have given the value as 1.

To assign values to these array variables, we can assign them as shown below:

The first value “Hello” is assigned to the variable “k” with the position of the array in brackets as 0 and the second value “Hi” is assigned to the variable “k” by mentioning the number inside the brackets.

To show these two variable values, we can use the message box as shown in the following image.

We have used two message boxes to show two values of the same variable. When we run the macro, we get the following message boxes one after the other.

First Message Box                  Second Message Box

In the above code, we have defined the array as static i.e., we have defined the size of an array well in advance. However, if we are not sure of the size of the array, we can use dynamic arrays.

Types Of VBA Arrays

Mainly there are 5 types of VBA arrays, and those are as follows:

1. Static Array
2. Dynamic Array
3. One Dimensional Array
4. Two-Dimensional Array
5. Multi-Dimensional Array

We will discuss them one by one with a practical example.

#1 – Static Array

A static array is nothing but an array with a fixed length. When we define an array, we fix the length of the array well in advance.

Assume we need to store five product information in cells A1:A5. First, we must define the variable array with a fixed length of 0 to 4 as follows.

The array length reads like this.

Then we can assign the product names for the array variable, as shown in the following image.

To store the above-assigned values to cells, we can use the FOR NEXT loop with another variable.

This will store the value as shown in the following image.

#2 – Dynamic Array

Dynamic array type allows us to resize the arrays during the run time. For example, we are not sure how many products will enter the market next month, so in this case, we cannot really define the size of the array to hold the employee list.

To declare the array with dynamic nature, we need to use the same technique we used while declaring the static array but this time with empty parenthesis.

As you can see inside the parenthesis, we have not entered any lower limit (LBOUND) or upper limit (UBOUND).

Use REDIM Statement

When we have to change the size of the array after defining the variable, we must use the keyword REDIM, as shown below.

As we can see in the above code, we have not mentioned the lower limit of the array variable. When we use the VBA REDIM statement, we are not allowed to change the lower bound of the array variable.

Now we can assign 5 values to the array variable.

Use the following loop to store the data in cells.

This will store the values from cells A1 to A5.

However, when we have to add one more product during run time, we can use another REDIM statement as follows.

When we run the macro, we will get only the last assigned value of the 6^th position i.e., Guava in cell A6.

The reason for this is that when we use the REDIM statement for the second time, it reallocates the memory to the array variable, and all the previously assigned values are erased.

Use REDIM PRESERVE Statement

The Preserve keyword helps us to preserve all the old values when we assign new values to the array variable.

When we run this code, it should store all 6 product values.

#3 – One Dimensional Array

As the name suggests, a one-dimensional array can hold data from only one row or column.

For instance, we will insert city names into the first column i.e., column A. The following code is for our reference.

When we run the code, it will insert city names into column A.

However, when we have to insert country names against these cities, we cannot because the array we defined is one-dimensional.

#4 – Two-Dimensional Array

This is an advanced version of the one-dimensional array i.e.; we can access both rows and columns using this array type.

Assume we must insert country names against the city names in the previous example. First, we have to define the array dimension as highlighted in the image below.

Inside the parenthesis, we have added the column limit as 1 to 2, i.e., access two columns and five rows.

For the first row, we can assign the value as follows.

Inside the parenthesis, we have mentioned (1,1 ),i.e., first-row first column.

Next, against the city London, we can supply the country as shown.

Inside the parenthesis, we have mentioned (1,2), i.e., first-row second column.

In this way, we can supply the city and country names for all the five cities as shown in the following image.

To insert these values into cells, we need to use nested loops as below.

This will insert the values as shown.

#5 – Multi-Dimensional Array

This is an array that is to access multiple rows and multiple columns. Assume, we need to insert a ranking for each city and country then we must have one more column.

To do this, we can define the column length as 1 to 3.

Then we can assign the values as shown below.

Inside the array parenthesis, the first number represents the row, and the second number represents the column. Assign ranking numbers for all the cities.

Now tweak the loop slightly to insert the above values.

We have changed the nested loop length from 1 to 2 to, 1 to 3.

This will insert the values as below.

Important Things To Note

• The array length starts from 0 in VBA.
• An array can contain only one data type.
• When you perform VBA coding, you have to save the file as a Macro-Enabled workbook i.e., in the XLSM file extension.
• When we use the REDIM statement for the second time, all the old values will be erased from the memory of the array and it will return a blank.
• REDIM Preserve retrieves all the old values when reassigning the values during run time.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand VBA Arrays, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA arrays. Here we explain the top 5 types of VBA arrays along with its properties, declaration and examples. You may learn more from the following articles –

• VBA Loops
• VBA CSTR
• VBA Format