VBA Arrays

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:

  1. Create multiple variables to fetch the cell values and give them to the program.
  2. 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.

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Arrays (wallstreetmojo.com)

The Properties of VBA Arrays

The properties of VBA arrays are listed as follows:

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.

Arrays in VBA Excel 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.

Arrays in VBA Excel 2

After storing data in an array, it is ready to be used as a variable in VBA codingVBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

Types of VBA Arrays

The VBA arrays can be categorized as follows:

  1. Static array
  2. Dynamic array
  3. One-dimensional array
  4. Two-dimensional array
  5. 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:

Arrays in VBA Excel (Static Array)

#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:

Arrays in VBA Excel Dynamic Array).

#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?

You can download this Arrays in VBA Excel Template here – Arrays in VBA Excel Template

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 editorVBA EditorThe Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft software.read more is “Alt+F11.”

ALT + F11

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

VBA Editor in Excel

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.”

Step 1

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

Step 1-1

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.

Step 2

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 2-1

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 3

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.

Step 4

The Cautions Governing the use of VBA Arrays

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

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,[delimiter])”

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,[delimiter,[limit,[compare]]])”

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.

Recommended Articles

This has been a guide to VBA Arrays. Here we discuss the different types of arrays, how to declare and use them. We also discussed the array in VBA examples. You can download the template from the website. You may also look at these useful Excel tools-

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>