Use this Proven Method to Create Any Class Module
HTML-код
- Опубликовано: 22 дек 2021
- 👉 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: bit.ly/3J8Zzyf
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
Create any Class Module Using This Simple Method
In this video, I'm going to show you a simple method to create any Class Module. This will save you a lot of wasted time and frustration as it will help you create the class you want.
#ClassModule #VBAClassModule
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) 
Наука
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. 👏
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!
Clearly explained. Thanks!
Paul, just great. Thanks very much.
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 Paul, really useful and practical.
Thank you Paul, Very informative
As usual you have explained it quite brilliantly, however, could you please mention a few examples of its real world implementation or use cases
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.
Fantastic idea. Great Job. Keep it up.
Thanks Paul
Your videos r excellent lam learning a lot from u in VBA Thank u.
very less video on vba class module ! yours are best Video ! , thanks for making it !
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.
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?
Great work, very good explanation. It helped me lot in implementation of small projects. Thanks🙏🙏❤❤
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.
Pure gold
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.
Thanks!Paul
You're welcome
Super 👍, 🎉, good morning 🌄
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 😥.
How we can use collectionExt class when we want for example show the item by the number of key like: debug.print coll(1) because now it not work.
Can anybody help me? What use as an accountant do I have of working with modules?
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 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.
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.
The more i learn python oop, the more it makes sense to me for class module in vba. Strange, aint it.?
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?
Didn't go far with the tutorial. I am a novice in VBA. What is you suggestion on where to start from please.
You rush too much next time take your time