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.
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) .
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!!!!
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.
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.
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 RU-vid 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!
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.
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
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?
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!
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
