Arrays in VBA – Excel

Today we will learn about arrays in VBA. We will learn about how to declare and initialize arrays of different data types to store values. We will learn about the lower bound and upper bound of an array and the usage of variant type arrays.

So let us begin learning.

What is an Array?

An Array in VBA stores a set of elements having similar kind of information. We need to define a data type for the elements and the maximum number of elements of an array that it can store. All the elements of an array have the same Data Type.

Each element of an array has an index assigned to it and the indexes begin from 0 to the maximum limit of an Array.

Let us say we have an array of Student_marks of integer type containing marks of 10 students. The indexing of each element of the Student_marks array would start from 0 to 9.

Upper and Lower Bound of an Array

By now, you have learned that the index of an array element begins from zero. That is, the index of the first element of an array is zero by default.

The Index of the first element of an array is known as Lower Bound while the Index of the Last element is known as Upper Bound.

When we declare an array like this:-

Dim myArray(3) As Integer

In this example, we are specifying only the upper bound to 3. The lower bound is zero so the indexing of elements starts from 0 to 3.

We can also specify the Lower Bound of an array in VBA.

Dim myArr(1 to 3) As Integer

Here 1 is the lower bound and 3 is the upper bound. Therefore, we can make the array element begin from index 1. The three elements of this array are myArr(1), myArr(2), and myArr(3).

Variant Array

A variant array can store the elements of the variant data type. This implies that we can store the value of any data type as an array element.

Dim varArr(2) As Variant
'storing an integer
varArr(0)=45
'storing a boolean
varArr(1)=True
'storing a text string
varArr(2)="ExcelUnlocked"

The thing to remember is that, once you store a value of a specific data type in an element, the element becomes compatible with that data type only and it cannot be changed in the future. For example, this is invalid.

'declaring an array as Variant
Dim arr(1) As Variant
'storing an Integer value
arr(1)=45
'changing to another integer value
arr(1)=32
'changing to String is invalid as arr(1) can now contain only Integral values
arr(1)+"ExcelUnlocked"         'this would cause error

Declaring an Array

An array is a type of variable that is declared using the keywords Dim, Public, Static, or Private.

Dim <array_name>(<upper_bound>) As <data_type>

While declaring an array, you need to follow these steps.

• Use the Dim keyword, followed by the name of the Array and a pair of brackets.

Dim Student_marks()

• Specify the array limit in the curly brackets. If you do not specify the limit, then the array would be treated as a Dynamic array which is not the context of this article.

Dim Student_marks(4)

• Now we will define the data type of the array. If you do not specify the data type, then the elements of the array would be of variant type.

Dim Student_marks(4) As Integer

This Student_marks array would contain five elements indexed from 0 to 4.

Initialize an Array

By default, all the elements of an Integer array contain zero, String array elements contain empty strings and Boolean Array elements would contain False.

Initializing Each Element Individually

Assigning values to the elements of an array is known as initialization. We can assign values to each element of an Integer array separately as follows.

'declaring an integer type array
Dim intArr(3) As Integer
'assigning values individually to each element of array
intArr(0)=10
intArr(1)=34
intArr(2)=92
intArr(3)=98

Using For Loop to Assign Values to Array Elements

We can use a For Loop to iterate through each element of the Arrays in VBA and assign values to them. The For Loop would start from 0 to the array limit. We can use the InputBox function to ask the value of array elements from the user.

Dim studentNames(5) As String
'UBound returns the upper bound of array which is 5 in this case
for i=0 to UBound(studentNames)
studentNames(i)=InputBox("Enter the " & i & " array element")
next i

Using the Array Function – Applicable for Variant Arrays only

When we do not specify the data type of the array, it is automatically treated as a variant array. We would use the Array function for the variant type array to initialize.

Dim varArr()
'no need to specify the size
varArr()=Array("Alex","Marshall","Lily")

We cannot do this in a single line of code. Which is why this would be invalid in VBA.

Dim varArr()=Array("Alex","Marshall","Lily") 'this is invalid

With this ends the topic of Arrays in VBA.

Thank you for reading.