Объект ArrayList похож на объект Collection, но имеет гораздо больше методов и свойств и, следовательно, гораздо большую гибкость с точки зрения программирования.
У объекта Collection есть только два метода (Add, Remove) и два свойства (Count, Item), тогда как у Array List их гораздо больше. Кроме того, объект Collection доступен только для чтения. После добавления значений индексированное значение не может быть изменено, тогда как в списке массивов редактирование возможно.
Многие методы Array List используют параметры. В отличие от многих стандартных методов VBA, ни один из этих параметров не является необязательным. Кроме того, некоторые методы и свойства не всегда используют заглавные буквы при вводе так же, как в Excel VBA. Однако они все еще работают.
Размер объекта ArrayList увеличивается и уменьшается в зависимости от количества содержащихся в нем элементов. Перед использованием в качестве массива не требуется определять размеры.
Список массивов является одномерным (таким же, как объект Collection), а тип данных по умолчанию - Variant, что означает, что он принимает данные любого типа, будь то числовые, текстовые или датированные.
Во многих отношениях Array List устраняет ряд недостатков объекта Collection. Он, безусловно, гораздо более гибок в том, что он может делать.
Объект Array List не входит в стандартную библиотеку VBA. Вы можете использовать его в своем коде Excel VBA, используя позднюю или раннюю привязку
| 1234 | Sub LateBindingExample ()Dim MyList As ObjectУстановите MyList = CreateObject ("System.Collections.ArrayList")Конец подписки |
| 123 | Sub EarlyBindingExample ()Dim MyList как новый ArrayListКонец подписки |
Чтобы использовать пример раннего связывания, вы должны сначала ввести ссылку в VBA на файл «mscorlib.tlb».
Вы делаете это, выбирая «Инструменты | Ссылки ‘из окна редактора Visual Basic (VBE). Появится всплывающее окно со всеми доступными ссылками. Прокрутите вниз до «mscorlib.dll» и установите рядом с ним флажок. Нажмите OK, и эта библиотека теперь является частью вашего проекта:
Одним из больших недостатков объекта Array List является то, что он не имеет «Intellisense». Обычно, когда вы используете объект в VBA, например диапазон, вы увидите всплывающий список всех доступных свойств и методов. Вы не получите этого с объектом Array List, и иногда требуется тщательная проверка, чтобы убедиться, что вы правильно написали метод или свойство.
Кроме того, если вы нажмете F2 в окне VBE и выполните поиск по «arraylist», ничего не будет отображаться, что не очень помогает разработчику.
Ваш код будет работать значительно быстрее с ранним связыванием, потому что он все скомпилирован заранее. При позднем связывании объект должен компилироваться по мере выполнения кода.
Распространение вашего приложения Excel, содержащего список массивов
Как уже указывалось, объект ArrayList не является частью Excel VBA. Это означает, что любой из ваших коллег, которым вы распространяете приложение, должен иметь доступ к файлу «mscorlib.tlb».
Этот файл обычно находится в:
C: \ Windows \ Microsoft.NET \ Framework \ v4.0.30319
Возможно, стоит написать некоторый код (с использованием метода Dir), чтобы проверить, существует ли этот файл, когда пользователь загружает приложение, чтобы он испытал «мягкую посадку», если он не найден. Если его нет и код запускается, возникнут ошибки.
Кроме того, у пользователя должна быть установлена правильная версия .Net Framework. Даже если у пользователя более поздняя версия, необходимо установить V3.5, иначе ваше приложение не будет работать.
Объем объекта списка массивов
С точки зрения объема объект Array List доступен только при открытой книге. Он не сохраняется при сохранении книги. Если книга открывается повторно, объект Array List необходимо воссоздать с использованием кода VBA.
Если вы хотите, чтобы ваш список массивов был доступен для всего кода в вашем модуле кода, вам необходимо объявить объект списка массивов в разделе «Объявление» в самом верху окна модуля.
Это гарантирует, что весь ваш код в этом модуле сможет получить доступ к списку массивов. Если вы хотите, чтобы какой-либо модуль в вашей книге имел доступ к объекту Array List, определите его как глобальный объект.
| 1 | Глобальная коллекция MyCollection как новый список массивов |
Заполнение и чтение из вашего списка массивов
Самое основное действие, которое вы хотите предпринять, - это создать список массивов, поместить в него некоторые данные и затем доказать, что данные можно прочитать. Все примеры кода в этой статье предполагают, что вы используете раннее связывание и добавили «mscorlib.tlb» в ссылки VBA, как описано выше.
| 123456789101112 | Sub ArrayListExample ()‘Создать новый объект списка массивовDim MyList как новый ArrayList‘Добавить элементы в списокMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"‘Просмотрите список массивов, чтобы подтвердить значенияДля N = 0 To MyList.Count - 1MsgBox MyList (N)Следующий NКонец подписки |
В этом примере создается новый объект ArrayList, он заполняется 3 элементами и выполняется итерация по списку, отображающему каждый элемент.
Обратите внимание, что индекс ArrayList начинается с 0, а не с 1, поэтому вам нужно вычесть 1 из значения Count.
Вы также можете использовать цикл For… Each для чтения значений:
| 123456789101112 | Sub ArrayListExample ()‘Создать новый объект списка массивовDim MyList как новый ArrayList‘Добавить элементы в списокMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"‘Просмотрите список массивов, чтобы подтвердить значенияДля каждого я в моем спискеMsgBox IДалее яКонец подписки |
Редактирование и изменение элементов в списке массива
Основным преимуществом списка массивов перед коллекцией является то, что элементы в списке можно редактировать и изменять в вашем коде. Объект Collection доступен только для чтения, тогда как объект Array List доступен для чтения и записи.
| 123456789101112131415 | Sub ArrayListExample ()‘Создать новый объект списка массивовDim MyList как новый ArrayList‘Добавить элементы в списокMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"«Измените элемент 1 с« Item2 »на« Changed »MyList (1) = "Изменено"«Просмотрите список массивов, чтобы убедиться, что изменение сработало.Для каждого я в моем списке‘Отображаемое название элементаMsgBox IДалее яКонец подписки |
В этом примере второй элемент, «Item2», изменяется на значение «Changed» (помните, что индекс начинается с 0). Когда итерация запускается в конце кода, будет отображаться новое значение.
Добавление массива значений в список массивов
Вы можете вводить значения в свой список массивов, используя массив, содержащий список этих значений или ссылки на значения ячеек на листе.
| 123456789101112131415161718 | Sub AddArrayExample ()‘Создать объект списка массивовDim MyList как новый ArrayList‘Перебирать значения массива, добавляя их в список массивовДля каждого v в массиве ("A1", "A2", "A3")‘Добавьте каждое значение массива в списокMyList.Add vСледующий‘Перебирать значения массива со ссылками на листы, добавляя их в список массивовДля каждого v в массиве (Диапазон ("A5"). Значение, Диапазон ("A6"). Значение)MyList.Add vСледующий‘Просмотрите список массивов, чтобы подтвердить значенияДля N = 0 To MyList.Count - 1‘Показать элемент спискаMsgBox MyList.Item (N)Следующий NКонец подписки |
Чтение / получение диапазона элементов из списка массива
Используя метод GetRange в списке массивов, вы можете указать количество последовательных элементов, которые необходимо извлечь. Два обязательных параметра - это начальная позиция индекса и количество элементов, которые нужно получить. Код заполняет второй объект Array List подмножеством элементов, которые затем можно читать отдельно.
| 123456789101112131415161718 | Sub ReadRangeExample ()‘Определите объектыDim MyList как новый ArrayList, MyList1 как объект«Добавить элементы в объект« Мой список »MyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item6"MyList.Add "Item4"MyList.Add "Item7"‘Захватить 4 элемента в« MyList », начиная с позиции индекса 2.Установите MyList1 = MyList.GetRange (2, 4)‘Итерируйте по объекту MyList1, чтобы отобразить подмножество элементовДля каждого я в MyList1‘Отображаемое название элементаMsgBox IДалее яКонец подписки |
Поиск элементов в списке массива
Вы можете проверить, есть ли указанный элемент в вашем списке, используя метод «Содержит». Это вернет True или False
| 1 | MsgBox MyList.Contains ("Item2") |
Вы также можете узнать фактическую позицию индекса с помощью метода «IndexOf». Вам нужно указать начальный индекс для поиска (обычно 0). Возвращаемое значение - это индекс первого экземпляра найденного элемента. Затем вы можете использовать цикл, чтобы изменить начальную точку на следующее значение индекса, чтобы найти дальнейшие экземпляры, если есть несколько повторяющихся значений.
Если значение не найдено, возвращается значение -1.
Этот пример демонстрирует использование «Содержит», элемент не найден, и цикл по списку массивов, чтобы найти положение всех повторяющихся элементов:
| 1234567891011121314151617181920212223242526 | Sub SearchListExample ()‘Определите список массивов и переменныеDim MyList как новый список ArrayList, Sp как целое число, Pos как целое число‘Добавить новые элементы, включая дубликатMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item1"«Проверить, есть ли элемент 2 в списке» - возвращает значение «Истина».MsgBox MyList.Contains ("Item2")‘Получить индекс несуществующего значения - возвращает -1MsgBox MyList.IndexOf ("Элемент", 0)‘Установите начальную позицию для поиска на нольSp = 0«Просмотрите список, чтобы получить все позиции элемента 1».Делать‘Получить позицию индекса следующего« Item1 »на основе позиции в переменной« Sp »Pos = MyList.IndexOf ("Item1", Sp)‘Если других экземпляров« Item1 »не найдено, выйдите из цикла.Если Pos = -1, то выйти из Do‘Показать следующий найденный экземпляр и позицию индексаMsgBox MyList (Pos) & "по индексу" & Pos‘Добавьте 1 к последнему найденному значению индекса - теперь это станет новой начальной позицией для следующего поиска.Sp = Pos + 1ПетляКонец подписки |
Обратите внимание, что используемый поисковый текст чувствителен к регистру и подстановочные знаки не принимаются.
Вставка и удаление элементов
Если вы не хотите добавлять свои элементы в конец списка, вы можете вставить их в определенную позицию индекса, чтобы новый элемент находился в середине списка. Номера индексов будут автоматически скорректированы для последующих позиций.
| 123456789101112131415 | Sub InsertExample ()‘Определить объект списка массивовDim MyList как новый ArrayList‘Добавить элементы в список массиваMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item1""Вставить" Item6 "в позицию индекса 2MyList.Insert 2, "Item6"‘Перебирайте элементы в списке массивов, чтобы показать новый порядок и позицию индекса.Для N = 0 To MyList.Count - 1MsgBox MyList (N) и «Индекс» и NСледующий NКонец подписки |
В этом примере "Item6" добавляется в список в позиции индекса 2, поэтому "item3", который был в позиции индекса 2, теперь перемещается в позицию индекса 3.
Отдельный элемент можно удалить с помощью метода «Удалить».
| 1 | MyList.Remove "Item" |
Обратите внимание, что если имя элемента не найдено, ошибки не возникает. Все последующие порядковые номера будут изменены в соответствии с удалением.
Если вы знаете позицию индекса элемента, вы можете использовать метод «RemoveAt», например.
| 1 | MyList.RemoveAt 2 |
Обратите внимание, что если указанная позиция индекса больше, чем количество элементов в списке массива, то будет возвращена ошибка.
Вы можете удалить диапазон значений из списка с помощью метода RemoveRange. Параметры - это начальный индекс, а затем количество элементов, которые нужно удалить, например.
| 1 | MyList.RemoveRange 3, 2 |
Обратите внимание, что вы получите ошибку в своем коде, если количество элементов, смещенных от начального значения, больше, чем количество элементов в списке массивов.
В обоих методах «RemoveAt» и «RemoveRange» было бы целесообразно использовать некоторый код, чтобы проверить, не превышают ли указанные номера индексов общее количество элементов в списке массивов, чтобы уловить любые возможные ошибки. Свойство «Count» даст общее количество элементов в списке массива.
| 12345678910111213141516171819202122232425 | Sub RemoveExample ()‘Определить объект списка массивовDim MyList как новый ArrayList‘Добавить элементы в список массиваMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item1"MyList.Add "Item4"MyList.Add "Item5""Вставить" Item6 "в позицию индекса 2MyList.Insert 2, "Item6"‘Remove‘ Item2 ’MyList.Remove "Item2"‘Remove‘ Item ’- этого нет в списке массивов, но не вызывает ошибкуMyList.Remove "Item"‘Удалите элемент из позиции индекса 2MyList.RemoveAt 2‘Удалите 2 последовательных элемента, начиная с позиции индекса 2.MyList.RemoveRange 3, 2‘Просмотрите список массивов, чтобы показать, что осталось, и в какой позиции индекса он сейчас находится.Для N = 0 To MyList.Count - 1MsgBox MyList (N) и «Индекс» и NСледующий NКонец подписки |
Обратите внимание: если вы используете «RemoveAt» для удаления элемента в определенной позиции, то, как только этот элемент будет удален, все последующие позиции индекса изменятся. Если у вас есть несколько удалений с использованием позиции индекса, то хорошей идеей будет начать с самого высокого номера индекса и сделать шаг назад до нулевой позиции, чтобы вы всегда удаляли правильный элемент. Таким образом, у вас не будет проблем
Сортировка списка массивов
Еще одно большое преимущество перед коллекцией состоит в том, что вы можете отсортировать элементы в порядке возрастания или убывания.
Объект Array List - единственный объект в Excel VBA с методом сортировки. Метод сортировки очень быстрый, и это может быть важным соображением при использовании списка массивов.
В объекте коллекции требовалось некоторое нестандартное мышление, чтобы отсортировать все элементы, но со списком массивов это очень просто.
Метод «Сортировка» выполняет сортировку по возрастанию, а метод «Обратный» - по убыванию.
| 12345678910111213141516171819202122 | Sub ArrayListExample ()‘Создать объект списка массивовDim MyList как новый ArrayList‘Добавить товары в несортированном порядкеMyList.Add "Item1"MyList.Add "Item3"MyList.Add "Item2"‘Сортировка товаров в порядке возрастанияMyList.Sort‘Перебирайте элементы, чтобы отображать их в порядке возрастания.Для каждого я в моем списке‘Отображаемое название элементаMsgBox IДалее я‘Сортировка товаров в порядке убыванияMyList.Reverse‘Перебирайте элементы, чтобы отображать их в порядке убыванияДля каждого я в моем списке‘Отображаемое название элементаMsgBox IДалее яКонец подписки |
Клонирование списка массивов
Список массивов имеет возможность создать клон или копию самого себя. Это полезно, если пользователь вносит изменения в элементы с помощью внешнего интерфейса и вашего кода VBA, но вам нужно сохранить копию элементов в их исходном состоянии в качестве резервной копии.
Это может предоставить пользователю функцию «Отменить». Возможно, они внесли изменения и захотят вернуться к исходному списку.
| 123456789101112131415 | Sub CloneExample ()‘Определите два объекта - список массивов и объектDim MyList как новый ArrayList, MyList1 как объект‘Заполните первый объект элементамиMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"‘Скопируйте MyList в MyList1Установите MyList1 = MyList.Clone‘Просмотрите MyList1, чтобы доказать клонированиеДля каждого я в MyList1‘Отображаемое название элементаMsgBox IДалее яКонец подписки |
«MyList1» теперь содержит все элементы из «MyList» в том же порядке.
Копирование массива списка в обычный объект массива VBA
Вы можете использовать простой метод для копирования списка массивов в обычный массив VBA:
| 123456789101112131415 | Sub ArrayExample ()‘Создайте объект списка массивов и стандартный объект массиваDim MyList как новый ArrayList, NewArray как вариант‘Заполните список массива элементамиMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"‘Скопируйте список массивов в новый массивNewArray = MyList.ToArray‘Итерируйте по новому массиву - обратите внимание, что счетчик списка массивов обеспечивает максимальный индексДля N = 0 To MyList.Count - 1‘Отображаемое название элементаMsgBox NewArray (N)Следующий NКонец подписки |
Копирование массива списка в диапазон листа
Вы можете скопировать свой список массивов на конкретный рабочий лист и ссылку на ячейку без необходимости перебирать список массивов. Вам нужно только указать первую ссылку на ячейку
| 123456789101112131415 | Sub RangeExample ()‘Создать новый объект списка массивовDim MyList как новый ArrayList‘Добавить элементы в списокMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"‘Очистить целевой листЛисты ("Лист1"). UsedRange.Clear‘Копировать элементы в строкеЛисты ("Sheet1"). Диапазон ("A1"). Resize (1, MyList.Count) .Value = MyList.toArray‘Копировать элементы вниз по столбцуЛисты ("Sheet1"). Range ("A5"). Resize (MyList.Count, 1) .Value = _WorksheetFunction.Transpose (MyList.toArray)Конец подписки |
Очистить все элементы из списка массива
Существует простая функция (Очистить) для полной очистки списка массивов.
| 1234567891011121314 | Sub ClearListExample ()‘Создать объект списка массивовDim MyList как новый ArrayList‘Добавить новые товарыMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"‘Показать количество товаровMsgBox MyList.Count‘Очистить все элементыMyList.Clear‘Покажите количество предметов, чтобы доказать, что очистка сработалаMsgBox MyList.CountКонец подписки |
В этом примере создаются элементы в списке массивов, а затем этот список очищается. Окна сообщений подтверждаются до и после количества элементов в списке массива.
Сводка методов списка массивов для Excel VBA
| Задача | Параметры | Примеры |
| Добавить / изменить элемент | Ценить | MyList.Add «Item1» |
| MyList (4) = «Item2» | ||
| Клонировать список массивов | Никто | Dim MyList As Object |
| Установите MyList2 = MyList.Clone | ||
| Копировать в массив | Никто | Dim MyArray как вариант |
| MyArray = MyList.ToArray | ||
| Копировать в диапазон листа (строку) | Никто | Листы («Sheet1»). Диапазон («A1»). Resize (1, MyList.Count) .Value = MyList.ToArray |
| Копировать в диапазон листа (столбец) | Никто | Листы («Лист1»). Диапазон («A3»). Изменить размер (MyList.Count, 1) .Value = WorksheetFunction.Transpose (MyList.ToArray) |
| Создавать | «System.Collections.ArrayList» | Dim MyList As Object |
| Установите MyList = CreateObject («System.Collections.ArrayList») | ||
| Объявить | N / A | Dim MyList As Object |
| Найти / проверить, существует ли предмет | Предмет для поиска | MyList.Contains («Элемент2») |
| Найдите позицию элемента в ArrayList | 1. Предмет, который нужно найти. | Тусклый индекс |
| 2. Позиция для начала поиска. | IndexNo = MyList.IndexOf («Элемент3», 0) | |
| IndexNo = MyList.IndexOf («Элемент5»; 3) | ||
| Получить количество предметов | Никто | MsgBox MyList.Count |
| Вставить элемент | 1. Индекс - позиция для вставки. | MyList.Insert 0, «Item5» |
| 2 Значение - объект или значение для вставки. | MyList.Insert 4, «Item7» | |
| Прочитать предмет | Индекс - длинное целое число | MsgBox MyList.Item (0) |
| MsgBox MyList.Item (4) | ||
| Прочитать элемент, добавленный последним | Индекс - длинное целое число | MsgBox MyList.Item (list.Count - 1) |
| Прочитать элемент, добавленный первым | Индекс - длинное целое число | MsgBox MyList.Item (0) |
| Читать все (для каждого) | N / A | Тусклый элемент как вариант |
| Для каждого элемента в MyList | ||
| Элемент MsgBox | ||
| Следующий элемент | ||
| Читать все (для) | Индекс - длинное целое число | Тусклый я до тех пор, пока |
| Для i = 0 To MyList.Count - 1 | ||
| MsgBox i | ||
| Далее я | ||
| Удалить все элементы | Никто | MyList.Clear |
| Удалить элемент в позиции | Позиция индекса, где находится элемент | MyList.RemoveAt 5 |
| Удалить элемент по имени | Элемент, который нужно удалить из ArrayList | MyList.Remove «Item3» |
| Удалить ряд предметов | 1. Индекс - начальная позиция. | MyList.RemoveRange 4,3 |
| 2. Счетчик - количество элементов, которые нужно удалить. | ||
| Сортировать по убыванию | Никто | MyList.Reverse |
| Сортировать по возрастанию | Не | MyList.Sort |
