Powerful Excel VBA Array Techniques – Complete Tutorial

Powerful Excel VBA Array Techniques

An array is a group of variables. To get a value from an array in Excel VBA, use its name and index number.

One-dimensional Array

Follow these steps to define and initialize a one-dimensional array with desired values.

Insert a command button on your worksheet, then enter the following code:

Dim Films(1 To 5) As String

Films(1) = "Lord of the Rings"
Films(2) = "Speed"
Films(3) = "Star Wars"
Films(4) = "The Godfather"
Films(5) = "Pulp Fiction"

MsgBox Films(4)

Results when you clicked the command button:

Explanation: In the first line, the code defines ‘Films’ as a string array. The array consists of five elements. Next, we assign an initial value to each item in the array. At the end, the message box displays the fourth item.

Two-dimensional Array

You can create a two-dimensional array by completing the steps below. This time, we will take the names directly from the sheet.

Insert a command button on your worksheet and enter the code below:

Dim Genres (1 to 5, 1 to 2) As String
Dim i As Integer, j As Integer

For i = 1 To 5
    For j = 1 To 2
        Genres (i, j) = Cells(i, j).Value
    Next j
Next i

MsgBox Genres(4, 2)

Results when you clicked the command button:

Explanation: In the first line, the code defines a string array with the name “Genres”. The array has two dimensions. It contains a total of 5 rows and 2 columns. Tip: rows go first, then columns. Two Integer variables control a double loop, which assigns values to each position in the array during initialization. Finally, a message box will show an array element from cell B4.

1/5 Completed! Learn much more about arrays ➝
Next Chapter: Function and Sub