Excel VBA Arrays

In VBA, an array is a memory location which is capable of storing some value or more than one value in it. There are 3 types of arrays in VBA, One Dimension, Two Dimensions and Multi Dimensions. One dimension uses one index such single category of age group, Two Dimension uses 2 indexes such as people and their ages and Multi Dimension array has more than 2 indexes such as surface height at different levels. We can declare Static and Dynamic variables in Excel for Array.

How to Use Excel VBA Arrays?

Let’s understand how to use Excel VBA Arrays and their types with some examples.

You can download this VBA Arrays Excel Template here – VBA Arrays Excel Template

Example #1

Take a look at the below example. X is a variable which holds the data type of integer.

Code:

Sub Array_Example1()
Dim x As Integer

x = 1

End Sub

Now assign a value of 1 to the declared variable x.

Let’s insert the value of 1 to the cell A1.

Code:

Sub Array_Example1()
Dim x As Integer

x = 1

Range("A1").Value = x
End Sub

The x value is equal to 1 and in the range, A1 the value will be inserted as the value of x i.e. the value of x is 1. Now run the code using F5 key or manually to see results.

In the above example, x holds only one variable that’s all. But if I want to insert 5 consecutive numbers by using the single variable I need to use the type array variable which can hold many variable values in a single variable name.

Example #2

Now take a look at the below example. A variable name is x and the data type is LONG. But while declaring the variable itself I have opened the bracket and mentioned 1 to 5. This means variable x will hold 5 different types of values.

Code:

Sub Array_Example()

Dim x(1 To 5) As Long, i As Integer

End Sub

After that, I have assigned the values to each variable. X(1) = 20 means the first variable should be equal to the value of 20. X(2) = 25 means the second variable should be equal to the value of 25 and so on.

Code:

Sub Array_Example()

    Dim x(1 To 5) As Long, i As Integer

    x(1) = 20
    x(2) = 25
    x(3) = 44
    x(4) = 78
    x(5) = 96

End Sub

Later I have declared one more variable called “I” this is the second type of a variable and holds the data type of integer.

In the next step, I have applied FOR loops to insert assigned numbers to the array in the first column. I have set the value of the variable i to 1 and I have instructed the loop to run for 1 to 5 times. When the loop is running for the first time i value will be equal to 1. CELLS (I,1).value = x(i) this means for the first time i is equal to 1 i.e. CELLS(1,1).value = x(1), in the first row first column (Cell A1) the value will be the first array (x(1)) value i.e. 20.

When the loop runs for the second time i value becomes 2 i.e. CELLS (2, 1).value = x (2), in the second row first column (A2) the value will be the second array ( x(2) ) value i.e. 25.

Code:

Sub Array_Example()

Dim x(1 To 5) As Long, i As Integer

x(1) = 20
x(2) = 25
x(3) = 44
x(4) = 78
x(5) = 96

For i = 1 To 5

Cells(i, 1).Value = x(i)

Next i

End Sub

Like this when the loops keep running the values will keep changing. When the loops run for the third time A3 cell value will be 44, the fourth-time loop runs A4 cell value will be 78 when the loops run for the final time or fifth time A5 cell value will be 96.

After running the code using F5 key or manually, we will get results as shown below.

Types of Arrays in Excel

Arrays have different types in VBA. There are five types of arrays available in excel.

Static Array
Dynamic Array
One Dimensional Array
Two Dimensional Array
Multi-Dimensional Array

Static Array

In this type of array, the length of the array is pre-determined in advance and remains constant.

Code:

Sub Static_Example()

Dim ArrayType(1 To 3) As Integer

ArrayType(1) = 0
ArrayType(2) = 1
ArrayType(3) = 2

Cells(1, 1).Value = ArrayType(1)
Cells(1, 2).Value = ArrayType(2)
Cells(1, 3).Value = ArrayType(3)

End Sub

In the above code, ArrayType length is determined well in advance as 1 to 3 and the data type is Integer.

After running the code using F5 key or manually, we will get results as shown below.

Dynamic Array

In this type of array, the length of the array is not pre-determined well in advance.

Code:

Sub Dynamic_Example()

Dim ArrayType() As Variant

ReDim ArrayType(3)

ArrayType(1) = "My Name"
ArrayType(2) = "is"
ArrayType(3) = "Excel"

Cells(1, 1).Value = ArrayType(1)
Cells(1, 2).Value = ArrayType(2)
Cells(1, 3).Value = ArrayType(3)

End Sub

In this type of array data is Variant and the length is not determined here. After declaring the variable I have assigned the length of the array by using ReDim function. This array will insert the values like this Cell A1 = My Name, Cell B1 = is, Cell C1 = Excel.

One Dimensional Array

In this type of array, the length is determined but in one dimension it works.

Code:

Sub One_Dimensional()

Dim OneDimension(1 To 3) As String

OneDimension(1) = 40
OneDimension(2) = 50
OneDimension(3) = 15

End Sub

Show these value in VBA Message Box.

Code:

Sub One_Dimensional()

Dim OneDimension(1 To 3) As String

OneDimension(1) = 40
OneDimension(2) = 50
OneDimension(3) = 15

MsgBox OneDimension(1) & "," & OneDimension(2) & "," & OneDimension(3)

End Sub

Run this code using F5 key or manually and we will get the following result.

Two Dimensional Array

In this type of array, the length is determined in two dimensions and it works.

Code:

Sub Two_Dimensional()

Dim TwoDimension(1 To 2, 1 To 2) As Long
Dim i As Integer
Dim j As Integer

TwoDimension(1, 2) = 40
TwoDimension(2, 1) = 50
TwoDimension(1, 1) = 15
TwoDimension(2, 2) = 10

End Sub

Now to store these values to the cells below code.

Code:

Sub Two_Dimensional()

Dim TwoDimension(1 To 2, 1 To 2) As Long
Dim i As Integer
Dim j As Integer

TwoDimension(1, 2) = 40
TwoDimension(2, 1) = 50
TwoDimension(1, 1) = 15
TwoDimension(2, 2) = 10

For i = 1 To 2
    For j = 1 To 2
        Cells(i, j) = TwoDimension(i, j)
    Next  j
Next  i

End Sub

This will store the data like below.

Multi-Dimensional Array

In this type of array, the length is determined but in multi-dimension, it works.

Code:

Sub Multi_Dimensional()

Dim TwoDimension(1 To 3, 1 To 2) As Long
Dim i As Integer
Dim j As Integer

MultiDimension(1, 1) = 15
MultiDimension(1, 2) = 40
MultiDimension(2, 1) = 50
MultiDimension(2, 2) = 10
MultiDimension(3, 1) = 98
MultiDimension(3, 2) = 54

If you look at the above code firstly I have declared the array as 1 to 3 and then 1 to 2. This means when I am writing the array firstly I can use only 1 to 3 numbers but in the second space, I can use only 1 to 2 not 1 to 3.

Using Loop we can insert values in cells. I have used two loops for a multi-dimensional array.

Code:

Sub Multi_Dimensional()

Dim TwoDimension(1 To 3, 1 To 2) As Long
Dim i As Integer
Dim j As Integer

MultiDimension(1, 1) = 15
MultiDimension(1, 2) = 40
MultiDimension(2, 1) = 50
MultiDimension(2, 2) = 10
MultiDimension(3, 1) = 98
MultiDimension(3, 2) = 54

For i = 1 To 3
    For j = 1 To 2
        Cells (i, j) = MultiDimension (i,  j)
    Next j
Next i

End Sub

After running the code using F5 key or manually, we will get results as shown below.

Things to Remember

An array will count the values from zero, not from 1.
Array (0, 0) means first-row first column.
This excel macro file needs to be saved as a macro-enabled workbook.
In case of Dynamic array, we need to assign the value of array by using REDIM function in VBA.

Excel VBA Arrays

How to Use Excel VBA Arrays?

Example #1

Example #2

Types of Arrays in Excel

Static Array

Dynamic Array

One Dimensional Array

Two Dimensional Array

Multi-Dimensional Array

Things to Remember

Recommended Articles

About Us

Apps

Resources

Certification Courses