Всё про коллекции в VBA

Автор Дмитрий Якушев На чтение24 мин. Просмотров1.8k.

Леонард Лаудер

Я не строитель зданий, я строитель коллекций

Содержание

1. Краткое руководство по коллекциям
2. Введение
3. Что такое коллекция?
4. Коллекции против Массивов
5. Пример: когда Массив лучше
6. Пример: когда Коллекция лучше
7. Еще одно преимущество коллекций
8. Недостаток коллекций
9. Как создать коллекцию
10. Незначительная разница между этими методами
11. Удаление всех элементов из коллекции
12. Добавление предметов в коллекцию
13. Before и After
14. Доступ к элементам коллекции
15. Элементы в коллекции доступны только для чтения?
16. Добавление разных типов
17. Добавление элементов с помощью ключа
18. Когда использовать ключи
19. Недостаток использования ключей в коллекциях
20. Доступ ко всем элементам в коллекции
21. Использование цикла For
22. Использование цикла For Each
23. For Each против For
24. Скорость
25. Аккуратнее
26. Порядок
27. Сортировка коллекции
28. Использование коллекций с функциями и подпрограммами
29. Передача Коллекции в Sub / Function
30. Передача ByVal против ByRef
31. Возврат коллекции из функции
32. Заключение

Краткое руководство по коллекциям

Задача	Пример
Объявить	Dim coll As Collection
Создать во время выполнения	Set coll = New Collection
Объявить и создать	Dim coll As New Collection
Добавить элемент	coll.Add «Яблоко»
Доступ к элементу	coll(1) or coll(2)
Элемент доступа добавлен в первый раз	coll(1)
Доступ к элементу добавлен в последний раз	coll(coll.Count)
Получить количество предметов	coll.Count
Доступ ко всем предметам	Dim i As Long For i = 1 To coll.Count    Debug.Print coll(i)  Next i
Доступ ко всем предметам (For Each)	Dim fruit As Variant For Each fruit In coll    Debug.Print fruit  Next fruit
Удалить элемент	coll.Remove(1)
Удалить все элементы	Set coll = New Collection

Введение

Коллекции являются очень важной частью VBA. Если вы пользовались VBA какое-то время, вы будете использовать Коллекции. Наиболее распространенными из них являются
Workbooks, Worksheets, коллекции Range и Cells.

В следующем коде показаны некоторые примеры использования коллекции Workbooks VBA.

1 2 3 4 5 6 7 8 9 10

' Workbooks это коллекция всех открытых рабочих книг       ' Count - это количество книг в коллекции.     Debug.Print Workbooks.Count       ' Напечатайте полное имя книги с именем Пример.xlsm     Debug.Print Workbooks("Пример.xlsm").FullName       ' Напечатайте полное название книги, которая была открыта второй     Debug.Print Workbooks(2).FullName

Коллекции похожи на массивы, поэтому важно понимать, что они из себя представляют и чем они отличаются от массивов.

Что такое коллекция?

Коллекции и массивы используются для группировки переменных. Они оба хранят набор похожих предметов, например список студенческих оценок или названий стран. Использование коллекции или массива позволяет быстро и легко манипулировать большим количеством предметов.

В своей статье о массивах я объяснил простым языком, что такое массивы и почему они так полезны. Я кратко изложу эту информацию здесь.

Если вы сохраняете оценки одного ученика, вы можете легко сделать это, используя одну переменную.

1 2	Dim mark As Long    mark = sheetMarks.Range("A1")

Однако в большинстве случаев вам придется иметь дело с несколькими студентами. Представьте, что вы хотите хранить оценки 100 учеников. Если вы не использовали коллекции или массивы, вам нужно было бы создать сотню переменных — одну переменную для хранения оценки для каждого учащегося.

Другая проблема заключается в том, что вы должны использовать эти переменные по отдельности. Если вы хотите сохранить 100 отметок, вам понадобится строка кода каждый раз, когда вы хотите сохранить значение в переменной.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

' Объявите переменную для каждой оценки     Dim mark1 As Long     Dim mark2 As Long     .     .     .     Dim mark100 As Long       ' Сохраните отметки на листе в переменной     mark1 = sheetMarks.Range("A1")     mark2 = sheetMarks.Range("A2")     .     .     .     mark100 = sheetMarks.Range("A100")

Как вы можете видеть в приведенном выше примере, написание такого кода означало бы сотни строк повторяющегося кода. Когда вы используете коллекцию или массив, вам нужно только объявить одну переменную. Использование цикла с коллекцией или массивами означает, что вам нужна только одна строка для добавления или чтения значений.

Если мы переписываем приведенный выше пример с использованием коллекции, нам нужно всего несколько строк кода

1 2 3 4 5 6 7 8 9

' Создать коллекцию    Dim collMarks As New Collection      ' Прочитайте 100 значений в коллекцию    Dim c As Range    For Each c In Sheet1.Range("A1:A100")        ' Эта строка используется для добавления всех значений        collMarks.Add c.Value    Next

Коллекции против Массивов

Мы рассмотрели, что общего у коллекций и массивов. Так в чем же разница и зачем использовать один вместо другого?

Основное отличие состоит в том, что с массивом вы обычно устанавливаете размер один раз. Это означает, что вы знаете размер до того, как начнете добавлять элементы. Позвольте мне объяснить это на примере.

Пример: когда Массив лучше

Представьте, что у вас есть лист оценок учеников с одним учеником на строку:

Вы хотите хранить информацию о каждом ученике. В этом примере вы можете легко посчитать количество строк, чтобы получить количество студентов. Другими словами, вы знаете количество предметов заранее.

1 2 3 4 5 6 7

' Получить последнюю строку - это количество студентов    Dim lStudentCount As Long    lStudentCount = Sheet1.Range("A" & Rows.Count).End(xlUp).Row      ' Создать массив правильного размера    Dim arr() As Long    ReDim arr(1 To lStudentCount)

В примере кода, как видите, мы получаем количество студентов путем подсчета строк. Затем мы можем использовать это для создания массива правильного размера.

Массивы используются, когда размер фиксирован.

Давайте теперь посмотрим на второй пример, где мы не знаем количество предметов заранее.

Пример: когда Коллекция лучше

В этом примере у нас есть один и тот же рабочий лист студента, но на этот раз нам нужны только студенты с заданными критериями. Например, только студенты из Твери или Москвы, которые изучают математику или историю. Другими словами, вы не будете знать, как выбрать ученика, пока не прочитаете его данные на рабочем листе.

Представьте также, что студенты могут быть добавлены или удалены из списка во время работы приложения.

Таким образом, в этом примере количество студентов не является фиксированным и сильно меняется. Здесь вы не знаете количество студентов заранее. Поэтому вы не знаете, какой размер массива создать.

Вы можете создать массив максимально возможного размера. Проблема в том, что у вас будет много пустых слотов, и вам придется добавить код, чтобы справиться с ними. Если вы прочитаете 50 учеников с максимумом 1000, то у вас будет 950 неиспользуемых слотов массива.

Вы также можете изменить размер массива для каждого элемента по мере его добавления. Это очень неэффективно и довольно грязно.

Так что для этого примера лучше использовать коллекцию.

1 2 3 4 5 6 7 8 9

' Объявить    Dim coll As New Collection      ' Добавить элемент - VBA следит за изменением размера    coll.Add "Яблоко"    coll.Add "Слива"      ' удалить элемент - VBA следит за изменением размера    coll.Remove 1

Когда вы добавляете или удаляете элемент в коллекцию, VBA выполняет все изменения размера за вас. Вам не нужно указывать размер или выделять новые пробелы. VBA делает это самостоятельно. Все, что вам нужно сделать, это добавить элемент или удалить его.

Коллекция используется, когда размер часто изменяется.

Еще одно преимущество коллекций

Коллекции гораздо проще использовать, чем массивы, особенно если вы новичок в программировании. Большую часть времени вы делаете три вещи с коллекциями:

1. Создать коллекцию
2. Добавьте несколько предметов
3. Прочитайте предмет

Так что, если вы не имеете дело с большим количеством предметов, тогда использование Коллекции может быть намного удобнее в использовании.

Недостаток коллекций

Основные типы данных (т.е. переменные, такие как string, date, long, currency и т.д.) в коллекциях доступны только для чтения. Вы можете добавить или удалить элемент, но не можете изменить его значение. Если вы собираетесь изменять значения в группе элементов, вам нужно будет использовать массив.

Если вы храните объект в коллекции, вы можете изменить значение, так как коллекция хранит ссылку, а не фактический объект.

Коллекция только для чтения

Теперь, когда мы знаем, когда и зачем использовать коллекцию, давайте посмотрим, как ее использовать.

Как создать коллекцию

Вы можете объявить и создать в одной строке, как это делает следующий код:

1 2	' Объявить и создать  Dim coll As New Collection

Как видите, вам не нужно указывать размер. После того, как ваша коллекция создана, вы можете легко добавлять в нее элементы.

Вы также можете объявить и затем создать коллекцию, если и когда вам это нужно.

1 2 3 4 5

' Объявить Dim coll As Collection   ' Создать Коллекцию Set coll = New Collection

Незначительная разница между этими методами

Разница между этими методами заключается в том, что для первого всегда создается коллекция. Для второго метода коллекция создается только при достижении строки Set. Таким образом, вы можете установить код для создания коллекции только при соблюдении определенного условия.

1 2 3 4 5 6 7

' Объявить     Dim coll As Collection       ' Создать коллекцию, если файл найден     If filefound = True Then         Set coll = New Collection     Endif

Преимущество использования этого метода минимально. Выделение памяти было важно еще в 1990-х годах, когда память компьютера была ограничена. Если вы не создаете огромное количество коллекций на медленном ПК, вы никогда не заметите никакой выгоды.

Использование Set означает, что коллекция будет вести себя иначе, чем когда вы устанавливаете коллекцию в ничто. Следующий раздел объясняет это.

Удаление всех элементов из коллекции

Коллекция не имеет функции RemoveAll. Однако, чтобы удалить все элементы из коллекции, вы можете просто установить ее в новую коллекцию.

1	Set Coll = New Collection.

VBA удалит коллекцию, потому что мы больше не ссылаемся на нее. Когда мы удаляем все предметы, мы обычно хотим использовать коллекцию снова, поэтому мы эффективно убиваем двух зайцев одним выстрелом, используя этот метод.

1 2 3 4 5 6 7 8 9 10 11

Sub UdalitKollekciyu()       Dim coll1 As New Collection           coll1.Add "яблоко"     coll1.Add "слива"           ' Оригинальная коллекция удалена     Set coll1 = New Collection   End Sub

Следует помнить одну тонкую вещь: если у нас есть две или более переменных, которые ссылаются на одну и ту же коллекцию, она не будет удалена (см. Очистка памяти в VBA).

В приведенном ниже примере исходные элементы коллекции не удаляются, так как он все еще является ссылкой по coll2.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Sub KollekciyaNeUdalyaetsya()       Dim coll1 As New Collection, coll2 As Collection           coll1.Add "яблоко"     coll1.Add "слива"           ' Coll1 и Coll2 оба ссылаются на коллекцию     Set coll2 = coll1           ' Coll1 теперь ссылается на новую коллекцию     Set coll1 = New Collection           ' Coll2 относится к оригинальной коллекции - печатает яблоко     Debug.Print coll2(1)   End Sub

Добавление предметов в коллекцию

Добавить предметы в коллекцию просто. Вы используете свойство add, за которым следует значение, которое вы хотите добавить.

1 2	collFruit.Add "Яблоко" collFruit.Add "Слива"

Вы можете иметь любой базовый тип в коллекции, например, Double

1 2	collTotals.Add 45.67 collTotals.Add 34.67

Когда вы добавляете элементы таким способом, они добавляются в следующий доступный индекс. В примере с фруктами яблоко добавляется в положение 1, а слива — в положение 2.

Before и After

Вы можете использовать параметры «Before» или «After», чтобы указать, где вы хотите разместить элемент в коллекции. Обратите внимание, что вы не можете использовать оба этих аргумента одновременно.

1 2 3 4

collFruit.Add "Яблоко" collFruit.Add "Слива" ' Добавить лимон перед первым пунктом collFruit.Add "Лимон", Before:=1

После этого кода порядок коллекции выглядит так:

1. Лимон
2. Яблоко
3. Слива

1 2 3 4

collFruit.Add "Яблоко" collFruit.Add "Слива" ' Добавьте лимон после первого пункта collFruit.Add "Лимон", After:=1

После этого кода порядок коллекции выглядит так:

1. Яблоко
2. Лимон
3. Слива

Доступ к элементам коллекции

Для доступа к элементам коллекции вы просто используете индекс. Как мы увидели, индекс — это позиция элемента в коллекции на основе порядка, в котором они были добавлены.

Порядок также можно установить с помощью параметра «Before» или «After».

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Sub dostup()       Dim coll As New Collection       coll.Add "Яблоко"     coll.Add "Слива"       ' Будет напечатано Яблоко     Debug.Print coll(1)       ' добавьте апельсин в начало     coll.Add "Апельсин", Before:=1       ' будет напечатан Апельсин     Debug.Print coll(1)       ' Будет печатать Яблоко, так как оно сейчас в положении 2     Debug.Print coll(2)   End Sub

Вы также можете использовать свойство элемента для доступа к элементу в коллекции. Это метод по умолчанию для коллекции, поэтому следующие строки кода эквивалентны.

1 2	Debug.Print coll(1) Debug.Print coll.Item(1)

Элементы в коллекции доступны только для чтения?

Это очень важный момент. Когда базовый тип данных хранится в коллекции, он доступен только для чтения. Базовый тип данных — это строка, дата, целое число, длина и т.д.

Если вы попытаетесь обновить элемент коллекции, вы получите сообщение об ошибке. Следующий код выдает ошибку «Требуется объект».

1 2 3 4 5 6 7 8 9 10

Sub NapisatZnachenie()       Dim coll As New Collection       coll.Add "Яблоко"       ' Эта строка вызывает ОШИБКУ     coll(1) = "Слива"   End Sub

Вы можете изменить объект, который хранится в коллекции

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Sub IzmenitObekt()       Dim coll As New Collection     Dim o As New Class1           ' Добавить объект в коллекцию     o.fruit = "Яблоко"     coll.Add o           ' Изменить фруктовую часть Class1     coll(1).fruit = "Слива"           ' Печатает Слива     Debug.Print coll(1).fruit   End Sub

Это может показаться противоречивым поведением, но тому есть веская причина. Любой элемент, добавленный в коллекцию, доступен только для чтения. Однако при добавлении объекта в коллекцию объект не добавляется как элемент. Переменная с адресом памяти объекта добавляется в качестве элемента.

Это происходит незаметно, чтобы вы этого не заметили. Переменная item на самом деле доступна только для чтения, но объект, на который она указывает, — нет.

Все, что вам нужно помнить, это то, что базовые типы данных в коллекции доступны только для чтения. Объекты в коллекции могут быть изменены.

Вы можете прочитать больше об объектах в памяти здесь.

Добавление разных типов

Вы также можете добавлять различные типы предметов в коллекцию.

1 2 3

collFruit.Add "Яблоко" collFruit.Add 45 collFruit.Add #12/12/2019#

Это редко нужно. В VBA коллекции Sheets содержат листы типа Worksheet и типа Chart. (Чтобы создать лист с диаграммой, просто щелкните правой кнопкой мыши любую диаграмму, выберите «Переместить» и установите переключатель «Новый лист»).

Следующий код отображает тип и имя всех листов в текущей книге. Обратите внимание, что для доступа к другому типу необходимо, чтобы переменная For Each была вариантом, иначе вы получите ошибку.

1 2 3 4 5 6 7 8 9

Sub listi()       Dim sh As Variant     For Each sh In ThisWorkbook.Sheets         ' Тип отображения и название листа         Debug.Print TypeName(sh), sh.Name     Next   End Sub

При доступе к различным элементам переменная For Each должна быть вариантом. Если это не так, вы получите ошибку при доступе к другому типу, который вы объявили. Если мы объявим sh в качестве рабочего листа в приведенном выше примере, это приведет к ошибке при попытке доступа к листу типа Chart.

Редко вам понадобится коллекция разных типов, но, как видите, иногда это может быть полезно.

Добавление элементов с помощью ключа

Вы также можете добавить элементы, используя ключ, как показано в следующем примере:

1 2 3

collMark.Add Item:=45, Key:="Петр"   Debug.Print "Оценки Петра: ",collMark("Петр")

Я включил имена параметров, чтобы сделать приведенный выше пример понятным. Однако вам не нужно этого делать. Просто помните, что ключ является вторым параметром и должен быть уникальной строкой.

Следующий код показывает второй пример использования ключей.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Sub IspolzovanieKlyuchei()       Dim collMark As New Collection       collMark.Add 45, "Петр"     collMark.Add 67, "Юрий"     collMark.Add 12, "Ольга"     collMark.Add 89, "Елена"       ' Печатать оценку Елены     Debug.Print collMark("Елена")       ' Печатать оценку Петра     Debug.Print collMark("Петр")   End Sub

Использование ключей имеет три преимущества:

1. Если заказ изменится, ваш код все равно получит доступ к нужному товару
2. Вы можете напрямую получить доступ к элементу, не читая всю коллекцию
3. Это может сделать ваш код более читабельны

В коллекции Workbooks VBA доступ к рабочей книге гораздо лучше по ключу (имени), чем по индексу. Порядок зависит от порядка, в котором они были открыты, и поэтому является довольно случайным.

1 2 3 4 5 6 7

Sub IspolzovanieWorkbook()       Debug.Print Workbooks("Пример.xlsm").Name       Debug.Print Workbooks(1).Name   End Sub

Когда использовать ключи

Пример использования ключей: представьте, что у вас есть набор идентификаторов для 10 000 учащихся вместе с их оценками.

У вас также есть несколько отчетов на листе, которые содержат списки идентификаторов студентов. Для каждого из этих рабочих листов вам необходимо распечатать отметку для каждого учащегося.

Вы можете сделать это, добавив 10 000 студентов в коллекцию, используя их идентификатор в качестве ключа. Когда вы читаете удостоверение личности с листа, вы можете получить прямой доступ к оценкам этого студента.

Если вы не используете ключ, вам придется искать по 10 000 идентификаторов для каждого идентификатора в отчете.

Недостаток использования ключей в коллекциях

В коллекциях есть два недостатка ключей

1. Вы не можете проверить, существует ли Ключ.
2. Вы не можете обновить значение, хранящееся в ключе

Первый вопрос легко обойти. Следующий код проверяет, существует ли ключ.

1 2 3 4 5 6 7 8 9

Function Exists(coll As Collection, key As String) As Boolean       On Error Goto EH       coll.Item key           Exists = True EH: End Function

Вы можете использовать это так:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

Sub TestExists()       Dim coll As New Collection     coll.Add Item:=5, key:="Яблоко"     coll.Add Item:=8, key:="Слива"           ' Печатает истина     Debug.Print Exists(coll, "Яблоко")     ' Печатает ложь     Debug.Print Exists(coll, "Апельсин")     ' Печатает истина     Debug.Print Exists(coll, "Слива")       End Sub

Второй вопрос не так легко обойти, если у вас нет хороших знаний в программировании.

Если вы хотите использовать ключи, есть альтернатива Коллекции. Вы можете использовать словарь. Словарь предоставляет больше возможностей для работы с клавишами. Вы можете проверить, существуют ли ключи, обновить значения в ключах, получить список ключей и так далее.

Доступ ко всем элементам в коллекции

Для доступа ко всем элементам в коллекции вы можете использовать цикл For или цикл For Each. Давайте рассмотри каждый из них.

Использование цикла For

В обычном цикле For вы используете индекс для доступа к каждому элементу. В следующем примере выводится имя всех открытых рабочих книг.

1 2 3 4 5 6 7 8

Sub VseWorkbook()       Dim i As Long     For i = 1 To Workbooks.Count         Debug.Print Workbooks(i).Name     Next i   End Sub

Вы можете видеть, что мы используем диапазон от 1 до Workbooks.Count. Первый элемент всегда находится в первом положении, а последний элемент всегда находится в положении, указанном свойством Count коллекции.

В следующем примере распечатываются все элементы в пользовательской коллекции.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Sub IspolzovanieKollekcii()       ' Объявить и создать коллекцию     Dim collFruit As New Collection       ' Добавить элементы     collFruit.Add "Яблоко"     collFruit.Add "Слива"     collFruit.Add "Груша"       ' Печать всех элементов     Dim i As Long     For i = 1 To collFruit.Count         Debug.Print collFruit(i)     Next i   End Sub

Использование цикла For Each

Цикл For Each, который является специализированным циклом, используется для коллекций. Он не использует индекс, а формат показан в следующем примере.

1 2 3 4 5 6 7 8

Sub VseWorkbookForEach()       Dim book As Variant     For Each book In Workbooks         Debug.Print book.Name     Next   End Sub

Формат цикла For:

For i = 1 To Coll.Count
Next

где i — long, Coll — коллекция

Формат цикла For Each:

For Each var In Coll
Next

где var — вариант, а Coll — коллекция

Чтобы получить доступ к каждому элементу:

For: Coll(i)
For Each: Var

В следующем примере показаны циклы для приведенного выше примера пользовательской коллекции.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

Sub IspolzovanieOboihCiklov()       ' Объявить и создать коллекцию     Dim collFruit As New Collection       ' Добавить элементы     collFruit.Add "Яблоко"     collFruit.Add "Слива"     collFruit.Add "Груша"       ' Печать всех элементов, используя For     Dim i As Long     For i = 1 To collFruit.Count         Debug.Print collFruit(i)     Next i       ' Печать всех элементов, используя For Each     Dim fruit As Variant     For Each fruit In collFruit         Debug.Print fruit     Next fruit   End Sub

For Each против For

Важно понимать разницу между двумя циклами.

Цикл For Each

• быстрее
• аккуратнее писать
• имеет только один заказ — от низкого индекса до высокого

Цикл For

• медленнее
• не так аккуратен
• можно получить доступ в другом порядке

Давайте сравним циклы по каждому из этих атрибутов

Скорость

For Each считается быстрее, чем цикл For. В настоящее время это проблема, только если у вас большая коллекция и / или медленный компьютер/сеть.

Аккуратнее

Цикл For Each аккуратнее писать, особенно если вы используете вложенные циклы. Сравните следующие циклы. Оба печатают названия всех рабочих листов в открытых рабочих книгах.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

Sub PechatatNazvaniyaFor()       ' Напечатайте названия рабочих листов из всех открытых рабочих книг     Dim i As Long, j As Long     For i = 1 To Workbooks.Count         For j = 1 To Workbooks(i).Worksheets.Count             Debug.Print Workbooks(i).Name, Workbooks(i).Worksheets(j).Name         Next j     Next i   End Sub   Sub PechatatNazvaniyaForEach()       ' Напечатайте названия рабочих листов из всех открытых рабочих книг     Dim bk As Workbook, sh As Worksheet     For Each bk In Workbooks         For Each sh In bk.Worksheets             Debug.Print bk.Name, sh.Name         Next sh     Next bk   End Sub

Цикл For Each гораздо удобнее для написания и менее подвержен ошибкам.

Порядок

Порядок цикла For Each всегда от самого низкого индекса до самого высокого. Если вы хотите получить другой заказ, вам нужно использовать цикл For. Порядок цикла For можно изменить. Вы можете прочитать предметы в обратном порядке. Вы можете прочитать раздел предметов или вы можете прочитать каждый второй элемент.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

Sub ChitatSpravaNalevo()   ' Просмотрите листы справа налево Dim i As Long For i = ThisWorkbook.Worksheets.Count To 1 Step -1 Debug.Print ThisWorkbook.Worksheets(i).Name Next i   ' Пройдите первые 3 листа For i = 1 To 3 Debug.Print ThisWorkbook.Worksheets(i).Name Next i   ' Пройдите каждый второй лист For i = 1 To ThisWorkbook.Worksheets.Count Step 2 Debug.Print ThisWorkbook.Worksheets(i).Name Next i   End Sub

Цикл For дает больше гибкости, но реальность такова, что большую часть времени базовый порядок — это все, что вам нужно.

Сортировка коллекции

Для коллекции VBA нет встроенной сортировки. Однако мы можем использовать QuickSort.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

Sub QuickSort(coll As Collection, first As Long, last As Long)       Dim vCentreVal As Variant, vTemp As Variant       Dim lTempLow As Long   Dim lTempHi As Long   lTempLow = first   lTempHi = last       vCentreVal = coll((first + last) \ 2)   Do While lTempLow <= lTempHi         Do While coll(lTempLow) < vCentreVal And lTempLow < last       lTempLow = lTempLow + 1     Loop           Do While vCentreVal < coll(lTempHi) And lTempHi > first       lTempHi = lTempHi - 1     Loop           If lTempLow <= lTempHi Then             ' Поменять значения       vTemp = coll(lTempLow)               coll.Add coll(lTempHi), After:=lTempLow       coll.Remove lTempLow               coll.Add vTemp, Before:=lTempHi       coll.Remove lTempHi + 1               ' Перейти к следующим позициям       lTempLow = lTempLow + 1       lTempHi = lTempHi - 1             End If         Loop       If first < lTempHi Then QuickSort coll, first, lTempHi   If lTempLow < last Then QuickSort coll, lTempLow, last     End Sub

Вы можете использовать это так:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Sub TestSort()       Dim coll As New Collection     coll.Add "Москва"     coll.Add "Тверь"     coll.Add "Пенза"     coll.Add "Новосибирск"           QuickSort coll, 1, coll.Count           Dim v As Variant     For Each v In coll         Debug.Print v     Next       End Sub

Использование коллекций с функциями и подпрограммами

Использовать коллекцию в качестве параметра или возвращаемого значения очень легко. Мы рассмотрим их по очереди.

Передача Коллекции в Sub / Function

Это просто передать коллекцию в функцию или подпункт. Она передается как любой параметр, как показано в следующем примере кода.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

Sub IspolzovatColl()       ' Создать коллекцию     Dim coll As New Collection       ' Добавить элементы     coll.Add "Яблоко"     coll.Add "Апельсин"       ' Перейти на sub     PrintColl coll   End Sub   ' Sub принимает коллекцию в качестве аргумента Sub PrintColl(ByRef coll As Collection)       Dim item As Variant     For Each item In coll         Debug.Print item     Next   End Sub

Вы можете увидеть, насколько полезен вспомогательный PrintColl в примере. На нем будут напечатаны все элементы ЛЮБОЙ коллекции. Размер или тип элемента не имеет значения. Это показывает, насколько гибкими должны быть коллекции.

Передача ByVal против ByRef

Здесь следует помнить одну тонкую вещь: передача по значению (By Val) и передача по ссылке (ByRef) немного отличаются.

Для простой передачи переменной значение означает, что копия создана. Это означает, что если функция / подчиненный элемент изменится, значение не изменится, когда вы вернетесь к вызывающей процедуре.

В следующем примере мы передаем итоговое значение, используя ByVal и ByRef. Вы увидите, что после того, как мы передаем использование ByRef, значение изменилось в вызывающей процедуре.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

Sub TipiPeredachi()       Dim total As Long     total = 100       PassByValue total     ' Печатает 100     Debug.Print total       PassByReference total     ' Печатает 555     Debug.Print total   End Sub   Sub PassByValue(ByVal total As Long)     ' значение изменилось только в этом sub     total = 555 End Sub   Sub PassByReference(ByRef total As Long)     ' значение также изменилось за пределами этого sub     total = 555 End Sub

Использование ByVal и ByRef с коллекцией немного отличается. Если вы добавляете или удаляете элемент, коллекция в исходном вызывающем абоненте также будет изменена. Таким образом, Subs в следующем примере удалят первый элемент из исходной коллекции.

1 2 3 4 5 6 7

Sub UdalitByRef(ByRef coll As Collection)     coll.Remove 1 End Sub   Sub RemoveByVal(ByVal coll As Collection)     coll.Remove 1 End Sub

Причина этого заключается в том, что переменная Collection содержит указатель. Это означает, что он содержит адрес коллекции, а не фактическую коллекцию. Поэтому, когда вы добавляете или удаляете элемент, вы меняете то, на что указывает указатель, а не сам указатель. Однако, если вы измените указатель, он будет изменен за пределами подпрограммы.

Вам не нужно беспокоиться об указателях. Все, что вам нужно знать, это то, как это влияет на поведение передачи параметра. Если для параметра коллекции ничего не задано, поведение зависит от того, использовали ли вы ByRef или ByVal.

• Использование ByRef сбросит исходную коллекцию
• Использование ByVal не изменит оригинальную коллекцию

1 2 3 4 5 6 7 8 9

' Очистит исходную коллекцию Sub PassByRef(ByRef coll As Collection)     Set coll = Nothing End Sub   ' Не изменит исходную коллекцию Sub PassByVal(ByVal coll As Collection)     Set coll = Nothing End Sub

Возврат коллекции из функции

Возврат коллекции из функции — это то же самое, что вернуть любой объект. Вам нужно использовать ключевое слово Set. В следующем примере вы увидите, как вернуть коллекцию.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

Sub OtchetOFruktah()     ' ПРИМЕЧАНИЕ. Мы не используем ключевое слово New для создания 'коллекции.     ' Коллекция создается в функции CreateCollection.     Dim coll As Collection       ' получить coll от функции CreateCollection     Set coll = CreateCollection       ' сделать что-то с coll здесь   End Sub   Function CreateCollection() As Collection       Dim coll As New Collection       coll.Add "Слива"     coll.Add "Груша"       ' Возврат коллекции     Set CreateCollection = coll   End Function

Примечание: вы не используете ключевое слово New при объявлении коллекции в подпункте OtchetOFruktah(). Потому что коллекция создается в CreateCollection (). Когда вы возвращаете коллекцию, вы просто назначаете переменную коллекции, указывающую на эту коллекцию.

Заключение

Коллекции — очень полезная часть VBA. Их гораздо проще использовать, чем массивы, и они очень полезны, когда вы много добавляете и удаляете элементы. У них есть только четыре свойства:
Add, Remove, Count и Item . Поэтому они очень просты в освоении.

 Основные пункты этой статьи:

1. Коллекции — это способ хранения группы элементов вместе.
2. VBA имеет свои собственные коллекции, такие как
   Workbooks, Worksheets и Cells .
3. Элементы не обязательно должны быть одного типа, но обычно одного. Коллекция VBA Sheets может содержать как листы, так и листы диаграмм.
4. Коллекция позволяет легко выполнять одну и ту же задачу для нескольких элементов, например, распечатать все значения.
5. Коллекции похожи на массивы, поскольку в них хранятся группы похожих элементов.
6. Коллекции лучше при добавлении и удалении большого количества элементов.
7. Коллекции проще в использовании, чем массивы.
8. Массивы более полезны, когда количество элементов фиксировано.
9. Массивы более эффективны при чтении и записи в ячейки или из них .
10. Базовые типы данных (т.е. не-объекты) в коллекции доступны только для чтения, а массивы — для чтения / записи.
11. Вы можете создать коллекцию, используя только Dim или Dim с помощью Set.
12. Вы можете удалить всю коллекцию, установив для нее значение Nothing. Но зависит от того, как она была создана.
13. Вы можете добавлять элементы в определенную позицию в коллекции, используя аргументы «Before» и «After» с помощью функции Add.
14. Вы можете использовать ключи с коллекцией для прямого доступа к элементу. Коллекции не имеют хорошей поддержки ключей, поэтому обычно лучше использовать коллекцию Dictionary, когда вам нужно использовать ключи.
15. Вы можете использовать циклы For и For Each для доступа ко всем элементам в коллекции. Цикл For Each более эффективен, но позволяет просматривать коллекцию только в одном порядке.
16. Вы можете легко передать коллекцию в качестве аргумента в функцию или подпрограмму.
17. Вы можете легко вернуть коллекцию из функции.

Источник