vba help - to understand dictionary code

[Mallesh23]

Hi Team,

Below code works and gives me correct output as per my requirement.
But I am not able to understand some part of the code ,

Can add comment plz. and how it is working ,here in items Dictionary objects are added.


line are :-
If Not dict.Exists(arr_Fruit(i, 1)) Then Set dict(arr_Fruit(i, 1)) = CreateObject("Scripting.Dictionary")
dict(arr_Fruit(i, 1))(arr_Fruit(i, 2)) = 1

and
If dict.Exists(c.Value) Then c.Offset(, 1).Value = Join(dict(c.Value).Keys(), "/")

Option Explicit

Sub test1()
Dim dict As New Scripting.Dictionary
Dim i As Long
Dim arr_Fruit As Variant

arr_Fruit = Range("a2:c6").Value

'---------Add Data to Dictionary---------
dict.CompareMode = vbTextCompare

For i = LBound(arr_Fruit, 1) To UBound(arr_Fruit, 1)
    If Not dict.Exists(arr_Fruit(i, 1)) Then Set dict(arr_Fruit(i, 1)) = CreateObject("Scripting.Dictionary")
    dict(arr_Fruit(i, 1))(arr_Fruit(i, 2)) = 1
Next i
Dim c As Range
For Each c In Range("f2:f4")
  If dict.Exists(c.Value) Then c.Offset(, 1).Value = Join(dict(c.Value).Keys(), "/")
Next c

End Sub

Input Range("a1:C10"), Look up column F and expected output is in Column G.

Book1
	A	B	C	D	E	F	G
1	Country	Fruit	Color			Name	Fruit/color - Output
2	India	Mango	Yellow			India	Mango
3	Australia	Apple	Green			Australia	Apple
4	England	Banana	Yellow			England	Banana/Mango
5	England	Mango	Blue
6	England	Mango	Blue
Sheet1

 

[GWteB]

The person who wrote the code is the best person to consult.
As a good alternative, I can recommend this article ...

Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery

This post contains everything you need to know about using the VBA Dictionary. There are tons of code examples including a real world application.

excelmacromastery.com

 

[Mallesh23]

Hi Getwb,

I refer it, Really very useful site with so much information in it.


Thanks
mg

 

[GWteB]

You are welcome.

 

[Mallesh23]

Hi Team,

What is the meaning of below lines. I got solution in forum,
but would like to know how it works.

line are :-
If Not dict.Exists(arr_Fruit(i, 1)) Then Set dict(arr_Fruit(i, 1)) = CreateObject("Scripting.Dictionary")
dict(arr_Fruit(i, 1))(arr_Fruit(i, 2)) = 1

and
If dict.Exists(c.Value) Then c.Offset(, 1).Value = Join(dict(c.Value).Keys(), "/")

Thanks
mg

 

[Peter_SSs]

A dictionary object consist of pairs of items: A 'Key' and the associated 'Item'
Commonly that might be something like somebody's name and their age
Key: "Tom"
Item: 34

If Not dict.Exists(arr_Fruit(i, 1)) Then Set dict(arr_Fruit(i, 1)) = CreateObject("Scripting.Dictionary")

In the case of the above code it is slightly different in that the 'Item' is not a single piece of data like a number or string but a whole new dictionary.
If the value in column 1 of your array is not already a Key in the 'dict' dictionary then add it as a Key and make the associated Item a new (empty) dictionary object.

dict(arr_Fruit(i, 1))(arr_Fruit(i, 2)) = 1

Put the value from column 2 of your array as a Key (with Item = 1) into the newly created dictionary that is associated with the Key of the item in column 1 of your array.

Using the array created from A2:C6 in post 1 you would end up with 'dict' being something like this

dict Key1: "India" dict Item1: A dictionary with one entry (Key: "Mango" Item: 1)
dict Key2: "Australia" dict Item2: A dictionary with one entry (Key: "Apple" Item: 1)
dict Key3: "England" dict Item3: A dictionary with two entries ([Key: "Banana" Item: 1],[Key: "Mango" Item: 1])

If dict.Exists(c.Value) Then c.Offset(, 1).Value = Join(dict(c.Value).Keys(), "/")

Example: If dict("England") exists, which it does, then one cell to the right put the string created by joining the keys from the England dictionary with "/" so the result would be
"Banana/Mango"
.. just as you see in cell G4

 

[Mallesh23]

Hi Peter,

Thank you so much for simplifying above code how it works., Understood it. Really useful ?


Thanks
mg

 

[Peter_SSs]

No problem.