Об'єкт ArrayList схожий на об'єкт Collection, але він має набагато більше методів і властивостей, а отже, набагато більшу гнучкість з точки зору програмування.
Об’єкт Collection має лише два методи (Add, Remove) та дві властивості (Count, Item), тоді як List Array List має набагато більше. Крім того, об'єкт Collection тільки для читання. Після додавання значень індексоване значення не можна змінити, тоді як у списку масивів можна редагувати.
Багато методів списку масивів використовують параметри. На відміну від багатьох стандартних методів VBA, жоден із цих параметрів не є необов’язковим. Крім того, деякі методи та властивості не завжди вводяться з великої літери при введенні так само, як у Excel VBA. Однак вони все ще працюють.
Об'єкт ArrayList розширюється і скорочується за розміром відповідно до того, скільки елементів він містить. Перед використанням його не потрібно розмірювати, як масив.
Список масивів є одновимірним (таким же, як і об'єкт Collection), а типом даних за замовчуванням є Variant, що означає, що він прийматиме будь -які типи даних, будь то числові, текстові або дати.
Багато в чому Список масивів усуває ряд недоліків об'єкта Collection. Безумовно, він набагато гнучкіший у своїх можливостях.
Об'єкт Список масивів не є частиною стандартної бібліотеки VBA. Ви можете використовувати його у своєму коді Excel VBA, використовуючи пізнє або раннє прив’язування
| 1234 | Sub LateBindingExample ()Затемнити MyList як об'єктВстановити MyList = CreateObject ("System.Collections.ArrayList")End Sub |
| 123 | Sub EarlyBindingExample ()Dim MyList As New ArrayListEnd Sub |
Для того, щоб скористатися раннім прикладом прив’язки, потрібно спочатку ввести посилання у VBA на файл ‘mscorlib.tlb’
Для цього виберіть «Інструменти | Посилання ‘з вікна редактора Visual Basic (VBE). З'явиться спливаюче вікно з усіма доступними посиланнями. Прокрутіть униз до "mscorlib.dll" і поставте прапорець біля нього. Натисніть OK, і ця бібліотека тепер є частиною вашого проекту:
Одним з великих недоліків об’єкта списку масивів є те, що він не має „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 повинен бути встановлений, інакше ваша програма не працюватиме
Область застосування об’єкта списку масивів
З точки зору обсягу, об’єкт Список масивів доступний лише тоді, коли книга відкрита. Він не зберігається, коли зберігається книга. Якщо книга знову відкривається, то об’єкт Список масивів потрібно створити за допомогою коду VBA.
Якщо ви хочете, щоб ваш список масивів був доступний для всього коду у вашому модулі коду, вам потрібно оголосити об’єкт списку масивів у розділі «Оголошення» у верхній частині вікна модуля.
Це гарантує, що весь ваш код у цьому модулі зможе отримати доступ до списку масивів. Якщо ви хочете, щоб будь -який модуль у вашій книзі мав доступ до об’єкта Список масивів, то визначте його як глобальний об’єкт
| 1 | Глобальна колекція MyCollection як новий список масивів |
Заповнення та читання з вашого списку масивів
Найпростіша дія, яку ви хочете виконати, - це створити список масивів, внести в нього деякі дані, а потім довести, що дані можна прочитати. Усі приклади коду в цій статті передбачають, що ви використовуєте раннє прив’язування, і додали "mscorlib.tlb" до посилань VBA, як описано вище
| 123456789101112 | Sub ArrayListExample ()"Створити новий об'єкт списку масивівDim MyList As New ArrayList"Додати елементи до спискуMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"«Повторюйте список масивів, щоб довести значенняДля N = 0 до MyList.Count - 1Мій список MsgBox (N)Далі NEnd Sub |
У цьому прикладі створюється новий об’єкт ArrayList, заповнюється його 3 елементами та відбувається ітерація у списку, що відображає кожен елемент.
Зверніть увагу, що індекс ArrayList починається з 0, а не з 1, тому вам потрібно відняти 1 від значення Count
Ви також можете використовувати цикл "Для … Кожного", щоб прочитати значення:
| 123456789101112 | Sub ArrayListExample ()"Створити новий об'єкт списку масивівDim MyList As New ArrayList"Додати елементи до спискуMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"«Повторюйте список масивів, щоб довести значенняДля кожного я в MyListMsgBox IДалі яEnd Sub |
Редагування та зміна елементів у списку масивів
Основною перевагою списку масивів перед колекцією є те, що елементи у списку можна редагувати та змінювати у вашому коді. Об'єкт Collection тільки для читання, тоді як об'єкт Array List - для читання / запису
| 123456789101112131415 | Sub ArrayListExample ()"Створити новий об'єкт списку масивівDim MyList As New ArrayList"Додати елементи до спискуMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3""Змінити пункт 1 з" Елемент2 "на" Змінено "MyList (1) = "Змінено"«Повторіть список масивів, щоб довести, що зміни спрацювалиДля кожного я в MyList"Відобразити назву елементаMsgBox IДалі яEnd Sub |
У цьому прикладі другий елемент, "Item2", змінюється на значення "Changed" (пам'ятайте, що індекс починається з 0). Коли ітерація виконується в кінці коду, відображатиметься нове значення
Додавання масиву значень до списку масивів
Ви можете вводити значення до свого списку масивів за допомогою масиву, що містить список цих значень або посилання на значення клітинок на аркуші
| 123456789101112131415161718 | Sub AddArrayExample ()‘Створити об’єкт списку масивівDim MyList As New ArrayList"Перебирати значення масиву, додаючи їх до списку масивівДля кожного v масиву ("A1", "A2", "A3")"Додайте значення кожного масиву до спискуMyList.Add vДалі"Перебирати значення масиву з посиланнями на аркуші, додаючи їх до списку масивівДля кожного v в масиві (діапазон ("A5"). Значення, діапазон ("A6"). Значення)MyList.Add vДалі«Повторюйте список масивів, щоб довести значенняДля N = 0 до MyList.Count - 1"Відобразити елемент спискуMsgBox MyList.Item (N)Далі NEnd Sub |
Читання / отримання діапазону елементів зі списку масивів
Використовуючи метод GetRange у списку масивів, ви можете вказати низку послідовних елементів, які потрібно отримати. Необхідними двома параметрами є вихідна позиція індексу та кількість елементів, які потрібно отримати. Код заповнює другий об'єкт списку масивів підмножиною елементів, які потім можна прочитати окремо.
| 123456789101112131415161718 | Sub ReadRangeExample ()«Визначте об’єктиЗатемнити MyList як новий ArrayList, MyList1 як об'єкт"Додати елементи до об'єкта" MyList "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Далі яEnd Sub |
Пошук елементів у списку масивів
Ви можете перевірити, чи є названий елемент у вашому списку, за допомогою методу "Містить". Це поверне True або False
| 1 | MsgBox MyList.Contains ("Item2") |
Ви також можете знайти фактичну позицію індексу за допомогою методу "IndexOf". Вам потрібно вказати початковий індекс для пошуку (зазвичай 0). Повернене значення - це індекс першого екземпляра знайденого елемента. Потім можна використовувати цикл, щоб змінити початкову точку на наступне значення індексу, щоб знайти подальші екземпляри, якщо є кілька повторюваних значень.
Якщо значення не знайдено, повертається значення -1
У цьому прикладі демонструється використання "Містить", елемент не знайдено, а також перегляд списку масивів для визначення позиції всіх повторюваних елементів:
| 1234567891011121314151617181920212223242526 | Sub SearchListExample ()"Визначте список масивів та змінніDim MyList As New ArrayList, Sp As Integer, Pos As Integer"Додайте нові елементи, включаючи дублікатMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item1""Тест на наявність" Item2 "у списку - повертає TrueMsgBox MyList.Contains ("Item2")"Отримати індекс неіснуючого значення -повертає -1MsgBox MyList.IndexOf ("Елемент", 0)"Встановіть початкову позицію пошуку на нульSp = 0"Повторюйте список, щоб отримати всі позиції" Item1 "Зробити"Отримайте позицію індексу наступного" Елементу1 "на основі позиції у змінній" Sp "Pos = MyList.IndexOf ("Item1", Sp)"Якщо не знайдено жодних інших екземплярів" Item1 ", вийдіть із циклуЯкщо Pos = -1, то вийдіть з Do"Відображення наступного знайденого екземпляра та позиції індексуMsgBox MyList (Pos) & "за індексом" & Pos"Додайте 1 до останнього знайденого значення індексу - це тепер стає новою початковою позицією для наступного пошукуSp = Pos + 1ПетляEnd Sub |
Зауважте, що використаний текст пошуку чутливий до регістру, і символи підстановки не приймаються.
Вставлення та вилучення елементів
Якщо ви не хочете додавати свої елементи в кінець списку, ви можете вставити їх у певну позицію індексу, щоб новий елемент знаходився посередині списку. Номери індексу будуть автоматично коригуватися для наступних пунктів.
| 123456789101112131415 | Sub InsertExample ()‘Визначте об’єкт списку масивівDim MyList As New ArrayList"Додати елементи до списку масивівMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item1""Вставте" елемент 6 "у позицію індексу 2MyList.Insert 2, "Item6""Повторюйте елементи списку масивів, щоб показати новий порядок та позицію індексуДля N = 0 до MyList.Count - 1MsgBox MyList (N) & "Index" & NДалі NEnd Sub |
У цьому прикладі "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 As New ArrayList"Додати елементи до списку масивівMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item1"MyList.Add "Item4"MyList.Add "Item5""Вставте" елемент 6 "у позицію індексу 2MyList.Insert 2, "Item6""Видалити" Item2 "MyList.Remove "Item2""Видалити" елемент " - цього немає у списку масивів, але не виникає помилкаMyList.Remove "Item""Видаліть елемент у позиції індексу 2MyList.RemoveAt 2"Видаліть 2 послідовні елементи, починаючи з позиції індексу 2MyList.RemoveRange 3, 2«Повторіть список масив, щоб показати, що залишилося і в якому місці індексу він зараз знаходитьсяДля N = 0 до MyList.Count - 1MsgBox MyList (N) & "Index" & NДалі NEnd Sub |
Зауважте, що якщо ви використовуєте "RemoveAt", щоб видалити елемент у певній позиції, як тільки цей елемент буде видалено, усі наступні позиції індексу будуть змінені. Якщо у вас є декілька видалень за допомогою позиції індексу, тоді гарна ідея - почати з найбільшого номера індексу і зробити крок назад до позиції нуль, щоб ви завжди видаляли правильний елемент. Таким чином у вас не виникне проблеми
Сортування списку масивів
Ще одна велика перевага перед колекцією полягає в тому, що ви можете сортувати елементи за зростанням або спаданням.
Об’єкт «Список масивів» - єдиний об’єкт у Excel VBA з методом сортування. Метод сортування дуже швидкий, і це може бути важливою умовою для використання списку масивів.
В об’єкті колекції для сортування всіх елементів потрібне деяке “нестандартне” мислення, але зі списком масивів це дуже просто.
Метод "Сортувати" сортується за зростанням, а метод "Зворотний" - за спаданням.
| 12345678910111213141516171819202122 | Sub ArrayListExample ()"Створити об'єкт" Список масивів "Dim MyList As New ArrayList"Додайте елементи в несортуваному порядкуMyList.Add "Item1"MyList.Add "Item3"MyList.Add "Item2""Сортувати предмети за зростаннямMyList.Sort«Повторюйте елементи, щоб показати їх зростанняДля кожного я в MyList"Відобразити назву елементаMsgBox IДалі я"Сортувати предмети за спаданнямMyList.Reverse«Повторюйте елементи, щоб показати їх спаданняДля кожного я в MyList"Відобразити назву елементаMsgBox IДалі яEnd Sub |
Клонування списку масивів
Список масивів має можливість створити клон або його копію. Це корисно, якщо користувач вносить зміни до елементів за допомогою інтерфейсу та вашого коду VBA, але вам потрібно зберегти копію елементів у вихідному стані як резервну копію.
Це може надати користувачеві функцію "Скасувати". Можливо, вони внесли зміни та бажають повернутися до початкового списку.
| 123456789101112131415 | Sub CloneExample ()«Визначте два об’єкти - список масивів та об’єктЗатемнити MyList як новий ArrayList, MyList1 як об'єкт"Заповніть перший об'єкт елементамиMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3""Скопіювати мій список у MyList1Встановити MyList1 = MyList.Clone"Повторіть через MyList1, щоб довести клонуванняДля кожного я в MyList1"Відобразити назву елементаMsgBox IДалі яEnd Sub |
"MyList1" тепер містить усі елементи з "MyList" у тому ж порядку
Копіювання масиву списку в звичайний об’єкт масиву VBA
Ви можете скористатися простим методом, щоб скопіювати список масивів у звичайний масив VBA:
| 123456789101112131415 | Sub ArrayExample ()«Створіть об’єкт списку масивів та стандартний об’єкт масивуDim MyList As New ArrayList, NewArray As Variant"Заповнити список масивів елементамиMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3""Скопіюйте список масивів до нового масивуNewArray = MyList.ToArray«Ітерація по новому масиву - зверніть увагу, що кількість списків масивів забезпечує максимальний індексДля N = 0 до MyList.Count - 1"Відобразити назву елементаMsgBox NewArray (N)Далі NEnd Sub |
Копіювання масиву списку в діапазон аркушів
Ви можете скопіювати свій список масивів на певний аркуш та посилання на клітинку без необхідності повторювати список масив. Потрібно вказати лише перше посилання на клітинку
| 123456789101112131415 | Піддіапазон Приклад ()"Створити новий об'єкт списку масивівDim MyList As New ArrayList"Додати елементи до спискуMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3""Очистіть аркуш ціліАркуші ("Лист1"). UsedRange.Clear"Копіювати елементи через рядокАркуші ("Sheet1"). Діапазон ("A1"). Змінити розмір (1, MyList.Count) .Value = MyList.toArray"Скопіювати елементи вниз у стовпецьАркуші ("Sheet1"). Діапазон ("A5"). Змінити розмір (MyList.Count, 1) .Value = _WorksheetFunction.Transpose (MyList.toArray)End Sub |
Очистити всі елементи зі списку масивів
Існує проста функція (Очистити) для повного очищення списку масивів
| 1234567891011121314 | Sub ClearListExample ()‘Створити об’єкт списку масивівDim MyList As New ArrayList"Додайте нові елементиMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3""Показати кількість предметівMsgBox MyList.Count"Очистити всі елементиMyList.Clear"Покажіть кількість предметів, щоб довести, що очищення спрацювалоMsgBox MyList.CountEnd Sub |
У цьому прикладі створюються елементи у списку масивів, а потім очищається список масивів. Поля повідомлень доводять до і після кількості елементів у списку масивів.
Зведення методів списку масивів для Excel VBA
| Завдання | Параметри | Приклади |
| Додати / змінити елемент | Цінність | MyList.Add “Item1” |
| MyList (4) = “Елемент2” | ||
| Клонування списку масивів | Жодного | Затемнити MyList як об'єкт |
| Встановіть MyList2 = MyList.Clone | ||
| Копіювати в масив | Жодного | Dim MyArray як варіант |
| MyArray = MyList.ToArray | ||
| Копіювати в діапазон аркуша (рядок) | Жодного | Аркуші ("Аркуш1"). Діапазон ("A1"). Змінити розмір (1, MyList.Count) .Value = MyList.ToArray |
| Копіювати в діапазон робочого аркуша (стовпець) | Жодного | Аркуші ("Sheet1"). Діапазон ("A3"). Змінити розмір (MyList.Count, 1) .Value = WorksheetFunction.Transpose (MyList.ToArray) |
| Створити | "System.Collections.ArrayList" | Затемнити MyList як об'єкт |
| Встановити MyList = CreateObject (“System.Collections.ArrayList”) | ||
| Заявити | Н/Д | Затемнити MyList як об'єкт |
| Знайдіть / перевірте, чи існує елемент | Предмет для пошуку | MyList.Contains ("Item2") |
| Знайдіть позицію елемента в ArrayList | 1. Предмет для пошуку. | Індекс затемнення: не так довго |
| 2. Позиція, з якої можна почати пошук. | IndexNo = MyList.IndexOf (“Item3”, 0) | |
| IndexNo = MyList.IndexOf (“Item5”, 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) |
| Прочитайте всі пункти (для кожного) | Н/Д | Затемнений елемент як варіант |
| Для кожного елемента в MyList | ||
| Елемент MsgBox | ||
| Наступний елемент | ||
| Прочитати всі предмети (Для) | Індекс - довге ціле число | Dim i As Long |
| Для i = 0 до MyList.Count - 1 | ||
| MsgBox i | ||
| Далі i | ||
| Видалити всі елементи | Жодного | MyList.Clear |
| Видаліть елемент на місці | Позиція індексу, де знаходиться елемент | MyList.RemoveAt 5 |
| Видалити елемент за назвою | Елемент для видалення зі списку масив | MyList.Remove “Item3” |
| Видаліть ряд елементів | 1. Покажчик - вихідна позиція. | MyList.RemoveRange 4,3 |
| 2. Count - кількість елементів, які потрібно видалити. | ||
| Сортувати за спаданням | Жодного | MyList.Reverse |
| Сортувати в порядку зростання | Не | MyList.Sort |
