• Skip to primary navigation
  • Skip to content
  • Skip to primary sidebar
  • Skip to footer
EDUCBA

EDUCBA

MENUMENU
  • Resources
        • Excel Charts

          • Histogram Chart Excel
          • Basic Excel Formulas
          • Text to Columns in Excel
        • Excel Charts
        • Excel Tips

          • Excel Gantt Chart
          • IFERROR with VLOOKUP
          • Data Table in Excel
        • Excel Tips
        • Excel Tools in Excel

          • Stacked Column Chart
          • Cheat Sheet of Excel Formulas
          • Excel Data Validation
        • Histogram chart in excel
        • Others

          • Resources (A-Z)
          • Excel Functions
          • Financial Functions in Excel
          • Logical Functions in Excel
          • Lookup Reference Functions in Excel
          • Maths Function in Excel
          • TEXT and String Functions in Excel
          • View All
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course 1
        • All in One Bundle

          All-in-One-Excel-VBA-Bundle
        • Excel course

          Excel-Training
        • Others

          • Excel advanced course
          • VBA Course
          • Excel Data Analysis Course
          • Excel for Marketing Course
          • Excel for Finance Course
          • View All
  • 120+ Courses All in One Bundle
  • Login

VBA ArrayList

Home » Excel » Blog » VBA » VBA ArrayList

VBA ArrayList

Excel VBA ArrayList

Data structures are used to store a series of data in programming languages. It binds to the memory rather than address. An ArrayList is one of the data structures in excel. Comparing to normal arrays in excel ArrayList is dynamic. Therefore, no initial declaration of size is needed. ArrayList is not a part of VBA it is associated with an external library which can be used with VBA.

ArrayList can be defined as a list of a nearby memory location. Where the values are retrieved using the index numbers. The list starts from an index number ‘0’, the first element will be inserted into the ‘0’ index and rest is followed by 1, 2, 3, etc. ArrayList offers plenty of built-in operations, sorting, adding, removing, reversing, etc. are some among them.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Adding the Library

To use the ArrayList into the VBA it needs to include the library ‘mscorlib.dll’ which comes with .NET framework.

  • Press F11 or right-click the sheet name to get the code window. Go to the VBA code window, from the main menu select Tools.

VBA ArrayList Example 1-1

  • The tools menu contains ‘references’ option and it consists of a list of libraries which supports VBA for including different objects. Click on the Reference option.

VBA ArrayList Example 1-2

  • It will lead you to a window with a list of different libraries which supports in VBA and Excel. Scroll down to find the ‘dll’. Tick mark to confirm the selection then press ‘OK’ button.

VBA ArrayList Example 1-3

Now the library is included in your VBA code and it will support different methods associated with an ArrayList.

How to Create VBA ArrayList in Excel?

Below are the different examples to create VBA ArrayList in Excel.

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

Excel VBA ArrayList – Example #1

How to Add Values to the ArrayList using VBA?

ArrayList act as a list where we can add values. This will automatically store in the different portions starting from 0,1, 2, etc. The values can add or insert to the ArrayList using the add method.

Popular Course in this category
Cyber Week Sale
VBA Training (3 Courses, 12+ Projects) 3 Online Courses | 13 Hands-on Projects | 45+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.7 (2,738 ratings)
Course Price

View Course

Related Courses
Excel Advanced Training (14 Courses, 21+ Projects)Excel Data Analysis Training (12 Courses, 6+ Projects)

In this example, you will learn how to add a list of values into an ArrayList. Follow the below steps to add ArrayList using VBA Code in excel.

Step 1: To add a list of values to an ArrayList create a function arraylist1.

Code:

Private Sub arraylist1()

End Sub

VBA ArrayList Example 1-4

Step 2: Now we want to include the ArrayList into the function as an object where a list is declared as an ArrayList.

Code:

Private Sub arraylist1()

Dim alist As ArrayList

End Sub

VBA ArrayList Example 1-5

Step 3: Since this is an object to use it, you have to create an instance of the ArrayList. Set a new instance for this object.

Code:

Private Sub arraylist1()

Dim alist As ArrayList
Set alist = New ArrayList

End Sub

VBA ArrayList Example 1-6

Step 4: Now using the ‘Add’ property of an ArrayList adds the values to the ArrayList. Where the list is added into the index values in an order 0,1,2,3 etc.

Code:

Private Sub arraylist1()

Dim alist As ArrayList
Set alist = New ArrayList
alist.Add "192" 'index(0)
alist.Add "168" 'index(1)
alist.Add "1" 'index(2)
alist.Add "240" 'index(3)

End Sub

VBA ArrayList Example 1-7

Step 5: To check whether the values got added into the list, let’s print the array values using a message box. To print the values each index is printed since the values are stored in these partitions.

Code:

Private Sub arraylist1()

Dim alist As ArrayList
Set alist = New ArrayList
alist.Add "192" 'index(0)
alist.Add "168" 'index(1)
alist.Add "1" 'index(2)
alist.Add "240" 'index(3)
MsgBox ("\\" & alist(0) & "." & alist(1) & "." & alist(2) & "." & alist(3))

End Sub

VBA ArrayList Example 1-8

Step 6: Press F5 or run button to run the program and the values will be printed as below. Here an IP address is stored in the ArrayList and while printing the values extra notations are concatenated to form the IP address in a proper format.

Result of Example 1-9

Automation error in VBA

It is a common error happens while running an ArrayList. An automation error may encounter ‘Run-time Error ‘-2146232576 (80131700) Automation Error’

Automation Error

This is because of not the correct version of the .NET framework installed. To work with ArrayList you must have minimum .NET 3.5 or the higher versions of .NET framework.

Excel VBA ArrayList – Example #2

Sorting ArrayList Using VBA Code 

ArrayList supports different functions like sorting, reversing, etc. this help to sort the values inserted into an ArrayList. Once you add a list into the ArrayList it is possible to reverse the inserted list.

Follow the below steps to sort the ArrayList using VBA Code:

Step 1: Create a function called arraysort1 to perform the sorting within the inserted values into an ArrayList.

Code:

Sub arraysort1()

End Sub

VBA ArrayList Example 2-1

Step 2: Declare an object ‘arraysort’ of the ArrayList. Use this object to add and sort the values within the ArrayList.

Code:

Sub arraysort1()

Dim arraysort As ArrayList

End Sub

VBA ArrayList Example 2-2

Step 3: Similar to the first example need to create a new instance of the declared object. Set this instance as a new ArrayList.

Code:

Sub arraysort1()

Dim arraysort As ArrayList
Set arraysort = New ArrayList

End Sub

VBA ArrayList Example 2-3

Step 4: Now using the ‘Add’ method insert the elements to the ArrayList. Which is not possessing any order on values. Randomly inserted some values into the list.

Code:

Sub arraysort1()

Dim arraysort As ArrayList
Set arraysort = New ArrayList
arraysort.Add "13"
arraysort.Add "21"
arraysort.Add "67"
arraysort.Add "10"
arraysort.Add "12"
arraysort.Add "45"

End Sub

VBA ArrayList Example 2-4

Step 5: To note the difference in the ArrayList, let’s print the ArrayList after inserting the values and before sorting it.

Code:

Sub arraysort1()

Dim arraysort As ArrayList
Set arraysort = New ArrayList
arraysort.Add "13"
arraysort.Add "21"
arraysort.Add "67"
arraysort.Add "10"
arraysort.Add "12"
arraysort.Add "45"

MsgBox (arraysort(0) & vbCrLf & arraysort(1) _
& vbCrLf & arraysort(2) & vbCrLf & arraysort(3) _
& vbCrLf & arraysort(4) & vbCrLf & arraysort(5))

End Sub

VBA ArrayList Example 2-5

Step 6: Press F5 on the keyboard or run button on the code window to run the program to print the ArrayList. The ArrayList is printed in the same order as it is inserted since we use the index numbers in its correct order.

Result of Example 2-6

Step 7: Now to this list apply the sort property of the ArrayList. Use the sort method to sort the inserted list. The sort property will sort the list of values in ascending order by default.

Code:

Sub arraysort1()

Dim arraysort As ArrayList
Set arraysort = New ArrayList
arraysort.Add "13"
arraysort.Add "21"
arraysort.Add "67"
arraysort.Add "10"
arraysort.Add "12"
arraysort.Add "45"

arraysort.Sort

MsgBox (arraysort(0) & vbCrLf & arraysort(1) _
& vbCrLf & arraysort(2) & vbCrLf & arraysort(3) _
& vbCrLf & arraysort(4) & vbCrLf & arraysort(5))

End Sub

VBA ArrayList Example 2-7

Step 8: Hit F5 or Run button under VBE to run this code, Where the values are sorted and printed in order from smallest value to largest.

Result of Example 2-8

Excel VBA ArrayList – Example #3

Reversing the ArrayList using VBA Code

When you want to reverse the order of inserted values in an ArrayList reverse method is available. This will reverse the order of the list from its current order. Now we have already sorted the ArrayList in the previous example, which is in ascending order.

Let’s try to reverse the sorted array to make it descending order. Use the reverse method of ArrayList to do this.

Code:

Sub arraysort2()

Dim arraysort As ArrayList
Set arraysort = New ArrayList
arraysort.Add "13"
arraysort.Add "21"
arraysort.Add "67"
arraysort.Add "10"
arraysort.Add "12"
arraysort.Add "45"
arraysort.Sort

arraysort.Reverse

MsgBox (arraysort(0) & vbCrLf & arraysort(1) _
& vbCrLf & arraysort(2) & vbCrLf & arraysort(3) _
& vbCrLf & arraysort(4) & vbCrLf & arraysort(5))

End Sub

ArraySort.Reverse Example 3-1

After applying the reverse method, the ArrayList will become in descending order and use the message box to print the reversed array. The sorted list is changed from large value to small value.

Result of Example 3-2

Things to Remember

  • ArrayList is dynamic in nature; it does not require re-initialization.
  • Different built-in methods are associated with ArrayList.
  • Compared to the array, ArrayList is easy to use in Excel VBA.
  • The supporting .NET libraries should be included in the VBA to work with ArrayList.
  • ArrayList is a continuing memory location which identified using index values.

Recommended Articles

This is a guide to VBA ArrayList. Here we discuss how to create ArrayList in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. Guide to Using VBA Arrays
  2. Excel Sort By Number
  3. How to Sort in Excel Using VBA?
  4. Tutorials on Sorting in Excel

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar
Excel Functions Tutorials
  • VBA
    • VBA Max
    • VBA Environ
    • VBA Not
    • VBA LOOKUP
    • Programming in Excel
    • VBA Name Worksheet
    • VBA ScreenUpdating
    • VBA Do Loop
    • VBA Declare Array
    • VBA Macros
    • VBA Delete Sheet
    • VBA Editor
    • VBA Error Handling
    • VBA Goal Seek
    • VBA DateDiff
    • VBA On Error Resume Next
    • VBA Examples
    • VBA Counter
    • VBA Val
    • VBA Me
    • VBA Double
    • VBA Variable Types
    • VBA FreeFile
    • VBA Switch Case
    • VBA Unprotect Sheet
    • VBA Format Number
    • VBA ReDim
    • VBA IF Statements
    • VBA DoEvents
    • VBA Public Variable
    • VBA Columns
    • VBA Cdate
    • VBA Selection Range
    • VBA Hide Columns
    • VBA COUNTA
    • VBA Insert Row
    • VBA Protect Sheet
    • VBA AutoFill
    • VBA IF Not
    • VBA GetObject
    • VBA Debug Print
    • VBA Collection
    • VBA Text
    • VBA Randomize
    • VBA Variant
    • VBA Selection
    • VBA Right
    • VBA Date Format
    • VBA Rename Sheet
    • VBA Paste Values
    • VBA InputBox
    • VBA Conditional Formatting
    • VBA Pause
    • VBA Pivot Table
    • VBA Message Box
    • VBA OFFSET
    • VBA ByRef
    • FileCopy in VBA
    • VBA StrConv
    • VBA SUB
    • VBA Boolean
    • VBA Global Variables
    • VBA Worksheets
    • VBA IIF
    • VBA Close UserForm
    • VBA Variable Declaration
    • VBA Join
    • VBA Dictionary
    • VBA Operators
    • VBA Charts
    • VBA For Each Loop
    • VBA FileDialog
    • VBA Format
    • VBA MID
    • VBA GetOpenFileName
    • VBA DatePart
    • VBA Code
    • VBA Option Explicit
    • VBA FileSystemObject (FSO)
    • VBA ASC
    • VBA UserForm
    • VBA Cells
    • VBA InStrRev
    • VBA Class Module
    • VBA Constants
    • VBA Length of String
    • VBA DateSerial
    • VBA StrComp
    • VBA Array Length
    • VBA Check File Exists
    • VBA Ubound
    • VBA CDBL
    • VBA Activate Sheet
    • VBA DateValue
    • VBA Borders
    • VBA Comment
    • VBA Intersect
    • VBA Workbook
    • VBA Concatenate
    • VBA Name
    • VBA Find and Replace
    • VBA Tutorial
    • VBA CSTR
    • VBA Save As
    • VBA Hyperlink
    • VBA Print
    • VBA Switch
    • VBA END
    • VBA Like
    • VBA Set
    • VBA excel programming
    • VBA Call Sub
    • VBA Object
    • VBA RoundUp
    • VBA ArrayList
    • VBA Named Range
    • VBA PowerPoint
    • VBA Block Comment
    • VBA OverFlow Error
    • VBA Insert Column
    • VBA Lcase
    • VBA List Box
    • VBA Delete File
    • VBA Clear Contents
    • VBA TextBox
    • VBA Font Color
    • VBA Range Cells
    • VBA INT
    • VBA UCASE
    • VBA Value
    • VBA Remove Duplicates
    • VBA Break for Loop
    • VBA Sleep
    • VBA Do Until Loop
    • VBA Union
    • VBA Long
    • VBA Copy Paste
    • VBA Data Types
    • VBA Delete Column
    • VBA Enum
    • VBA IsEmpty
    • VBA 1004 Error
    • VBA RegEx
    • VBA IsNumeric
    • VBA Paste
    • VBA Transpose
    • VBA Left
    • VBA Delete Row
    • VBA Integer
    • VBA Active Cell
    • VBA InStr
    • VBA Round
    • VBA Subscript out of Range
    • VBA Dim
    • VBA Replace
    • VBA Sort
    • VBA String
    • VBA Split
    • VBA Wait
    • VBA MOD
    • VBA Time
    • VBA TIMER
    • VBA While Loop
    • VBA Date
    • Excel VBA MsgBox
    • VBA IFError
    • VBA Color Index
    • VBA Match
    • VBA Case
    • VBA Arrays
    • VBA GoTo
    • VBA On Error
    • VBA Range
    • VBA Do While Loop
    • VBA Number Format
    • VBA Loops
    • VBA TRIM
    • VBA Find
    • VBA Select Case
    • VBA Else If
  • Excel Functions (10+)
  • Excel Tools (97+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (12+)
  • Lookup Reference Functions in Excel (30+)
  • Maths Function in Excel (39+)
  • TEXT and String Functions in Excel (25+)
  • Date and Time Function in Excel (20+)
  • Statistical Functions in Excel (55+)
  • Information Functions in Excel (4+)
  • Excel Charts (44+)
  • Excel Tips (195+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (14+)
  • Excel Formula and Functions (20+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • VBA Training
  • Excel Advanced Training
  • Excel Data Analysis Training
Footer
About Us
  • Who is EDUCBA?
  • Sign Up
  •  
Free Courses
  • Free Online Excel Course
  • Free Vba Course
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
Resources
  • Resources (A To Z)
  • Excel Charts
  • Excel Tips
  • Excel Tools in Excel
  • Excel Functions
  • Financial Functions in Excel
  • Logical Functions in Excel
  • Lookup Reference Functions in Excel
  • Maths Function in Excel
  • TEXT and String Functions in Excel
  • Date and Time Function in Excel
  • Statistical Functions in Excel
  • Information Functions in Excel
Apps
  • iPhone & iPad
  • Android
Support
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions

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

EDUCBA

Download VBA ArrayList Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Let’s Get Started
Please provide your Email ID
Email ID is incorrect

Limited Period Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More

Limited Period Offer - Limited Period Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More