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 VBAArray Variable is a variable which stores multiple values in a single variable. To use an array variable in VBA, you must first define it. You can define the array variable with or without its length.read more are an integral part of any coding language. By using arrays in excelArray formulas are extremely helpful and powerful formulas that are used in Excel to execute some of the most complex calculations. There are two types of array formulas: one that returns a single result and the other that returns multiple results.read more, 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.
- Go to Tools > References.
- Object library reference window will appear in front of you. Select the option “mscorlib.dll.”
- Click on OK too. Now we can access the VBA ArrayList.
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
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
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 ValueEnd Sub
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
Now run the code using the F5 key or manually then, we will see “Hello,” “Good,” and “Morning” in the VBA message boxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.
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 codeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.
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
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.
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.
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 –
- 3 Courses
- 12 Hands-on Projects
- 43+ Hours
- Full Lifetime Access
- Certificate of Completion
How to create list from range in Excel?
You may need to create a list by a range of data frequently, for instance, a range of data on 24 solar terms as shown as below. Of course, you can copy and paste them into a list manually, but it seems somewhat tedious if there are many columns in the range. Actually, there are a couple of tricks to create a list from specified range in Microsoft Excel easily.
Create list from range with VBA
Create list from range with Kutools for Excel
Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Read More...Free Download...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Read More...Free Download...
Create list from range with VBA
In Excel, you also can use VBA code to quickly import a list or range.
1. Hold ALT button and press F11 on the keyboard to open a Microsoft Visual Basic for Application window.
2. Click Insert > Module, and copy the VBA into the module.
VBA: Create list from rangeSub UniqueList() 'Updateby20140304 Dim InputRng As Range, OutRng As Range xTitleId = "KutoolsforExcel" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Range:", xTitleId, InputRng.Address, Type:=8) Set OutRng = Application.InputBox("OutPut to (single cell):", xTitleId, Type:=8) For i = 1 To InputRng.Rows.Count For j = 1 To InputRng.Columns.Count OutRng.Value = InputRng.Cells(i, j).Value Set OutRng = OutRng.Offset(1, 0) Next Next End Sub
3. Click Run button or press F5 to run the VBA. A dialog displayed on the screen, and select the range of data that you will create a list with. See screenshot:
5. Click OK, select a cell to output the list from the pop-up dialog, then click OK. See screenshot:
Then the range of data has been created as a list in the selected cell.
Create list from range with Kutools for Excel
The handy tool – Kutools for Excel's Transform Range function also can quickly and easily create a list from a range data.
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Get it Now
1. Select the data range you want to create it as a list, and click Kutools > Range Converter > Transform Range. See screenshot:
2. In the pop-up Transform Range dialog, check Range to single column, and click OK, another dialog displayed for you to select a blank cell to output the list, click OK. See screenshot:
3. Then the list is created.
Click here for more information about Transform Ranges.
A list box is a list from where a user can select an item. To create a list box in Excel VBA, execute the following steps.
1. On the Developer tab, click Insert.
2. In the ActiveX Controls group, click List Box.
3. Drag a list box on your worksheet.
Note: you can change the name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. For now, we will leave ListBox1 as the name of the list box.
Create a Workbook Open Event. Code added to the Workbook Open Event will be executed by Excel VBA when you open the workbook.
4. Open the Visual Basic Editor.
5. Double click on This Workbook in the Project Explorer.
6. Choose Workbook from the left drop-down list and choose Open from the right drop-down list.
7. To add items to the list box, add the following code lines to the Workbook Open Event:
.AddItem "New York"
Note: use Sheet2 if your list box is located on the second worksheet, Sheet3 if your list box is located on the third worksheet, etc. If you use these code lines outside the Workbook Open event, you might want to add the following code line before these code lines. This code line clears the list box. This way your items won't be added multiple times if you execute your code more than once.
8. To link this list box to a cell, right click on the list box (make sure design mode is selected) and click on Properties. Fill in D3 for LinkedCell.
Note: also see the ListFillRange property to fill a list box with a range of cells.
9. Save, close and reopen the Excel file.
Although in some situations it can be useful to directly place a list box on your worksheet, a list box is particularly useful when placed on a Userform.
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.
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.
- 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.
- 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.
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.
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.
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
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 ArrayListEnd Sub
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 ArrayListSet alist = New ArrayList End Sub
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 ArrayListSet 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
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 ArrayListSet 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
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.
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’
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
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 ArrayListEnd Sub
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 ArrayListSet arraysort = New ArrayList End Sub
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 ArrayListSet arraysort = New ArrayList arraysort.Add "13" arraysort.Add "21" arraysort.Add "67" arraysort.Add "10" arraysort.Add "12" arraysort.Add "45" End Sub
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 ArrayListSet 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
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.
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 ArrayListSet 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
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.
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 ArrayListSet 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
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.
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.
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 –
- VBA Arrays
- VBA Sort
- VBA XML
- VBA Month
Create list vba
The VBA ArrayList is also a very useful data structure if you want to work with dynamic VBA arrays but don’t want the hassle of having to constantly redefine (Redim) the size of the array. ArrayLists don’t have a fixed size so you can keep adding items to it. However, in VBA in can be better superseded by the Native VBA Collection.
VBA ArrayList example
Below is an example of creating a VBA ArrayList and adding an item:Dim arrList as Object Set arrList = CreateObject("System.Collections.ArrayList") 'Create the ArrayList arrList.Add "Hello" 'Adding items to an ArrayList arrList.Add "You" arrList.Add "There" arrList.Add "Man" 'Get number of items Debug.Print arrList.Count 'Result: 3 For Each item In arrList Debug.Print item Next Item 'Result: Hello, You, There, Man
You can remove all items of a particular value from a VBA ArrayList by using Remove:Dim arrList as Object Set arrList = CreateObject("System.Collections.ArrayList") 'Create the ArrayList arrList.Add "Hello" 'Add "Hello" arrList.Add "You" 'Add "You" arrList.Remove "You" 'Remove "You" For Each item In arrList Debug.Print item Next Item 'Result: Hello
To remove items at a particular index use RemoveAt instead. Remember that indexing starts at 0.Dim arrList as Object Set arrList = CreateObject("System.Collections.ArrayList") 'Create the ArrayList arrList.Add "Hello" 'Add "Hello" arrList.Add "You" 'Add "You" arrList.Add "There" 'Add "There" arrList.RemoveAt (0) For Each item In arrList Debug.Print item Next Item 'Result: You, There
Get and Count items
To get an item at a particular index using the Item property. Remember that indexing in the VBA ArrayList starts at 0.
To count the number of items in the VBA ArrayList simply use the Count property:Dim arrList as Object Set arrList = CreateObject("System.Collections.ArrayList") 'Create the ArrayList arrList.Add "Hello" 'Add "Hello" arrList.Add "You" 'Add "You" Debug.Print arrList.Count 'Result: 2 Debug.Print arrList.Item(1) 'Result: You
To clear a VBA ArrayList simply use Clear:Dim arrList as Object Set arrList = CreateObject("System.Collections.ArrayList") 'Create the ArrayList arrList.Add "Hello" 'Add "Hello" arrList.Add "You" 'Add "You" arrList.Clear Debug.Print arrList.Count 'Result: 0
On a regular basis use rather the VBA Collectioninstead of the VBA ArrayList
You can declare an array to work with a set of values of the same data type. An array is a single variable with many compartments to store values, while a typical variable has only one storage compartment in which it can store only one value. Refer to the array as a whole when you want to refer to all the values it holds, or you can refer to its individual elements.
For example, to store daily expenses for each day of the year, you can declare one array variable with 365 elements, rather than declaring 365 variables. Each element in an array contains one value. The following statement declares the array variable with 365 elements. By default, an array is indexed beginning with zero, so the upper bound of the array is 364 rather than 365.
To set the value of an individual element, you specify the element's index. The following example assigns an initial value of 20 to each element in the array.
Changing the lower bound
You can use the Option Base statement at the top of a module to change the default index of the first element from 0 to 1. In the following example, the Option Base statement changes the index for the first element, and the Dim statement declares the array variable with 365 elements.
You can also explicitly set the lower bound of an array by using a To clause, as shown in the following example.
Storing Variant values in arrays
There are two ways to create arrays of Variant values. One way is to declare an array of Variant data type, as shown in the following example:
The other way is to assign the array returned by the Array function to a Variant variable, as shown in the following example.
You identify the elements in an array of Variant values by index, no matter which technique you use to create the array. For example, the following statement can be added to either of the preceding examples.
Using multidimensional arrays
In Visual Basic, you can declare arrays with up to 60 dimensions. For example, the following statement declares a 2-dimensional, 5-by-10 array.
If you think of the array as a matrix, the first argument represents the rows and the second argument represents the columns.
Use nested For...Next statements to process multidimensional arrays. The following procedure fills a two-dimensional array with Single values.
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
- Steam vr workshop
- Project panel premiere
- Warp core pickups
- Updating opencore
- Supreme comic 1
- Alestorm t shirts
- Napt ip
- Handsome country singers
A couple of times the woman caught my eyes and smiled faintly. Damn, is she flirting with me. Or it seems to me it's all from excitement. The woman was 10 years older than me, well, how could she be interested in a puny 20-year-old student.