VBA- Arrays

VBA- Arrays

An array is a way to store more than one value with a common name.

By definition, “An array is a group of variables with having same data type (Homogeneous Data Type) and name.”

Sometimes, we need to store more than one value in a single variable at a time. When a series of data is stored in a single variable, then it is refer as array. The position of first element in the array is 0 because the first index of the array start with 0 to n-1.

We can have one dimensional array, two dimensional array (2D) or even a multidimensional array (up to 60)

There are various method to declare array in VBA-

Declared with size of 5

Dim arr(5) as Integer

‘ If we don’t want the first position to be 0 then

Dim MyArray(1 To 5) As Integer

‘Using Array Parameter

Dim arr3

arr3 = Array(“Ajay”,”Amit”,”Ashok”)


Sub Disp()

‘Declare an Array, which can store upto 4 integer values

Dim Arr(4) As String

‘Now we can assign 4 values to given array

Arr(0) = “Mango”

Arr(1) = “Apple”

Arr(2) = “Banana”

Arr(3) = “Papaya”

‘We can print any index once values are stored in array

MsgBox “First two Values are ” & Arr(0) & ” and ” & Arr(1)

End Sub


Example to enter series of names from user and store them into the next available cells in the sequence

Sub arr()

Dim IName(5) As String

For i = 1 To 5

IName(i) = InputBox(“Enter student Name”)

Cells(i, 1) = IName(i)


End Sub



Example to fetch names from excel range (Refer previous example)


Sub readarr()

Dim arr(10) as String

For i = 0 To 5

arr(i) = Range(“A” & i + 1)

MsgBox “Value at ” & i & ” is ” & arr(i)


End Sub

The Join Function in Array

A Function, which returns a String that, contains a specified number of substrings in an array. So using Join Function you may merge or concatenate array element into a single group.


Sub test()

Dim Trainees(0 To 5) As String

Trainees(0) = “Vikas”

Trainees(1) = “Kamal”

Trainees(2) = “Jyoti”

Trainees(3) = “Chanchal”

Trainees(4) = “Netrapal”

Trainees(5) = “Hari”

Dim myList As String

‘Lets join all string with comma seperator

myList = Join(Trainees, “, “)

MsgBox myList

End Sub


The Split Function in Array

Sometimes we have to take the information from a given string, separate it into multiple strings, and then place those strings in an array or in a specific place. In that case split function of array could be the best approach. So, the Split Function used to split text into multiple text based on a given criteria.

Example: To split movie name one by one from the array list

Sub test()

Dim Movies() As String

Dim MovieList As String

Dim i As Integer

MovieList = “Bajrangi Bhaijaan,Sultan,Chennai Express,PK”

Movies = Split(MovieList, “,”)

For i = LBound(Movies) To UBound(Movies)

MsgBox Movies(i)


End Sub


Multidimensional Arrays

An array can have one dimension or more than one. If it has more than one, it is called a multidimensional array.

Example To Read Range of Data from a Excel sheet

Sub twod()

Dim List(1 To 5, 1 To 3) As String

Dim i As Integer, j As Integer

For i = 1 To 5

For j = 1 To 3

List(i, j) = Cells(i, j).Value

‘MsgBox List(i, j)

Next j

Next i

End Sub


Example: to accept information from user and store the same into the excel sheet

Sub MyArrayList()

Dim Name(3) As String

Dim ID(3) As String

Dim Mark(3) As Integer
For i = 1 To 3
Name(i) = InputBox(“Enter student Name”)
ID(i) = InputBox(“Enter student ID”)
Mark(i) = InputBox(“Enter student Mark”)
Cells(i, 1) = Name(i)
Cells(i, 2) = ID(i)
Cells(i, 3) = Mark(i)

End Sub

Enjoy the tutorial? Please spread the word :)

Follow by Email