The scripting dictionary is a way to store unique items via a key and item (Keys and Items are terms in the dictionary. It is a fantastic tool to store data based on a unique key. It is powerful in that it the keys can be used to store and consolidate data.
In this post I will take you though trapping a range inside the dictionary and referencing it back into Excel. So we will store a range 10 rows long, then output only the unique items in the range.
The following video takes you through this article.
Addressing a Range
Take the following range of data. We will use the scripting dictionary to push the data into the .ITEMS and then use a VBA array to extract the unique column of our choice.
We may wish to output the category or the payment. It depends how we reference our coding.
Setting up the dictionary is rather straight forward - you can create a scripting dictionary object directly without referencing the Microsoft Scripting Runtime. The following line is enough to create a dictionary reference.
With CreateObject("Scripting.Dictionary")
The other method is to reference the Microsoft Scripting Runtime and this is done in the settings in VBA.
However the above is not necessary for this procedure to work. It runs without the reference.
Running the Code
The following is the VBA code to generate unique data from a range of data. It will take the data from the current region of an array and it will summarise the data into a unique list output to a range of your choosing.
Option Explicit
Sub ScriptA()
Dim i As Long
Dim ar As Variant
ar = [A1].CurrentRegion
ar = Array(.keys, .items)
[G1].Resize(.Count, 1) = Application.Transpose(ar)
What is Happening
The range is pushed into an array, called (ar), the array stores all of the data. A simple FOR LOOP then iterates through the data inside the array.
The following line is where the rubber hits the road. Everything before this line is set up, while the .Item line allows the array (ar) to be referenced and the unique data put into the dictionary.
.Item(ar(i, 1)) = .Item(ar(i, 1))
When the loop is closed after this line the job is done. Now all you have to do is extract the data from the dictionary to where ever you want in your workbook.
I used the following line to push the data back into the Array:
ar = Array(.keys, .items)
However, you could just use:
ar = Array(.keys)
As only one column is being referenced.
Finally the range where the data is outputted is resized to the same size a the array which holds the dictionary.
[G1].Resize(.Count, 1) = Application.Transpose(ar)
Where I pushed the data to cell G1 and resized the range from that starting point.
The above is the output from the dictionary.
Changing the Code
If you wanted to change the reference point so a different column was output the following line could be changed.
.Item(ar(i, 2)) = .Item(ar(i, 2))
I shifted the column being analysed from column 1 to column 2. This will create a unique list of items from the payment methods and the following is the output.
The next two parts in the series - Unlocking the Scripting Dictionary are at the following link.
Summing with the Scripting Dictionary
Hope you have enjoyed the article and the video. The following Excel file goes with this tutorial and the video above.
