Sample Visual Basic macros for working with arrays in Excel

An array is like a table that contains elements of the same data type.

By using an array as a variable you can avoid declaring a lot of variables.Instead you collect them in one place, your array, and then you can look them up, when you need to read or write a value.

Every element in an array has an index number, and you can find the element by using the index number as a reference. You can also loop through arrays.

To create a one-dimensional array, execute the following steps.

Place a Button and checks this below code.

Sub arrayexample()

Dim Names(1 To 3) As String

Names(1) = "Suresh"

Names(2) = "Mahesh"

Names(3) = "Ramesh"

MsgBox "Hello  " & Names(2)

End Sub

Result when you click the command button on the sheet:

Explanation: the first code line declares a String array with names. The array consists of five elements. Next, we initialize each element of the array. Finally, we display the 3rd element using a MsgBox.

Two-dimensional Array

To create a two-dimensional array, execute the following steps. This time we are going to read the names from the sheet.

Assign some data as you see the above image and place a command button and execute below code and see the result.

Sub arrayexample()

Dim names(1 To 4, 1 To 2) As String

Dim i As Integer, j As Integer

For i = 1 To 4

    For j = 1 To 2

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

    Next j

Next i

MsgBox names(3, 2)

End Sub

 Result When you click a button.

Explanation: the first code line declares a String array with name. The array has two dimensions. It consists of 4 rows and 2 columns. Tip: rows go first, then columns. The other two variables of type Integer are used for the Double Loop to initialize each element of the array. Finally, we display the element at the intersection of row 4 and column 2.

Do you like this post...Please subscribe to daily news letter.