EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

VBA Arrays

By Ashwani JaiswalAshwani Jaiswal

Home » VBA » Blog » VBA Array Functions » VBA Arrays

VBA Arrays in Excel

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

VBA Arrays Example 1-1

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

Example 1-2

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 the F5 key or manually to see results.

Example 1-3

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

Example 2-1

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

Example 2-2

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

Example 2-3

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 the F5 key or manually, we will get results as shown below.

Example 2-4

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

Static Arrays Example 1-1

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 the F5 key or manually, we will get results as shown below.

  Static Arrays Example 1-2

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

Dynamic Arrays Example 1

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 the ReDim function. This array will insert the values like this Cell A1 = My Name, Cell B1 = is Cell C1 = Excel.

Dynamic Arrays Example 1-2

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

One Dimensional Array 1

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

One Dimensional Array 2

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

One Dimensional Array 3

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

Two Dimensional Array 1

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

Two Dimensional Array 2

This will store the data like below.

Two Dimensional Array 3

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

Multi Dimensional Array 1

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

Multi Dimensional Array 2

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

Multi Dimensional Array 3

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 the case of a Dynamic array, we need to assign the value of the array by using the REDIM function in VBA.

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. VBA Function
  2. VBA Range Object
  3. VBA VLOOKUP
  4. VBA Web Scraping

All in One Software Development Bundle (600+ Courses, 50+ projects)

600+ Online Courses

50+ projects

3000+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
VBA Array Functions
  • VBA Arrays
    • VBA Arrays
    • VBA ReDim
    • VBA Dim
    • VBA Sort
    • VBA Array Length
    • VBA ArrayList
    • VBA UBound
    • VBA Join
    • VBA Collection
    • VBA Declare Array
    • VBA ReDim Array
    • VBA Array Length
    • VBA Dynamic Array
    • VBA Filter
    • VBA Lbound
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) Learn More