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

Enjoy the tutorial? Please spread the word :)

Follow by Email