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.
- VBA Arrays can store multiple values within a single variable name.
- There are five types of VBA arrays – Static, Dynamic, One-Dimensional, Two-Dimensional, and Multi-Dimensional Array
- With the REDIM statement, we can define the length of the array during the run time of the macro. However, we can only alter the upper limit of the array, not the lower limit.
- A one-dimensional array can retrieve values from either one row or column.
- A two-dimensional array can access both rows and columns.
- An array cell reference starts with a row number, followed by the associated column number.
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:
- Static Array
- Dynamic Array
- One Dimensional Array
- Two-Dimensional Array
- 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 6th 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)
Yes, VBA arrays start with zero. For example, the array variable “K (0 to 4)” means it can hold 5 values.
Using VBA arrays, we can easily avoid lengthy codes, especially when we need to declare multiple variables to hold the data of the same data type. By defining the array variable, we can hold multiple values based on the upper limit and lower limit of the array.
VBA Arrays can hold any data type, but all the values that we assign should be of the same data type.
VBA Arrays work based on the size allocated to them. They can hold any amount of data of the same data type values.
This article must be helpful to understand VBA Arrays, with its formula and examples. You can download the template here to use it instantly.
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 –
Leave a Reply