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

Leave a Reply

Your email address will not be published.

Enjoy the tutorial? Please spread the word :)

Follow by Email