VBA Arrays in Excel

Arrays are used to store data of similar data types. Suppose there are 300 students rather than declaring 300 variables for students, we can declare one array where we can store 300 elements. In this article, we will learn about excel VBA arrays. 

Declaration of Array 

Declaring an array is similar to declaring a variable in VBA. If we just mention, the size of a variable in parenthesis, then the variable becomes an array. For example, An array is declared with the name of arr, of 50. Here, the size of the array is 51, and the data type is the currency.

Syntax: Dim arr(size_of_array) as data_type

Note: In other programming languages, like C, C++, javascript, python etc. If an array of size n is created, then the indexes, range from [0, n-1], where 0 and n-1 are included, but in VBA by default, if an array of size n is created, then the indexes, range from [0, n], where 0 and n are included. Hence, VBA array of declaration n, is ultimately an array of size n+1. 

Assigning Values to the Array

Assigning values means that an element is set to the value. For example, an array name arr of 50 is declared. Then a loop is passed on the array, and each element of the array is assigned the value 30. 

Syntax: arr(index) = value

Changing Lower and Upper Bound

As, we discussed, above in a Note. That, arr(50), has an array size 51. So, what if we want to create an array of size 50, with 0-based or 1-based indexing? For example, if we want to change the lower bound from 0 to 1 then we can do it two ways: 

Note: By default, the lower bound of an array is 0, and the upper bound of an array is n. 

where, 

lower bound is the lowest index of the array, 

upper bound is the highest index of the array.  

Using “Option Base”

Option Base statement is written at the top of the module to change the default index from 0 to 1. 

Using the “To” clause

We can, Explicitly, can set the lower bound using the To clause. For example, arr(1 To 51), this array has a size of 50, with 1-based indexing. 

Storing values of Variant data types in Arrays

Variant data types are those where any kind of data type can be stored like string, integer, date, etc. There are two methods, to store values in variant data types in arrays: 

Method 1

The size of the array is predefined, i.e. 3. For example, an array of 2 is created. The value of arr[0] is a string, arr[1] is also a string, and arr[2] is a number. 

Method 2

The size of the array is not predefined, and the values are assigned, directly to the array. Use, Array() function to achieve this. For example, arr = Array(“Ayush”, “123 Ayush”, 123). 

Using Multidimensional Arrays 

Declaration of a Multidimensional Array

We can declare a 2-dimensional array, by adding an additional size by comma separation. For example, if we want to create an array of 4 rows and 6 columns. Then the array declaration could be, Dim arr(1 To 4, 1 To 6) As Currency. 

Syntax: Dim arr(Rows, Columns) As data_type

Assigning Values to a Multidimensional Array

We will use a nested For Next loop to assign the values in the multidimensional array. The below Picture illustrates the nested for loop, assigning of values in a multidimensional array. 

Syntax: arr(i, j) = value

where, 

i = index of the row,

j = index of the column, 

value = the value which has to be assigned. 

Functions in Arrays

There are ten-plus in-built functions in an array. These functions, help make our task more handy and easy. Here, we will discuss some of the most commonly used functions in arrays in VBA. 

Erase

The Erase function clears all the elements in the array. For example, we have declared an array of size 3, and assigned values to them. Now, we want to erase all the elements in the array, i.e. Erase arr. 

Syntax: Erase Array_name

where, 

Array_name = It is the name of the array, whose elements are to be deleted. 

ReDim

The ReDim() function is used to change the dimension of the array. For example, initially, we declared an array of size 3, now we want to reassign the value of dimension to 2 in the array. The ReDim arr(1 To 2) can be used to achieve this. 

Syntax: ReDim Array_name(row, column)

where,

Array_name = It is the name of the array, for which the dimension has to be changed,

row = number of rows to be reassigned,

columns = number of columns to be reassigned

Join

The Join() function is used to join the elements of arrays into one variable. For example, an array of size 3, is created, and the values, assigned, to them are 10, 20, and 30. Now, if we want to concatenate, all the elements. Then, Join(arr) function can be used. The final output of the program will be 102030. 

Syntax: Join(Array_name) 

where, 

Array_name: It is the name of the array. For example, arr in the below image. 

Split

The Split() function, is used to split the string into an array, using a delimiter as a separator. For example, we have a string month_12 = “april,may,june”, now, we want to convert this string into an array, where each element is the name of the month. We can use Split(month_12, “,”), to achieve, this task. The final array will be (april, may, june). 

Syntax: Split(string, delimiter)

where, 

string = It is the string, from which the array has to be created, 

delimiter = It is the separation parameter, on which basis the string will break into the array. For the below example, “,”(comma) is the delimiter.