How to Use Nested Dictionaries in VBA

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

How to Use Nested Dictionaries in VBA

#ExcelVBALoops #VBADoUntil #VBADoWhile

Mentioned in the video:

Useful VBA Shortcut Keys
========================

Debugging:
Compile the code: Alt + D + L OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)

Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R

Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)

[Excelmacromastery]

Let me know in the comments about your experience with nested dictionaries

[Nell]

I was just trying to explain to a co-worker how to do this. You're a mind reader. Sending them this video for sure.

[hadibq]

Thanks Paul! that's an awesome tutorial 👍👍

[ousmanetall]

Vba is as powerful as the other great languages. That's it I have been answered to my question. Thanks a lot Paul, you're the best teacher of vba !

[karenjoy]

A few years ago, when I was teaching myself how to use nested dictionaries, I sure wish I had seen this video! Thanks for your clear explanations.

[RvoA]

Very well explained and easy to follow.

In the past I have used nested dictionaries mostly when working with data in JSON format from external sources.

[sukhdevsinghchauhan]

❤you create crisp to the point and wonderful content

Thanks

[OlympusMons-gdyu]

Thank you for this video, especially for the connection to JSON. I have found a typo: In the last slide the values in the New York dictionary should have been different from the values in the San Francisco dictionary.

[bogdanexit]

Yes. I learned from the Json converter, nested N dictionaries with nested N collections

[BrucePinnock]

Thank you for your continued education on VBA. I just want to share one way I sometimes use Dictionaries and would like to get your feedback. Let's say I want to store a main variable that has 3 pieces of related data. For example, Student name could be the main variable then related data could be class name, teacher's name and student's age. I would add this to a dictionary as dict.add "class name*teacher's name*student's age", "Student name". When retrieving from the dictionary I would then use the Split function to split the key, using the "*", into an array. I have never really tested this against a VBA Class so I am not sure how efficient it is memory-wise but, it has worked well for me. Would love to hear your thoughts

[kukuhwahyurinaldi]

Always had excellent explanation 👍, thank U Paul

[Chris-wbpz]

At 3:36 you show three levels of dictionaries. What would the syntax look like to update the values in the lowest dictionary (Street, City, State, Zip)?

[davidlean]

Nice, Especially the pictures alongside the code. You may find it beneficial to start with "the why". eg: We want a list of Students. For each Child, we need to store multiple Parental Contacts. Some parents have multiple fields; ie: 3 phone numbers.
So much easier to do linked lists & binary structures in C's indirect pointers.

[joaocustodio]

Paul, thanks for another great video.

[hammeedabdo.]

Mr. Paul, can you give us some other sources, channels, or even blogs that you recommend to us in the field of VBA?

[ThomasEschenmoser]

I have used nested dictionaries as explained in this video. I wanted to replace a pivot-table with nested dictionaries because I hoped this would be faster. It turns out that the pivot-table is as fast as the dictionaries. Since the pivot-table requires less vbs code, I decided to stay with pivot-tables. I have the impression that it is less effort to use pivot-tables instead of nested dictionaries.

[aNDy-qhem]

Hello Paul, I use nested dictionaries on a regular basis so it would be nice to know the way you advice how to efficiently filter and sort the parent dict by the field of the subDictionary object.

[beu]

Thanks a lot for this video!! Just tried the same stuff with a collection and it works the same way.
Nevertheless it's still not the same as the nested - built in - collections in Excel.
E.g.
I still can't find a way to create my data structure in that way... Any hint, where to look?

[m_marcamo]

I can only say: thank you!
Well, I can also add thank you!

[BenLinfordUK]

Are these more advanced topics covered in your Excel VBA Handbook course or any of the supplemental content that you offer when you run your webinar bonuses alongside it Paul?