Колекція - це об’єкт, що містить ряд подібних предметів. До них можна легко отримати доступ та ними маніпулювати, навіть якщо у колекції є велика кількість предметів.
Вже є вбудовані колекції з Excel VBA. Прикладом може служити колекція «Таблиці». Для кожного робочого аркуша в книзі є елемент у колекції "Таблиці".
Вбудовані колекції мають набагато більше доступних властивостей та методів, але вони недоступні у ваших власних колекціях, які ви створюєте.
Наприклад, за допомогою колекції можна отримати інформацію про певний аркуш. Наприклад, ви можете побачити назву робочого аркуша, а також, чи він видимий чи ні. За допомогою циклу For Each можна перебирати кожен аркуш колекції.
| 1234567 | Робочі аркуші підтесту ()Дім Ш як робочий аркушДля кожного Ш в аркушахMsgBox Sh.NameMsgBox Sh.VisibleДалі ШEnd Sub |
Ви також можете адресувати певний аркуш у колекції, використовуючи значення індексу або фактичну назву аркуша:
| 12 | Аркуші MsgBox (1). НазваMsgBox Sheets ("Sheet1"). Назва |
У міру додавання або видалення аркушів колекція аркушів збільшується або зменшується.
Зауважте, що у колекціях VBA номер індексу починається з 1, а не з 0
Колекції проти масивів
Масиви та колекції схожі за своїми функціями, оскільки обидві вони є методологіями, які дозволяють зберігати велику кількість даних, на які потім можна легко посилатися за допомогою коду. Однак у їх роботі є ряд відмінностей:
- Масиви є багатовимірними, тоді як колекції - лише одновимірними. Ви можете розмірити масив з кількома вимірами, наприклад
| 1 | Dim MyArray (10, 2) As String |
Це створює масив з 10 рядків з 2 стовпцями, майже як робочий аркуш. Колекція - це фактично один стовпець. Масив корисний, якщо вам потрібно зберегти ряд елементів даних, які стосуються один одного, наприклад ім'я та адреса. Ім'я буде в першому вимірі масиву, а адреса - у другому вимірі.
- Коли ви заповнюєте свій масив, вам потрібен окремий рядок коду, щоб додати значення до кожного елемента масиву. Якби у вас був двовимірний масив, вам насправді знадобиться 2 рядки коду - один рядок для адреси першого стовпця і один рядок для другого стовпця. З об'єктом Collection ви просто використовуєте метод Add, щоб новий елемент просто додавався до колекції, а значення індексу автоматично коригувалося відповідно до нього.
- Якщо вам потрібно видалити елемент даних, то це складніше в масиві. Ви можете встановити значення елемента як порожнє значення, але сам елемент все ще існує в масиві. Якщо ви використовуєте цикл For Next для ітерації по масиву, цикл поверне пусте значення, яке потребуватиме кодування, щоб переконатися, що пусте значення ігнорується. У колекції ви використовуєте методи «Додати або видалити», і всі індексації та зміна розміру автоматично вирішуються. Вилучений елемент повністю зникає. Масиви корисні для фіксованого розміру даних, але колекції краще для тих випадків, коли кількість даних може змінитися.
- Колекції доступні лише для читання, тоді як значення масиву можна змінювати за допомогою VBA. За допомогою колекції вам доведеться спочатку видалити значення, яке потрібно змінити, а потім додати нове змінене значення.
- У масиві можна використовувати лише один тип даних для елементів, який встановлюється під час визначення розміру масиву. Однак у масиві можна використовувати власні типи даних, які ви розробили самостійно. Ви можете мати дуже складну структуру масиву, використовуючи користувацький тип даних, який, у свою чергу, має кілька спеціальних типів даних під ним. У колекції можна додати використовувані типи даних для кожного елемента. Ви можете мати числове значення, дату або рядок - об’єкт колекції прийме будь -який тип даних. Якщо ви спробуєте розмістити рядкове значення в масиві, розмір якого є числовим, це видасть повідомлення про помилку.
- Колекції, як правило, простіші у використанні, ніж масиви. З точки зору кодування, коли ви створюєте об’єкт колекції, він має лише два методи (Додавання та видалення) та дві властивості (Count та Item), тому об’єкт жодним чином не складний у програмуванні.
- Колекції можуть використовувати ключі для пошуку даних. Масиви не мають цієї функції і вимагають циклічного коду для ітерації по масиву, щоб знайти конкретні значення.
- Розмір масиву потрібно визначити під час його першого створення. Ви повинні мати уявлення про те, скільки даних він буде зберігати. Якщо вам потрібно збільшити розмір масиву, ви можете скористатися «ReDim», щоб змінити його розмір, але вам потрібно використовувати ключове слово «Зберегти», якщо ви не хочете втратити дані, які вже зберігаються в масиві. Розмір колекції не потрібно визначати. Він просто росте і зменшується автоматично, коли елементи додаються або видаляються.
Обсяг об’єкта колекції
З точки зору обсягу, об’єкт колекції доступний лише тоді, коли книга відкрита. Він не зберігається, коли зберігається книга. Якщо книгу відкривають заново, колекцію потрібно заново створити за допомогою коду VBA.
Якщо ви хочете, щоб ваша колекція була доступна для всього коду у вашому модулі коду, вам потрібно оголосити об’єкт колекції у розділі «Оголошення» у верхній частині вікна модуля.
Це гарантує, що весь ваш код у цьому модулі зможе отримати доступ до колекції. Якщо ви хочете, щоб будь -який модуль у вашій книзі мав доступ до колекції, визначте її як глобальний об’єкт
| 1 | Глобальна колекція MyCollection як нова колекція |
Створення колекції, додавання елементів та доступ до них
Простий об’єкт колекції можна створити у VBA за допомогою такого коду:
| 123456 | Sub CreateCollection ()Dim MyCollection як нова колекціяMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"End Sub |
Код розмірює новий об’єкт під назвою “MyCollection”, а потім наступні рядки коду використовують метод Add для додавання 3 нових значень.
Потім ви можете використовувати код для ітерації своєї колекції, щоб отримати доступ до значень
| 123 | Для кожного елемента в MyCollectionПункт MsgBoxНаступний пункт |
Ви також можете перебирати свою колекцію за допомогою циклу For Next:
| 123 | Для n = 1 до MyCollection.CountMsgBox MyCollection (n)Далі п |
Код отримує розмір колекції за допомогою властивості Count, а потім використовує це початкове значення 1 для індексування кожного елемента
Цикл For Each швидше, ніж цикл For Next, але він працює лише в одному напрямку (від низького індексу до високого). Перевага For Next Loop полягає в тому, що ви можете використовувати інший напрямок (від високого індексу до низького), а також можете скористатися методом Step, щоб змінити приріст. Це корисно, якщо ви хочете видалити кілька елементів, оскільки вам потрібно буде запустити видалення з кінця колекції до початку, оскільки індекс зміниться у міру видалення.
Метод Add у колекції має 3 додаткові параметри - Key, Before і After
Ви можете використовувати параметри "До" та "Після", щоб визначити положення вашого нового елемента щодо інших, які вже є у колекції
Це робиться шляхом зазначення номера індексу, до якого потрібно, щоб ваш новий елемент був відносним.
| 123456 | Sub CreateCollection ()Dim MyCollection як нова колекціяMyCollection.Add "Item1"MyCollection.Add "Item2",, 1MyCollection.Add "Item3"End Sub |
У цьому прикладі "Item2" було вказано для додавання перед першим індексованим елементом у колекції (який є "Item1"). При повторному перегляді цієї колекції насамперед з'явиться "Item2", а потім "Item1" та "Item3"
Коли ви вказуєте параметри "До" або "Після", значення індексу автоматично коригується в колекції так, що "Item2" стає значенням індексу 1, а "Item1" переміщується до значення індексу 2
Ви також можете використовувати параметр "Ключ", щоб додати довідкове значення, яке можна використовувати для ідентифікації елемента колекції. Зауважте, що значення ключа має бути рядком і має бути унікальним у колекції.
| 1234567 | Sub CreateCollection ()Dim MyCollection як нова колекціяMyCollection.Add "Item1"MyCollection.Add "Item2", "MyKey"MyCollection.Add "Item3"MsgBox MyCollection ("MyKey")End Sub |
"Item2" було надано значення "Key" "MyKey", щоб ви могли посилатися на цей елемент, використовуючи значення "MyKey" замість номера індексу (2)
Зауважте, що значення "Ключ" має бути рядковим. Це не може бути інший тип даних. Зауважте, що колекція доступна лише для читання, і ви не можете оновити значення ключа, як тільки воно було встановлено. Крім того, ви не можете перевірити, чи існує значення ключа для певного елемента в колекції, або переглянути значення ключа, що є трохи недоліком.
Параметр "Ключ" має додаткову перевагу у тому, що ваш код стає більш читабельним, особливо якщо він передається колезі для підтримки, і вам не потрібно перебирати всю колекцію, щоб знайти це значення. Уявіть собі, якби у вас була колекція з 10 000 предметів, як важко було б посилатися на один конкретний предмет!
Вилучення елемента з колекції
Ви можете використати метод "Видалити", щоб видалити елементи зі своєї колекції.
| 1 | MyCollection.Remove (2) |
На жаль, нелегко, якщо у колекції є велика кількість елементів для визначення індексу елемента, який потрібно видалити. Тут параметр "Ключ" стане в нагоді під час створення колекції
| 1 | MyCollection.Remove ("MyKey") |
Коли елемент видаляється з колекції, значення індексу автоматично скидаються протягом усієї колекції. Тут параметр "Ключ" настільки корисний, коли ви видаляєте кілька елементів одночасно. Наприклад, ви можете видалити індекс позиції 105, і миттєво індекс позиції 106 стає індексом 105, і все, що над цим елементом, має значення індексу зміщено вниз. Якщо ви використовуєте параметр Key, не потрібно турбуватися про те, яке значення індексу потрібно видалити.
Щоб видалити всі елементи колекції та створити нову колекцію, знову використовуйте оператор Dim, який створює порожню колекцію.
| 1 | Dim MyCollection як нова колекція |
Щоб повністю видалити фактичний об’єкт колекції, ви можете встановити об’єкт на ніщо
| 1 | Встановити MyCollection = Нічого |
Це корисно, якщо ваш код більше не вимагає збору. Якщо встановити для об’єкта збору нічого, видаляються всі посилання на нього та звільняється пам’ять, яку він використовував. Це може мати важливі наслідки для швидкості виконання вашого коду, якщо у пам’яті знаходиться великий об’єкт, який більше не потрібен.
Порахуйте кількість предметів у колекції
Ви можете легко дізнатися кількість предметів у вашій колекції, використовуючи властивість "Count"
| 1 | MsgBox MyCollection.Count |
Ви б використовували цю властивість, якби використовували цикл For Next для перебору колекції, оскільки він надасть вам верхню межу для номера індексу.
Тестовий збір для певного значення
Ви можете перебирати колекцію, щоб шукати певне значення для елемента, використовуючи для кожної петлі
| 123456789101112 | Sub SearchCollection ()Dim MyCollection як нова колекціяMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Для кожного елемента в MyCollectionЯкщо Item = "Item2" ТодіЕлемент MsgBox & "Знайдено"Закінчити ЯкщоДаліEnd Sub |
Код створює невелику колекцію, а потім перебирає її, шукаючи елемент під назвою "item2". Якщо він знайдений, він відображає вікно повідомлення про те, що він знайшов конкретний елемент
Одним з недоліків цієї методології є те, що ви не можете отримати доступ до значення індексу або значення ключа
Якщо замість цього ви використовуєте цикл For Next, ви можете використовувати лічильник 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Закінчити ЯкщоДалі пEnd Sub |
Лічильник 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""Зафіксуйте кількість предметів у колекції для подальшого використанняЛічильник = MyCollection.Count"Повторювати колекцію, копіюючи кожен елемент у послідовну клітинку на" SortSheet "(стовпець A)Для n = 1 до MyCollection.CountАркуші ("SortSheet"). Клітинки (n, 1) = MyCollection (n)Далі п"Активуйте таблицю сортування та використовуйте програму сортування Excel для сортування даних у порядку зростанняАркуші ("SortSheet"). АктивуйтеДіапазон ("A1: A" & MyCollection.Count). ВиберітьActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Сортування аркуша"). Клавіша Sort.SortFields.Add2: = Діапазон (_"A1: A5"), SortOn: = xlSortOnValues, Порядок: = xlAscending, DataOption: = _xlSortNormalЗа допомогою ActiveWorkbook.Worksheets ("Сортувати аркуш"). СортуватиДіапазон .SetRange ("A1: A5").Головник = xlGuess.MatchCase = Неправда.Орієнтація = xlTopToBottom.SortMethod = xlPinYin.ЗастосуватиЗакінчити з"Видалити всі елементи з колекції - зверніть увагу, що цей цикл For Next виконується у зворотному порядкуFor n = MyCollection.Count To 1 Step -1MyCollection.Remove (n)Далі п"Скопіюйте значення комірки назад у порожній об'єкт колекції, використовуючи збережене значення (лічильник) для циклу"Для n = 1 До лічильникаMyCollection.Add Sheets ("SortSheet"). Cells (n, 1) .ValueДалі п«Повторіть колекцію, щоб довести порядок, в якому зараз є предметиДля кожного елемента в MyCollectionПункт MsgBoxНаступний пункт"Очистити аркуш (сортувальний аркуш) - при необхідності видаліть і йогоАркуші ("SortSheet"). Діапазон (клітинки (1, 1), клітинки (лічильник, 1)). ОчиститиEnd Sub |
Цей код спочатку створює колекцію з елементами, доданими у випадковому порядку. Потім він копіює їх у перший стовпець на аркуші (SortSheet).
Потім Code використовує функцію сортування Excel для сортування даних у стовпці за зростанням. Код також можна змінити для сортування за спаданням.
Потім колекція очищається від даних за допомогою циклу For Next. Зауважте, що параметр кроку використовується так, щоб він очищався від кінця колекції до початку. Це відбувається тому, що під час очищення значення індексу скидаються, якщо він очиститься з самого початку, він не очиститься належним чином (індекс 2 стане індексом 1)
Нарешті, використовуючи інший цикл For Next, значення елементів переносяться назад у порожню колекцію
Подальший цикл For Each Loop доводить, що колекція зараз у хорошому зростаючому порядку.
На жаль, це не стосується жодних значень ключа, які могли бути введені спочатку, оскільки значення ключів неможливо прочитати
Передача колекції підрозділу / функції
Колекція може бути передана до вкладеної функції або функції так само, як і до будь -якого іншого параметра
| 1 | Функція MyFunction (ByRef MyCollection як колекція) |
Важливо передати збір за допомогою "ByRef". Це означає, що використовується оригінальна колекція. Якщо колекція передається за допомогою "ByVal", це створює копію колекції, яка може мати сумні наслідки
Якщо копія створюється за допомогою "ByVal", то все, що змінює колекцію в межах функції, відбувається лише на копії, а не на оригіналі. Наприклад, якщо в межах функції до колекції додається новий елемент, він не з’явиться у вихідній колекції, що створить помилку у вашому коді.
Повернення колекції з функції
Ви можете повернути колекцію з функції так само, як і будь -який об'єкт. Необхідно використовувати ключове слово Set
| 12345 | Sub ReturnFromFunction ()Dim MyCollection As CollectionВстановити MyCollection = PopulateCollectionMsgBox MyCollection.CountEnd Sub |
Цей код створює підпрограму, яка створює об’єкт під назвою “MyCollection”, а потім використовує ключове слово “Set” для ефективного виклику функції для заповнення цієї колекції. Після того, як це буде зроблено, на дисплеї з'явиться вікно з повідомленням про кількість 2 елементів
| 1234567 | Функція PopulateCollection () як колекціяDim MyCollection як нова колекціяMyCollection.Add "Item1"MyCollection.Add "Item2"Встановити PopulateCollection = MyCollectionФункція завершення |
Функція PopulateCollection створює новий об'єкт колекції та заповнює його 2 елементами. Потім він передає цей об’єкт назад об’єкту колекції, створеному у вихідній підпрограмі.
Перетворення колекції в масив
Ви можете перетворити свою колекцію в масив. Можливо, вам захочеться зберегти дані там, де ними можна змінювати та маніпулювати. Цей код створює невелику колекцію, а потім передає її в масив
Зверніть увагу, що індекс колекції починається з 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 = 0 до 2MsgBox MyArray (n)Далі пEnd Sub |
Перетворення масиву в колекцію
Можливо, вам захочеться перетворити масив у колекцію. Наприклад, ви можете отримати доступ до даних швидше та елегантніше, ніж за допомогою коду, щоб отримати елемент масиву.
Майте на увазі, що це буде працювати лише для одного виміру масиву, оскільки колекція має лише один вимір
| 123456789101112131415 | Sub ConvertArrayIntoCollection ()Dim MyCollection як нова колекціяDim MyArray (2) як рядокMyArray (0) = "item1"MyArray (1) = "Елемент2"MyArray (2) = "Елемент3"Для n = 0 до 2MyCollection.Add MyArray (n)Далі пДля кожного елемента в MyCollectionПункт MsgBoxНаступний пунктEnd Sub |
Якщо ви все-таки хотіли використовувати багатовимірний масив, ви могли б об'єднати значення масиву разом для кожного рядка в масиві, використовуючи символ-роздільник між розмірами масиву, щоб під час читання значення колекції можна було програмно використовувати символ роздільника для відокремте значення.
Ви також можете перемістити дані до колекції на основі того, що додано значення першого виміру (індекс 1), а потім додано значення наступного виміру (індекс 2) тощо.
Якби масив мав, скажімо, 4 виміри, кожне четверте значення у колекції було б новим набором значень.
Ви також можете додати значення масиву для використання в якості ключів (за умови їх унікальності), що додасть простий спосіб визначення конкретних даних.
