vba: get unique values from array: Explanation

Question

I am having difficulties in trying to understand the following code. I found it here in the following link: vba: get unique values from array

Dim d As Object
    Set d = CreateObject("Scripting.Dictionary")
    'Set d = New Scripting.Dictionary


Dim i As Long
For i = LBound(myArray) To UBound(myArray)
    d(myArray(i)) = 1
Next i

Dim v As Variant
For Each v In d.Keys()
    'd.Keys() is a Variant array of the unique values in myArray.
    'v will iterate through each of them.
Next v

EDIT: I quite understand the whole code but I could not understand the last section being

Dim v As Variant
For Each v In d.Keys()
    'd.Keys() is a Variant array of the unique values in myArray.
    'v will iterate through each of them.
Next v

and I cannot understand this line

 d(myArray(i)) = 1

EDIT: I am looking for a code that executes the same objective but I would rather want to understand fully what I am applying than just copy and paste something that I do not understand.

More specifically, there are several elements in the macro that I am constructing and this one is among the ones I am looking for.

As first objective I need to find the duplicates in my dataset
After finding the duplicates I need to count how duplicates there are if there are 4 duplicates row my item is complete,
Then, if there are more than 4 duplicates, I need to transfer the duplicates to another sheet and store them there
On the other hand, if there are less than 4 duplicates, I would need to find such items and try to match these items with an inventory to try to find out the remaining elements in order to stack a full set of 4 elements.

This macro will serve me to fulfill point 4. Once I identify the items that have less than 4, I need to find such items and try to match them with the ones that I have in another sheet. I need to do so with the code above as I would rather use an array in a macro than functions in excel itself in order to avoid confusion to the user with phantom figures.

What are you trying to get this code to do? And what is your specific question? — SmrtGrunt, Apr 26 '19 at 16:13

score 2 · Accepted Answer · answered Apr 26 '19 at 16:19

Section by Section:

Dim d As Object
Set d = CreateObject("Scripting.Dictionary")
'Set d = New Scripting.Dictionary

This creates the dictionary object

Dim i As Long
For i = LBound(myArray) To UBound(myArray)
    d(myArray(i)) = 1
Next i

With dictionary objects, there are two ways to add a new item:

d.Add Key, Value

This method causes an error if the dictionary already has that key
d(Key) = Value

This method is technically a reassignment for the value of the specified key. If the key doesn't exist, it gets added implicitly. If the key already exists, the value just gets updated.

So what the code is doing, is it cycles through each value in the array and:

Reassigns the current index as the dictionary value with the current dictionary key (the array's value)
If the key doesn't already exist, create it.

This results in a dictionary with one key for every unique value. Any duplicate values would have just been reassigned the value for the existing key, instead of creating a new key.

Dim v As Variant
For Each v In d.Keys()
    'd.Keys() is a Variant array of the unique values in myArray.
    'v will iterate through each of them.
Next v

Elaboration: d.Keys returns all of the keys in the dictionary in the form of an array. Since the keys are the unique values from myArray, d.Keys is an array on the unique values from myArray.

vba: get unique values from array: Explanation

1 Answers1