Excel VBA Array

Excel VBA Array: One-Dimensional Array and Two-Dimensional Array

Array is a group of Variables that holds multiple values at a time. Like all other programming languages, MS Excel VBA also supports Array. Any element or a sub variable of an array can be accessed programmatically using the array name and the index number.

One-dimensional Array

To create a one-dimensional array, follow the steps below:

Insert a command button on a worksheet and add the following lines of code:

Dim Vegs(5) As String

Vegs(0) = “Potatoes”

Vegs(1) = “Carrots”

Vegs(2) = “Turnips”

Vegs(3) = “Green Chilli”

Vegs(4) = “Lady FInger”

MsgBox Vegs(4)

On clicking the command button, the result will be displayed in form of a msgbox.

Explanation: the first code line declares a String array with name Vegs. The array consists of five elements. Next, we initialize each element of the array. Note that, the array element starts from 0 index. Finally, the fifth element is displayed using MsgBox.

Two-dimensional Array

To create a two-dimensional array, follow the steps below. In this section, you will observe that the values are fetched from worksheet.

Place a command button on your worksheet and add the following code lines:

Dim Arr(5, 5) As String

Dim intRow As Integer, intCol As Integer

For intRow = 1 To 5

    For intCol = 1 To 2

        Arr(intRow, intCol) = Cells(intRow, intCol).Value

    Next intCol

Next intRow

MsgBox Arr(3, 1) & ” is a ” & Arr(3, 2)

On clicking the command button, the result will be displayed in form of a msgbox.

Explanation: the first code line declares a String array with name Arr. The array has two dimensions having 5 rows and 2 columns. The other two variables declared are of Integer type and are used for Double Loop to initialize each element of the array. Finally, the result of both dimensions is concatenated and displayed in form of a msgbox.