How to Use Nested Dictionaries in VBA
HTML-код
- Опубликовано: 8 ноя 2023
- 👉 Ready to master VBA?
- Check out my full courses: courses.excelmacromastery.com/
- Subscribe to the channel here: bit.ly/36hpTCY
-Want to download the source code for this video? Go here: shorturl.at/qCFIP
(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:
Using Web APIs video: • Use Excel VBA to Read ...
Dictionary Playlist: • Excel VBA Dictionary
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) 
Наука
Let me know in the comments about your experience with nested dictionaries
Thanks Paul! that's an awesome tutorial 👍👍
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.
Thank you for an inspiration, Paul!
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 !
Thanks
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.
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.
The best way to understand them is to play around with your own examples. Use a pen and paper if it helps.
@@Excelmacromastery using a pen and paper can definitely help, or even visualising things in a worksheet has helped me also in the past.
E.g. to understand the incrementation of counter variables throughout a loop (or loop within a loop moreso) and how that maps out visually against a structure (worksheet cell representation of a dictionary or array for example).
Thank you Mr. Paul!
Yes. I learned from the Json converter, nested N dictionaries with nested N collections .....
Always had excellent explanation 👍 , thank U Paul
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.
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
Useful information ❤
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.
Paul, thanks for another great video.
You're welcome.
I can only say: thank you!
Well, I can also add thank you!
Thanks.
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.
Mr. Paul, can you give us some other sources, channels, or even blogs that you recommend to us in the field of VBA?
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)?
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?
Hi Ben, I haven't covered Nest dictionaries anywhere else. The Excel VBA Handbook course focused on teaching how to build Excel VBA and the best practices around that. It also covers a lot different areas of VBA but doesn't go deep into advanced topics.
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.
Hi, I'm newbie and have a question: is it efficient to work with many dictionaries like 100 or more? What if I have 1000 rows where each one should have a dictionary? Worth it? Thanks
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. Worksheets("SheetName").Range("A1")....
I still can't find a way to create my data structure in that way... Any hint, where to look?
Using class modules. The worksheet object contains a range object.
Personal feedback: I really enjoy your videos, you discuss what I consider advanced concepts. However everytime you use "Apple", "Orange" and "Pear" I get lost and can't understand the concept. It would be more useful if you attempt to demonstrate it working in a real scenario. Unless I own a fruit store I'll not use Apples, Orange and Pear. In this video you did it a little and was easier for me, despite the fact that the key from the first level was really generic, no one would use "USA" as key for costumer. But I could see past that and understand. I generally understand better the concept of dictonaries in python but I have a block with it in VBA.
Agreed. I have trouble when abstract examples are used. I don't know if it's just me but I need things like "customer's", "accounts", "balances", transactions" to help understand the usefulness of something. Maybe I'm on some kind of spectrum, but my brain gets in a loop and stuck on, "I'm trying to do what with apples?" 😄
I understand what you point out, but the idea of using fruits is to get you to fully understand the concept. If you understand the mechanic of the dictionary, you can use it with apples, pesrs, customers and balances. But if you dont understand how to use it with fruits, you are copying what was said and nothing more
What happens if you issue the speadsheet to someone who doesn't have the "Microsoft Scripting runtime" checked in their setup? Does the code fail or do odd things? If it needs to be ticked how can you check for the problem and provide a set of instructions for a very new user to go in and set the tick in place?
It can fail so it's better to use late binding when distributing to users. Using a reference is early binding. See this video ruclips.net/video/W0FlKKpZXgw/видео.htmlsi=8sBiEkUKB9on6Xow
We must have some kind of telepathic connection. 😄 I'm currently working on a project where I could use this.
Last week I've been experimenting, creating multiple dictionaries from a table. I created dictionaries that look something like this;
Labor Operation Description Dictionary: Table Index {Key}, Labor Operation Description {Value}
Labor Operation Additional Notes Dictionary: Table Index {Key}, Labor Operation Additional Notes {Value}
Labor Operation Time To Complete Dictionary: Table Index {Key}, Labor Operation Time to Complete {Value}
I debated on this method over creating a collection, using the ".exists" method. After watching this video I can see where nested dictionaries would work, however to me it seems readability suffers. Am I wrong?
If you are going through this effort a collection seems much easier to follow. Why would you use nested dictionaries over a collection?