Коллекция - это объект, содержащий несколько похожих элементов. К ним можно легко получить доступ и управлять ими, даже если в коллекции имеется большое количество элементов.
Уже есть встроенные коллекции с Excel VBA. Примером может служить коллекция Sheets. Для каждого листа в книге есть элемент в коллекции Sheets.
Во встроенных коллекциях вам доступно гораздо больше свойств и методов, но они недоступны в ваших собственных коллекциях, которые вы создаете.
Например, вы можете использовать коллекцию для получения информации о конкретном листе. Например, вы можете увидеть имя рабочего листа, а также увидеть, отображается он или нет. Используя цикл For Each, вы можете выполнять итерацию по каждому листу в коллекции.
| 1234567 | Sub TestWorksheets ()Рабочий лист Dim Sh AsЗа каждую ш в листахMsgBox Sh.NameMsgBox Sh.VisibleДалее ШКонец подписки |
Вы также можете обратиться к конкретному рабочему листу в коллекции, используя значение индекса или фактическое имя рабочего листа:
| 12 | MsgBox Sheets (1) .NameMsgBox Sheets ("Sheet1"). Имя |
По мере добавления или удаления листов коллекция листов увеличивается или уменьшается в размере.
Обратите внимание, что в коллекциях VBA номер индекса начинается с 1, а не с 0
Коллекции против массивов
Массивы и коллекции схожи по своим функциям в том, что обе они представляют собой методологии, позволяющие хранить большой объем данных, на которые затем можно легко ссылаться с помощью кода. Однако у них есть ряд отличий в том, как они работают:
- Массивы многомерны, тогда как коллекции - только одномерные. Вы можете измерить массив с несколькими измерениями, например.
| 1 | Dim MyArray (10, 2) как строка |
Это создает массив из 10 строк с 2 столбцами, почти как рабочий лист. Коллекция - это фактически один столбец. Массив полезен, если вам нужно хранить несколько элементов данных, которые связаны друг с другом, например. имя и адрес. Имя будет в первом измерении массива, а адрес - во втором измерении.
- Когда вы заполняете свой массив, вам нужна отдельная строка кода, чтобы поместить значение в каждый элемент массива. Если бы у вас был двумерный массив, вам действительно понадобились бы 2 строки кода - одна строка для адресации первого столбца и одна строка для адресации второго столбца. С объектом Collection вы просто используете метод Add, чтобы новый элемент просто добавлялся в коллекцию, а значение индекса автоматически настраивалось в соответствии с требованиями.
- Если вам нужно удалить элемент данных, то в массиве все сложнее. Вы можете установить значения элемента на пустое значение, но сам элемент все еще существует в массиве. Если вы используете цикл For Next для итерации по массиву, цикл вернет пустое значение, которое потребует кодирования, чтобы убедиться, что пустое значение игнорируется. В коллекции вы используете методы Add или Remove, и все индексация и изменение размера выполняются автоматически. Удаленный предмет полностью исчезнет. Массивы полезны для данных фиксированного размера, но коллекции лучше, если количество данных может измениться.
- Коллекции доступны только для чтения, тогда как значения массивов можно изменять с помощью VBA. С коллекцией вам нужно сначала удалить значение, которое нужно изменить, а затем добавить новое измененное значение.
- В массиве вы можете использовать только один тип данных для элементов, который устанавливается при измерении массива. Однако в массиве вы можете использовать пользовательские типы данных, которые вы создали сами. У вас может быть очень сложная структура массива с использованием настраиваемого типа данных, который, в свою очередь, имеет несколько настраиваемых типов данных под ним. В коллекцию можно добавить типы данных об использовании для каждого элемента. У вас может быть числовое значение, дата или строка - объект коллекции будет принимать любой тип данных. Если вы попытаетесь поместить строковое значение в массив с числовыми размерами, появится сообщение об ошибке.
- Коллекции обычно проще в использовании, чем массивы. С точки зрения кодирования, когда вы создаете объект коллекции, он имеет только два метода (Add и Remove) и два свойства (Count и Item), поэтому этот объект ни в коем случае не сложен для программирования.
- Коллекции могут использовать ключи для поиска данных. Массивы не имеют этой функции и требуют циклического кода для итерации по массиву для поиска определенных значений.
- Размер массива необходимо определить при его первом создании. Вы должны иметь представление о том, сколько данных он будет хранить. Если вам нужно увеличить размер массива, вы можете использовать «ReDim», чтобы изменить его размер, но вам нужно использовать ключевое слово «Сохранить», если вы не хотите потерять данные, уже хранящиеся в массиве. Размер коллекции указывать не нужно. Он просто автоматически увеличивается и уменьшается по мере добавления или удаления элементов.
Объем объекта коллекции
Что касается области действия, объект коллекции доступен только тогда, когда рабочая книга открыта. Он не сохраняется при сохранении книги. Если книга открывается повторно, коллекцию необходимо воссоздать с использованием кода VBA.
Если вы хотите, чтобы ваша коллекция была доступна для всего кода в вашем модуле кода, вам необходимо объявить объект коллекции в разделе Declare в верхней части окна модуля.
Это гарантирует, что весь ваш код в этом модуле сможет получить доступ к коллекции. Если вы хотите, чтобы какой-либо модуль в вашей книге имел доступ к коллекции, определите его как глобальный объект.
| 1 | Global MyCollection как новая коллекция |
Создание коллекции, добавление элементов и доступ к элементам
В VBA можно создать простой объект коллекции, используя следующий код:
| 123456 | Подложка CreateCollection ()Dim MyCollection как новая коллекцияMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Конец подписки |
Код измеряет новый объект с именем MyCollection, а затем в следующих строках кода используется метод Add для добавления 3 новых значений.
Затем вы можете использовать код для итерации по вашей коллекции для доступа к значениям.
| 123 | Для каждого элемента в MyCollectionЭлемент MsgBoxСледующий пункт |
Вы также можете перебирать свою коллекцию с помощью цикла For Next Loop:
| 123 | Для n = 1 в MyCollection.CountMsgBox MyCollection (сущ.)Следующий n |
Код получает размер коллекции с помощью свойства Count, а затем использует это значение, начиная со значения 1 для индексации каждого элемента.
Цикл For Each быстрее, чем цикл For Next, но работает только в одном направлении (от низкого индекса к высокому). Цикл For Next Loop имеет то преимущество, что вы можете использовать другое направление (от высокого индекса к низкому), а также можете использовать метод Step для изменения приращения. Это полезно, когда вы хотите удалить несколько элементов, поскольку вам нужно будет запустить удаление от конца коллекции до начала, поскольку индекс будет меняться по мере удаления.
Метод Add в коллекции имеет 3 необязательных параметра - Key, Before и After.
Вы можете использовать параметры «До» и «После», чтобы определить положение вашего нового элемента относительно других, уже находящихся в коллекции.
Это делается путем указания порядкового номера, относительно которого вы хотите, чтобы ваш новый элемент располагался.
| 123456 | Подложка CreateCollection ()Dim MyCollection как новая коллекцияMyCollection.Add "Item1"MyCollection.Add "Item2",, 1MyCollection.Add "Item3"Конец подписки |
В этом примере «Item2» был указан для добавления перед первым проиндексированным элементом в коллекции (которым является «Item1»). При итерации по этой коллекции в первую очередь появится «Item2», за которым следуют «Item1» и «Item3».
Когда вы указываете параметр «До» или «После», значение индекса автоматически корректируется в коллекции, так что «Item2» становится значением индекса 1, а «Item1» перемещается к значению индекса 2.
Вы также можете использовать параметр «Key», чтобы добавить ссылочное значение, которое вы можете использовать для идентификации элемента коллекции. Обратите внимание, что значение ключа должно быть строкой и должно быть уникальным в коллекции.
| 1234567 | Подложка CreateCollection ()Dim MyCollection как новая коллекцияMyCollection.Add "Item1"MyCollection.Add «Item2», «MyKey»MyCollection.Add "Item3"MsgBox MyCollection ("MyKey")Конец подписки |
«Item2» получил значение «Key», равное «MyKey», так что вы можете ссылаться на этот элемент, используя значение «MyKey» вместо номера индекса (2).
Обратите внимание, что значение «Key» должно быть строковым. Это не может быть какой-либо другой тип данных. Обратите внимание, что коллекция доступна только для чтения, и вы не можете обновить значение ключа после его установки. Кроме того, вы не можете проверить, существует ли значение ключа для определенного элемента в коллекции, или просмотреть значение ключа, что является небольшим недостатком.
Параметр «Key» имеет дополнительное преимущество, делая ваш код более читабельным, особенно если он передается коллеге для поддержки, и вам не нужно перебирать всю коллекцию, чтобы найти это значение. Представьте, если бы у вас была коллекция из 10 000 предметов, насколько сложно было бы сослаться на один конкретный предмет!
Удаление предмета из коллекции
Вы можете использовать метод «Удалить» для удаления элементов из вашей коллекции.
| 1 | MyCollection.Remove (2) |
К сожалению, при большом количестве элементов в коллекции непросто определить индекс элемента, который вы хотите удалить. Здесь пригодится параметр «Ключ» при создании коллекции.
| 1 | MyCollection.Remove («MyKey») |
Когда элемент удаляется из коллекции, значения индекса автоматически сбрасываются на всем протяжении коллекции. Именно здесь параметр «Ключ» так полезен, когда вы удаляете сразу несколько элементов. Например, вы можете удалить индекс 105 элемента, и мгновенно индекс элемента 106 станет индексом 105, и все, что выше этого элемента, будет перемещено вниз. Если вы используете параметр Key, не нужно беспокоиться о том, какое значение индекса нужно удалить.
Чтобы удалить все элементы коллекции и создать новую коллекцию, вы снова используете оператор Dim, который создает пустую коллекцию.
| 1 | Dim MyCollection как новая коллекция |
Чтобы полностью удалить фактический объект коллекции, вы можете установить для объекта ничего
| 1 | Установите MyCollection = Nothing |
Это полезно, если коллекция больше не требуется вашему коду. Установка объекта коллекции в ничто удаляет все ссылки на него и освобождает память, которую он использовал. Это может иметь важные последствия для скорости выполнения вашего кода, если в памяти находится большой объект, который больше не требуется.
Подсчитайте количество предметов в коллекции
Вы можете легко узнать количество предметов в своей коллекции, используя свойство «Счетчик».
| 1 | MsgBox MyCollection.Count |
Вы бы использовали это свойство, если бы вы использовали цикл For Next Loop для итерации по коллекции, поскольку он предоставит вам верхний предел для номера индекса.
Сборник тестов для определенного значения
Вы можете перебирать коллекцию для поиска определенного значения для элемента с помощью цикла For Each Loop
| 123456789101112 | Sub SearchCollection ()Dim MyCollection как новая коллекцияMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Для каждого элемента в MyCollectionЕсли Item = "Item2", тоЭлемент MsgBox и «Найдено»Конец, еслиСледующийКонец подписки |
Код создает небольшую коллекцию, а затем перебирает ее в поисках элемента с именем «item2». Если он найден, он отображает окно сообщения о том, что он нашел конкретный элемент.
Одним из недостатков этой методологии является то, что вы не можете получить доступ к значению индекса или значению ключа.
Если вместо этого вы используете цикл For Next Loop, вы можете использовать счетчик For Next для получения значения индекса, хотя вы по-прежнему не можете получить значение «Key».
| 123456789101112 | Sub SearchCollection ()Dim MyCollection как новая коллекцияMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Для n = 1 в MyCollection.CountЕсли MyCollection.Item (n) = "Item2", тоMsgBox MyCollection.Item (n) & «найдено в позиции индекса» & nКонец, еслиСледующий nКонец подписки |
Счетчик For Next (n) предоставит позицию индекса
Сортировка коллекции
Нет встроенных функций для сортировки коллекции, но, используя некое «нестандартное» мышление, можно написать код для сортировки, используя функцию сортировки листов Excel. В этом коде для фактической сортировки используется пустой рабочий лист под названием «SortSheet».
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Sub SortCollection ()Dim MyCollection как новая коллекцияТусклый счетчик до тех пор, пока‘Соберите коллекцию из предметов в произвольном порядкеMyCollection.Add "Item5"MyCollection.Add "Item2"MyCollection.Add "Item4"MyCollection.Add "Item1"MyCollection.Add "Item3"‘Захватите количество предметов в коллекции для будущего использованияCounter = MyCollection.Count‘Итерировать по коллекции, копируя каждый элемент в последовательную ячейку в‘ SortSheet ’(столбец A).Для n = 1 в MyCollection.CountТаблицы ("SortSheet"). Ячейки (n, 1) = MyCollection (n)Следующий n‘Активируйте таблицу сортировки и используйте процедуру сортировки Excel для сортировки данных в порядке возрастания.Таблицы ("SortSheet"). АктивироватьДиапазон ("A1: A" и MyCollection.Count). ВыберитеActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.ClearActiveWorkbook.Worksheets («Таблица сортировки»). Ключ Sort.SortFields.Add2: = Диапазон (_"A1: A5"), SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = _xlSortNormalС ActiveWorkbook.Worksheets ("SortSheet"). Сортировка.SetRange Range ("A1: A5").Header = xlGuess.MatchCase = Ложь.Orientation = xlTopToBottom.SortMethod = xlPinYin.Подать заявлениеКонец с‘Удалите все элементы в коллекции - обратите внимание, что этот цикл For Next выполняется в обратном порядкеДля n = MyCollection.Count To 1 Step -1MyCollection.Remove (сущ.)Следующий n‘Скопируйте значения ячеек обратно в пустой объект коллекции, используя сохраненное значение (Counter) для цикла‘Для n = 1 счетчикMyCollection.Add Sheets ("SortSheet"). Cells (n, 1) .ValueСледующий n"Просмотрите коллекцию, чтобы проверить порядок, в котором сейчас находятся элементы.Для каждого элемента в MyCollectionЭлемент MsgBoxСледующий пункт‘Очистите рабочий лист (сортировочную таблицу) - при необходимости удалите и его.Таблицы ("SortSheet"). Диапазон (Ячейки (1, 1), Ячейки (Счетчик, 1)). ОчиститьКонец подписки |
Этот код сначала создает коллекцию с элементами, добавленными в случайном порядке. Затем он копирует их в первый столбец на листе (SortSheet).
Затем код использует функцию сортировки Excel для сортировки данных в столбце в порядке возрастания. Код также можно изменить для сортировки по убыванию.
Затем коллекция очищается от данных с помощью цикла For Next Loop. Обратите внимание, что параметр step используется для очистки от конца коллекции до начала. Это связано с тем, что при очистке значения индекса сбрасываются, если он очищен с самого начала, он не будет очищен правильно (индекс 2 станет индексом 1)
Наконец, с помощью другого цикла For Next Loop значения элементов передаются обратно в пустую коллекцию.
Дальнейший цикл For Each Loop доказывает, что коллекция теперь находится в хорошем порядке возрастания.
К сожалению, это не касается каких-либо значений ключей, которые могли быть введены изначально, поскольку значения ключей не могут быть прочитаны.
Передача коллекции в подфункцию
Коллекцию можно передать подпрограмме или функции так же, как и любой другой параметр.
| 1 | Функция MyFunction (ByRef MyCollection as Collection) |
Важно передать коллекцию с помощью ByRef. Это означает, что используется оригинальная коллекция. Если коллекция передается с использованием ByVal, создается копия коллекции, которая может иметь неприятные последствия.
Если копия создается с использованием ByVal, то все, что изменяет коллекцию внутри функции, происходит только с копией, а не с оригиналом. Например, если внутри функции в коллекцию добавляется новый элемент, он не появится в исходной коллекции, что создаст ошибку в вашем коде.
Возврат коллекции из функции
Вы можете вернуть коллекцию из функции так же, как и любой объект. Вы должны использовать ключевое слово Set
| 12345 | Подложка ReturnFromFunction ()Dim MyCollection как коллекцияУстановить MyCollection = PopulateCollectionMsgBox MyCollection.CountКонец подписки |
Этот код создает подпрограмму, которая создает объект с именем «MyCollection», а затем использует ключевое слово «Set» для эффективного вызова функции для заполнения этой коллекции. Как только это будет сделано, отобразится окно сообщения, в котором будет показано количество двух элементов.
| 1234567 | Функция PopulateCollection () как коллекцияDim MyCollection как новая коллекцияMyCollection.Add "Item1"MyCollection.Add "Item2"Установите PopulateCollection = MyCollectionКонечная функция |
Функция PopulateCollection создает новый объект коллекции и заполняет его двумя элементами. Затем он передает этот объект обратно в объект коллекции, созданный в исходной подпрограмме.
Преобразование коллекции в массив
Вы можете преобразовать свою коллекцию в массив. Возможно, вы захотите сохранить данные, где их можно будет изменять и манипулировать. Этот код создает небольшую коллекцию, а затем передает ее в массив
Обратите внимание на то, что индекс коллекции начинается с 1, тогда как индекс массива начинается с 0. В то время как коллекция состоит из 3 элементов, размер массива должен быть только 2, потому что есть элемент 0
| 1234567891011121314151617 | Sub ConvertCollectionToArray ()Dim MyCollection как новая коллекцияDim MyArray (2) как строкаMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Для n = 1 в MyCollection.CountMyArray (n - 1) = MyCollection (n)Следующий nДля n = от 0 до 2MsgBox MyArray (n)Следующий nКонец подписки |
Преобразование массива в коллекцию
Вы можете преобразовать массив в коллекцию. Например, вы можете захотеть получить доступ к данным более быстрым и элегантным способом, чем использование кода для получения элемента массива.
Имейте в виду, что это будет работать только для одного измерения массива, потому что коллекция имеет только одно измерение.
| 123456789101112131415 | Подложка ConvertArrayIntoCollection ()Dim MyCollection как новая коллекцияDim MyArray (2) как строкаMyArray (0) = "элемент1"MyArray (1) = "Item2"MyArray (2) = "Item3"Для n = от 0 до 2MyCollection.Add MyArray (n)Следующий nДля каждого элемента в MyCollectionЭлемент MsgBoxСледующий пунктКонец подписки |
Если вы действительно хотите использовать многомерный массив, вы можете объединить значения массива вместе для каждой строки в массиве, используя символ-разделитель между измерениями массива, чтобы при чтении значения коллекции вы могли программно использовать символ-разделитель для отделить значения.
Вы также можете переместить данные в коллекцию на основе того, что добавляется значение первого измерения (индекс 1), а затем добавляется значение следующего измерения (индекс 2) и так далее.
Если бы массив имел, скажем, 4 измерения, каждое четвертое значение в коллекции было бы новым набором значений.
Вы также можете добавить значения массива для использования в качестве ключей (при условии, что они уникальны), что упростит поиск определенных данных.
