Excel VBA Dictionary: How to use the Dictionary (1/4)

👉 Ready to master VBA?
(Note: If the download page doesn't work then make sure to turn off any popup blockers)

In this video you will see how to create and use a Dictionary in Excel VBA.

The Dictionary is an alternative data structure to the Collection and Array. The difference is that it contains a key and value which allow us to easily update values.

Table of Contents:
00:13 What is a Dictionary
01:12 How to add the Dictionary to VBA
01:47 How to add items to the Dictionary
02:57 How to update the value at an existing key
05:19 How to add to an existing value at a key

Shortcut Keys:
Alt + F11: Switch between Excel and the VBA Editor.
Ctrl + R: View the Project Properties Window.
Ctrl + Shift + 8(or Ctrl + *): Get the current region on a worksheet.
F5: Run the code from the current sub.
F9(or click left margin): Add a breakpoint to pause the code.
Tab: To move lines of code to the right(Indent)
Shift + Tab: To move lines of code to the left(Outdent).

[Excelmacromastery]

Hi everyone. Please enjoy the first video about VBA Dictionary. If you have any comments, please add them below.

[turkaykoc]

Your did a very good job, thank you so much for the clear explanations.

[VBAbyMBA]

So simple but have so many possibilities.
Thanks ☺️

[ryanquinn]

Best excel advice and vids.
Perfectly solves a problem trying to count duplicates of a list where I don’t want to sort the list, it has to process in order.
I have a SKU name I pull from JSON data and a file i end up downloading where the download is named SKU name for first then SKU name-1 for the second SKU name -2 so on.
This allows me to add the SKU as the key, value is duplicateCount starting at 0.
If the SKU name hasn’t shown up yet add it to dictionary, Otherwise increment up duplicateCount

If duplicateCount is 0 then keep SKU name as SKU, otherwise SKU-duplicateCount

[trinhthanhlinh]

Your explanation is trule comprehensive. Thank you Paul!

[UpExcel]

Very well explained...and I can see there will be plenty of uses so looking forward to the rest of the series

[franciscoaugustovarelaagui]

muchas gracias por la clase! Muy claro todo!!

[gilenomascarenhas]

Oh, man... this is pure gold. Thank you, sir.

[MyAudioBookCompilation]

Wow you have a different approach of using and explaining Dictionary. I have been using this for quite sometime but explaining Item like a Value makes it more understandable. I want to learn how to use API and i though your guidance will bring me there. Thank you so much.

[buithitrami]

Thanks Paul! It has been 2 weeks since your last video. Looking forward to more videos soon 😊

[rakeshkumarmadella]

Thank u so much Paul.. I am getting your emails every day.. your channel is very useful .. very nice wonderful great video.. I really loved it sir

[sakhilengwenya]

Please do an example on mapping data using dictionary and arrays

[vikuedutech]

Really wonderful and i have made a real dictionary in excel userform ..

[thearchibaldtuttle]

Nice, never used the watch window this way!

[mrbubetube]

I think the Peter Norton look went out with the 1980s dude

[dangelorrrr]

Hi Paul - you need to edit your above video description...
"In this Excel VBA video, we see how to use Class modules objects with Collections. One of the problems with collections is that it can only store one item from a row of data.

So how to we store data from multiple columns? We use class module objects.
"

[sebastienantunes]

Very useful video !
I was surprised by following the intellisense
dict.Add(“Apple”, 60)
does not work
but if I write
dict.Add “Apple”, 60
then it's ok
The intellisense misled me.
What is the difference between the 2 writings?
Thanks ...

[raulleopoldobini]

Hi! can you teach us how yo create a dictionary of collections of class collections?

[wattjock]

I am very grateful to your videos and am learning a lot.

I opened the Watch Window and added dict("Apple") to it as shown in video. The Code, the Watch Window, and the Immediate Window all work fine as I Step through (F8) the Code.

However; when I then add dict("Orange") to the Watch Window and step through with F8, I get the Error Message that "This Key is already associated with an element of this Collection".

But... if I use F5 to simply run the code, everything runs and the Immediate Window prints out all the correct Values.

What am I missing?

[andyalmanza]

Hi, great video. I ran into a problem I'm hoping you can help with. I added the Microsoft Scripting Runtime reference and created my dictionary variable the same way you did. However, the "Add" method is not available for my dictionary, why would that be?