Використання словника VBA
Словник VBA працює аналогічно об’єкту колекції, але він має більше властивостей та методів та пропонує більшу гнучкість
Словник зберігає дані в пам’яті і ними можна легко маніпулювати. Не потрібно автоматичного розрахунку, фонового резервного копіювання та оновлення екрана, тому ваш код працюватиме значно швидше.
Об’єкт словника працює подібно до звичайного словника, який би ви використовували, якщо хочете з’ясувати значення слова. Кожен запис в об’єкті словника має значення «ключ» та «елемент». Ви використовуєте "ключ" значення ключа для пошуку значення елемента в об'єкті словника так само, як і для звичайного словника.
Через те, як працює об’єкт словника, усі ключові значення мають бути унікальними, так само, як у звичайному словнику. Уявіть собі, якби ви відкрили свій звичайний словник, щоб знайти значення слова, і виявили, що це слово перераховане кілька разів з двома абсолютно різними визначеннями. Ви були б дуже розгублені!
Ключовими значеннями зазвичай є текст або цифри, або обидва. Користувачам часто легше запам'ятовувати назви ключів як текст, а не просто цифри.
Порівняно з об'єктом колекції, об'єкт колекції доступний лише для читання. Він має лише два методи (додавання та видалення) та дві властивості (кількість та елемент). Після того, як елемент був доданий до об’єкта колекції, його можна лише видалити, але не редагувати, що є громіздкою процедурою, якщо необхідно змінити значення елемента.
Об’єкт словника автоматично змінюватиметься відповідно до кількості елементів у ньому. Його не потрібно визначати за розміром, як звичайний масив
Об'єкт словника є одновимірним, а тип даних-"Варіант", тому до нього можна ввести будь-який тип даних, наприклад число, текст, дата
Словник VBA не є рідним для Excel, і до нього потрібно отримати доступ шляхом ранньої або пізньої прив’язки під час визначення об’єкта словника.
| 123 | Sub EarlyBindingExample ()Dim MyDictionary As New Scripting.DictionaryEnd Sub |
| 1234 | Sub LateBindingExample ()Dim MyDictionary As ObjectВстановити MyDictionary = CreateObject ("Scripting.Dictionary")End Sub |
Якщо ви використовуєте раннє прив’язування, ви повинні додати посилання на бібліотеку «Microsoft Scripting Runtime»
Для цього виберіть «Інструменти | Посилання »на панелі меню вікна редактора Visual Basic (VBE), і з’явиться спливаюче вікно зі списком доступних бібліотек.
Прокрутіть униз до пункту "Microsoft Scripting Runtime" і поставте прапорець біля нього. Натисніть OK, і ця бібліотека тепер є частиною вашого проекту VBA, і на неї можна посилатися за допомогою раннього прив’язки. У всіх прикладах коду в цій статті буде використано раннє прив’язування.
Ваш код буде працювати набагато швидше з ранньою прив'язкою, тому що весь він скомпільований заздалегідь. З пізнім прив'язкою об'єкт потрібно компілювати під час виконання коду
Бібліотека сценаріїв виконання містить "Intellisense". Під час написання коду ви побачите список доступних методів та властивостей, що допомагає запобігти помилкам у написанні, що спричинить помилки у вашій програмі
Крім того, якщо натиснути клавішу F2 у VBE і вибрати бібліотеку "Сценарії", ви побачите всі доступні методи та властивості та параметри, необхідні для кожного
Розповсюдження програми Excel, що містить словник
Як уже зазначалося, бібліотека Scripting Runtime не є частиною Excel VBA, тому, якщо ви розповсюджуєте свою програму серед інших користувачів, вони повинні мати доступ до бібліотеки Scripting Runtime на своєму комп’ютері. Якщо їх немає, то буде помилка.
Непогано додати код VBA, щоб перевірити наявність цієї бібліотеки під час завантаження програми Excel. Ви можете скористатися командою "Dir" для цього під час події "Робоча книга відкрито"
Розташування файлу: C: \ Windows \ SysWOW64 \ scrrun.dll
Сфера використання об’єкта словника
Об’єкт “Словник” доступний лише під час роботи книги Excel. Він не зберігається, коли зберігається книга.
Якщо ваш словник буде доступний для всіх процедур у вашому модулі, вам потрібно оголосити його (Dim) у розділі Declare у самому верху модуля
Ви визначаєте його як глобальний об’єкт, якщо хочете, щоб ваш словник використовувався у всьому коді.
| 1 | Глобальний словник MyDictionary як новий словник |
Наповнення та читання з вашого словника
Для початку вам потрібно створити словник, заповнити його деякими даними, а потім повторити його, щоб довести, що дані існують
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30Для n = 0 до MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Далі пEnd Sub |
Цей код створює новий об’єкт словника під назвою “MyDictionary”, а потім заповнює його трьома елементами. Метод Add має два параметри - Key та Item, і обидва вони є обов’язковими
Типи даних для ключа та елемента є варіантами, тому вони прийматимуть будь -який тип даних - числові, текстові, дати тощо
Перший елемент у словнику можна додати як:
| 1 | MyDictionary.Add 10, "MyItem1" |
Значення були змінені між ключем та елементом, але це все одно працюватиме, хоча тепер ключ пошуку стане 10.
Однак важливо розуміти, що ключове значення - це значення пошуку в словнику. Він працює дуже подібно до функції VLOOKUP в Excel. Оскільки всі ключі мають мати унікальні значення, ви можете вказати значення ключа та миттєво повернути значення елемента для цього ключа.
Зауважте, що індекс словника починається з 0, тому вам потрібно відняти 1 із кількості словників, що використовуються у циклі For… Next
Ви також можете використовувати For… Кожен цикл для читання значень у словнику:
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary як новий сценарій. Словник, я як варіантMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30Для кожного я в MyDictionary.KeysMsgBox I & "" & MyDictionary (I)Далі яEnd Sub |
Цей код буде повторювати кожен елемент і відображатиме ключ елемента та його значення
Використання індексного номера товару
Ви можете використовувати індексний номер ключа або елемента, щоб прочитати значення
| 123456789101112 | Номери підіндексів ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary.Keys (2)MsgBox MyDictionary.Items (1)End Sub |
Цей код поверне ключ "item3", оскільки індекс починається з 0, а значення елемента - 20
Ви можете звернутися до окремих значень ключів або елементів у колекціях Ключі або Елементи за допомогою номерів індексу.
Фільтрація словника
Не існує прямого методу для цього, але написати код досить просто:
| 1234567891011 | ПідфільтрDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Додайте "AAItem1", 10MyDictionary.Додайте "BBItem2", 20MyDictionary.Додайте "BBItem3", 30Для кожного фільтра I In (MyDictionary.Keys, "BB")MsgBox MyDictionary.Item (I)Далі яEnd Sub |
Значення фільтра працює лише з початку ключового значення. У фільтрі не можна використовувати символи підстановки. Цей код поверне два значення позицій з іменами ключів, що починаються на "BB"
Це дасть вам підмножину словника на основі вашого значення фільтра, яке потім можна перенести до іншого словника або робочого аркуша. Якщо ретельно спланувати імена ключів, переконатися, що до кожного з них є змістовна префікс, ви легко зможете розділити словник на різні складові частини.
Зміна значення позиції ключа
Об’єкт словника має велику перевагу перед колекцією в тому, що значення елемента можна змінити, наприклад.
| 1 | MyDictionary ("MyItem4") = "40" |
У колекції вам потрібно буде видалити цей запис, а потім повторно створити його.
Ось приклад коду:
| 12345678910111213 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30MyDictionary ("MyItem2") = "25"MyDictionary ("MyItem4") = "40"Для n = 0 до MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Далі пEnd Sub |
Наведений вище код встановлює три елементи в словнику, а потім змінює значення "MyItem2" з 20 на 25.
Він також змінює значення "MyItem4" на 40. Зауважте, що в операторах add коду "MyItem4" не додано. Коли ви змінюєте значення ключа, якого немає, воно створюється автоматично. Це надзвичайно зручно, оскільки помилка не спрацьовує, але це означає, що вам потрібно бути обережним з іменами ключів. Ненавмисна орфографічна помилка в назві ключа означатиме, що створюється новий ключ, а вихідне ім’я ключа все ще матиме старе значення.
Це може легко призвести до проблем цілісності об’єкта словника.
Перевірте, чи існує ключ
Ви можете перевірити, чи існує значення ключа в словнику
| 123456789 | Sub CheckExistsDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30MsgBox MyDictionary.Exists ("MyItem8")End Sub |
Код додає три елементи до нового об’єкта словника, а потім перевіряє наявність ключа (‘MyItem8’), якого немає у словнику. Це повертає False, але якби був використаний один із існуючих ключів, він повертає True
Підстановні знаки не приймаються. Текст пошуку за умовчанням також чутливий до регістру, але його можна змінити (див. Далі у статті)
Використання кількох значень у словнику
На відміну від масиву, об’єкт словника є лише одновимірним. Це може призвести до проблем, якщо у вас є кілька значень, які ви хочете поставити проти ключа.
Одним із способів цього є об’єднання кожного значення елемента за допомогою символу -роздільника між кожним значенням, наприклад. ‘|’
| 12345678910111213141516171819202122232425262728293031323334 | Sub MultipleValues ()'Створення об'єкта словника та зміннихDim MyDictionary як новий сценарій. Словник, V1 як ціле число, V2 як рядокDim V3 як дата, Temp як рядок, N як ціле число'Заповніть 3 змінні, щоб продемонструвати кілька значеньV1 = 5V2 = "Приклад декількох значень"V3 = "22 липня 2020 р."'Додайте об'єднане значення до словника за допомогою "|" роздільникMyDictionary.Add "MyMultipleItem", V1 & "|" & V2 & "|" & V3 & "|"'Захоплення об'єднаного значення словника зі словника у зміннуTemp = MyDictionary ("MyMultipleItem")'Ітерація через укладений рядок для відокремлення окремих значеньЗробити'Знайдіть положення роздільникаN = InStr (Temp, "|")'Якщо немає більше роздільників, вихідний цикл DoЯкщо N = 0, то вийдіть'Відображення тексту щодо знайденого роздільникаMsgBox ліворуч (Temp, N - 1)'Зрізати об'єднаний рядок до наступного символу після знаходження роздільникаТемп = Середина (Темп, N + 1)ПетляEnd Sub |
Інший спосіб обійти цю проблему-розробити власну систему під сценаріїв для імен ключів. Немає причин, чому ви не повинні використовувати дужки та цифри в іменах ключів
| 1234567891011 | Sub MultipleValues ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "Multiple (1)", 5MyDictionary.Add "Multiple (2)", "Приклад кількох значень"MyDictionary.Add "Multiple (3)", "22-Jul-2020"Для N = 1 до 3MsgBox MyDictionary ("Кілька (" & N & ")")Далі NEnd Sub |
Цей код додає до словника три ключі, але кожна назва ключа містить номер підскрипта в дужках. Потім можна звернутися до імені ключа, але з використанням номера підскрипту, з’єднаного в. Це дуже схоже на використання об’єкта масиву
Видалення елементів
Ви можете видалити окремі елементи, посилаючись на значення ключа
| 1 | MyDictionary.Remove (“MyItem2”) |
Зауважте, що оскільки імена ключів унікальні, це видаляє лише один конкретний ключ та значення елемента
Ви також можете повністю очистити словник
| 1 | MyDictionary.RemoveAll |
Ось приклад використання "Видалити" у VBA:
| 12345678910111213141516 | Sub RemoveValues ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MyDictionary.Remove ("Елемент2")Для N = 0 до MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Далі NMyDictionary.RemoveAllMsgBox MyDictionary.CountEnd Sub |
Код додає до словника три елементи, а потім видаляє "Item2". Потім він повторює словник, щоб довести, що "Item2" більше не існує
Нарешті, код видаляє всі елементи словника та відображає кількість словників, яка тепер дорівнює нулю.
Зміна чутливості регістру для пошуків
Якщо ви шукаєте ключ, він за замовчуванням чутливий до регістру. Однак ви можете скористатися властивістю "CompareMode", щоб змінити це.
Зауважте, що це потрібно зробити негайно у коді після створення об’єкта словника, але перед тим, як додати до словника будь -які дані. Після встановлення режиму порівняння його неможливо змінити в цьому словнику.
| 12345678910 | Sub ChangeCaseSensitivity ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary.Exists ("item2")End Sub |
У цьому прикладі для режиму порівняння встановлено значення «TextCompare», що означає, що він не враховує регістр. Вислів "Існує" в кінці прикладу поверне значення "Істина", незважаючи на те, що текст пошуку введений у нижньому регістрі.
В Excel є лише два значення, які можна використовувати для режиму порівняння. Двійкове порівняння чутливе до регістру, а порівняння тексту - не до регістру
Якщо для режиму порівняння встановлено значення Двійкове порівняння, вам слід бути обережним у назві ключів. Якщо ви встановили для імені перший символ великої літери, тоді, коли ви змінюєте значення, ви повинні переконатися, що перший символ все ще робиться великим. Якщо ви починаєте з малої літери, це буде інтерпретовано як новий ключ, і це може легко призвести до плутанини та помилок у вашому словнику
Пам’ятайте, що якщо ви зміните значення ключа, а ім’я ключа не існує через використання двійкового порівняння, новий ключ та значення будуть додані до словника.
Якщо замість цього ви використовуєте Порівняння тексту, будь -які зміни значення будуть передані ключу незалежно від регістру. Якщо ви спробуєте додати той самий елемент, але написаний з іншим регістром, ви отримаєте помилку, оскільки він уже існує.
Сортування словника
Як і у випадку з об’єктом колекції, не передбачено способу сортування словника за допомогою ключів або значень елементів.
Однак, оскільки код VBA знаходиться у робочій книзі Excel, дані словника можна перенести до Excel у табличній формі, а потім до нього можна застосувати засіб сортування Excel. Після цього словник можна очистити за допомогою "RemoveAll" і додати відсортовані значення з аркуша.
Цей код буде сортувати ключі та значення елементів
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 | Sub SortMyDictionary ()Dim MyDictionary як новий словникЗатемнений лічильник як довго'Створення словника з елементами випадкового порядкуMyDictionary.Add "Item5", 5MyDictionary.Add "Item2", 15MyDictionary.Add "Item4", 11MyDictionary.Add "Item1", 2MyDictionary.Add "Item3", 19'Зафіксуйте кількість елементів у словнику для подальшого використанняЛічильник = MyDictionary.Count'Ітерація за допомогою словника, копіювання кожного ключа та елемента в послідовну клітинку на' Sheet1 '(стовпець A)Для N = 0 до MyDictionary.Count - 1Аркуші ("Аркуш1"). Клітинки (N + 1, 1) = MyDictionary.Keys (N)Аркуші ("Аркуш1"). Клітинки (N + 1, 2) = MyDictionary.Items (N)Далі N'Активуйте Sheet1 і використовуйте програму сортування Excel для сортування даних у порядку зростанняАркуші ("Аркуш1"). АктивуйтеДіапазон ("A1: B" & MyDictionary.Count). ВиберітьActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Діапазон (_"A1: A5"), SortOn: = xlSortOnValues, Порядок: = xlAscending, DataOption: = _xlSortNormalЗа допомогою ActiveWorkbook.Worksheets ("Sheet1"). СортуватиДіапазон .SetRange ("A1: A5").Головник = xlGuess.MatchCase = Неправда.Орієнтація = xlTopToBottom.SortMethod = xlPinYin.ЗастосуватиЗакінчити з'Очистити всі елементи зі словникаMyDictionary.RemoveAll'Скопіюйте значення клітинки назад у порожній об'єкт словника, використовуючи збережене значення (лічильник) для циклу'Для N = 1 До лічильникаMyDictionary.Add Sheets ("Sheet1"). Cells (N, 1) .Value, Sheets ("Sheet1"). Cells (N, 2) .ValueДалі N«Повторіть словник, щоб довести порядок, у якому зараз є предметиДля N = 0 до MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Далі N'Очистіть робочий аркуш (аркуш1) - при необхідності також видаліть йогоАркуші ("Аркуш1"). Діапазон (комірки (1, 1), клітинки (лічильник, 2)). ОчиститиEnd Sub |
Цей код створює словник із доданими п'ятьма значеннями випадкового порядку. Він фіксує кількість елементів у змінній, а потім перебирає словник, передаючи значення ключа та елемента в окремі стовпці на аркуші.
Потім він сортує завантажений діапазон, використовуючи стовпець А як поле сортування. Словник повністю очищається за допомогою методу "RemoveAll", а потім код перебирає значення клітинок на аркуші, додаючи їх назад до словника.
Нарешті, код повторюється за словником, відображаючи значення ключа та елемента, об’єднані, щоб довести, що сортування спрацювало.
Змінюючи параметри в коді сортування, дані можна сортувати за значеннями елементів.
Копіювання списку ключів на робочому аркуші
Ви можете скопіювати список усіх ключових значень на аркуш за допомогою такого коду:
| 12345678910 | Sub CopyKeyList ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30Аркуші ("Sheet1"). Діапазон ("A1"). Значення = Приєднатися (MyDictionary.Keys, vbLf)End Sub |
Це дасть результат на вашому аркуші:
Ви можете скопіювати весь словник на аркуш за допомогою цього коду:
| 12345678910 | Sub CopyIntoWorksheet ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30Діапазон ("A1"). Змінити розмір (MyDictionary.Count, 1) = Робочий лист Функція.Транспонування (MyDictionary.Keys)Діапазон ("B1"). Змінити розмір (MyDictionary.Count, 1) = Функція робочого аркуша. Транспонувати (MyDictionary.Items)End Sub |
Ваш робочий лист буде виглядати так:
Порівняння словника зі збірником
Словник швидший за збірник.
Колекція вже входить до складу VBA. Словник потребує посилання на Словник сценаріїв Microsoft, щоб додати його, або об’єкт, створений за допомогою пізнього прив’язки
Елемент колекції можна написати лише один раз і прочитати багато разів. У словнику значення елемента можна змінити. За допомогою колекції елемент потрібно видалити, а потім змінений елемент додати назад.
Колекція працює над значеннями індексу, тому важко визначити, де яке значення індексу належить. Словник працює над унікальними ключовими значеннями, які використовуються для визначення об’єкта
У великій колекції відновлення одного елемента відбувається повільніше, ніж у словнику
У колекції ключі використовуються лише для пошуку даних і не можуть бути вилучені. У словнику ключі можна перевірити на наявність і використовувати для пошуку певного елемента.
Колекції враховують регістр, і це неможливо змінити. У словнику режим порівняння можна налаштувати так, щоб він відповідав регістру або не сприймав регістр
У колекції значення ключа повинні бути рядками. У Словнику це можуть бути будь -які типи даних, наприклад числові, дата тощо
Видалення всіх елементів у колекції передбачає повторне визначення об’єкта колекції. У словнику для цього використовується метод "RemoveAll".
