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.”
corporate training excel corporate training excel

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.corporate training solutions excel 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


training for excel macros

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


training for excel macros

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


Enjoy the tutorial? Please spread the word :)

Follow by Email