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.