Welcome to Excel Avon
Create Dictionary in Excel VBA
DOWNLOAD USED EXCEL FILE FROM HERE>>
In today’s post, we will explain you how to create dictionary in excel VBA, 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.
Using VBA Dictionary, we can group all kinds of data in a dictionary to get access to all the items with a single variable. For example, we can use the create Dictionary to create a collection of key-value combinations. Then, once the object links to keys, we can call them later by just using the key name.
Below you can see that here we can see the key and key value; we will use them in VBA.
Let us now understand How to Create dictionary in Excel VBA.
How to Create Dictionary in Excel VBA
Well, you must know that how to create dictionary in excel VBA we will learn in this article. Well, it is easy to make it in VBE also, let’s understand. So, we have to go like last time, first go to the Developer Tab then click on the option of Visual Basic as shown in the image below.
On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.
Microsoft Runtime Library must be activated to create dictionary. to activate go to Tools option and then References and on that we select Microsoft Script Runtime Library and click OK.
Once the module is inserted, and the library is activated, we’ll write a subroutine to create dictionary.
Sub CreateDictionary() End Sub
Declaring variables which are using in the entire project for worksheet, Dictionary, variant, String, and Long.
Sub CreateDictionary() Dim WS As Worksheet Dim Dic As Dictionary Dim Ky As Variant Dim vStr As String Dim i As Long End Sub
Now we will set WS as ActiveSheet.
Sub CreateDictionary() Dim WS As Worksheet Dim Dic As Dictionary Dim Ky As Variant Dim vStr As String Dim i As Long Set WS = ActiveSheet End Sub
After the set active sheet, we will define. the value of i which is the range.
Sub CreateDictionary() Dim WS As Worksheet Dim Dic As Dictionary Dim Ky As Variant Dim vStr As String Dim i As Long Set WS = ActiveSheet For i = 2 To 7 next i End Sub
Now we will write the key range.
Sub CreateDictionary()
Dim WS As Worksheet
Dim Dic As Dictionary
Dim Ky As Variant
Dim vStr As String
Dim i As Long
Set WS = ActiveSheet
For i = 2 To 7
Ky = WS.Range("A" & i).Value
Next i
End Sub
Now we will write the range of string value.
Sub CreateDictionary()
Dim WS As Worksheet
Dim Dic As Dictionary
Dim Ky As Variant
Dim vStr As String
Dim i As Long
Set WS = ActiveSheet
For i = 2 To 7
Ky = WS.Range("A" & i).Value
vStr = WS.Range("B" & i).Value
Next i
End Sub
To create a dictionary, we have to define a new dictionary variable, we can either create a delay variable or an instant variable, here we have used a delay variable.
Type of Dictionary Creation:
We can create dictionaries by 2 ways
1) Instant Creation:
Sub CreateDictionary()
Dim Dic As New Dictionary
End Sub
1) Delay Creation:
Sub CreateDictionary()
Dim Dic As Dictionary
Set Dic = New Dictionary
End Sub
Now Let's use delay creation method in example
Sub CreateDictionary()
Dim WS As Worksheet
Dim Dic As Dictionary
Dim Ky As Variant
Dim vStr As String
Dim i As Long
Set WS = ActiveSheet
Set Dic = New Dictionary
For i = 2 To 7
Ky = WS.Range("A" & i).Value
vStr = WS.Range("B" & i).Value
Next i
End Sub
And now we will add the dictionary here we will use both the parameters.
Sub CreateDictionary()
Dim WS As Worksheet
Dim Dic As Dictionary
Dim Ky As Variant
Dim vStr As String
Dim i As Long
Set WS = ActiveSheet
Set Dic = New Dictionary
For i = 2 To 7
Ky = WS.Range("A" & i).Value
vStr = WS.Range("B" & i).Value
Dic.Add Ky, vStr
Next i
End Sub
And now we will print the value of dictionary using key.
Sub CreateDictionary()
Dim WS As Worksheet
Dim Dic As Dictionary
Dim Ky As Variant
Dim vStr As String
Dim i As Long
Set WS = ActiveSheet
Set Dic = New Dictionary
For i = 2 To 7
Ky = WS.Range("A" & i).Value
vStr = WS.Range("B" & i).Value
Dic.Add Ky, vStr
Next i
Debug.Print Dic("2B")
End Sub
Use debug.Print and click on run button.
When we click on the run button, we will get the value of 2B printed in the immediate window.
If we want to print the key by writing it in lower case, then the value not printed as binary compare mode is active by default.
Sub CreateDictionary()
Dim WS As Worksheet
Dim Dic As Dictionary
Dim Ky As Variant
Dim vStr As String
Dim i As Long
Set WS = ActiveSheet
Set Dic = New Dictionary
For i = 2 To 7
Ky = WS.Range("A" & i).Value
vStr = WS.Range("B" & i).Value
Dic.Add Ky, vStr
Next i
Debug.Print Dic("2b")
End Sub
So, we will remove the case sensitive and add text compare by removing the compare mode from binary.
Sub CreateDictionary()
Dim WS As Worksheet
Dim Dic As Dictionary
Dim Ky As Variant
Dim vStr As String
Dim i As Long
Set WS = ActiveSheet
Set Dic = New Dictionary
Dic.CompareMode = TextCompare
For i = 2 To 7
Ky = WS.Range("A" & i).Value
vStr = WS.Range("B" & i).Value
Dic.Add Ky, vStr
Next i
Debug.Print Dic("2b")
End Sub
After activating the text compare, we will run the code.
The value will be printed in the immediate window as soon as the code is run.
We will print the value of the other key.
Sub CreateDictionary()
Dim WS As Worksheet
Dim Dic As Dictionary
Dim Ky As Variant
Dim vStr As String
Dim i As Long
Set WS = ActiveSheet
Set Dic = New Dictionary
Dic.CompareMode = TextCompare
For i = 2 To 7
Ky = WS.Range("A" & i).Value
vStr = WS.Range("B" & i).Value
Dic.Add Ky, vStr
Next i
Debug.Print Dic("1A")
End Sub
Use debug.Print and click on run button.
When we click on the run button, we will get the value of 1A printed in the immediate window.
So, I hope you have understood How to Create Dictionary in Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.
DOWNLOAD USED EXCEL FILE FROM HERE>>
You can also see well-explained video here about How to Create Dictionary in Excel VBA
