VBA Arrays

Excel VBA Arrays

Excel VBA Array is nothing but a variable which can hold the same data type. An Array is a group of a variable which can store more than one variable. Variable name will be the same but can hold different values in a single variable. A VBA array is a type of variable which is used to store lists of data of the same type.

If we have 5 cells which hold numbers we need to declare 5 variables to hold 5 different numbers in the range. But by using array we can hold 5 different values in a single variable itself.

How to Use Excel VBA Arrays?

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

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 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.

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

Recommended Articles

This has been a guide to VBA Arrays. Here we discussed Types of Arrays in VBA and how to use Excel VBA Arrays along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

1. FIND Function in Excel with Examples
2. What is VBA Function in Excel?
3. Guide to VBA Range Object
4. How to use VBA VLOOKUP Function?