Foreversoft.ru

IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel vba dictionary

Excel vba dictionary

Если вы программируете на VBA/VBS, то рано или поздно вынуждены будете познакомиться с объектом Dictionary . Если в двух словах, то Dictionary — это продвинутый массив. Как вы знаете, массив — это упорядоченный набор неких (обычно однородных) элементов. Вот типичный массив:

Элементы пронумерованы и доступны по номеру индекса. Индекс всегда числовой.

А вот, что из себя представляет Dictionary (словарь):

Как видите, каждому элементу поставлен в соответствие не просто числовой индекс, а уникальный ключ, который в данном случае представляет из себя текстовую строку (имена). Двух одинаковых ключей в словаре быть не может, но могут быть одинаковые элементы (хоть все одинаковые). Таким образом словарь — это обычно некий список, снабжённый ключом, при помощи которого вы хотите извлекать полезную информацию (элементы). В указанном примере мы имеем, допустим, имена детей в качестве ключа, а в качестве элементов, поставленных в соответствие ключу, скажем, количество карманных денег у ребёнка.

С другой стороны нечто подобное можно же сделать, используя массив. Давайте объявим двумерный массив:

Должно быть у словаря есть какие-то преимущества перед таким использованием массивов? И это действительно так!

Давайте пока просто перечислим важнейшие преимущества:

Словарь контролирует уникальность ключей. Два одинаковых ключа не могут быть добавлены в словарь. Это важное свойство, так как программисту очень часто требуется обеспечить или проконтролировать уникальность каких-либо наборов значений, и в этом может с успехом быть использован Dictionary ;

Словарь очень эффективно (при помощи встроенного алгоритма бинарного поиска) осуществляет извлечение элементов по известному ключу. В десятки раз быстрее, чем обычный перебор;

У словаря есть встроенный метод ( Exists ), при помощи которого можно понять, добавлен ли некий ключ в коллекцию;

Словарь позволяет добавлять новые элементы и удалять любые элементы, что, работая с массивами, сделать гораздо сложнее;

Словарь может вернуть все ключи и все элементы в виде отдельных одномерных массивов.

2. Создание Dictionary

Существует несколько способов создать объект типа Dictionary . Ознакомимся с ними:

Считается, что методы, использующие позднее связывание надёжнее в плане обеспечения работоспособности программы на разных компьютерах, так как не зависят от настроек Tools — References. редактора VBA.

Однако, учитывая, что библиотека Microsoft Scripting Runtime присутствует везде, начиная с Windows 2000 , я думаю, что вы без какого-либо ущерба можете использовать методы раннего связывания. Раннее связывание хорошо тем, что оно несколько быстрее работает, а также во время разработки вы можете пользоваться функцией завершения кода (когда среда программирования вам подсказывает имеющиеся у объекта свойства и методы). Выбор за вами.

3. Свойства и методы объекта Dictionary

ТипИдентификаторОписание
СвойствоCountdicObject.Count
Возвращает количество элементов в словаре. Только для чтения.
СвойствоItemdicObject.Item(key)[ = newitem]
Устанавливает или возвращает элемент с указанным ключом. Чтение/запись.
СвойствоKeydicObject.Key(key) = newkey
Заменяет ключ элемента на новое значение.
СвойствоCompareModedicObject.CompareMode[ = compare]
Устанавливает и возвращает режим сравнения текстовых ключей в словаре. Чтение/запись.
МетодAdddicObject.Add (key, item)
Добавляет пару ключ-элемент в словарь.
МетодExistsdicObject.Exists(key)
Возвращает true, если указанный ключ существует в словаре, либо false — в противном случае.
МетодItemsdicObject.Items( )
Возвращает массив, состоящий из всех элементов, имеющихся в коллекции.
МетодKeysdicObject.Keys( )
Возвращает массив, состоящий из всех ключей, имеющихся в коллекции.
МетодRemovedicObject.Remove(key)
Удаляет из словаря элемент с указанным ключом.
МетодRemoveAlldicObject.RemoveAll( )
Полностью очищает словарь от элементов. Сам объект словаря при этом не уничтожается.

4. Наполнение словаря

4.1. Типы данных ключа и элемента

Dictionary наполняется по одному элементу. Не существует способов наполнить словарь массово. Чтобы добавить в словарь новый элемент вы должны иметь уникальный ключ и сам элемент, который под этим ключом будет храниться в словаре.

В качестве типа данных для элемента может быть использовано практически всё что угодно: числа, логический тип, строки (в том числе пустые), дата-время, массивы, любые объекты (листы, диапазоны, коллекции, другие словари, пустой указатель Nothing ).

В качестве типа данных для ключа могут быть использованы: числа, строки, дата-время, объекты, но не массивы.

UDT (User Defined Type) не может напрямую использоваться в качестве ключа и/или элемента, но данное ограничение можно обойти, объявив аналог UDT , создав класс и определив в нём свойства аналогичные имеющимся в UDT . А поскольку класс — это объектный тип, то его уже можно использовать для ключей и элементов.

4.2. Через метод Add

На листе Example , прилагаемого к статье файла, есть таблица с TOP30 стран по площади их территории. Для области данных этой таблицы объявлен именованный диапазон SquareByCountry . Пример ниже добавляет все строки указанногот ИД в Dictionary по принципу страна ( key ) — площадь ( item ):

Как видите, для добавления элемента (item) мы в 12-й строке кода использовали метод Add объекта dicCountry . Если в нашей таблице будет задвоена страна, то при попытке добавить в словарь элемента с ключом, который в словаре уже есть, будет сгенерировано исключение:

4.3. Через свойство Item

Используя свойство Item , также можно добавлять пары ключ-элемент, однако, при попытке добавить дублирующий ключ исключения сгенерировано НЕ БУДЕТ , а элемент будет заменён на новый (с потерей старого). Это очень полезно — иметь возможность выбирать способы наполнения словаря, отличающиеся реакцией на задвоение ключей.

Читать еще:  Excel преобразование типов

4.4. Неявное добавление ключа в Dictionary

И ещё один неожиданный и я бы сказал экзотический способ пополнения словаря. Если упомянуть свойство Item по ПРАВУЮ сторону оператора присваивания, то он оказывается добавит в словарь key с пустым item, если данного key не существует в коллекции. Если же такой key уже существует, то никаких действий предпринято не будет.

Ещё раз хочу обратить ваше внимание, что элемент (item) при таком пополнении коллекции будет пустым ( Empty ). Это можно использовать, если вам нет необходимости что-то хранить в элементах в качестве полезной нагрузки (например, когда вы просто строите список уникальных значений, встречающихся в столбце таблицы).

Если вы читаете словарь через Item (а это, собственно, самый логичный и распространенный метод), и при этом хотите избежать добавления пустых ключей в словарь, используйте предварительно метод Exists , что контроля наличия такого ключа в коллекции.

5. Удаление элементов

Есть 2 варианта удаления элементов из словаря:

Analyst Cave

VBA Dictionary – Using the VBA Dictionary. Key Value pairs

Visual Basic for Applications (VBA) facilitates many useful data structures – its not just VBA Arrays you can use. COM libraries provide additional data structures such as the VBA Dictionary, ArrayList, Queue, Stack or SortedList.
But before we jump into explaining how these more complex data structures work and how to use them, let’s start with the basics – VBA array. Often you needn’t reach out for more complex structures, so it’s important to understand first the limits of VBA Arrays.

VBA Dictionary example

The VBA Dictionary is probably the second most often used data structure in VBA. Dictionaries are great at storing key-value pairs of data similarly as the VBA Collection object does.

The VBA Dictionary, however, offers some functionality that is not available with the VBA Collection object e.g. the option to specify a comparison method for Keys, which I will demonstrate below. This allows for a case-sensitive Key.

Ok, so let’s start with a simple example of how the VBA Dictionary can be used.

VBA Dictionary Late Binding

VBA Dictionary Early Binding

In case you prefer to want to declare variables as the Dictionary object you need to reference the Microsoft Scripting Runtime library. To do this go to the Tools menu and select References . From the pop-up window scroll down and select the library mentioned above. This will allow you to use the VBA Dictionary like this:

Both methods are ok with the difference that with Early Binding if you share your Excel file with another user he/she may have to first reference the Microsoft Scripting Runtime otherwise will be getting errors.

Hence I personally prefer Late Binding limiting any necessary actions from anyone I would be sharing my files with.For learning purposes, however, it may be easier to reference the library and use Early Binding allowing you to see the properties of the Dictionary using the suggestions ( CTRL + Space ).

Traversing items and keys

Below methods for traversing either all keys or items in a VBA Dictionary

Removing items

You can remove either a single specific item by key using the Remove property or RemoveAll to remove all items in the Dictionary:

Similar data structures

  • Hashtable — similar to the Dictionary >Creating your own structures (VBA Class)

If none of the above data structures are suitable for your needs you can always create your own VBA Class. A Class is a VBA Module that can be instantiated as an object (similarly as the objects above), have its own properties etc. Classes are a good way of encapsulating abstraction and complexity.

VBA Dictionary

Excel VBA Dictionary

Using VBA Dictionary we can group all kinds of data in a dictionary to get access to all the items with a single variable. We can use the dictionary to create a collection of key-value combinations. Once the object is linked to keys, later on, we can call them by just using the key name.

VBA Dictionary is very tough to get inside but we will try our level best to make it easy for you to understand. We can compare both Dictionary and Collection on the same scale but some of the VBA dictionaries offer some of the functionalities that are not available with VBA Collections object.

Working with VBA Dictionaries

In order to work with VBA Dictionaries first thing we need to do is to set the object reference to ‘Microsoft Scripting Runtime’.

To set the reference follow the below steps.

Step 1: Go to Tools > References.

Step 2: Scroll down and select the ‘Microsoft Scripting Runtime’ option then click ok.

Now we can access VBA Dictionary with Scripting Library.

Create Instance of Dictionary with VBA Code

After setting the reference to ‘Microsoft Scripting Runtime’ we need to create an instance of the VBA Dictionary. First, declare the variable as Scripting.Dictionary

Code:

Now the variable “Dict” is an object variable. For object variable, we need to set the object reference by using the word “New”.

Читать еще:  Конвертация из пдф в word

Now we can access all the properties and methods of the dictionary.

Now declare one variable as DictResult.

Now using the “Dict” variable we will create a new key.

Key is what the word we are adding is. Let’s add Mobile Phone name as “Redmi”.

Item is nothing but the definition to the word (key) we have added. This definition of the phone is its price so I will add the price to 15000.

Now for another variable “DictResult”, we will add keyword using “Dict” variable.

Key is the word we have created in the previous step i.e. name of the phone.

Now the variable “DictResult” has the item of the key we have added. Now show the result of the variable in the VBA message box.

Code:

Now run the code manually or using the F5 key and a message box will show you the price (item) of the phone (key) we have added using “Dict”.

Understanding KEY & ITEM

If you have not understood KEY & ITEM let me explain you with a simple example. Imagine the real world dictionary, with this dictionary we have words (keys) and meaning of those words (item). Similarly, words are Keys and definition or meaning is the Item.

Now, look at one more example of a Dictionary. Assume you are searching for a phone number of a particular person. How do you search?

Obviously, by using the name we have used while saving the phone number. Here we have two things one is the Name of the Person & the second one is Phone Number.

Name of the Person is Key.

The Phone Number is Item.

If you want the example of Excel we can give VLOOKUP as an example. We use the formula to look for values based on the LOOKUP VALUE (Key). The result returned by the VLOOKUP function is called as Item.

Check Whether the Mobile Phone is there or not

Imagine you are giving a user form to your customers to check the mobile phone’s price with a simple input box. Below Excel VBA code will present a Input Box in front of the user and they need to enter the brand of phone they are looking for, if the brand name is there in the dictionary it will show the price of the respective phone or else it will display the message as “Phone You are Looking for Doesn’t Exist in the Library”.

Code:

Run this code using F5 key or manually and see the result.

Recommended Articles

This has been a guide to VBA Dictionary. Here we learn how to use VBA Dictionary to create a collection of key-value combinations in excel along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

VBA Dictionary

Excel VBA Dictionary

Dictionary, in general, has a collection of words with their defined meaning and applications. The same concept can be used in Excel VBA Dictionary creation, where we can add, update, change and delete as many words as we can. In VBA Dictionary we can use the collection of array or words to define or create any category. As in a normal word dictionary, we see words with their meaning, categories, application, pronunciation, etc. In VBA Dictionary as well we can define all the necessary characteristics and value which we want to assign to any word or variable.

To use VBA Dictionary, we need to enable Microsoft Scripting Runtime function which is available in Tool menu option under References.

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

Tool -> References -> Microsoft Scripting Runtime

VBA Dictionary has a few major properties as explained below;

  1. Count = It returns the count of a number of variable in the dictionary key.
  2. Item = We can get the item value of specific key with this.
  3. Key = By this we can replace any key with another one.
  4. Add = It is used for adding key value to a dictionary.
  5. CompareMode = It is used for comparing the different keys in dictionary.

Steps to Create Dictionary in Excel VBA

To create a VBA Dictionary, first, we need to activate Microsoft Scripting Runtime which is the first step towards activating the Excel VBA Dictionary. Follow the steps to create a Dictionary in Excel using VBA Code.

Step 1: For this go to Tool menu and select References option from there as shown below.

Step 2: Now from the window of References – VBA Project, select Microsoft Scripting Runtime, which will help us in updating the VBA dictionary. Once done click on OK.

Step 3: Now we need a module to write the code for VBA dictionary. So open a Module from Insert menu option as shown below.

Step 4: Now write the subprocedure for VBA Dictionary in any name as shown below.

Code:

Step 5: Now we will define a variable as Dictionary and assign it the function Dictionary as shown below. To avoid confusion, you can choose any other name as per your choice.

Читать еще:  Как удалить пустую страницу в word

Code:

Step 6: Now set that variable Dictionary as New Dictionary as shown below.

Code:

Step 7: Now we will open the With-End with Loop as shown below.

Code:

Step 8: As we already discussed the different properties or application which we can choose in Excel VBA Dictionary. So here we will select .Add which is used for adding the words in the dictionary with any Key as shown below. Here we have randomly taken the key as 11.

Code:

Step 9: Now we will assign an Item to that Key. We chose the word “Dog” for Key 11.

Code:

Step 10: Similarly, we add a few more words. Let’s say those words be “Cat” and “Rat” with Key 12 and 13 respectively.

Code:

Step 11: Now insert Debug.Print command to print the values stored in used keys as per numbers fed.

Code:

This completes our code. Now to understand how words will be added into Excel VBA Dictionary, we need to add some more watch windows.

Step 12: Go to View menu and select Watch Window and Immediate Window as shown in the below list.

After that our VBA window will probably look like as shown below.

We adjust the opened Immediate and Watch window anywhere in a screen as per our need.

Step 13: Now add the Watch list in Watch window. For this Right-click on it and click on Add Watch as shown below.

Step 14: In the watch window, name the expression and select the Module in which you have written the code. Here our module is Module1. And click on OK at last.

In the watch window, we will get our Watch list which is named as Dictionary as shown below.

Step 15: Now run the code by clicking on the Play button or by pressing the F5 key. We will see our first word “Dog” will get added into the dictionary and will be visible in an Immediate window as shown below.

Step 16: To see the update in Watch window, press F8 key to run each step one by one. There will be a point where we will see Watch window got updated with the item and key number assigned to each number. Then click on the plus sign in Watch window to explore the things stored in each Key numbers.

In that we will be able to see the Count of variables added into our dictionary, item numbers and assigned key value to them and what type of variable they are as shown below.

Here Count is 3 because we have chosen 3 different words to be added into our dictionary. For item Dog, Cat and Rat, the values stored are 11, 12, and 13 respectively. And in the context column, we will be able to see the Module which we have selected as Module1.

Step 17: Now to see the different values of item, we will need to change the Key number in Debug.Print as shown below. Here we chose 12.

Step 18: Now again run the code by clicking on the Play button or by pressing the F5 key. We will see, in an Immediate window, next item Cat is added.

Step 19: This will be changed if we again add 13 in Debug.Print as shown below.

Step 20: Now again run the code by clicking on the Play button or by pressing the F5 key.

This is how we can create and add multiple words into Excel VBA Dictionary as per our choice.

Pros of Excel VBA Dictionary

  • We can insert as many customized words of our choice which could be used any database creation.
  • This is very easy to find the added word with the help of Key assigned to it.
  • Apart from addition, we can remove, update and compare the already added words into the dictionary.

Cons of Excel VBA Dictionary

  • We need to run the complete code again after changing the key number inPrint to see different words added into the dictionary.

Things to Remember

  • Immediate and Watch windows are required to see the words added to the dictionary.
  • Any type of word, number range, characters can be added with defined meaning.
  • An immediate window shows the word which is added into the dictionary.
  • Watch window shows the value and type of words added into the dictionary.

Recommended Articles

This is a guide to VBA Dictionary. Here we discuss examples to create a Dictionary in Excel using VBA code along with an example and downloadable excel template. You can also go through our other suggested articles –

Ссылка на основную публикацию
Adblock
detector