Use this Proven Method to Create Any Class Module

Рет қаралды 26,403

2 жыл бұрын

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
SUBSCRIBE TO THE CHANNEL: bit.ly/36hpTCY
DOWNLOAD THE SOURCE CODE FOR THIS VIDEO: bit.ly/3J8Zzyf
Related Training
The Excel VBA Handbook Course(TheExcelVBAHandbook.com)
Webinar Archives - 60+ Hours of VBA training(excelmacromastery.com/excel-v...)
Free Excel VBA Resources
Excel VBA Articles (excelmacromastery.com/vba-art...)
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)

Пікірлер: 46

@Excelmacromastery 2 жыл бұрын

I hope you enjoy the video. Let me in the comments if this method is something you will use...

@wayneedmondson1065 2 жыл бұрын

Thanks Paul! Very helpful in understanding more about Class Modules. Thanks for sharing. Thumbs up!!

@BenLinfordUK 10 ай бұрын

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. 👏

@peterlilley6819 2 жыл бұрын

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!

@maciejolejnik6706 2 жыл бұрын

Clearly explained. Thanks!

@edgarsantarosa9847 2 жыл бұрын

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!

@Excelmacromastery 2 жыл бұрын

Thanks Edgar and Merry Christmas

@joaocustodio2094 2 жыл бұрын

Paul, just great. Thanks very much.

@elsheep6951 2 жыл бұрын

Thanks Paul, really useful and practical.

@kissisvarmas 2 жыл бұрын

Thank you Paul, Very informative

@jimfitch 2 жыл бұрын

Thank you, Paul. Great tutorial! I’m still building comfort in writing Class Modules. Your tutorial have inspired confidence.

@Excelmacromastery 2 жыл бұрын

Thanks Jim. Glad you liked it.

@smiqpk 2 жыл бұрын

As usual you have explained it quite brilliantly, however, could you please mention a few examples of its real world implementation or use cases

@7Denial7 2 жыл бұрын

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.

@Excelmacromastery 2 жыл бұрын

That's great to hear Artem. Thanks for sharing.

@aNDy-qh1em 2 жыл бұрын

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.

@monochromaticspider 2 жыл бұрын

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.

@prakashsrinivasan7840 2 жыл бұрын

Your videos r excellent lam learning a lot from u in VBA Thank u.

@exceltech1076 Жыл бұрын

Fantastic idea. Great Job. Keep it up.

@societyuser6659 2 жыл бұрын

Great work, very good explanation. It helped me lot in implementation of small projects. Thanks🙏🙏❤❤

@tomharrington1453 2 жыл бұрын

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?

@xf9639 2 жыл бұрын

Thanks Paul

@sinus2220 2 жыл бұрын

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?

@Excelmacromastery 2 жыл бұрын

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.

@aNDy-qh1em 2 жыл бұрын

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.

@Excelmacromastery 2 жыл бұрын

Contains is only for strings. Further modification is needed for other types.

@MrMallesh1 2 жыл бұрын

very less video on vba class module ! yours are best Video ! , thanks for making it !

@schymi841 2 жыл бұрын

Pure gold

@balakumar.n4891 2 жыл бұрын

Thanks!Paul

@Excelmacromastery 2 жыл бұрын

You're welcome

@Fab2Mc 2 жыл бұрын

Super 👍, 🎉, good morning 🌄

@bayurukmanajati1224 2 жыл бұрын

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 😥.

@davidunger7425 2 жыл бұрын

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.

@Excelmacromastery 2 жыл бұрын

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.

@davidunger7425 2 жыл бұрын

@@Excelmacromastery Thanks for the explanation Paul.

@khalidalisawi8037 2 жыл бұрын

thanks for your video, I learned a lot. my excel doesn't accept the collectionext I tried a lot. my excel version is 2010

@Excelmacromastery 2 жыл бұрын

Collection Ext is the name of the class module.

@henrikijonkoping4694 2 жыл бұрын

Can anybody help me? What use as an accountant do I have of working with modules?

@Tesla72PL Жыл бұрын

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.

@fnscooter 2 жыл бұрын

Would vbBinaryCompare allow you to store integers or doubles, or is further modification required to make collectionExt work for things other than strings?

@Excelmacromastery 2 жыл бұрын

It needs to be modified further for other types.

@noviceprogrammer2011 2 жыл бұрын

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?