Welcome to Excel Avon
Update Items in Dictionary using Excel VBA
DOWNLOAD USED EXCEL FILE FROM HERE>>
In today’s post, we will explain you how to update items in a dictionary created in Excel VBA, in the previous post we explained how to create a dictionary. How to update items with the help of Excel VBA. 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 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 have two values of one key, but we will update items it knows we will update using VBA.
Let us now understand How to Update Items in Dictionary using Excel VBA.
How to Update Items in Dictionary using Excel VBA
Well, you must know how to update items dictionary in Excel VBA, we will learn in this article. Well, it can be updated manually but we will update items with the help of VBE, 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 Update items in 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 Update items in dictionary.
Sub UseDictionary() End Sub
Here we are Declaring Instant variables for Dictionary.
Sub UseDictionary() Dim Dic As New Dictionary End Sub
Declaring more variables which are using in the entire project for worksheet, long, variant, and String.
Sub UseDictionary() Dim Dic As New Dictionary Dim WS As Worksheet Dim i As Long Dim Ky As Variant Dim vStr As String End Sub
Now we will set worksheet as ActiveSheet.
Sub UseDictionary() Dim Dic As New Dictionary Dim WS As Worksheet Dim i As Long Dim Ky As Variant Dim vStr As String Set WS = ActiveSheet End Sub
After the set active sheet, here I will make a loop the value of i which is the range.
Sub UseDictionary() Dim Dic As New Dictionary Dim WS As Worksheet Dim i As Long Dim Ky As Variant Dim vStr As String Set WS = ActiveSheet For i = 2 To 8 Next i End Sub
Here we will write the range of dictionary key.
Sub UseDictionary()
Dim Dic As New Dictionary
Dim WS As Worksheet
Dim i As Long
Dim Ky As Variant
Dim vStr As String
Set WS = ActiveSheet
For i = 2 To 8
Ky = WS.Range("A" & i).Value
Next i
End Sub
Here we will write the range of value of dictionary key which is present in column B.
Sub UseDictionary()
Dim Dic As New Dictionary
Dim WS As Worksheet
Dim i As Long
Dim Ky As Variant
Dim vStr As String
Set WS = ActiveSheet
For i = 2 To 8
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 UseDictionary()
Dim Dic As New Dictionary
Dim WS As Worksheet
Dim i As Long
Dim Ky As Variant
Dim vStr As String
Set WS = ActiveSheet
For i = 2 To 8
Ky = WS.Range("A" & i).Value
vStr = WS.Range("B" & i).Value
Dic.Add Ky, vStr
Next i
End Sub
Use debug.Print and click on run button.
When we click on run button, we get an error pop up window opens ‘this key is already associated with an element of this collection’
After getting the error we will create a condition in it we will use dictionary. Exists, retun true if a specified key exists in the dictionary object. false if it does not.
Sub UseDictionary()
Dim Dic As New Dictionary
Dim WS As Worksheet
Dim i As Long
Dim Ky As Variant
Dim vStr As String
Set WS = ActiveSheet
For i = 2 To 8
Ky = WS.Range("A" & i).Value
vStr = WS.Range("B" & i).Value
If Not Dic.Exists(Ky) Then
End If
Next i
End Sub
Now we will add values in dictionary use add function.
Sub UseDictionary()
Dim Dic As New Dictionary
Dim WS As Worksheet
Dim i As Long
Dim Ky As Variant
Dim vStr As String
Set WS = ActiveSheet
For i = 2 To 8
Ky = WS.Range("A" & i).Value
vStr = WS.Range("B" & i).Value
If Not Dic.Exists(Ky) Then
Dic.Add Ky, vStr
End If
Next i
End Sub
Use debug.Print and click on run button.
The value has been added after the code is run, but now we will print the value of this dictionary key(6f).
Sub UseDictionary()
Dim Dic As New Dictionary
Dim WS As Worksheet
Dim i As Long
Dim Ky As Variant
Dim vStr As String
Set WS = ActiveSheet
For i = 2 To 8
Ky = WS.Range("A" & i).Value
vStr = WS.Range("B" & i).Value
If Not Dic.Exists(Ky) Then
Dic.Add Ky, vStr
End If
Next i
Debug.Print Dic("6f")
End Sub
Value is print but this value the value of the first key.
Now we will remove the condition then now we will use dictionary key = value(vstr) in it.
Sub UseDictionary()
Dim Dic As New Dictionary
Dim WS As Worksheet
Dim i As Long
Dim Ky As Variant
Dim vStr As String
Set WS = ActiveSheet
For i = 2 To 8
Ky = WS.Range("A" & i).Value
vStr = WS.Range("B" & i).Value
Dic(Ky) = vStr
Next i
Debug.Print Dic("6f")
End Sub
Use debug.Print and click on run button.
Value is printed in immediate window, but now can see key updated items. Second item of 6f Key.
If we change the case of dictionary key, then will it print the updated value let’s see.
After changing the case of dictionary key, we will run the code.
The value of the first key is because the dictionary add function is case sensitive.
So, we will use compare method of dictionary function and compare method will do text comparison to print updated value.
Sub UseDictionary()
Dim Dic As New Dictionary
Dim WS As Worksheet
Dim i As Long
Dim Ky As Variant
Dim vStr As String
Set WS = ActiveSheet
Dic.CompareMode = TextCompare
For i = 2 To 8
Ky = WS.Range("A" & i).Value
vStr = WS.Range("B" & i).Value
Dic(Ky) = vStr
Next i
Debug.Print Dic("6f")
End Sub
By adding text compare we will run the code
Value is printed in immediate window again; Now you can see key value is updated.Just we change the case of key, and we can print value of key.
So, I hope you have understood How to Update Items in Dictionary using 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 Update Items in Dictionary using Excel VBA
