How to Create and Access Arrays in Excel VBA

VBA Arrays and Excel

In this tutorial, you will learn:

• How to create arrays in VBA by using different ways
• Accessing array elements by index
• One-dimensional and two definitional arrays (using Excel Sheet)
• A few useful array methods

How to declare arrays in VBA

First Way:

For creating arrays

• Declare it first by using the Dim keyword like any other variable.
• Size of the array in parenthesis
• The data type of the array

For example

Dim arr(5) As String

This will create an array of five elements.

Second way:

Dim arr(2 To 5) As String

So, provide the index position of the elements.

In that case, an array of four elements from 2 to 5 index is created.

An array can also be created as simply as it:

Dim arr

VBA will assign it the appropriate data type and its size is also non-fixed.

An example of creating, assigning, and accessing array elements

In this example, we will declare a string array of five elements.

Then we will assign values to elements by index numbers.

Finally, we will access the third element value in a message box. Have a look:

VBA code:

Output:

Second way of creating and accessing arrays

We are creating an array without specifying the size and data type.

VBA code:

Output:

An example of specifying start and end index in the array

In this example, we are creating an array of fixed size by specifying the start and end index numbers at the time of declaration (as shown in the syntax above).

We will give 2 to 6 index numbers as below:

Output:

If you provide 0 index then the following error occurs, like arr_day(0) = “Sun”:

Iterating through each element of the array by For..Each

By using For..Each loop, you may iterate through each element of the array.

In the example below, we have an array of five elements. We will iterate through each array element, and collect its values in a string variable.

After the loop ends, we will display all array elements with concatenation and linebreak:

VBA Code:

Result:

Using LBound and UBound functions to get array length

You may use LBound and UBound functions to get array length as shown in the example below.

Array length of one-dimensional array by UBound function:

Output:

An example of creating 2D array based on Excel cells

This is how a two-dimensional array is declared in VBA:

Dim arr(3,5) As Integer

Or with “To”

Dim arr_products(1 To 8, 1 To 4) As String

Using Excel sheet to create a 2D array:

Sample Data in our sheet

VBA Code:

Result:

Displaying a whole two-dimensional array

Now we will display the whole array in the message box.

For that, we used For..Next (with inner for loop) for assigning cell values to array elements

Another for loop is used to iterate through all 2-D array elements.

In each iteration, we used concatenations and line breaks for better result display.

Product ID, Names, Price, and Status are gathered in a string-type variable.

Finally, we displayed the whole array in a message box as shown below:

VBA code:

Result: