Class Modules in VBA: Made Super Simple

Рет қаралды 29,940

Күн бұрын

Class Modules in VBA: Made Super Simple
Class Modules are the biggest stumbling block for users trying to move to the next level in VBA. Many people struggle to grasp what they are for, when to use them and how to use them in their own code. In this video I am going to show you with real-world examples, How any one can use Class Modules in their code.
#ExcelVBA #VBAClassModules #ClassModules
SUBSCRIBE TO THE CHANNEL: bit.ly/36hpTCY
DOWNLOAD THE SOURCE CODE FOR THIS VIDEO: bit.ly/3UVeZMR
Related Training
The Excel VBA Handbook Course(TheExcelVBAHandbook.com)
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)
Table of Contents:
00:00 - Introduction
01:00 - Bank Account example
02:58 - Issues with Standard VBA code
03:05 - Using a Class Module for Account
10:49 - The Elements of a Class Module
13:37 - How to use Set and New
14:45 - Guidlines for creating Class Modules
15:43 - Collection Example

Пікірлер: 69

@Excelmacromastery Жыл бұрын

Let me know of your struggles(or successes) with Class Modules in the comments below

@BenjaminHouot Жыл бұрын

succes for sure :) 20 years of vba help a lot ;)

@karibeauchamp4358 15 сағат бұрын

You have made this subject... So easy to follow. Absolutely FANTASTIC. THANK YOU SO MUCH!!!!

@faanmuller4569 Жыл бұрын

Words fail to adequately describe Mr. Kelly's mastery. Simply unique.

@Excelmacromastery Жыл бұрын

Thanks Faan

@crawdad4823 10 ай бұрын

Super-clear, thanks. I wish all internet tutorials were this well-organized!

@serdip Жыл бұрын

Another excellent lecture! This video came along just at the time when I wanted to review the fundamentals of class modules in VBA. Thank you!

@Excelmacromastery Жыл бұрын

Glad you liked it.

Ай бұрын

thank you, i found this video very useful, now i feel the urge to learn how to write elegant code

@hanshallebeek8161 Жыл бұрын

Inspiring as always. You really cleared the sky with this tutotrial, after 30+ years of VBA programming Classes still hold (now held) a secret for me and I just counldn't grasp the essentials. Thanks Paul "IT" Always crosses your path ...

@Excelmacromastery Жыл бұрын

Thanks Hans

@johnbutler2750 Жыл бұрын

Still struggling to fully “get” class’s but wrote one to help me manipulate txt files and it was very useful. Hoping I find another use.

@Excelmacromastery Жыл бұрын

Sounds good John.

@kmslogic Жыл бұрын

Great introduction, love to see new videos from you.

@Excelmacromastery Жыл бұрын

Thanks

@BenjaminHouot Жыл бұрын

Very good presentation of class module

@houstonvanhoy7767 Жыл бұрын

This video definitely is on a higher level. I'll have to view it several times. 🤔 But I will get there.☑

@josealvesferreira1683 Жыл бұрын

Very good, thanks

@m_marcamo Жыл бұрын

Tanks Paul. My first class module will have the method: .SendThanksToPaul (and I will call it inside a loop 😄)

@Excelmacromastery Жыл бұрын

Ha Ha. I like it!

@Pedritox0953 Жыл бұрын

Great video!

@Excelmacromastery Жыл бұрын

Glad you like it.

@LilaBdrKarki-kb1mn Жыл бұрын

You have given a good explanation of class in vba through this example. Thank you sir. I need vba codes to replace an excel file stored in google drive with new version of the same file from my PC. Please guide me.

@serdip Жыл бұрын

Another advantage of using Property procedures is that we can include validation code. I think that in its current state, the clsAccount class accepts negative values in the Credit() and Debit() methods, which should not be acceptable, I believe. Additionally, it seems that the Property Let Debit() procedure should not complete the transaction if the specified amount exceeds m_Balance. Next level design would be, in my opinion, to create an interface IAccount for example, that has the properties and methods common to all kinds of accounts. Then separate classes for checking, savings, personal, commercial account types could be created, each one implementing IAccount, whose methods are exposed to the calling code but the internal implementation details, of which such as interest calculations, overdraft fees etc. would be specific to each class. Next next level (is that a thing? LOL) would be to include RaiseEvent WarnCustomer() in the class modules that could be invoked by, say Credit() when m_Balance exceeds $250,000 (the FDIC insurance limit, I think), or by Debit() when the remaining m_Balance falls below a threshold, which could be different depending on the type of account. Thanks again for another very clear, concise, and eminently practical lecture on VBA development techniques.

@Excelmacromastery Жыл бұрын

You made very good points and that is a good overview of how an accounts application would develop. One thing that is interesting about creating class modules is that the objects you create depend on the requirements rather than the real world item. For example, you often see OO examples with cars. We all know and agree on what a car is in real life. But if you created applications for car rental, car building, car fixing etc. then it is likely that car would be a different type of object in each. This is what often causes confusion.

@serdip Жыл бұрын

@@Excelmacromastery Thanks very much. You have made an important observation. In summary, the level and type of abstraction applied to a class object is a function of the requirements of the application. You pointed out that the "car" object can be implemented in dramatically different ways according to the application using it. Many classes (pardon the pun) on OOP that I have taken seem to suggest that objects (or the classes from which they are derived, more properly) model real world entities, which is as a practical matter rarely the case. Sure, the classes may be named after real world objects but their implementation will almost never be fully capable of emulating a concrete, real world object. This is to be expected, since most applications are designed to provide a specific set of limited functionalities provided in the requirements. We abstract a limited set of properties and methods of the object in order to satisfy the project requirements. Even within the limitations of VBA, extensible and robust object models can be constructed to provide rich functionality on par with some fully object oriented languages. Thanks once again for another awesome presentation!

@celestinosaraiva107 Жыл бұрын

I Believe the goal here was to keep it aSap (as Simple as possible) if possible ;). Of course further developments can be done in order to get it close to reality but the point here is Classes, not Accounting

@rmdfra 9 ай бұрын

Hello. You said that by writing 'Set p = New clsPoint' it is possible to define in runtime how many objects are to be created in a for loop. What that loop would look like? Would an array of type clsPoint be needed? Your content is great, thanks.

@alexalexeenko2472 6 ай бұрын

Good job

@hammeedabdo.82 Жыл бұрын

Thanks Mr. Paul, Please , we want to apply it through more professional applications, so that the understanding is better.

@Excelmacromastery Жыл бұрын

You're welcome. I may do another example project soon. Here is a previous one kzfaq.info/get/bejne/rLuXYLF5kr3Vo6c.html

@hammeedabdo.82 Жыл бұрын

@@Excelmacromastery Yes, this is great, we need more applictions, so that the understanding is better.

@alterchannel2501 Жыл бұрын

Fantastic information. Thank you. I have a question: how did you make the edges of your msgboxes rounded??? VBA gives me only sharp old style edges????

@benlindford Жыл бұрын

I believe this is purely based on Windows version @Alter Channel Paul is using Windows 11, I believe....

@alterchannel2501 Жыл бұрын

Need to upgrade then 😅 thanks

@Excelmacromastery Жыл бұрын

@@benlindford You're right. I haven't make any change to VBA or Excel.

@derekpowles7960 Жыл бұрын

In several places you say 'copy the code' when it should be 'move' or 'cut and paste' ,it confused me for a moment.

@YvesAustin Жыл бұрын

Thank you for a very complete video. As an intermediate vba programmer, and having watched several tutorials over class modules (and you have a few!), I am even more confused now than before. I am still at the very beginning, meaning I really do not understand the value of class modules and what problems they are trying to solve. I would create a public function to calculate balance after debit and credit (which is what you also offer as an option). I am not sure I fully understand the concept. Is it simply to get a naming convention that pleases the eye? You mention three issues with a standard procedure. The first (messy input) is solved by initializing the class module sub which appears to be the same as passing the variable through a public function (or?), in the second you mention there are no connection between the variables and the subs, I am really confused about this statement. Last, if you allow a user to add code to your code, then the code is not protected and that is not good practice. I really really want to learn about these class modules. I am a still at a loss as to their true value. Pls accept my apologies. This is by no means a criticism to your work, which is fantastic, rather a statement from an average coder trying to get better. Thank you again.

@Excelmacromastery Жыл бұрын

The purpose of class modules is to group data(variables) and their related subs/functions together. The idea is that the code is similar to a collection of block that can be connected to each other. The opposite of this is having independent subs and functions that can all call each other. Check out how the workbook, worksheet, ranges objects work to get a better understanding.

@free3690 Жыл бұрын

I've used class modules before, but eventually switched back to regular modules due to the difficulties I faced during the debugging process. Whenever there was a problem with a function within a class module, the debugger would jump to the first line, making it quite challenging to pinpoint and resolve the issue.

@Excelmacromastery Жыл бұрын

Hi Karl, You can used Tools->Options then General Tab and then select "Break in Class Modules" and it will stop on the line in the class module. I know this is not obvious at all and took me a while to figure it out too.

@free3690 Жыл бұрын

@@Excelmacromastery Thanks so much for sharing that tip with me! I honestly had no idea that such an option existed. I'll keep this in mind and give class modules another shot the next time I'm working on a project. Thanks again for your thoughtful comments. Please keep uploading these amazing videos.

@rogerh2694 9 ай бұрын

6:55 I believe you said "methods"? I didn't know Scottish people would pronouce it that way 🤔 Had to put on subtitles to figure this one out 😁

@joeguirg Жыл бұрын

Hey Paul, Thanks for the video. Very basic question around minute 3: the balance variable is being passed to two subs CreditAccount and DebitAccount. I don't understand how the value of the balance variable is maintained outside of the subs? In other words when the line Call CreditAccount(balance, 100) is executed, the variable balance is updated inside the sub and once the sub is exited then I thought the balance variable disappears. In order for the balance variable to be passed outside the sub, wouldn't this need to be a function instead of a sub? Can you help me clear up this confusion?

@Excelmacromastery Жыл бұрын

Because I didn't specify ByVal in the parameter declaration it means that it is ByRef by default . ByRef parameters mean that you are using the variable as outside the sub - you are passing a reference to it.

@joeguirg Жыл бұрын

@@Excelmacromastery I've heard you say in other content that it's not best practice to pass in variables ByRef. Is that correct and if so would utilizing functions instead of subs be a better approach? Thank you.

@joeguirg Жыл бұрын

Separate but related question, when should one prepend the call to a sub/function with Call versus not prepending? In other words what's the difference between: 1) Call CreditAccount(balance, 100) and 2) CreditAccount(balance, 100)?

@nadermounir8228 Жыл бұрын

Thank you for your video. I think class modules make the code more complicated because it requires a lengthier code to write. In other words, creating private variable makes it difficult. Why can't we just use public subs ? What i don't get at all is why we have to to dim a variable as new collection for instance then set it to be equal a new collection. It is very hard to comprehend.

@Excelmacromastery Жыл бұрын

Not really. Creating a class requires slightly more code than standard VBA. But a well designed object will save you a ton of code.

@nadermounir8228 Жыл бұрын

@Excelmacromastery what is the benefit of using private variables in a class? Can we keep it public ? I am going to subscribe to your VBA courses to learn more

@Excelmacromastery Жыл бұрын

@@nadermounir8228 It hides them from outside the class. This means that they cannot be accidentally changed and it makes the object cleaner as the caller doesn't see them. BTW _"why we have to to dim a variable as new collection for instance then set it to be equal a new collection"_ It is actually not necessary to use new twice. You use "Dim New" if you are creating the object once and "Set New" if you plan to create multiple objects of the type - usually this is done in a for loop. See this video(kzfaq.info/get/bejne/Y9maY9mnvrqRfZ8.html) on objects for more in.

@nadermounir8228 Жыл бұрын

@Excelmacromastery Thank you for the explanation. Which course do u recommend me to take? I see you have 2 courses in VBA and I am confused which one to start with ?

@Excelmacromastery Жыл бұрын

@@nadermounir8228 The Excel VBA Handbook Course(theexcelvbahandbook.com/) teaches how to build VBA applications from scratch. It requires some knowledge of VBA. VBA Fundamentals(excelmacromastery.com/vba-beginners-course) is a beginners course in VBA with exercises and assignments.

@jrcryo Жыл бұрын

So if i understand well, it could be "easy" to create a class module that replicate the behaviour of the dictionary and the collection.

@Excelmacromastery Жыл бұрын

Relatively easy depending on what you wish to do.

@schymi841 Жыл бұрын

Unfortunately VBA is very limited when it comes to OOP concepts. That is why it's not used in big projects that require advanced class structure. For me the big disadvantage is that you cannot pass parameters to the constructor. This may seem like a small issue but it can actually cause huge problems with data integrity as you cannot force the user to properly initialize an object with some required attributes...

@Excelmacromastery Жыл бұрын

I agree about the constructor. The way VBA that objects work in VBA is that you create the object and then set the appropriate properties. I prefer the constructor params. Despite the limitations of Class modules compared to other languages you can still get a lot of benefits from them and create nicely designed code.

@7Denial7 Жыл бұрын

I have come up with a simple solution, which bypasses this constructor limitation. All you need is make your class a default instance and make a "create" default function in your class. If you are interested I can share the code. It's simple and works just like a constructor. So the only way you Can create a New instance is this create method

@michaelkrailo5725 8 ай бұрын

@@7Denial7 Are you saying the create function is creating a single instance class and due to the function, it has the parameters? Then what about multiple instances? one of the main features of classes are the collection of instances of the class.

@7Denial7 8 ай бұрын

@@michaelkrailo5725 you will be able to create multiple instances. What I'm saying is 1. You create your class, 2. you set the attributes for your class which enable for this class to have a default instance, so you can refer to this default instance simply by the name of your class 3. You write the code for the "create" function inside of your class. This function receives all the parameters you intend your constructor to have, and returns a New instance of your class. As you can see, the return type of this function is the type of your class. So the default member of the class spawns New instances. 4. You May want to set the "create" function as a default member of the class. If so, set the nesessary attributes for this function. So, as a result, lets say you named your class "MyClass" and did all the aforementioned actions. The code for creating a New instance will be as follows: Dim Obj as MyClass Set Obj = MyClass(#parameters of the create function#) Or Set Obj = MyClass.Create(#parameters of the create function#) As you see, the newing up of New instances is encapsulated inside the create function

@7Denial7 8 ай бұрын

@@michaelkrailo5725 and If you want the parameters to be written into the hidden members of your class, the code of the "create" function should be something like that: Public function Create(param1.. ParamN) as MyClass Set Create = MyClass HiddenMember1 = param1 ................ HiddenMemberN = paramN Set MyClass = nothing End function

@derekpowles7960 Жыл бұрын

when you add the empty code below Private Sub Class_Initialize() End Sub Private Sub Class_Terminate() End Sub On compile/run, the compiler removes this code. A simple blind comment in each procedure will keep the code.

@Excelmacromastery Жыл бұрын

But if you don't use it then why keep it?