Коллекция сортировки Excel vba

Обновлено: 30.06.2024

Сортировка в VBA — довольно сложная задача, поскольку сам VBA не поддерживает сортировку значений. Вы должны использовать такой алгоритм, как QuickSort. Но что, если вы хотите отсортировать объекты? Это еще хуже и сложнее, алгоритмы становятся более сложными и менее понятными. К счастью, вы снова можете использовать библиотеку mscorlib.dll и с помощью интерфейса ArrayList и IComparer реализовать собственный метод сортировки объектов.

Предпосылки

Класс сотрудников

Во-первых, нам нужна коллекция объектов, которые мы хотим отсортировать. Давайте создадим простой класс всего с тремя свойствами, сохраним его в модуле класса и назовем его классом Employee.

Класс MyComparer

Если меньше 0, x меньше y.
Если 0, x равно y.
Если больше 0, x больше y.

Мы должны реализовать этот метод в нашем классе. Затем, на основе значения свойства нашего объекта, мы выполняем либо строковое сравнение с функцией VBA StrComp в случае свойства FullName, либо числовое сравнение со стандартными операторами сравнения VBA в случае свойства ID или Birthdate (поскольку даты VBA внутренне хранятся также как числа).< /p>

Код в стандартном модуле

Чтобы сэкономить время и написать, мы создаем фабричную функцию, которая создает и возвращает наш объект Employee.

Наша основная процедура (ниже) заслуживает некоторого описания. Первая часть понятна, мы создаем новый экземпляр класса MyComparer, а также коллекцию ArrayList с некоторыми объектами Employees.

Во второй части мы устанавливаем компаратор в нужное поле и выполняем сортировку объектов с помощью метода ArrayList Sort_2. Но подождите, что? Метод Sort_2? Да :).

При использовании объектов mscorlib в VBA необходимо учитывать некоторые факты:

Перегрузка функций изначально не поддерживается VBA
Перегруженные методы переименованы. Как правило, первый метод в документации MSDN будет иметь обычное имя (по умолчанию), а последующие методы (перегрузки) будут перечислены с символом подчеркивания.
Перегрузки метода Sort выглядят следующим образом:

Документация MSDN	Альтернатива VBA
Sort()	Sort()
Сортировка(IComparer)	Sort_2(IComparer)
Сортировка(Int32 , Int32, IComparer)	Sort_3(Int32, Int32, IComparer)

И циклы существуют только для вывода вывода на экран. Если все пойдет хорошо, вы должны увидеть следующий вывод в окне Immediate. Объекты сотрудников хорошо отсортированы по идентификатору, полному имени, а затем по дате рождения.

В следующем примере Visual Basic для приложений (VBA) используется метод Sort для сортировки коллекции Items для папки Tasks по умолчанию по свойству "DueDate" и отображаются даты выполнения по очереди.

Можно ли отсортировать массив в VBA?

В языке VBA нет поддержки сортировки значений, хранящихся в массиве. Один из методов, который можно использовать для сортировки массивов, состоит в том, чтобы поместить данные на рабочий лист, отсортировать данные на рабочем листе, а затем прочитать значения из рабочего листа в массив.

Что такое коллекции в VBA?

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

Как использовать функцию сортировки в VBA?

Как отсортировать по алфавиту в Excel VBA?

Как отсортировать данные в алфавитном порядке (от А до Я) в столбце с помощью методов Excel и VBA

Выберите диапазон, содержащий данные, которые вы хотите отсортировать.
Выберите вкладку "Главная".
Нажмите «Сортировка и фильтр» в группе «Редактирование».
Нажмите "Пользовательская сортировка".
Выберите столбец, по которому вы хотите выполнить сортировку.
Выберите тип сортировки.

Что такое словарь в VBA?

Объект словаря VBA связывает ключи с элементами, чтобы вы могли получить свои элементы позже, просто вызвав ключ по имени. Словарь VBA — это объект верхнего уровня в библиотеке объектов Microsoft Scripting Runtime. Словари в VBA могут быть сложной концепцией для понимания.

Есть ли в VBA функция сортировки?

и вам нужны данные в определенной сортировке. В таких случаях можно использовать функцию VBA SORT, которая станет удобным инструментом для справки. Если вы хотите отсортировать ячейки A1:A10, вы должны указать Range(A1:A10) Order — это параметр, который позволяет вам сортировать данные в порядке возрастания или убывания.

Что такое объект коллекции в Visual Basic?

В Visual Basic коллекция — это класс, который полезен для гибкого управления группой объектов, чтобы выполнять различные операции, такие как вставка, обновление, удаление, получение и т. требования.

Что такое ReDim в VBA?

Операция ReDim используется для определения размера или изменения размера динамического массива, который уже был официально объявлен с помощью инструкции Private, Public или Dim с пустыми скобками (без индексов измерения). Вы можете многократно использовать оператор ReDim для изменения количества элементов и измерений в массиве.

Консультационные услуги Pearson Software

Процедуры сбора и словаря

На этой странице описывается около 14 процедур, связанных с объектами "Коллекция" и "Словарь".

Ссылки и библиотеки кода
Объект Dictionary является частью библиотеки Microsoft Scripting Runtime Library, поэтому ваш проект должен ссылаться на эту библиотеку. В VBA перейдите в меню «Инструменты», выберите «Ссылки», прокрутите и выберите «Среда выполнения Microsoft Scriping». В процедурах на этой странице используются функции VBA «Разделить», «Заменить» и «Объединить», поэтому у вас должен быть Excel 2000 или более поздней версии. Код не будет работать в Excel 97 или более ранних версиях. Для кода, используемого в функциях на этой странице, требуется модуль modArraySupport, доступный здесь в виде загружаемого файла bas и описанный на странице Функции для массивов VBA, а также модуль modQSortInPlace, доступный здесь в виде загружаемого файла bas и описанный в разделе Сортировка. Массивы со страницей QSort. Вы должны импортировать эти модули кода в свой проект VBA.

На этой странице описаны следующие процедуры:

ArrayToCollection
ArrayToDictionary
CollectionToArray
CollectionToDictionary
CollectionToRange
CreateDictionaryKeyFromCollectionItem
DictionaryToArray
DictionaryToCollection
DictionaryToRange
KeyExistsInCollection < br />RangeToCollection
RangeToDictionary
SortCollection
SortDictionary

Эта процедура берет значение из массива Arr и добавляет его в коллекцию Coll. Существующее содержимое Coll сохраняется. Arr может быть одномерным или двумерным массивом. Если Arr — одномерный массив, его содержимое добавляется в Coll без ключей. Если Arr является двумерным массивом, предполагается, что первый столбец массива является элементом, добавляемым в Coll, а второй столбец массива считается значением ключа для этого элемента. Если значение ключа представляет собой пустую строку, элемент добавляется без ключа. Если значение ключа не является пустой строкой, элемент добавляется в Coll с этим значением ключа. Код для ArrayToCollection показан ниже:

Эта процедура загружает содержимое двумерного массива Arr в указанный объект словаря Dict. Arr должен быть двумерным массивом. Первый столбец массива — это элемент, который нужно добавить в Dict, а второй столбец массива — это ключ для элемента. Эта функция возвращает True в случае успеха или False в случае ошибки (скорее всего, из-за дублирования ключа). Существующие записи в Dict сохраняются. Код для функции ArrayToDictionary показан ниже:

Эта процедура загружает массив со значением коллекции. Коллекция Coll может включать в себя любые типы переменных, включая объекты, другие типы, определяемые пользователем. Функция стирает существующее содержимое переменной Arr. Arr должен быть динамическим массивом. Процедура возвращает True в случае успеха или False в случае ошибки. Код для CollectionToArray показан ниже:

Эта процедура преобразует объект-коллекцию Coll в объект-словарь Dict. Поскольку элемент словаря должен иметь уникальный ключ, эта процедура вызывает процедуру с именем CreateDictionaryKeyFromCollectionItem, чтобы получить соответствующий ключ для использования в словаре. Вам решать написать код в функции CreateDictionaryKeyFromCollectionItem для создания уникального ключа. Существующее значение, поэтому словарь Dict Dictionary уничтожается, а словарь создается заново.

Процедура CreateDictionaryKeyFromCollectionItem объявляется следующим образом:

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

Код для CollectionToDictionary и CreateDictionaryKeyFromCollectionItem показан ниже:

Эта функция заполняет диапазон элементами коллекции. Элементы коллекции Coll будут записаны в диапазон, начиная с StartCells. Если диапазон StartCells составляет одну ячейку, коллекция записывается в ячейки, начиная с StartCell и перемещаясь вниз в том же столбце. Если StartCells ссылается на две ячейки, Коллекция записывается в той же ориентации, что и StartCells. То есть две ячейки StartCells находятся в одной строке (например, A1:B1), Коллекция записывается поперек строки, начиная с StartCells. Если две ячейки StartCells находятся в одном столбце (например, A1:A2), Коллекция записывается в столбец, начиная с StartCells. Если StartsCells содержит более двух ячеек, запись будет производиться ТОЛЬКО в эти ячейки. StartCells должен быть диапазоном из одной области.Если элемент в коллекции является объектом, он пропускается.

Код для CollectionToRange показан ниже:

Эта процедура заполняет массив Arr элементами и ключами словаря Dict. Arr должен быть объявлен как массив вариантов, например, созданный массив представляет собой двумерный массив с отсчетом от 0. Каждая строка в Arr является одним элементом Dict. Столбец 0 Arr — это ключ элемента словаря, а столбец 1 Arr — это элемент в словаре. Элемент Item of the Dictionary может быть любого типа, включая объекты, переменной, кроме типа User-Defined. Arr исправляется следующим образом:

Исходное содержимое Arr уничтожается. Код для DictionaryToArray показан ниже:

Эта процедура преобразует объект Dictionary Dict в объект Collection Coll. Эта процедура преобразует существующий Dictionary в новый объект Collection. Ключи из Словаря используются в качестве ключей для Коллекции. Эта функция возвращает True в случае успеха или False в случае ошибки. Содержимое Dict не изменяется. Если параметр PreserveColl опущен или имеет значение False, существующее содержимое коллекции Coll уничтожается. Если PreserveColl имеет значение True, существующее содержимое Coll сохраняется. Если PreserveColl имеет значение true, то существует вероятность того, что мы столкнемся с повторяющимися значениями ключа для коллекции. Если StopOnDuplicateKey опущен или имеет значение false, эта ошибка игнорируется, но элемент из Dict Dictionary не будет добавлен в Coll Collection. Если значение StopOnDuplicateKey равно True, процедура завершится, и не все элементы словаря Dict будут скопированы в коллекцию Coll. Коллекция Coll будет находиться в неопределенном состоянии. Код DictionaryToCollection показан ниже:

Эта функция заполняет диапазон элементами словаря. Элементы Dictionary Dict будут записаны в диапазон, начиная с StartCells. Если диапазон StartCells составляет одну ячейку, Словарь записывается в ячейки, начиная с StartCell и перемещаясь вниз в том же столбце. Если StartCells ссылается на две ячейки, словарь записывается в той же ориентации, что и StartCells. То есть две ячейки StartCells находятся в одной строке (например, A1:B1), Словарь записывается поперек строки, начиная с StartCells. Если две ячейки StartCells находятся в одном столбце (например, A1:A2), словарь записывается в столбце, начиная с StartCells. Если StartsCells содержит более двух ячеек, запись будет производиться ТОЛЬКО в эти ячейки. StartCells должен быть диапазоном из одной области. Если элемент в коллекции является объектом, он пропускается.

Код DictionaryToRange показан ниже:

Эта процедура возвращает True, если KeyName существует в Coll Collection, или False, если KeyName не существует в Coll. Код для KeyExistsInCollection показан ниже:

Эта процедура загружает объект "Коллекция" с элементами и, при необходимости, ключами из диапазона на рабочем листе. KeyRange — это диапазон ячеек, содержащих ключевые значения. Этот параметр может быть ничем. Если KeyRange имеет значение Nothing, элементы добавляются в коллекцию без ключей. ItemRange — это диапазон ячеек, содержащих элементы для добавления в коллекцию. ItemRange НЕ может быть ничем. Если и KeyRange, и ItemRange не равны Nothing, то они должны быть одного размера и содержать только одну область. Coll — это объект Collection, к которому должны быть добавлены элементы. Если Coll имеет значение Nothing, создается новая Коллекция. Параметр RangeAsObject указывает, следует ли добавить в коллекцию объект диапазона или текст ячейки. Если RangeAsObject опущен или False, текст каждой ячейки в ItemRange добавляется в коллекцию. Если RangeAsObject имеет значение True, в коллекцию добавляются объекты диапазона, а не значения в диапазонах.

StopOnDuplicateKey и ReplaceOnDuplicateKey определяют, какое действие следует предпринять при обнаружении повторяющегося ключа. Если StopOnDuplicateKey опущен или имеет значение True, функция завершается при обнаружении повторяющегося ключа и возвращает значение False. Элементы, добавленные в коллекцию до условия дублирования ключа, останутся в коллекции. ReplaceOnDuplicateKey игнорируется, если StopOnDuplicateKey опущен или имеет значение True. Если StopOnDuplicateKey имеет значение False, то если ReplaceOnDuplicateKey имеет значение True, существующий элемент с данным ключом удаляется из коллекции и заменяется новым элементом. Если ReplaceOnDuplicateKey имеет значение False или опущено, функция продолжит обработку, но элемент, вызвавший дублирование ключа, не будет добавлен в коллекцию.

Функция возвращает True в случае успеха или False в случае ошибки. Процедура показана ниже. Для этой функции требуется функция KeyExistsInCollection, описанная выше.

Эта процедура загружает объект Dictionary с элементами и ключами из диапазона на рабочем листе. KeyRange — это диапазон ячеек, содержащих ключевые значения. ItemRange — это диапазон ячеек, содержащих элементы для добавления в коллекцию.И KeyRange, и ItemRange не должны иметь значение Nothing, они должны быть одного размера и содержать только одну область Area. Dictis объект Dictionary, к которому должны быть добавлены элементы. Если Dict имеет значение Nothing, создается новый словарь. Параметр RangeAsObject указывает, следует ли добавить в словарь объект диапазона или текст ячейки. Если RangeAsObject опущен или False, текст каждой ячейки в ItemRange добавляется в словарь. Если RangeAsObject имеет значение True, в словарь добавляются объекты диапазона, а не значения в диапазонах.

StopOnDuplicateKey и ReplaceOnDuplicateKey определяют, какое действие следует предпринять при обнаружении повторяющегося ключа. Если StopOnDuplicateKey опущен или имеет значение True, функция завершается при обнаружении повторяющегося ключа и возвращает значение False. Элементы, добавленные в Словарь до условия дублирования ключа, останутся в Словаре. ReplaceOnDuplicateKey игнорируется, если StopOnDuplicateKey опущен или имеет значение True. Если StopOnDuplicateKey имеет значение False, то если ReplaceOnDuplicateKey имеет значение True, существующий элемент с данным ключом удаляется из Словаря и заменяется новым элементом. Если ReplaceOnDuplicateKey имеет значение False или опущено, функция продолжит обработку, но элемент, вызвавший дублирование ключа, не будет добавлен в словарь.

Процедура показана ниже:

Эта процедура сортирует объект Collection по значениям элементов в Collection. Объект Collection должен содержать только простые типы переменных — объекты, массивы и определяемые пользователем типы не допускаются. (Если вы хотите отсортировать коллекцию, содержащую переменные объекта, используйте описанную ниже процедуру CollectionToArray, отсортируйте массив с помощью процедуры QSortObjectsInPlace, описанной на странице «Сортировка массивов объектов», а затем загрузите отсортированный массив объектов обратно в коллекцию.)

В этой процедуре Coll — это коллекция, которую вы хотите отсортировать, Descending указывает, что сортировка должна быть в порядке убывания (значение по умолчанию для этого параметра — False, сортировка в порядке возрастания), а CompareMode указывает, будут ли текстовые сравнения нечувствительны к регистру ("A" = "a", CompareMode = vbTextCompare) или чувствительны к регистру ("A" <> "a", CompareMode = vbBinaryCompare). Если параметр CompareMode опущен, по умолчанию используется vbTextCompare для сортировки без учета регистра.

Одно важное предостережение относительно сортировки коллекций: поскольку невозможно получить ключ элемента в коллекции (это связано с плохим дизайном объекта коллекции), все значения ключа теряются при сортировке коллекции. отсортировано. Если вам нужно сохранить ключи, вы должны в первую очередь использовать объект Dictionary, а не объект Collection. Процедура CollectionToDictionary, описанная ниже, преобразует объект Collection в объект Dictionary, но вы должны предоставить код, который создает уникальный ключ для каждого элемента в Collection, который будет использоваться в качестве ключа во вновь созданном объекте Dictionary. Код для SortCollection показан ниже:

Эта процедура сортирует объект Dictionary либо по значению ключа, либо по значению элемента. Если SortByKey имеет значение True, то словарь сортируется по значениям ключей элементов в словаре. В этом случае словарь может содержать данные любого типа, кроме пользовательских типов. Он может включать переменные типа объекта. Если SortByKey имеет значение False, Словарь сортируется по значению элементов в Словаре. В этом случае Словарь может включать только простые типы переменных. Он не может содержать объекты, массивы или определяемые пользователем типы. По умолчанию сортировка идет по возрастанию. Установите для параметра Descending значение True, чтобы сортировать в порядке убывания. По умолчанию при сравнении текста не учитывается регистр ("A" = "a"). Для сортировки в режиме с учетом регистра ("A" <> "a") установите для параметра CompareMode значение vbBinaryCompare. Код для SortDictionary показан ниже:

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

В Excel VBA уже есть встроенные коллекции. Примером может служить коллекция Sheets. Для каждого листа в книге есть элемент в коллекции Sheets.

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

Например, вы можете использовать коллекцию для получения информации о конкретном рабочем листе. Например, вы можете увидеть имя рабочего листа, а также то, виден он или нет. Используя цикл For Each, вы можете перебирать каждый рабочий лист в коллекции.

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

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

Обратите внимание, что в коллекциях VBA номер индекса начинается с 1, а не с 0

Коллекции и массивы

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

Массивы многомерны, тогда как коллекции одномерны. Вы можете измерить массив несколькими измерениями, например,

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

При заполнении массива вам потребуется отдельная строка кода для помещения значения в каждый элемент массива. Если бы у вас был двумерный массив, вам фактически потребовалось бы 2 строки кода — одна строка для обращения к первому столбцу и одна строка для обращения ко второму столбцу. С объектом Collection вы просто используете метод Add, чтобы новый элемент просто добавлялся в коллекцию, а значение индекса автоматически корректировалось в соответствии с требованиями.
Если вам нужно удалить элемент данных, то в массиве это сделать сложнее. Вы можете установить значения элемента в пустое значение, но сам элемент все еще существует в массиве. Если вы используете цикл For Next для итерации по массиву, цикл вернет пустое значение, которое потребуется кодировать, чтобы убедиться, что пустое значение игнорируется. В коллекции вы используете методы Add или Remove, и все индексирование и изменение размера выполняется автоматически. Удаленный элемент полностью исчезает. Массивы удобны для фиксированного размера данных, но коллекции лучше подходят для случаев, когда количество данных может меняться.
Коллекции доступны только для чтения, тогда как значения массива можно изменить с помощью VBA. В коллекции вам придется сначала удалить значение, которое нужно изменить, а затем добавить новое измененное значение.
В массиве для элементов можно использовать только один тип данных, который задается при измерении массива. Однако в массиве вы можете использовать пользовательские типы данных, которые вы разработали сами. У вас может быть очень сложная структура массива с использованием пользовательского типа данных, который, в свою очередь, имеет несколько пользовательских типов данных под ним. В коллекцию можно добавить используемые типы данных для каждого элемента. У вас может быть числовое значение, дата или строка — объект коллекции примет любой тип данных. Если вы попытаетесь поместить строковое значение в массив с числовыми размерами, это приведет к появлению сообщения об ошибке.
Коллекции, как правило, проще в использовании, чем массивы. С точки зрения программирования, когда вы создаете объект-коллекцию, у него есть только два метода (Добавить и Удалить) и два свойства (Счетчик и Элемент), поэтому программировать объект совсем несложно.
Коллекции могут использовать ключи для поиска данных. Массивы не имеют этой функции и требуют циклического кода для перебора массива для поиска определенных значений.
Размер массива необходимо определить при его первом создании. Вы должны иметь представление о том, сколько данных он будет хранить. Если вам нужно увеличить размер массива, вы можете использовать «ReDim», чтобы изменить его размер, но вам нужно использовать ключевое слово «Сохранить», если вы не хотите потерять данные, уже содержащиеся в массиве. Размер коллекции указывать не нужно. Он просто автоматически увеличивается и уменьшается по мере добавления или удаления элементов.

Область объекта коллекции

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

Если вы хотите, чтобы ваша коллекция была доступна для всего кода в вашем модуле кода, вам необходимо объявить объект коллекции в разделе "Объявление" в верхней части окна модуля

Это гарантирует, что весь ваш код в этом модуле сможет получить доступ к коллекции. Если вы хотите, чтобы любой модуль в вашей книге имел доступ к коллекции, определите его как глобальный объект

Читайте также: