VBA Arrays

Last Updated :

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

What is VBA Arrays in Excel?

A VBA array in excel is a storage unit or a variable which can store multiple data values. These values must necessarily be of the same data type. This implies that the related values are grouped together to be stored in an array variable.

For example, a large organization which operates in several countries employs 10,000 people. To store the details of all employees, a database containing hundreds of rows and columns is created. So, either of the following actions can be performed:

Create multiple variables to fetch the cell values and give them to the program.
Create an array consisting of multiple related values.

The action “a” is far more complicated and time-consuming than action “b.” For this reason, we use VBA arrays.

To use an array variable, it needs to be declared or defined first. While declaring, the length of the array may or may not be included.

The purpose of using an array is to hold an entire list of items in its memory. In addition, with an array, it is not required to declare each variable to be fetched from the dataset.

The difference between an array and a regular variable of VBA is that the latter can hold a single value at a given time. Moreover, while using multiple regular variables, every variable needs to be defined prior to its usage.

What is VBA Arrays in Excel?

The Properties of VBA Arrays

The properties of VBA arrays are listed as follows:

In an array, the data of the same type are grouped together. So, creating an array is similar to creating a separate memory unit which can hold data.
The array to be used must correspond with the kind of dataset. For instance, if the dataset has a single row or a single column, a one-dimensional array is used. A two-dimensional array is used if the dataset has multiple rows and columns.
An array can function as static or dynamic. A dynamic array can include an infinite number of rows and columns. In contrast, a static array holds a limited number of rows and columns, which are defined at the time of creation.

Note: During run time, the size of a static array cannot be modified while a dynamic array can be resized.

The Working of a VBA Array

An array works similar to the mathematical rule of a matrix. This implies that an array identifies the data by its location.

For example, if the number 20 is required in cell B3, we write the location in the code as (3, 2). The number “3” represents the row number and “2” represents the column number.

In Excel, the code of locations is known as upper bound and lower bound. By default, the locations in Excel begin from 0 and not from 1. Hence, “A1” is considered as row number 0 and not as row number 1.

Likewise, columns also begin from 0 and not from 1.

Note: In the preceding example of cell B3, Excel counts the rows and columns from 1.

If we define this array as static, it cannot hold more than the defined variables. Hence, if the user is not sure about the number of values to be memorized by an array, it is advisable to use a dynamic array.

The data is entered in the array, as shown in the following image.

After storing data in an array, it is ready to be used as a variable in VBA coding.

Types of VBA Arrays

The VBA arrays can be categorized as follows:

Static array
Dynamic array
One-dimensional array
Two-dimensional array
Multi-dimensional array

Let us consider every array one by one.

#1 – Static Array

A static array has a fixed, pre-defined count of the values that can be stored in it. The code of a static array is written as follows:

#2 – Dynamic Array

A dynamic array does not have a pre-defined count of values that can be stored in it. The code of a dynamic array is written as follows:

#3 – One-dimensional Array

A one-dimensional array can hold data either from a single row or a single column of Excel.

A table consists of the marks obtained by 5 students of a class. The Excel data and the code of a one-dimensional array are shown as follows:

Arrays in VBA Excel (One dimensional array)

Arrays in VBA Excel (One dimensional array) 1

#4 – Two-dimensional Array

A two-dimensional array can store values from multiple rows and columns of Excel.

A table consists of marks obtained by two students in Mathematics and Science. The Excel data and the code of a two-dimensional array are shown as follows:

Arrays in VBA Excel (Two dimensional array)

Arrays in VBA Excel (Two dimensional array) 1

#5 – Multi-dimensional Array

A multi-dimensional array can store data from multiple sheets which contain several rows and columns. An array can have a maximum of 60 dimensions.

A table consists of the marks obtained by two students in Mathematics, Science, and Accountancy. The Excel data and the code of a multi-dimensional array are shown as follows:

Arrays in VBA Excel (Multidimensional Array)

Arrays in VBA Excel (Multidimensional Array) 1

How to use Arrays in VBA?

An array can be used in several situations. It is particularly used when there are a large number of variables to be declared and it is not feasible to define each and every variable.

The shortcut to open the VBA editor is “Alt+F11.”

Once the VBA editor opens, the code can be entered in “ThisWorkbook,” as shown in the following image.

The Procedure of Using an Array

The steps of using an array are listed as follows:

Step 1: Choose the type of array. It can be either dynamic or static.

For a dynamic array, the dimension is defined as “variant.”

For a static array, the dimension is defined as “static.”

Step 2: Define the rows and columns to be stored in the array.

Enter “1” within the brackets, as shown in the following image. This implies that the array can hold the values of two rows. This is because Excel begins counting from 0.

If rows and columns both are required, one needs to define them. So, enter “1 to 2” and “1 to 3” as arguments. The same is shown in the following image.

The argument “1 to 2” implies two rows and “1 to 3” implies three columns.

Note: In this case, Excel counts the rows and columns from 1 and not from 0.

Step 3: Enter the data as input in the array.

The data is entered in the form of (i,j), where “i” represents the row and “j” represents the column. Since the data is entered cell-wise, “a (1,1)” implies cell A1.

Hence, 30 will be entered in cell A1.

Step 4: Close the code.

Once the data for the array has been entered, the last step is to close the code. This is shown in the following image.

The Cautions Governing the use of VBA Arrays

The cautions to be observed while creating VBA arrays are listed as follows:

Remember that by default, Excel counts the rows and columns beginning from zero. So, if “i” is 2, it implies three rows. The same applies to the column entries (j) as well.
Ensure that the data entered in the array begins from (0,0), i.e., the first row and the first column.
Use the “VBA REDIM” function to change the size of a dynamic array.
Use “integer” as the dimension of a two-dimensional array that accepts integer values.
Save the Excel file with the “.xlsm” extension otherwise, the VBA coding will not run the next time.

Frequently Asked Questions

1. Define VBA arrays.

A VBA array is capable of storing a series of data values. The condition is that all the values should be of the same data type. These values are known as the elements of an array.

For assigning values to an array, the location number of Excel is used. This number is known as the index or the subscript of an array. By default, the array index begins from zero unless an “Option Base 1” statement is entered at the beginning of the module.

With the “Option Base 1” statement, the array index begins from one.

Arrays are classified as follows:

• Static or dynamic based on whether the array size is fixed or changeable.
• One-dimensional, two-dimensional, or multi-dimensional based on the number of rows and columns of the source dataset from where values are assigned to an array.

2. Why are VBA arrays used?

An array is used for the following reasons:

• It helps avoid writing multiple lines of code. Since a regular variable can store a single value at a given time, each variable which is to be stored needs to be defined.
• It allows easy accessibility to the elements of the array. Every item of the array can be accessed with its row and column number which are specified while assigning values.
• It allows grouping the data of the same type. Since the related data is placed together, it simplifies working with large datasets.
• It adds the speed factor to working with Excel. Arrays make it easier to organize, retrieve, and alter data. This helps in improving productivity.

3. How to use the JOIN and SPLIT functions with respect to a VBA array?

The JOIN function combines several substrings of an array and returns a string. The JOIN function is the reverse of the SPLIT function.

The syntax of the JOIN function is stated as follows:

“Join(sourcearray,)”

The arguments are explained as follows:

• Sourcearray: This is a one-dimensional array consisting of substrings to be joined.
• Delimiter: This is a character that separates the substrings of the output. If this is omitted, the delimiter space is used.

Only the argument “sourcearray” is required.

The SPLIT function divides the whole string into multiple substrings. It splits on the basis of the specified delimiter. It returns a one-dimensional and zero-based array.

The syntax of the SPLIT function is stated as follows:

“Split(expression,]])”

The arguments are explained as follows:

• Expression: This is the entire string which is to be split into substrings.
• Delimiter: This is a character like space, comma etc., that separates the substrings of the expression. If this is omitted, the space is considered as the delimiter.
• Limit: This is the number of substrings to be returned. It returns all the substrings if the limit is set at “-1.”
• Compare: This is the comparison method to be used. Either a binary (vbBinaryCompare) or textual comparison (vbTextCompare) can be performed.

Only the argument “expression” is required. The remaining arguments are optional.