WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA ArrayList

VBA ArrayList

In vba we have built in collections of data types, functions variables and other important statements but similar to this we have array list in VBA in which a user can modify and put own collections of variables and user defined functions in an array, there are certain keywords for the array list to design it.

Excel VBA ArrayList

VBA ArrayList is a kind of data structure we use in VBA to store the data. ArrayList in Excel VBA is a class used to create an array of values. This, unlike traditional arrays, where those arrays have a fixed length, but Array List doesn’t any fixed length.

VAB ArrayList is not part of the VBA list; rather, it is an external library or object which we need to set the reference before we start accessing it.

Arrays in VBA are an integral part of any coding language. By using arrays in excel, we can store data with a single variable name by declaring the “lower limit & upper limit.”

With regular arrays, we need to decide the lower limit and upper limit of the array. We need to decide well in advance at the time of declaring the variable in the case of Static Arrays, and in the case of Dynamic Arrays, we need to decide the length of the array after declaring the array by using the “ReDim” statement in VBA.

However, we have one more option where we can store the “N” number of values without declaring the lower limit and upper limit. In this article, we will show you about that option i.e., “VBA ArrayList.”

To set the reference to VBA ArrayList object to follow the below steps.

Step 1: Go to Tools > References.

Reference step 1

Step 2: Object library reference window will appear in front of you. Select the option “mscorlib.dll.”

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Step 3: Click on OK too. Now we can access the VBA ArrayList.

Reference step 2

Examples of VBA ArrayList in Excel

Below are the examples of Excel VBA ArrayList.

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

Example #1 – Create Instance of VBA ArrayList

Since Excel VBA ArrayList is an external object, we need to create an instance to start using this. To create an instance, follow the below steps.

Step 1: Declare the variable as “ArrayList.”

Code:

Sub ArrayList_Example1()

   Dim ArrayValues As ArrayList

End Sub

VBA ArrayList Example 1

Step 2: Since the Array List is an object, we need to create a new instance.

Code:

Sub ArrayList_Example1()

  Dim ArrayValues As ArrayList

  Set ArrayValues = New ArrayList

End Sub

VBA ArrayList Example 1-1

Step 3: Now, we can keep storing values to the array variable by using the “Add” method. In the below image, I have added three values.

Code:

Sub ArrayList_Example1()

  Dim ArrayValues As ArrayList

  Set ArrayValues = New ArrayList

  ArrayValues.Add "Hello" 'First Value
  ArrayValues.Add "Good" 'Second Value
  ArrayValues.Add "Morning" 'Three Value

End Sub

VBA ArrayList Example 1-2

Now we have assigned three values, how do we identify which is the first one and how can we show up the values or use them to our needs.

If you remember traditional array type, we refer the first array value like this “ArrayName(0)”

Similarly, we can use the same technique here, as well.

ArrayValue(0) = “Hello”
ArrayValue(1) = “Good”
ArrayValue(2) = “Morning”

Let show this in the message box.

Code:

Sub ArrayList_Example1()

  Dim ArrayValues As ArrayList

  Set ArrayValues = New ArrayList

  ArrayValues.Add "Hello" 'First Value
  ArrayValues.Add "Good" 'Second Value
  ArrayValues.Add "Morning" 'Three Value

  MsgBox ArrayValues(0) & vbNewLine & ArrayValues(1) & vbNewLine & ArrayValues(2)

End Sub

Example 1-3

Now run the code using the F5 key or manually then, we will see “Hello,” “Good,” and “Morning” in the VBA message box.

VBA ArrayList Example 1-4

Like this, we can store any number of values with Array List Object.

Example #2 – Store Values to Cells Using VBA ArrayList

Let’s see the example of storing the assigned values to the cells in the worksheet. Now, look at the below VBA code.

Code:

Sub ArrayList_Example2()

  Dim MobileNames As ArrayList, MobilePrice As ArrayList
  Dim i As Integer
  Dim k As Integer

  Set MobileNames = New ArrayList

  'Names of the mobile
   MobileNames.Add "Redmi"
   MobileNames.Add "Samsung"
   MobileNames.Add "Oppo"
   MobileNames.Add "VIVO"
   MobileNames.Add "LG"

   Set MobilePrice = New ArrayList

   MobilePrice.Add 14500
   MobilePrice.Add 25000
   MobilePrice.Add 18500
   MobilePrice.Add 17500
   MobilePrice.Add 17800

End Sub

Example 2

With two array lists, I have stored Names of the Mobile and Prices of the Mobile. Now we need to insert these values to the worksheet for this. We need to use loops. The below loop will do the job for me.

Example 2-1

Below is the Overall code to store values to the worksheet.

Code:

Sub ArrayList_Example2()

  Dim MobileNames As ArrayList, MobilePrice As ArrayList
  Dim i As Integer
  Dim k As Integer

  Set MobileNames = New ArrayList

 'Names of the mobile
  MobileNames.Add "Redmi"
  MobileNames.Add "Samsung"
  MobileNames.Add "Oppo"
  MobileNames.Add "VIVO"
  MobileNames.Add "LG"
 
  Set MobilePrice = New ArrayList

  MobilePrice.Add 14500
  MobilePrice.Add 25000
  MobilePrice.Add 18500
  MobilePrice.Add 17500
  MobilePrice.Add 17800

  k = 0

  For i = 1 To 5
  Cells(i, 1).Value = MobileNames(k)
  Cells(i, 2).Value = MobilePrice(k)
  k = k + 1
  Next i

End Sub

When we run the code manually or using the F5 key then, we will get the below result.

VBA ArrayList Example 2-2

Recommended Articles

This has been a guide to VBA ArrayList. Here we learn how to create ArrayList in VBA, which is used to store data along with simple to advanced examples. Below are some useful excel articles related to VBA –

  • Excel VBA Debug Print
  • VBA UCase
  • Text Box in VBA
  • Excel VBA Declare Array
1 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

WallStreetMojo

Download VBA ArrayList Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More