Revisiting your class videos and I have to say, that the syntax and inner workings is finally starting to click. My praise and thanks as always Paul. 👏
Class modules made my scraping through football websites easier, because I get the full data and then I print it out with the changes I wanted and even faster because I use array to range print. Thanks Paul for the hint in your videos, Merry Xmas to you all!
Thanks for the amazing videos Paul. I really like this approach, for those interested, I like to see the Items tree nested under the CollectionExt Class instance in the Expression window e.g. a tree node that breaks open showing Item 1, Item 2, etc. To achieve this I changed the following statement at the top of the CollectionExt class from "Private m_coll as New Collection" to "Public Items as New Collection". This is particularly helpful when assigning objects to properties!
Thank you Paul for your excellent lessons! Thanks to you I came to finally understand how classes work. And I created a fully animated dynamic Form for my co workers to generate different documents out of source data base. I used classes to add and remove dynamically different animation properties to objects on the Form!!! And it looks so great that I Cant believe I made it! By the way, consider bringing up Raise Events and event creation in your future lessons please.
Paul, may I just also propose an alternative to iteration in 'Contains' method that would search item by key. Perhaps this should be faster for big collections. In this case missing item key will return an error to be handled to return 'false' and 'true' if found.
This is generally how you want to work with collections in VBA, but it does come with the requirement that items are added to the collection with a key and that no two identical items can be added. This is mostly reasonable, as iterating collections is slow, but there could be times when you want to just pile things into your collection.
Very cool clip! Two questions: 1) Isn't boolean false by default? Why do you use Contains = False? 2) This one is more complex: Let's say you need to create an array of ranges (each range is single row / multiple columns). I've used two methods: for i = 1 to 5 Set MyArr(i) = Range("A" & i & ":C" & i) Set MyArr(i) = Columns("A:C").Rows(i) Next i Now if I want to print just the first and third cell from any given row, *only* the array defined using first method allows me to print them in this manner (e.g. 4th row): debug.print MyArr(4)(1), MyArr(4)(3). Array created using the second method returns entire 4th row (3 columns) when I print MyArr(4)(1). Why is this so?
It is the default but I state it explicitly so that it is clear what Contains is. It is not obvious to everyone that the default is False especially if you use different languages.
Thank you, Paul! What is a nice extention for collection! Just, as far as 'Contains' method is concerned - your exampke works fine with string, in case the type of collection items is not string but other type like instance of class X. In such case we might need to pass the name of ID field of class X to the compare method.
Love the video. I like the additional collection features. I might add a function to return the position of an item in the collection. Forgive me for quibbling-- I know its OK to bend the rules sometimes-- but doesn't the "exit for" command inside your loop violate the "always exit a loop, procedure, function at the bottom" rule?
Thanks Paul for another excellent video, very clear explanations. Just for my own clarification I'd like to raise a minor point. In the class function "Contains", the first line sets Contains = FALSE. Isn't this line redundant, as Contains is automatically FALSE on entry, and only gets changed to True if a match is found? Just asking, thank you.
Yes. A function/variable will be automatically set to False on creation. However, it is better to show it explicitly in your code as it avoids confusion.
Oh, I see now. I remember have a class that gone wrong at some line in its private procedure. The error is 'subscript out of range'. But instead teleporting me to the error line, the 'Debug' buttons just teleporting me to the initial caller (set myClass = newClass). I confused and decided to add many breakpoints inside the class to kno where the error happens 😥.
Would vbBinaryCompare allow you to store integers or doubles, or is further modification required to make collectionExt work for things other than strings?
Something looks strange in your code but somehow it works! These two lines are in the class collectionExt: Public Function Clone() As collectionExt Dim newColl As New collectionExt How come it's not recursive?
I hope you enjoy the video. Let me in the comments if this method is something you will use...
Thanks Paul! Very helpful in understanding more about Class Modules. Thanks for sharing. Thumbs up!!
Revisiting your class videos and I have to say, that the syntax and inner workings is finally starting to click.
My praise and thanks as always Paul. 👏
Class modules made my scraping through football websites easier, because I get the full data and then I print it out with the changes I wanted and even faster because I use array to range print. Thanks Paul for the hint in your videos, Merry Xmas to you all!
Thanks Edgar and Merry Christmas
Thanks for the amazing videos Paul. I really like this approach, for those interested, I like to see the Items tree nested under the CollectionExt Class instance in the Expression window e.g. a tree node that breaks open showing Item 1, Item 2, etc. To achieve this I changed the following statement at the top of the CollectionExt class from "Private m_coll as New Collection" to "Public Items as New Collection". This is particularly helpful when assigning objects to properties!
Fantastic idea. Great Job. Keep it up.
Thank you Paul for your excellent lessons! Thanks to you I came to finally understand how classes work. And I created a fully animated dynamic Form for my co workers to generate different documents out of source data base. I used classes to add and remove dynamically different animation properties to objects on the Form!!! And it looks so great that I Cant believe I made it! By the way, consider bringing up Raise Events and event creation in your future lessons please.
That's great to hear Artem. Thanks for sharing.
As usual you have explained it quite brilliantly, however, could you please mention a few examples of its real world implementation or use cases
Clearly explained. Thanks!
Paul, just great. Thanks very much.
Thank you, Paul. Great tutorial! I’m still building comfort in writing Class Modules. Your tutorial have inspired confidence.
Thanks Jim. Glad you liked it.
Thanks Paul, really useful and practical.
Your videos r excellent lam learning a lot from u in VBA Thank u.
Paul, may I just also propose an alternative to iteration in 'Contains' method that would search item by key. Perhaps this should be faster for big collections. In this case missing item key will return an error to be handled to return 'false' and 'true' if found.
This is generally how you want to work with collections in VBA, but it does come with the requirement that items are added to the collection with a key and that no two identical items can be added. This is mostly reasonable, as iterating collections is slow, but there could be times when you want to just pile things into your collection.
Great work, very good explanation. It helped me lot in implementation of small projects. Thanks🙏🙏❤❤
Very cool clip!
Two questions:
1) Isn't boolean false by default? Why do you use Contains = False?
2) This one is more complex:
Let's say you need to create an array of ranges (each range is single row / multiple columns).
I've used two methods:
for i = 1 to 5
Set MyArr(i) = Range("A" & i & ":C" & i)
Set MyArr(i) = Columns("A:C").Rows(i)
Next i
Now if I want to print just the first and third cell from any given row, *only* the array defined using first method allows me to print them in this manner (e.g. 4th row): debug.print MyArr(4)(1), MyArr(4)(3).
Array created using the second method returns entire 4th row (3 columns) when I print MyArr(4)(1). Why is this so?
It is the default but I state it explicitly so that it is clear what Contains is. It is not obvious to everyone that the default is False especially if you use different languages.
Thank you Paul, Very informative
Thank you, Paul! What is a nice extention for collection! Just, as far as 'Contains' method is concerned - your exampke works fine with string, in case the type of collection items is not string but other type like instance of class X. In such case we might need to pass the name of ID field of class X to the compare method.
Contains is only for strings. Further modification is needed for other types.
very less video on vba class module ! yours are best Video ! , thanks for making it !
Love the video. I like the additional collection features. I might add a function to return the position of an item in the collection. Forgive me for quibbling-- I know its OK to bend the rules sometimes-- but doesn't the "exit for" command inside your loop violate the "always exit a loop, procedure, function at the bottom" rule?
Thanks Paul
Thanks Paul for another excellent video, very clear explanations. Just for my own clarification I'd like to raise a minor point. In the class function "Contains", the first line sets Contains = FALSE. Isn't this line redundant, as Contains is automatically FALSE on entry, and only gets changed to True if a match is found? Just asking, thank you.
Yes. A function/variable will be automatically set to False on creation. However, it is better to show it explicitly in your code as it avoids confusion.
@@Excelmacromastery Thanks for the explanation Paul.
Super 👍, 🎉, good morning 🌄
Thanks!Paul
You're welcome
Pure gold
Could you please make a video on how to add references in VBA using coding?
Oh, I see now. I remember have a class that gone wrong at some line in its private procedure.
The error is 'subscript out of range'. But instead teleporting me to the error line, the 'Debug' buttons just teleporting me to the initial caller (set myClass = newClass). I confused and decided to add many breakpoints inside the class to kno where the error happens 😥.
Would vbBinaryCompare allow you to store integers or doubles, or is further modification required to make collectionExt work for things other than strings?
It needs to be modified further for other types.
thanks for your video, I learned a lot. my excel doesn't accept the collectionext I tried a lot. my excel version is 2010
Collection Ext is the name of the class module.
Something looks strange in your code but somehow it works!
These two lines are in the class collectionExt:
Public Function Clone() As collectionExt
Dim newColl As New collectionExt
How come it's not recursive?
Can anybody help me? What use as an accountant do I have of working with modules?
Didn't go far with the tutorial. I am a novice in VBA. What is you suggestion on where to start from please.
The more i learn python oop, the more it makes sense to me for class module in vba. Strange, aint it.?
You rush too much next time take your time