Excel VBA Introduction Part 39 - Dictionaries
HTML-код
- Опубликовано: 22 июл 2024
- If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!
You can buy our Introduction to Excel VBA book here www.lulu.com/shop/andrew-goul...
By Andrew Gould
www.wiseowl.co.uk - The Dictionary class is similar to the Collection class in that it allows you to store a custom collection of objects. This video teaches you how to use dictionaries, including how to reference the Scripting Runtime Object Library, how to create a new Dictionary and add, remove, count and reference the items in it. You'll also learn about the Items and Keys arrays contained within a Dictionary and how to loop over these arrays in order to process all of the items that the Dictionary contains.
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
Best videos because, planned, and edited. No wasting time showing errors or typos like the other utube tutorials or waiting to think out the proper procedure, very well developed.
Excelent teacher, I really admire your job and lessons.
Thank you!
I'm REALLY enjoying the learning VBA series, please keep up the GREAT work! Thank you.
To echo Kumar, it's been a great journey to learn VBA with you. I've watched all 39 videos up to this one. Thank you so much for this great resource.
An amazing video great explanation 👍
Thank you Nader!
Wow, great tutorials. I know a little bit about Visual Basic Programming (Wrote a few small programs for my job) But i always wondered about VBA, you took a subject, and made it so very easy to understood. You make learning seem easy. I thank you very much for your videos. Great job!!!!
This is an excellent series! I have been programming in VBA for some time and I have still learned a lot from this series. Thank you so much!
Great video. Simply amazing, as always.
Thanks, It solved my problem connected with selecting tabs based on colour. Without watching your tutorial I would have never thought that it's possibility of adding sheets in precise colour to directories and next using it in string arrays (in my excel file each colour is responsible for diffrent type of data so it's really usufull for me, now I have simple way of selecting sheets which store specific type of data) .
Hi Andrew, thanks for the tutorials!
Your teaching method is great, very clear and easy to understand (and I have no problem with your accent!)
It would be great if you could build further on this video by elaborating retrieval of data from dictionaries based upon multiple criteria, looping, etc. in the most effective and efficient way.
Love the instruction.
Thanks a lot..Videos are very helpful... Do you have any video on doing look up using dictionaries.
Hi Andrew
It has been an awesome journey with you to learn VBA. I am all time fan of your.
With specific to this last video where you explains use of Dictionary.
I had a problem with the dictionary.items(z).tilte property, it throws run time error 424 Object requried. Imagining that I have defined all the variables as per my code, i could not find solution of this.
Intersting stream
Thanks Henrik!
Hi Andrew,
I have searched everywhere on youtube but i cant find this anywhere. I have seen that class interface (implement) is really powerful.
Is there any chance you could do a video on
Class interface
Creating objects within objects
Working with windows Api's etc
That will complete the set on vba and i dont see any other examples like this
Hopefully you can
That would be awesome
Thanks
Inspiring
I have heard that the fastest lookup way is by using dictionary.If I got a couple ten thousand items and I need to lookup for 5 values of the code(at the same row),in this case,how I add all items from another workbook into dictionary and keep it updated,and do I have do build 5 dictionary for that?
Hey the tutorial is quite good but i had one question how do i store a dynamic array into dictionary ?
What level of expertise will one have after completing all your excel vba videos?
Im thinking introductory.
Do you know of any intermediate to advanced resources available online? They seem lacking.
Thank you btw.
I manage to loop out all the values out of my dictionary, but I can not retrieve the values by typing:
MyDictionary2 ("1234")
I have a similar myDictionary1 in another module that works prima nicely and that contains more advanced data.
I have tryed as a variable with different datatypes as string, integer, long or double. But the variable showes the same as the debug.print window, exept for the extra blankspace at the end but I even tryed to add that.
The key showes as 1234 in the debug.print window when looping over the key as a variant variable.
I do not get it.
It all worked so nice in myDictionary1 and not at all for myDictionary2 in the other module.
I go true the code with F8, and I can then see that the myDictionary2("1234") never works, it returns Empty.
One other observation, after running the Code once... I re-start the Code (F8) and I see that the Dictionary is still full of Items until the Set line runs, at which time it resets the new Dictionary.
Will the dictionary stay in memory until it a new instance of the Dictionary is run or it is Set to Nothing? Will it reside in Memory even after the Workbook is closed?
This depends on where you declare your variables! Variables retain their values/references until they go out of scope. If you declare a variable within a subroutine it goes out of scope when that subroutine ends. If you declare a variable outside a subroutine it retains its value/reference even when a subroutine which uses the variable ends. It will definitely lose its scope and values/references when you close the workbook however. There's a summary of scope in VBA here docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/understanding-scope-and-visibility
And a description of the lifetime of variables here docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/understanding-the-lifetime-of-variables
I hope that helps!
I am pretty well versed in Excel and use it extensively but... I have decided to learn VBA in Excel for a very specific and personal project and am just now getting started learning. I greatly appreciate RUclips Channels such as yours for the Tutorials! I am going to have many questions and appreciate any answers that are provided.
Any reason why you would write "Dim MyFilms as New Collection"? Wouldn't doing that render the "Set MyFilms = New Collection" line unnecessary?
Hi there, welcome to the channel! I'll do my best to keep up with the questions so feel free to keep asking. You're correct that "Dim MyFilms as New Collection" means that you don't have to explicitly create a new instance of the Collection class by writing "Set MyFilms = New Collection". If you delete the "Set MyFilms = New Collection" statement, any time you reference MyFilms, VBA will first check to see if the MyFilms variable references an instance of the Collection class and, if not, automatically creates one. There are a couple of reasons why using these auto-instancing variables may not be the best approach - Chip Pearson mentions it in the introduction to his article on Classes in VBA www.cpearson.com/excel/classes.aspx and there's a good discussion here in the original question and responses to it stackoverflow.com/questions/8489507/is-the-poor-performance-of-excel-vba-auto-instancing-a-myth
Hope that helps!
@@WiseOwlTutorials ~ thanks for the answer!
So basically, its not a really about a speed/performance issue (ms of differences) it is because if there is already an instance of MyFilms, the code would throw an error and not run. Is that the main gist of the two articles you cited?
@@wattjock2405 Almost, but not quite! If there's already an instance of the Collection class referenced by the MyFilms variable, the code would happily continue with the existing instance. If there isn't already an instance, then one would be created. Perhaps the main argument against using auto-instancing variables is that you don't have control over when the instance of the class is created. If that's something that's important to know in your procedure then avoid the auto-instancing variable (Dim x As New Class) and use separate Dim and Set statements.
Hope that's a bit clearer!
@@WiseOwlTutorials ~ while stepping through this Code (F8), and putting the FilmsDictionary in the Watch Window,
and using Dim as New line... Value reads until the Code's first run through the For Next Loop, at which point the first Key is added to the Dictionary.
Conversely, using the Set line, a new blank Dictionary immediately, prior to running the For Next Loop.
I think I understand now... thanks!
@@wattjock2405 Yes perfect, that's it!
how to deal with the data when sheet is added to a dictionary, sir
If you make a few more on Charts and Graphs, I will like all your videos and pay you a dollar.
sometimes I am having difficulty of listening and understanding of what was saying due to accent e.g beginning 16:36 sorry but no offense, but nonetheless the overall video was superb helpful to us
Thank you for this wonderful tutorial video
thank you, please never stop making video tutorials about vba's, one of your avid fan here
peace out :)
One's difficulty is another's joy. Grreat accent!!! I underrstood perrfectly!!! Thank you for the video, excellent explanation.