Използване на 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 Editor (VBE) и ще се появи изскачащ прозорец със списък на наличните библиотеки.
Превъртете надолу до „Microsoft Scripting Runtime“ и поставете отметка в квадратчето до него. Щракнете върху OK и тази библиотека вече е част от вашия VBA проект и може да бъде препратена с помощта на ранно свързване. Всички примери за код в тази статия ще използват ранно свързване.
Вашият код ще работи значително по -бързо с ранно свързване, защото целият е компилиран предварително. При късно свързване обектът трябва да се компилира, докато кодът се изпълнява
Библиотеката за изпълнение на скриптове има „Intellisense“. Докато пишете кода си, ще се появяват списъци с налични методи и свойства, което помага да се предотвратят грешки в правописа, което ще причини грешки в програмата ви
Също така, ако натиснете F2 в рамките на VBE и изберете библиотеката „Scripting“, ще видите всички налични методи и свойства и необходимите параметри за всеки
Разпространение на вашето приложение Excel, съдържащо речник
Както вече беше посочено, библиотеката Scripting Runtime не е част от Excel VBA, така че ако разпространявате приложението си сред други потребители, те трябва да имат достъп до библиотеката Scripting Runtime на своя компютър. Ако не са, тогава ще възникне грешка.
Добра идея е да включите VBA код, за да проверите дали тази библиотека присъства, когато се зареди приложението ви Excel. Можете да използвате командата „Dir“, за да направите това на събитието „Workbook Open“
Местоположението на файла е 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)Следващ nEnd Sub |
Този код създава нов обект на речника, наречен „MyDictionary“ и след това го попълва с три елемента. Методът Add има два параметъра - Key и Item и те са задължителни
Типовете данни за ключ и елемент са и двата варианта, така че те ще приемат всякакъв тип данни - числови, текстови, дата и т.н.
Първият елемент в речника може да бъде добавен като:
| 1 | MyDictionary.Add 10, "MyItem1" |
Стойностите са обърнати между ключ и елемент, но това все още ще работи, въпреки че ключът за търсене сега ще стане 10.
Важно е обаче да се разбере, че ключовата стойност е стойността за търсене в речника. Работи по много подобен начин на функцията VLOOKUP в Excel. Тъй като всички ключове трябва да имат уникални стойности, можете да посочите стойност на ключ и незабавно да върнете стойността на елемента за този ключ.
Имайте предвид, че индексът на речника започва с 0, така че трябва да извадите 1 от броя на речниците, използван в цикъла For… Next
Можете също да използвате For… Всеки цикъл, за да прочетете стойностите в речника:
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting.Dictionary, I As VariantMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30За всеки I в 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 | Sub FilterDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "AAItem1", 10MyDictionary.Add "BBItem2", 20MyDictionary.Add "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)Следващ nEnd Sub |
Горният код настройва три елемента в речника и след това променя стойността на „MyItem2“ от 20 на 25.
Той също така променя стойността на „MyItem4“ на 40. Обърнете внимание, че в инструкциите за добавяне на кода не е добавен „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 As New Scripting.Dictionary, 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, тогава излезте от Do'Показване на текст спрямо намереното разделително устройство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 „Множество (3)“, „22-юли-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 ("Item2")За 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“, което означава, че той не е чувствителен към регистър. Изразът „Exists“ в края на примера ще върне True, въпреки факта, че текстът за търсене е с малки букви.
В Excel има само две стойности, които могат да се използват за режим на сравнение. Двоичното сравнение е чувствително към регистъра, а текстовото сравнение не е чувствително към регистъра
Ако сте задали режим за сравнение на Binary Compare, трябва да внимавате при именуването на ключовете си. Ако зададете име за първа буква с главна буква, тогава, когато променяте стойността, трябва да се уверите, че все още правите първата буква главна. Ако започнете с малки букви, това ще се интерпретира като нов ключ и лесно може да доведе до объркване и грешки в речника ви
Не забравяйте, че ако промените стойност за ключ и името на ключа не съществува поради използването на двоично сравнение, нов ключ и стойност ще бъдат добавени към речника.
Ако вместо това използвате Text Compare, тогава всяка промяна в стойността ще отиде в ключа, независимо от регистъра. Ако се опитате да добавите същия елемент, но написан с различен регистър, ще получите грешка, тъй като той вече съществува.
Сортиране на речника
Както при обекта за събиране, не е предвиден метод за сортиране на речника, използвайки ключове или стойности на елементи.
Въпреки това, тъй като кодът VBA се намира в работна книга на Excel, данните от речника могат да бъдат прехвърлени в Excel в таблична форма и след това инструментът за сортиране на Excel може да бъде приложен към него. След това речникът може да бъде изчистен с помощта на „RemoveAll“ и сортираните стойности да се добавят от работния лист.
Този код ще сортира както ключовете, така и стойностите на елементите
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 | Sub SortMyDictionary ()Dim MyDictionary As New DictionaryDim Counter As Long„Изградете речник с елементи от произволен редMyDictionary.Add "Item5", 5MyDictionary.Add "Item2", 15MyDictionary.Add "Item4", 11MyDictionary.Add "Item1", 2MyDictionary.Add "Item3", 19'Уловете броя елементи в речника за бъдеща употребаБрояч = MyDictionary.Count„Итерация чрез речник, копиране на всеки ключ и елемент в последователна клетка на„ Sheet1 “(колона А)За N = 0 към MyDictionary.Count - 1Листове ("Sheet1"). Клетки (N + 1, 1) = MyDictionary.Keys (N)Листове ("Sheet1"). Клетки (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: = Range (_"A1: A5"), SortOn: = xlSortOnValues, Ред: = xlAscending, DataOption: = _xlSortNormalС ActiveWorkbook.Worksheets ("Sheet1"). СортиранеДиапазон .SetRange ("A1: A5").Header = xlGuess.MatchCase = False.Ориентация = xlTopToBottom.SortMethod = xlPinYin.ПриложиКрай с„Изчистете всички елементи от речникаMyDictionary.RemoveAll„Копирайте стойностите на клетката обратно в празния обект на речника, като използвате съхранената стойност (Counter) за цикъла„За N = 1 За контраMyDictionary.Add Sheets ("Sheet1"). Cells (N, 1) .Vueue, Sheets ("Sheet1"). Cells (N, 2) .ValueСледва N„Повторете чрез речника, за да докажете реда, в който елементите са в моментаЗа N = 0 към MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Следва N„Изчистете работния лист (Sheet1) - ако е необходимо, изтрийте и негоЛистове ("Sheet1"). Диапазон (клетки (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 Scripting Dictionary, за да бъде добавен, или обект, създаден чрез късно свързване
Елемент от колекция може да бъде написан само веднъж и прочетен многократно. В речник стойността на елемента може да бъде променена. С колекция елементът трябва да бъде премахнат и след това промененият елемент да бъде добавен обратно.
Колекцията работи върху стойности на индекса, което може да бъде трудно да се определи коя стойност на индекса принадлежи. Речникът работи върху уникални ключови стойности, които се използват за намиране на елемент
Извличането на един елемент е по -бавно в голяма колекция, отколкото в речник
В колекция ключовете се използват само за търсене на данни и не могат да бъдат извлечени. В речника ключовете могат да бъдат тествани за съществуване и могат да се използват за намиране на конкретен елемент.
Колекциите са чувствителни към регистъра и това не може да се промени. В Речник режимът за сравнение може да бъде настроен така, че да дава чувствителност към малки или малки букви
В колекция ключовите стойности трябва да са низове. В речника те могат да бъдат всякакъв тип данни, напр. число, дата и др
Премахването на всички елементи в колекция включва предефиниране на обекта на колекцията. Речникът има метода „RemoveAll“ за това.
