Использование словаря VBA
Словарь VBA работает аналогично объекту коллекции, но имеет больше свойств и методов и предлагает большую гибкость.
Словарь хранит данные в памяти, и им легко управлять. Нет необходимости в автоматическом вычислении, фоновом резервном копировании и обновлении экрана, поэтому ваш код будет работать значительно быстрее.
Объект словаря работает аналогично обычному словарю, который вы использовали бы, если хотите узнать значение слова. Каждая запись в объекте словаря имеет значение «ключ» и значение «элемент». Вы используете «ключ» значение ключа для поиска значения элемента в объекте словаря аналогично тому, как вы использовали бы обычный словарь.
Из-за того, как работает объект словаря, все значения ключей должны быть уникальными, так же, как и в обычном словаре. Представьте, что вы открыли свой обычный словарь, чтобы найти значение слова, и нашли слово, указанное более одного раза, с двумя совершенно разными определениями. Вы бы очень запутались!
Ключевыми значениями обычно являются текст, числа или и то, и другое. Пользователям часто легче запоминать названия клавиш в виде текста, а не просто цифр.
По сравнению с объектом коллекции, объект коллекции доступен только для чтения. У него есть только два метода (Добавить и Удалить) и два свойства (Счетчик и Элемент). После того, как элемент был добавлен к объекту коллекции, его можно только удалить, но не редактировать, что является громоздкой процедурой, если значение элемента необходимо изменить.
Объект словаря автоматически изменится в размере, чтобы соответствовать количеству элементов в нем. Его не нужно определять по размеру, как у обычного массива.
Объект словаря является одномерным, а тип данных - «Вариант», поэтому в него можно ввести любой тип данных, например числовой, текст, дата
Словарь VBA не является встроенным в Excel, и при определении объекта словаря к нему требуется доступ посредством раннего или позднего связывания.
| 123 | Sub EarlyBindingExample ()Dim MyDictionary как новый Scripting.DictionaryКонец подписки |
| 1234 | Sub LateBindingExample ()Dim MyDictionary как объектУстановите MyDictionary = CreateObject ("Scripting.Dictionary")Конец подписки |
Если вы используете раннее связывание, вы должны добавить ссылку на библиотеку Microsoft Scripting Runtime.
Вы делаете это, выбирая «Инструменты | Ссылки »в строке меню окна редактора Visual Basic (VBE), и появится всплывающее окно со списком доступных библиотек.
Прокрутите вниз до «Microsoft Scripting Runtime» и установите рядом с ним флажок. Нажмите OK, и эта библиотека теперь является частью вашего проекта VBA, и на нее можно ссылаться с помощью раннего связывания. Во всех примерах кода в этой статье будет использоваться раннее связывание.
Ваш код будет работать значительно быстрее с ранним связыванием, потому что он все скомпилирован заранее. При позднем связывании объект должен компилироваться по мере выполнения кода.
В библиотеке Scripting Runtime есть «Intellisense». По мере написания кода вы увидите списки доступных методов и свойств, которые помогают предотвратить ошибки в правописании, которые могут вызвать ошибки в вашей программе.
Кроме того, если вы нажмете F2 в VBE и выберете библиотеку «Scripting», вы увидите все доступные методы и свойства, а также параметры, необходимые для каждого
Распространение вашего приложения Excel, содержащего словарь
Как уже указывалось, библиотека среды выполнения сценариев не является частью Excel VBA, поэтому, если вы распространяете свое приложение среди других пользователей, они должны иметь доступ к библиотеке среды выполнения сценариев на своем компьютере. В противном случае возникнет ошибка.
Рекомендуется включить код VBA, чтобы проверить наличие этой библиотеки при загрузке приложения Excel. Вы можете использовать команду «Dir», чтобы сделать это в событии «Workbook Open».
Расположение файла: C: \ Windows \ SysWOW64 \ scrrun.dll.
Объем объекта словаря
Объект Dictionary доступен только при открытой книге Excel. Он не сохраняется при сохранении книги.
Если ваш словарь должен быть доступен для всех подпрограмм в вашем модуле, вам необходимо объявить его (Dim) в разделе Declare в самом верху модуля.
Вы определяете его как глобальный объект, если хотите, чтобы ваш словарь использовался во всем коде.
| 1 | Global MyDictionary как новый словарь |
Заполнение и чтение из вашего словаря
Для начала вам нужно создать словарь, заполнить его некоторыми данными, а затем выполнить итерацию по нему, чтобы доказать, что данные существуют.
| 1234567891011 | Подложка PopulateReadDictionary ()Dim MyDictionary как новый Scripting.DictionaryMyDictionary.Add «MyItem1», 10MyDictionary.Add «MyItem2», 20MyDictionary.Add «MyItem3», 30Для n = 0 В MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Следующий nКонец подписки |
Этот код создает новый объект словаря с именем «MyDictionary», а затем заполняет его тремя элементами. Метод Add имеет два параметра - Key и Item, и оба они обязательны.
Типы данных для Key и Item являются вариантами, поэтому они принимают любой тип данных - числовые, текстовые, даты и т. Д.
Первый элемент в словаре можно добавить как:
| 1 | MyDictionary.Add 10, «MyItem1» |
Значения были поменяны местами между Key и Item, но это все равно будет работать, хотя ключ поиска теперь станет 10.
Однако важно понимать, что значение ключа - это значение подстановки в словаре. Она работает аналогично функции ВПР в Excel. Поскольку все ключи должны иметь уникальные значения, вы можете указать значение ключа и мгновенно вернуть значение элемента для этого ключа.
Обратите внимание, что индекс словаря начинается с 0, поэтому вам нужно вычесть 1 из счетчика словаря, используемого в цикле For… Next.
Вы также можете использовать цикл For… Each для чтения значений в словаре:
| 1234567891011 | Подложка PopulateReadDictionary ()Dim MyDictionary как новый Scripting.Dictionary, I как вариантMyDictionary.Add «MyItem1», 10MyDictionary.Add «MyItem2», 20MyDictionary.Add «MyItem3», 30Для каждого я в MyDictionary.KeysMsgBox I & "" & MyDictionary (I)Далее яКонец подписки |
Этот код будет перебирать каждый элемент и отображать ключ элемента и значение элемента.
Использование порядкового номера позиции
Вы можете использовать порядковый номер ключа или элемента, чтобы прочитать значение
| 123456789101112 | Sub IndexNumbers ()Dim MyDictionary как новый Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary.Keys (2)MsgBox MyDictionary.Items (1)Конец подписки |
Этот код вернет ключ «item3», поскольку индекс начинается с 0, а значение элемента 20
Вы можете ссылаться на отдельные значения ключа или элемента в коллекциях ключей или элементов, используя номера индексов.
Фильтрация словаря
Прямого способа сделать это не существует, но написать код для этого довольно просто:
| 1234567891011 | Sub FilterDictionary ()Dim MyDictionary как новый Scripting.DictionaryMyDictionary.Add «AAItem1», 10MyDictionary.Add «BBItem2», 20MyDictionary.Add «BBItem3», 30Для каждого элемента "Я в фильтре" (MyDictionary.Keys, "BB")MsgBox MyDictionary.Item (I)Далее яКонец подписки |
Значение фильтра работает только с начала значения ключа. В фильтре нельзя использовать подстановочные знаки. Этот код вернет два значения элементов с названиями ключей, начинающимися с «BB».
Это даст вам подмножество словаря на основе значения вашего фильтра, которое вы затем сможете перенести в другой словарь или рабочий лист. Тщательно спланировав имена ключей, убедившись, что для каждого из них есть значащий префикс, вы легко сможете разделить словарь на различные составные части.
Изменение значения элемента ключа
Объект словаря имеет большое преимущество перед коллекцией в том, что значение элемента может быть изменено, например.
| 1 | MyDictionary ("MyItem4") = "40" |
В коллекции вам нужно будет удалить эту запись, а затем создать ее заново.
Вот пример кода:
| 12345678910111213 | Подложка PopulateReadDictionary ()Dim MyDictionary как новый 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)Следующий nКонец подписки |
Приведенный выше код устанавливает три элемента в словаре, а затем изменяет значение MyItem2 с 20 на 25.
Он также изменяет значение «MyItem4» на 40. Обратите внимание, что в операторах добавления кода «MyItem4» не был добавлен. Когда вы меняете значение несуществующего ключа, он создается автоматически. Это очень удобно, так как не возникает никаких ошибок, но это означает, что вам нужно быть осторожным с именами ключей. Непреднамеренная орфографическая ошибка в имени ключа будет означать, что создается новый ключ, а исходное имя ключа по-прежнему будет иметь старое значение.
Это может легко привести к проблемам с целостностью объекта словаря.
Проверьте, существует ли ключ
Вы можете проверить, существует ли значение ключа в словаре
| 123456789 | Sub CheckExistsDictionary ()Dim MyDictionary как новый Scripting.DictionaryMyDictionary.Add «MyItem1», 10MyDictionary.Add «MyItem2», 20MyDictionary.Add «MyItem3», 30MsgBox MyDictionary.Exists ("MyItem8")Конец подписки |
Код добавляет три элемента к новому объекту словаря, а затем проверяет ключ («MyItem8»), которого нет в словаре. Это возвращает False, но если бы использовался один из существующих ключей, он вернул бы True.
Подстановочные знаки не принимаются. По умолчанию поисковый текст также чувствителен к регистру, но это можно изменить (см. Далее в статье).
Использование нескольких значений в словаре
В отличие от массива объект словаря является только одномерным. Это может привести к проблемам, если у вас есть несколько значений, которые вы хотите сопоставить с ключом.
Один из способов - объединить каждое значение элемента, используя символ-разделитель между каждым значением, например. ‘|’
| 12345678910111213141516171819202122232425262728293031323334 | Подмножественные значения ()'Создать объект словаря и переменныеDim MyDictionary как новый Scripting.Dictionary, V1 как целое число, V2 как строкаDim V3 как дата, температура как строка, N как целое число'Заполните 3 переменные, чтобы продемонстрировать несколько значенийV1 = 5V2 = "Пример нескольких значений"V3 = "22 июля 2020 г."'Добавить объединенное значение в словарь, используя "|" разделительMyDictionary.Add "MyMultipleItem", V1 & "|" & V2 & "|" & V3 & "|"'Захватить объединенное значение словаря из словаря в переменнуюTemp = MyDictionary ("MyMultipleItem")'Перебираем составную строку для разделения отдельных значенийДелать'Найдите положение разделителяN = InStr (Temp, "|")'Если разделителей больше нет, цикл DoЕсли N = 0, то выйти из Do'Отображать текст относительно позиции найденного разделителяMsgBox Left (Temp, N - 1)'Обрезать объединенную строку до следующего символа после найденного разделителяТемпература = Средняя (Температура, N + 1)ПетляКонец подписки |
Другой способ обойти эту проблему - разработать собственную систему сценариев для имен ключей. Нет причин, по которым вы не должны использовать скобки и цифры в названиях клавиш.
| 1234567891011 | Подмножественные значения ()Dim MyDictionary как новый Scripting.DictionaryMyDictionary.Add «Несколько (1)», 5MyDictionary.Add «Несколько (2)», «Пример нескольких значений»MyDictionary.Add "Multiple (3)", "22-Jul-2020"Для N = от 1 до 3MsgBox MyDictionary ("Несколько (" & N & ")")Следующий NКонец подписки |
Этот код добавляет в словарь три ключа, но каждое имя ключа содержит номер субсценария в скобках. Затем вы можете ссылаться на имя ключа, но используя номер субсценария, присоединенный к нему. Это очень похоже на использование объекта массива
Удаление элементов
Вы можете удалить отдельные элементы, указав значение ключа
| 1 | MyDictionary.Remove («MyItem2») |
Обратите внимание: поскольку имена ключей уникальны, удаляется только один конкретный ключ и значение элемента.
Вы также можете полностью очистить словарь
| 1 | MyDictionary.RemoveAll |
Вот пример использования «Удалить» в VBA:
| 12345678910111213141516 | Sub RemoveValues ()Dim MyDictionary как новый 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.CountКонец подписки |
Код добавляет в словарь три элемента, а затем удаляет «Item2». Затем он просматривает словарь, чтобы доказать, что "Item2" больше не существует.
Наконец, код удаляет все элементы в словаре и отображает счетчик словаря, который теперь равен нулю.
Изменение чувствительности к регистру при поиске
Если вы выполняете поиск ключа, по умолчанию он чувствителен к регистру. Однако вы можете использовать свойство «CompareMode», чтобы изменить это.
Обратите внимание, что это необходимо сделать в коде сразу после создания объекта словаря, но перед добавлением каких-либо данных в словарь. После установки режима сравнения его нельзя изменить в этом словаре.
| 12345678910 | Sub ChangeCaseSensitivity ()Dim MyDictionary как новый Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary.Exists ("item2")Конец подписки |
В этом примере установлен режим сравнения «TextCompare», что означает, что регистр не учитывается. Оператор «Exists» в конце примера вернет True, несмотря на то, что весь текст для поиска находится в нижнем регистре.
В 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'Захватить количество элементов в словаре для будущего использованияCounter = 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: = Range (_"A1: A5"), SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = _xlSortNormalС ActiveWorkbook.Worksheets ("Sheet1"). Сортировать.SetRange Range ("A1: A5").Header = xlGuess.MatchCase = Ложь.Orientation = 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)). ОчиститьКонец подписки |
Этот код создает словарь с пятью добавленными значениями случайного порядка. Он фиксирует количество элементов в переменной, а затем выполняет итерацию по словарю, передавая значения ключа и элемента в отдельные столбцы на листе.
Затем он сортирует загруженный диапазон, используя столбец A в качестве поля сортировки. Словарь полностью очищается с помощью метода «RemoveAll», а затем код выполняет итерацию по значениям ячеек на листе, добавляя их обратно в словарь.
Наконец, код выполняет итерацию по словарю, отображая объединенные значения ключа и элемента, чтобы доказать, что сортировка сработала.
Изменяя параметры в коде сортировки, данные можно было сортировать по значениям элементов.
Копирование списка ключей на рабочий лист
Вы можете скопировать список всех ключевых значений на лист, используя следующий код:
| 12345678910 | Sub CopyKeyList ()Dim MyDictionary как новый Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30Таблицы ("Sheet1"). Диапазон ("A1"). Значение = Присоединиться (MyDictionary.Keys, vbLf)Конец подписки |
Результат будет на вашем листе:
Вы можете скопировать весь словарь на лист, используя этот код:
| 12345678910 | Sub CopyIntoWorksheet ()Dim MyDictionary как новый Scripting.DictionaryMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30Диапазон ("A1"). Изменить размер (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Keys)Диапазон ("B1"). Изменить размер (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Items)Конец подписки |
Ваш рабочий лист будет выглядеть так:
Сравнение словаря с коллекцией
Словарь быстрее, чем сборник.
Коллекция уже находится в VBA. Для словаря требуется ссылка на словарь сценариев Microsoft для добавления или объект, созданный с использованием позднего связывания.
Элемент коллекции можно записать только один раз и прочитать много раз. В Словаре значение элемента можно изменить. В случае коллекции элемент необходимо удалить, а затем снова добавить измененный элемент.
Коллекция работает со значениями индекса, поэтому бывает сложно определить, какое значение индекса и где принадлежит. Словарь работает с уникальными ключевыми значениями, которые используются для поиска элемента.
Получение одного элемента в большой коллекции происходит медленнее, чем в словаре.
В Коллекции ключи используются только для поиска данных и не могут быть получены. В словаре можно проверить наличие ключей и использовать их для поиска определенного элемента.
Коллекции чувствительны к регистру, и это нельзя изменить. В Словаре режим сравнения может быть настроен на чувствительность к регистру или без учета регистра
В коллекции ключевые значения должны быть строками. В Словаре они могут быть любым типом данных, например. число, дата и т. д.
Удаление всех элементов в Коллекции включает переопределение объекта Коллекции. В Словаре для этого есть метод «RemoveAll».
