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 ...
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????
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.
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.
@@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!
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
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.
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.
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.
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.
@@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.
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.
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.
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?
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.
@@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.
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)?
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 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
@@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(th-cam.com/video/-sj0vvYT-Mk/w-d-xo.html) on objects for more in.
@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 ?
@@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.
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...
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.
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
@@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.
@@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
@@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
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.
Let me know of your struggles(or successes) with Class Modules in the comments below
succes for sure :) 20 years of vba help a lot ;)
Words fail to adequately describe Mr. Kelly's mastery. Simply unique.
Thanks Faan
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 ...
Thanks Hans
Super-clear, thanks. I wish all internet tutorials were this well-organized!
You have made this subject... So easy to follow. Absolutely FANTASTIC. THANK YOU SO MUCH!!!!
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!
Glad you liked it.
El mejor video sobre módulos de clase. Gracias
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.
Sounds good John.
This video definitely is on a higher level. I'll have to view it several times. 🤔 But I will get there.☑
thank you, i found this video very useful, now i feel the urge to learn how to write elegant code
Tanks Paul.
My first class module will have the method:
.SendThanksToPaul
(and I will call it inside a loop 😄)
Ha Ha. I like it!
Very good presentation of class module
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????
I believe this is purely based on Windows version @Alter Channel
Paul is using Windows 11, I believe....
Need to upgrade then 😅 thanks
@@benlindford You're right. I haven't make any change to VBA or Excel.
Great introduction, love to see new videos from you.
Thanks
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.
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.
@@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!
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
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.
Great video!
Glad you like it.
Very good, thanks
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.
Thanks Mr. Paul, Please , we want to apply it through more professional applications, so that the understanding is better.
You're welcome. I may do another example project soon. Here is a previous one th-cam.com/video/vUg-NH-Wqss/w-d-xo.html
@@Excelmacromastery Yes, this is great, we need more applictions, so that the understanding is better.
Good job
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.
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.
@@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.
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.
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.
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?
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.
@@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.
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)?
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 😁
In several places you say 'copy the code' when it should be 'move' or 'cut and paste' ,it confused me for a moment.
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.
Not really. Creating a class requires slightly more code than standard VBA. But a well designed object will save you a ton of code.
@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
@@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(th-cam.com/video/-sj0vvYT-Mk/w-d-xo.html) on objects for more in.
@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 ?
@@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.
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...
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.
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
@@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.
@@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
@@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
So if i understand well, it could be "easy" to create a class module that replicate the behaviour of the dictionary and the collection.
Relatively easy depending on what you wish to do.
Class module í like logarithm invention by the british that take human reason to the 3 dimensiin level
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.
But if you don't use it then why keep it?
please mr can I contact you by email