When I first met this class, I thought “Why? After all, there are simple arrays”. And then I tried it and I don’t know how I lived without it before.
Let’s start with an example
Let’s assume that on the active sheet in column 1 there is a list of employees’ full names.
Our task is to collect only unique full names in an array and sort it in descending order (well, such a slightly strange task). Let’s solve it first without using
ArrayListand at the end we compare the result.
To get unique values, create a function
GetDistinctItems and pass the column with the full name to it. In the function itself, we run through the loop
For Each for all full names and add unique ones to the object
Dictionary). Next method
Keys extracting elements into an additional function
DescendingSort (we use bubble sort) and get the sorted values into a variable
Sortedwhich we return as the result of the function.
Public Sub Main() Dim FullNameColumn As Range Set FullNameColumn = ActiveSheet.UsedRange.Columns(1) ' Получаем первый столбец. Dim DistinctList As Variant DistinctList = GetDistinctItems(FullNameColumn) ' Передаем диапазон в функцию. Debug.Print Join(DistinctList, vbCrLf) ' Выводим результат. End Sub Public Function GetDistinctItems(ByRef Range As Range) As Variant Dim Data As Variant: Data = Range.Value ' Преобразуем диапазон в массив. Dim Buffer As Object: Set Buffer = CreateObject("Scripting.Dictionary") ' Создаем объект Dictionary. Dim Item For Each Item In Data If Not Buffer.Exists(Item) Then Buffer.Add Item, Empty ' Проверяем наличие элемента и добавляем если отсутствует. Next Dim Sorted As Variant Sorted = DescendingSort(Buffer.Keys()) ' Сортируем функцией DescendingSort. GetDistinctItems = Sorted ' Возвращаем результат. End Function Public Function DescendingSort(ByRef Data As Variant) As Variant Dim i As Long For i = LBound(Data) To UBound(Data) - 1 Dim j As Long For j = i + 1 To UBound(Data) If Data(i) < Data(j) Then Dim Temp As Variant Temp = Data(j) Data(j) = Data(i) Data(i) = Temp End If Next Next DescendingSort = Data End Function
Trivially? Quite. Compact? Well, in general, yes, but in the end we will write even more compactly, and at the same time we will solve the problem of writing a new function, if suddenly the result needs to be sorted in ascending order.
What is it
Let’s begin with that
ArrayList this is a namespace class
mscorlibwhich implements the interface
IList. Naturally, in VBA it is somewhat cut in terms of methods, otherwise it could not be (for example, there are no methods
BinarySearch). However, you can (and should) work with it.
Basically, it’s a dynamic array. It does not need to be redefined by itself to change the dimension, it is enough to add elements using the method
Add. Somewhere I read that at a low level (may experts forgive me, I don’t know if I’m using this phrase correctly here) there are some nuances in terms of performance, but, frankly, for the entire time I used this object, I did not notice any problems and the time the macro ran because of it if and grows in general, it is not critical at all.
strength brother convenience?
At least in that it is a dynamic array. You just add elements through the method and you don’t need to bother with the topic
ReDim (and even more so
Preserve) and calculating the size of the future array.
And then the goodies begin.
First, we can unload all elements with one method
ToArray. As the name suggests, it converts all elements of an object into a regular array of type
Secondly, we can make a list of unique values by checking their presence with the method
Thirdly, you can forget about the function
UBoundbecause this class has a property
Countwhich, as you might guess, returns the number of elements placed in the object.
Fourth, it is possible to quickly sort the elements both in ascending order (method
Sort), and in descending order (first we use the method
Sortand after the method
Well, quickly run through the remaining properties:
Provides access to an element by its index.
Returns the index of the element. Required argument
StartFrom will help to find each subsequent index of the same elements.
Removes an element by index.
Removes the given element.
Removes a range of elements.
StartPosition points to the index of the first element,
Count by the number of elements in the range to be removed.
Removes all elements.
Adds an element at the given position.
Creates a copy of the object (essentially creates a new object rather than returning a reference to the current one).
How to create this miracle
Create class object
ArrayList can be done using the function
Dim List As Object Set List = CreateObject("System.Collections.ArrayList")
or through Tools -> Reference connect the library mscorlib.dlland then create as a regular object:
Dim List As New ArrayList
The disadvantage of both bindings is that you will not get the interface of the object. I personally don’t know the reason, but for some reason VBA in Excel (I didn’t check anywhere else) does not see the properties and methods of this class (in late binding, no object has them anyway, since the variable type
Objectbut in the early one it usually is).
You can, of course, get part of the interface by declaring a variable with the type
IList and after that assign it an instance
ArrayListbut by doing so, we will lose most of the functionality, for example, methods
Let’s go back to the beginning
Remember our example? I offer a solution with new knowledge.
Now we add unique values to the object
ArrayList), before checking the method
Contains the presence of the full name in the list of elements. At the end of the cycle, apply the method
Reverse to get a descending list. We unload the result by the method
ToArray. Agree, this time everything is much more compact.
Public Sub Main() Dim FullNameColumn As Range Set FullNameColumn = ActiveSheet.UsedRange.Columns(1) ' Получаем первый столбец. Dim DistinctList As Variant DistinctList = GetDistinctItems(FullNameColumn) ' Передаем диапазон в функцию. Debug.Print Join(DistinctList, vbCrLf) ' Выводим результат. End Sub Public Function GetDistinctItems(ByRef Range As Range) As Variant Dim Data As Variant: Data = Range.Value ' Преобразуем диапазон в массив. Dim Buffer As Object: Set Buffer = CreateObject("System.Collections.ArrayList") ' Создаем объект ArrayList. Dim Item For Each Item In Data If Not Buffer.Contains(Item) Then Buffer.Add Item ' Проверяем наличие элемента и добавляем если отсутствует. Next Buffer.Sort: Buffer.Reverse ' Сортируем по возрастанию, а потом переворачиваем (по убыванию). GetDistinctItems = Buffer.ToArray() ' Выгружаем в виде массива. End Function
What is the result
As a result, we have an advantage over the class
Collection in that there is a check for the presence of an element in the list (without dancing with a tambourine) and fast unloading as an array (without writing a loop).
in front of the class
Dictionaryperhaps, the advantage is that there is no need to prescribe keys (if they are not initially needed).
Well, both of the above lose in terms of sorting, adding elements by index, etc.
In general, a fairly easy-to-use class for working with one-dimensional arrays. Of course, get data from an object
Range much easier in a regular array, but if you need to create a new one (for example, in a loop), then, as for me,
ArrayList excellent option.
PS (problems, problems)
After writing the article, I noticed that my example on a clean PC does not work, it appears automation error -2146232576 when creating an ArrayList object.
Judging by this answer, mscorlib requires .NET Framework 3.5 to be enabled.
You can do this through Control Panel -> Programs -> Enable or disable Windows components -> check the box next to .NET Framework 3.5 (includes .NET 2.0 and 3.0) after which the necessary files for the operation of the component will be downloaded to the PC.
Necessarily After the done actions, restart Excel. I got an error during installation. Fixed by turning off Excel and re-enabling the component.
By the way, there were no such problems on my working PC; this component was already connected by the organization (or by default in early Windows, I don’t know for sure).
Thank you for reading to the end.
How about using this class? Write in the comments!
Also, subscribe to my telegrams.