Коришћење ВБА речника
ВБА речник функционише на сличан начин као објекат збирке, али има више својстава и метода и нуди већу флексибилност
Речник чува податке у меморији и њима се лако може руковати. Не захтевају се аутоматско израчунавање, позадинско прављење резервних копија и освежавање екрана, па ће ваш код радити знатно брже.
Објекат речника функционише на сличан начин као и обичан речник који бисте користили ако желите да сазнате значење речи. Сваки унос у објекту речника има вредност „кључ“ и вредност „ставка“. Користите вредност „кеи“ кеи за тражење вредности ставке у објекту речника, на сличан начин на који бисте користили конвенционални речник.
Због начина рада објекта речника, све кључне вредности морају бити јединствене, на исти начин као у конвенционалном речнику. Замислите да сте отворили свој уобичајени речник да бисте потражили значење речи и нашли реч наведену више пута са две потпуно различите дефиниције. Били бисте јако збуњени!
Кључне вредности су обично текст или бројеви, или обоје. Корисницима је често лакше да запамте имена кључева као текст, а не само као бројеве.
У поређењу са објектом збирке, објекат збирке је само за читање. Има само два метода (додавање и уклањање) и два својства (број и ставка). Када се ставка дода објекту збирке, може се само уклонити, али не и уредити, што је гломазна процедура ако је потребно променити вредност ставке.
Објекат речника ће аутоматски променити величину тако да одговара броју ставки у њему. Не мора се дефинисати у величини, попут конвенционалног низа
Објекат речника је једнодимензионалан, а тип података је „Варијанта“, па се у њега може унети било који тип података, нпр. нумерички, текст, датум
ВБА речник није изворни за Екцел и потребно му је приступити раним или касним везивањем при дефинисању објекта речника
| 123 | Суб ЕарлиБиндингЕкампле ()Дим МиДицтионари Ас Нев Сцриптинг.ДицтионариЕнд Суб |
| 1234 | Суб ЛатеБиндингЕкампле ()Дим МиДицтионари Ас ОбјецтПостави МиДицтионари = ЦреатеОбјецт ("Сцриптинг.Дицтионари")Енд Суб |
Ако користите рано везивање, морате додати референцу у библиотеку „Мицрософт Сцриптинг Рунтиме“
То можете учинити избором „Алати | Референце “на траци менија прозора Висуал Басиц Едитор (ВБЕ) прозора и појавиће се искачући прозор са листом доступних библиотека.
Померите се надоле до „Мицрософт Сцриптинг Рунтиме“ и означите поље поред ње. Притисните У реду и ова библиотека је сада део вашег ВБА пројекта и може се референцирати помоћу раног везивања. Сви примери кода у овом чланку користиће рано везивање.
Ваш код ће се изводити знатно брже са раним везивањем, јер је све унапред компајлирано. Са касним везивањем, објекат се мора компајлирати док се код извршава
Библиотека Сцриптинг Рунтиме има „Интеллисенсе“. Док пишете свој код, видећете да се појављују листе доступних метода и својстава, што помаже у спречавању грешака у правопису, што ће изазвати грешке у вашем програму
Такође, ако притиснете Ф2 унутар ВБЕ -а и одаберете библиотеку „Скриптовање“, видећете све доступне методе и својства и потребне параметре за сваки
Дистрибуција ваше Екцел апликације која садржи речник
Као што је већ истакнуто, библиотека Сцриптинг Рунтиме није део програма Екцел ВБА, па ако своју апликацију дистрибуирате другим корисницима, они морају имати приступ библиотеци Скриптинг Рунтиме на свом рачунару. Ако нису, доћи ће до грешке.
Било би добро укључити неки ВБА код да бисте проверили да ли је ова библиотека присутна када се учита ваша Екцел апликација. Да бисте то урадили, можете да користите команду „Дир“ на догађају „Отворена радна свеска“
Локација датотеке је Ц: \ Виндовс \ СисВОВ64 \ сцррун.длл
Обим предмета речника
Објекат Речник је доступан само док је Екцел радна свеска отворена. Не чува се када се сачува радна свеска.
Да би ваш речник био доступан свим рутинама унутар вашег модула, морате га пријавити (Дим) у одељку Децларе на самом врху модула
Дефинишете га као глобални објекат ако желите да се ваш речник користи у вашем коду.
| 1 | Глобални речник као нови речник |
Напуњавање и читање из вашег речника
За почетак морате да креирате речник, попуните га неким подацима, а затим га поновите да бисте доказали да подаци постоје
| 1234567891011 | Суб ПопулатеРеадДицтионари ()Дим МиДицтионари Ас Нев Сцриптинг.ДицтионариМиДицтионари.Адд "МиИтем1", 10МиДицтионари.Адд "МиИтем2", 20МиДицтионари.Адд "МиИтем3", 30За н = 0 у МиДицтионари.Цоунт - 1МсгБок МиДицтионари.Кеис (н) & "" & МиДицтионари.Итемс (н)Нект нЕнд Суб |
Овај код ствара нови објекат речника под називом „МиДицтионари“, а затим га попуњава са три ставке. Метода Адд има два параметра - Кеи и Итем, и оба су обавезна
Типови података за кључ и ставку су обе варијанте, па ће прихватити било коју врсту података - нумеричке, текстуалне, датуме итд.
Прва ставка у речнику може се додати као:
| 1 | МиДицтионари.Адд 10, "МиИтем1" |
Вредности су измењене између кључа и ставке, али ово би и даље функционисало, иако би кључ за претрагу сада постао 10.
Међутим, важно је схватити да је кључна вредност вредност за претраживање у речнику. Ради на врло сличан начин као и функција ВЛООКУП у Екцелу. Пошто сви кључеви морају имати јединствене вредности, можете навести вредност кључа и одмах вратити вредност ставке за тај кључ.
Имајте на уму да индекс речника почиње са 0 па морате одузети 1 од броја речника који се користи у петљи За … Следеће
Такође можете користити Фор… Свака петља за читање вредности у речнику:
| 1234567891011 | Суб ПопулатеРеадДицтионари ()Дим МиДицтионари Ас Нев Сцриптинг.Дицтионари, И Ас ВариантМиДицтионари.Адд "МиИтем1", 10МиДицтионари.Адд "МиИтем2", 20МиДицтионари.Адд "МиИтем3", 30За сваки И у МиДицтионари.КеисМсгБок И & "" & МиДицтионари (И)Затим самЕнд Суб |
Овај код ће понављати сваку ставку и приказати кључ ставке и вредност ставке
Коришћење индексног броја ставке
За читање вредности можете користити индексни број кључа или ставке
| 123456789101112 | Под индексни бројеви ()Дим МиДицтионари Ас Нев Сцриптинг.ДицтионариМиДицтионари.ЦомпареМоде = Упореди текстМиДицтионари.Адд "Итем1", 10МиДицтионари.Адд "Итем2", 20МиДицтионари.Адд "Итем3", 30МсгБок МиДицтионари.Кеис (2)МсгБок МиДицтионари.Итемс (1)Енд Суб |
Овај код ће вратити кључ „итем3“ јер индекс почиње од 0, а вредност ставке 20
Помоћу индексних бројева можете се позвати на појединачне вредности кључа или ставке у збиркама Кључеви или Ставке.
Филтрирање Речника
Не постоји директан метод за то, али је прилично једноставно написати код да бисте то урадили:
| 1234567891011 | Суб ФилтерДицтионари ()Дим МиДицтионари Ас Нев Сцриптинг.ДицтионариМиДицтионари.Адд "ААИтем1", 10МиДицтионари.Адд "ББИтем2", 20МиДицтионари.Адд "ББИтем3", 30За сваки филтер у мени (МиДицтионари.Кеис, "ББ")МсгБок МиДицтионари.Итем (И)Затим самЕнд Суб |
Вредност филтера ради само од почетка вредности кључа. У филтеру не можете да користите заменске знакове. Овај код ће вратити две вредности ставки са називима кључева који почињу са „ББ“
Ово ће вам дати подскуп речника на основу ваше вредности филтера, који затим можете пренети у други речник или на радни лист. Пажљивим планирањем назива кључева, водећи рачуна да за сваки од њих постоји смислени префикс, лако ћете моћи да поделите речник на различите саставне делове.
Промена вредности ставке кључа
Речнички објекат има велику предност у односу на збирку у томе што се вредност ставке може променити нпр.
| 1 | МиДицтионари ("МиИтем4") = "40" |
У збирци бисте морали да избришете тај унос, а затим га поново креирате.
Ево примера кода:
| 12345678910111213 | Суб ПопулатеРеадДицтионари ()Дим МиДицтионари Ас Нев Сцриптинг.ДицтионариМиДицтионари.Адд "МиИтем1", 10МиДицтионари.Адд "МиИтем2", 20МиДицтионари.Адд "МиИтем3", 30МиДицтионари ("МиИтем2") = "25"МиДицтионари ("МиИтем4") = "40"За н = 0 у МиДицтионари.Цоунт - 1МсгБок МиДицтионари.Кеис (н) & "" & МиДицтионари.Итемс (н)Нект нЕнд Суб |
Горњи код поставља три ставке у речнику, а затим мења вредност „МиИтем2“ са 20 на 25.
Такође мења вредност „МиИтем4“ на 40. Имајте на уму да у изразима адд кода није додат „МиИтем4“. Када промените вредност кључа који не постоји, он се аутоматски креира. Ово је изузетно згодно јер се не покреће грешка, али то значи да морате бити опрезни са именима кључева. Нехотична правописна грешка у имену кључа би значила да је креиран нови кључ, а оригинални назив кључа би и даље имао стару вредност.
То би лако могло довести до проблема интегритета у објекту речника.
Тестирајте да ли кључ постоји
Можете проверити да ли у речнику постоји вредност кључа
| 123456789 | Суб ЦхецкЕкистсДицтионари ()Дим МиДицтионари Ас Нев Сцриптинг.ДицтионариМиДицтионари.Адд "МиИтем1", 10МиДицтионари.Адд "МиИтем2", 20МиДицтионари.Адд "МиИтем3", 30МсгБок МиДицтионари.Екистс ("МиИтем8")Енд Суб |
Код додаје три ставке новом објекту речника, а затим тестира кључ („МиИтем8“) који се не налази у речнику. Ово враћа Фалсе, али да је коришћен један од постојећих кључева, вратиће се Труе
Џокер знакови се не прихватају. Текст за претрагу такође подразумевано разликује велика и мала слова, али то се може променити (погледајте касније у чланку)
Коришћење више вредности у речнику
За разлику од низа, објекат речника је само једнодимензионалан. Ово може довести до проблема ако имате неколико вредности које желите да поставите против кључа.
Један од начина да се ово заобиђе је да се свака вредност ставке споји употребом знака разграничења између сваке вредности, нпр. ‘|’
| 12345678910111213141516171819202122232425262728293031323334 | Под вишеструке вредности ()'Креирајте објекат речника и променљивеДим МиДицтионари Ас Нев Сцриптинг.Дицтионари, В1 као цео број, В2 као низДим В3 као датум, Темп као низ, Н као цео број'Попуните 3 променљиве да бисте приказали више вредностиВ1 = 5В2 = "Пример више вредности"В3 = "22. јул 2020."'Додајте повезану вредност у речник помоћу "|" граничникМиДицтионари.Адд "МиМултиплеИтем", В1 & "|" & В2 & "|" & В3 & "|"'Снимите повезану вредност речника из речника у променљивуТемп = МиДицтионари ("МиМултиплеИтем")'Поновите кроз уједињени низ да бисте одвојили појединачне вредностиУрадити'Пронађи положај граничникаН = ИнСтр (Темп, "|")'Ако нема више граничника, излазна петља ДоАко је Н = 0, изађите из До'Прикажи текст у односу на положај граничника који је пронађенМсгБок лево (Темп, Н - 1)'Скратите уједињени низ на следећи знак након што је разграничење пронађеноТемп = средња (Темп, Н + 1)ЛоопЕнд Суб |
Други начин да заобиђете овај проблем је да дизајнирате сопствени систем скриптирања за називе кључева. Нема разлога зашто у називима кључева не користите заграде и бројеве
| 1234567891011 | Под вишеструке вредности ()Дим МиДицтионари Ас Нев Сцриптинг.ДицтионариМиДицтионари.Адд "Мултипле (1)", 5МиДицтионари.Адд "Мултипле (2)", "Пример више вредности"МиДицтионари.Адд "Мултипле (3)", "22-Јул-2020"За Н = 1 до 3МсгБок МиДицтионари ("Више (" & Н & ")")Следећи Н.Енд Суб |
Овај код додаје три кључа у речник, али сваки назив кључа садржи број под скрипте у заградама. Затим се можете позвати на назив кључа, али користећи број под скрипте спојен у. Ово је врло слично коришћењу објекта низа
Брисање ставки
Појединачне ставке можете уклонити позивањем на вредност кључа
| 1 | МиДицтионари.Ремове („МиИтем2“) |
Имајте на уму да пошто су називи кључева јединствени, ово уклања само један одређени кључ и вредност ставке
Такође можете потпуно очистити речник
| 1 | МиДицтионари.РемовеАлл |
Ево примера коришћења „Уклони“ у ВБА:
| 12345678910111213141516 | Суб РемовеВалуес ()Дим МиДицтионари Ас Нев Сцриптинг.ДицтионариМиДицтионари.Адд "Итем1", 10МиДицтионари.Адд "Итем2", 20МиДицтионари.Адд "Итем3", 30МиДицтионари.Ремове ("Итем2")За Н = 0 до МиДицтионари.Цоунт - 1МсгБок МиДицтионари.Кеис (Н) & "" & МиДицтионари.Итемс (Н)Следећи Н.МиДицтионари.РемовеАллМсгБок МиДицтионари.ЦоунтЕнд Суб |
Код додаје три ставке у речник, а затим уклања „ставку 2“. Затим понавља кроз речник како би доказао да „ставка2“ више не постоји
На крају, код уклања све ставке у речнику и приказује број речника, који је сада нула.
Промена осетљивости великих и малих слова за претраге
Ако тражите кључ, он подразумевано разликује велика и мала слова. Међутим, можете користити својство „ЦомпареМоде“ да бисте ово променили.
Имајте на уму да се то мора учинити одмах у коду након што креирате објекат речника, али пре него што додате било какве податке у речник. Након што је постављен режим упоређивања, он се не може променити у оквиру тог речника.
| 12345678910 | Суб ЦхангеЦасеСенситивити ()Дим МиДицтионари Ас Нев Сцриптинг.ДицтионариМиДицтионари.ЦомпареМоде = Упореди текстМиДицтионари.Адд "Итем1", 10МиДицтионари.Адд "Итем2", 20МиДицтионари.Адд "Итем3", 30МсгБок МиДицтионари.Екистс ("итем2")Енд Суб |
У овом примеру, режим упоређивања је постављен на „ТектЦомпаре“, што значи да не разликује велика и мала слова. Изјава ‘Екистс’ на крају примера вратиће Труе, упркос чињеници да је текст за претрагу у малим словима.
У Екцелу постоје само две вредности које се могу користити за режим упоређивања. Бинарно поређење разликује велика и мала слова, а поређење текста не разликује велика и мала слова
Ако имате режим упоређивања постављен на Бинарно поређење, морате бити опрезни при именовању кључева. Ако поставите име да као прво слово има велико слово, онда када мењате вредност, морате се уверити да први знак и даље чини великим словима. Ако почнете малим словом, ово ће се тумачити као нови кључ и лако би могло довести до забуне и грешака у вашем речнику
Упамтите да ако промените вредност кључа, а назив кључа не постоји због коришћења Бинарног поређења, нови кључ и вредност ће бити додати у речник.
Ако уместо тога користите Тект Цомпаре, све промене вредности ће доћи до кључа без обзира на величину слова. Ако покушате да додате исту ставку, али написану са другачијим словима, добићете грешку јер она већ постоји.
Сортирање Речника
Као и код објекта прикупљања, не постоји обезбеђена метода за сортирање речника, било помоћу кључева или вредности ставки.
Међутим, пошто ВБА код седи у Екцел радној свесци, подаци из речника се могу пренети у Екцел у табеларном облику, а затим се на њега може применити Екцел сортирање. Речник се тада може очистити помоћу „РемовеАлл“ и сортиране вредности додати са радног листа.
Овај код ће сортирати и кључеве и вредности ставки
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 | Под СортМиДицтионари ()Дим МиДицтионари Ас Нев ДицтионариДим Цоунтер Ас Лонг'Направите речник са насумичним ставкама редоследаМиДицтионари.Адд "Итем5", 5МиДицтионари.Адд "Итем2", 15МиДицтионари.Адд "Итем4", 11МиДицтионари.Адд "Итем1", 2МиДицтионари.Адд "Итем3", 19'Снимите број ставки у речнику за будућу употребуБројач = МиДицтионари.Цоунт„Итерацијом кроз речник копирајте сваки кључ и ставку у узастопну ћелију на„ листу 1 “(колона А)Фор Н = 0 То МиДицтионари.Цоунт - 1Табеле („Лист1“). Ћелије (Н + 1, 1) = МиДицтионари.Кеис (Н)Табеле („Лист1“). Ћелије (Н + 1, 2) = МиДицтионари.Итемс (Н)Следећи Н.'Активирајте Схеет1 и користите Екцел рутину сортирања да сортирате податке у растућем редоследуТабеле („Лист1“). АктивирајтеОпсег ("А1: Б" & МиДицтионари.Цоунт) .ИзаберитеАцтивеВоркбоок.Ворксхеетс ("Схеет1"). Сорт.СортФиелдс.ЦлеарАцтивеВоркбоок.Ворксхеетс ("Схеет1"). Сорт.СортФиелдс.Адд2 Кеи: = Ранге (_"А1: А5"), СортОн: = клСортОнВалуес, Редослед: = клАсцендинг, ДатаОптион: = _клСортНормалСа АцтивеВоркбоок.Ворксхеетс ("Схеет1"). Сортирај.СетРанге Ранге ("А1: А5").Хеадер = клГуесс.МатцхЦасе = Нетачно.Оријентација = клТопТоБоттом.СортМетход = клПинИин.ПрименитиЗавршити'Обриши све ставке из речникаМиДицтионари.РемовеАлл„Копирајте вредности ћелије назад у празан објекат речника користећи сачувану вредност (бројач) за петљуЗа Н = 1 За бројачМиДицтионари.Адд Схеетс ("Схеет1"). Целлс (Н, 1) .Вредност, Схеетс ("Схеет1"). Целлс (Н, 2) .ВалуеСледећи Н.„Поновите речник да бисте доказали редослед у којем се сада налазе ставкеФор Н = 0 То МиДицтионари.Цоунт - 1МсгБок МиДицтионари.Кеис (Н) & "" & МиДицтионари.Итемс (Н)Следећи Н.'Обришите радни лист (лист 1) - ако је потребно, избришите и његаТабеле („Лист 1“). Опсег (ћелије (1, 1), ћелије (бројач, 2)). ОбришиЕнд Суб |
Овај код ствара речник са пет додатих насумичних вредности редоследа. Он бележи број ставки у променљиву, а затим понавља кроз речник, преносећи вредности кључа и ставке у засебне колоне на радном листу.
Затим сортира преузети опсег, користећи колону А као поље за сортирање. Речник се потпуно брише методом „РемовеАлл“, а код затим понавља вредности ћелија на радном листу додајући их назад у речник.
Коначно, код понавља кроз речник, приказујући вредности кључа и ставке спојене како би доказао да је сортирање успело.
Променом параметара у коду за сортирање, подаци се могу сортирати према вредностима ставки.
Копирање листе кључева на радни лист
Можете да копирате листу свих кључних вредности на радни лист помоћу следећег кода:
| 12345678910 | Суб ЦопиКеиЛист ()Дим МиДицтионари Ас Нев Сцриптинг.ДицтионариМиДицтионари.ЦомпареМоде = Упореди текстМиДицтионари.Адд "Итем1", 10МиДицтионари.Адд "Итем2", 20МиДицтионари.Адд "Итем3", 30Табеле ("Лист1"). Опсег ("А1"). Вредност = Придруживање (МиДицтионари.Кеис, вбЛф)Енд Суб |
Ово ће произвести резултат на вашем радном листу:
Можете да копирате цео речник на радни лист помоћу овог кода:
| 12345678910 | Суб ЦопиИнтоВорксхеет ()Дим МиДицтионари Ас Нев Сцриптинг.ДицтионариМиДицтионари.Адд "Итем1", 10МиДицтионари.Адд "Итем2", 20МиДицтионари.Адд "Итем3", 30Опсег ("А1"). Промена величине (МиДицтионари.Цоунт, 1) = Функција радног листа.Пренос (МиДицтионари.Кеис)Опсег ("Б1"). Промена величине (МиДицтионари.Цоунт, 1) = Функција радног листа.Пренос (МиДицтионари.Итемс)Енд Суб |
Ваш радни лист ће изгледати овако:
Поређење речника са збирком
Речник је бржи од збирке.
Колекција је већ у оквиру ВБА. Речнику је потребна референца на Мицрософт Сцриптинг Дицтионари да би се додао или објект креиран помоћу касног везивања
Ставка збирке може се написати само једном и читати више пута. У Речнику се вредност ставке може променити. Са колекцијом, ставка се мора уклонити, а затим се промењена ставка поново додати.
Збирка ради на вредностима индекса, што може бити тешко одредити којој вредности индекса припада. Речник ради на јединственим кључним вредностима које се користе за лоцирање ставке
Преузимање једне ставке спорије је у великој збирци него у речнику
У збирци, кључеви се користе само за тражење података и није их могуће преузети. У речнику, кључеви се могу тестирати на постојање и могу се користити за проналажење одређене ставке.
Збирке разликују велика и мала слова и то се не може променити. У Речнику, режим упоређивања се може подесити тако да даје осетљивост на велика или мала слова
У збирци, вредности кључа морају бити низови. У Речнику могу бити било који тип података, нпр. нумерички, датум, итд
Уклањање свих ставки у збирци укључује поновно дефинисање објекта колекције. Речник за то има методу „РемовеАлл“.
