I had no idea and didn't expect VBA to have Interface like full-fledged OOP languages (although isn't a complete match) and I am sure too few people are aware of it's existence in VBA. Paul's enthusiasm in exploring both vertically and horizontally has touched every possible segments of VBA. Having confidence on teachers credentials/abilities important for learners and Paul has earned that very successfully.
Hi, you're the ultimate-VBA Boss! I program in VBA since 1998, in Excel and AutoCAD. Till now I thought Im quite good at it. Now I have to change my mind ;) Thank you!
What? Interfaces in VBA? Man, you definitely are the Final Boss of this game called Excel VBA. Always a pleasure to watch your videos for an express refresher and to learn tiny brilliant things that make our code look more professional and our lives easier. I haven't watched the video yet, but I don't doubt you have delivered yet another masterclass.
I hope you enjoy this video on the little known subject of VBA Interfaces. Let me know in the comments if you think you could benefit from using Class Interfaces or if you find them a bit too complex. For a practical use of Class Interfaces see how I implemented custom functions here(ruclips.net/video/ZYxa-Q-nSX0/видео.html))
Lately I started to bring a more OOP approach to VBA even if I use it mainly for automations and it seems overkill most of the time. So this was well welcome, as I had no idea of this approach in VBA, thank you.
I first subscribed to your channel because I was coding in VBA at that time. Now, I'm developing a program in C# and couldn't find a better explanation of how to use class interfaces anywhere. So, although I'm not even using VBA, your video proved to be very useful to me. Thanks and congratulations.
Hi Master!, you are a CRACK!, As Charley wrote, this is the best class to understand the concept and how to implement the interfaces, this is going to be very usefull for me, thank you for your time and dedication!
i guess Im asking the wrong place but does someone know of a trick to log back into an instagram account? I stupidly forgot the account password. I would love any help you can offer me!
We implemented these interface to four readings from an electronic instrument and was fantastic. I created a class interface and read current, voltage dc and ac and frequency of the signal in real time from the created object. now i will correlate all these reading implementing a reading every second calling another time object from a generic class., thanks for the tips it was very helpful.
surprised, never knew that VBA can implement interfaces or classes methods/properties although i use classes in my practice. As it's spoken: live and learn forever. Thanks a lot. The best VBA channel I've ever met
Shortly after watching this video I was in need of a solution to create a class constructor with parameters, which is solved, amongst other things, with interfaces (as long as one wants a neat solution). It was then when a whole new world in VBA opened before my eyes. It was also thanks to my C# knowledge that I could understand most of the "new" concepts I had no idea existed in VBA. But here comes the best part: my familiarity with VBA has helped me better understand those concepts I only knew of in C#! In other words, I not only have better coding techniques at my disposal in VBA now but everything has fallen into place for C# too! Cool!
These longer videos which demonstrate coding entire projects are very helpful. I especially like the last one where you show how to use Enum for row/column locations and store/return multiple values in a function. Before, I was declaring public variables for all of those things. More of these types of videos and OOP concepts would be great! THANK YOU!
Having coded in other languages, like Python, JavaScript, and C#, I always thought VBA was a sort of toy language... That is, until I saw your stellar videos! Thank you so much for your detailed and easy-to-follow tutorials on game-changing language/API features. You've saved me more times at my job than I can count!
Thank you for this. This is one of the few videos of yours I’ve seen, where I’m going to have to watch it a few times over, in order to understand what’s going on. I am a self-taught VBA coder who knows nothing of other languages, so the concepts are difficult to grasp after one viewing. I have no doubt that this is very powerful stuff, but as a few of your other commenters have said, in my somewhat naive approach to Excel/VBA, I would have done this using standard subs, functions, select cases, etc. Will persevere with your very helpful videos, though! Thanks again.
Thanks Paul - I am not sure "complex" is the right word/description. It's just a different way of approaching the problems. For me, I tried recently to get into classes/collections/properties (hadn't gotten to interfaces yet!)....and after struggling for two solid weeks - even with part -time expert help - I went "back' to good ol' arrays, functions and subs, and knocked out my fairly complex application in a couple days. I am not really proud of that, as I wanted to up my game. I learned that 30+ years of writing crappy BASIC and VBA code just doesn't prepare one for this next step up, lol. Not giving up...but this a real-world account from one amateur coder.
Master level confirmed! Just a shame there is only one like I can give! Never had the need for an interface in VBA or I hacked my way around :-) It's clearly neater the way you showed!
It's going to take some experimenting to wrap my head around this. Thank you for the instruction! I hate ElseIf. I tend to use Select Case. It's a lot cleaner and easier to understand, plus they can be used in "reverse". For example: Select Case True Case intA = rngA 'do something Case intB > fnTemp(67.5) 'do something else Case rngC = "Excel Macro Mastery", "How to Use Class Interfaces in Excel VBA" 'do other stuff Case blnQ (or if you'd rather, Case blnQ = True) 'do stuff if Boolean variable is T Case Else 'oops! End Select In this example, it finds the first True statement in the order that you set. Using If/ElseIf would take a lot more work and be very hard to manage.
very nicely done. After first seeing these design patterns 15 years ago in Java, I am seeing them in VBA. Feels like seeing an old friend in a different country :)
I have been writing in VBA for many years and all that time I wanted to have this functionality in VBA as well as in other languages. But I thought that there were no interfaces in VBA. I have never seen them in any VBA code. Now I want to run through all of my alive projects and rewrite them all.
Thanks for more great content! The first video I ever saw from Paul was on the VBA forms and it was explained so masterfully that I immediately subscribed. This series on Classes is a great way to introduce the use of classes and the VBA community is blessed to have such a individual as Paul to provide insights.
Interfaces in VBA is something nobody thinks of and actually lot of VBA developers don't know about it as me. How do you discover all these things and hats off to your research and patience. Very very well done. Thank you and hope see more videos like these
Your work is very helpful, when I first see your videos, I think that is very interesting, but don't necessarily have a problem that needs the solution, several months later, the light bulb goes off, Oh...that is where this is useful.
Thank you. I've had VBA interfaces explained to me a couple of times before, but this was the first time I actually clicked for me. Thank you for sharing.
This is a pretty good breakdown. I think the major un-intuitive part of VBA interfaces is how they require the underscore naming convention to implement the class subs/functions. There's no good feedback in that compile error message that tells you why your "Sub Calculate" needs to become "Sub iInterest_Calculate". I see that error message and think, "I *do* have a Calculate subroutine in here! What's the problem?". One more thing that might improve the maintainability of this code would be swapping the If statement in your ClassFactory() subroutine for a Select...Case block and swapping the interestType strings for enumerables (e.g. "addedInterest", "bestInterest", etc.) if they can't change at run-time (docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/enum-statement). Even if what you call the type in the table's range changes (changing "A" to "AX", for example), you can still assign it to the same enumerable ("addedInterest") and not break any downstream functions & subs that expect a specific interestType.
OMG, I have no idea we can use Interface with vba ! It's awesome. Please continue to make video about complex topic. It is exactly why i look all your videos. And this one is by far, one of the most interesting i could see.
Hi Paul - Great video as always. I have used class modules in a number of my projects since watching your videos and becoming a member. I have been revisiting some of my older projects which are still in use and recoding and improving them with things I have learnt from your website and tutorials. I am sure this latest aspect of interfaces will find its way into my coding. Keep up the good work
Great explanation. I’m wondering if it is necessary to set the oInterest object to Nothing at the end of the class factory or does VBA automatically do this? Also before the code was copied to the class factory, what happens to the old oInterestA objects when a New one is created from the clsInterestA class? Do they lose scope and that area of memory then becomes reusable?
This is a great video Paul! Thank you so much! I am still learning the best project applications to use a class module for but every one of your videos helps me to further understand how class modules work and how best to use them.
Paul's videos and articles are always good. A great demonstration of how to simplify client-side code by isolates out the selection logic based off polymorphism.
Excellent video. Old-timers like me can’t help think this replaced a lot of complexity with another lot of complexity, but the train of thought of having a simple utility module with functions like “calculate” and “display”, has long left the station. Have pity on us dinosaurs.
Great demonstration of class interface. Its a little bit a pity that VBA has not the power as JAVA in class programming. Thanks a lot. It will be nice to get more of these Videos to optimize and integrate Web as Interface from VBA.
Hello and thank you! But if the class A must have a function of its own, we are still forced to declare it in the interface? or there is a trick? It bothers me to have a SpecificToA() function available in the implementation of B....
Thank you, Paul, the piece for interfaces in VBA was unexpected and is useful! I just wish you could fully get rid of 'if' statement via calling appropriate interface by name. But I afraid you need to reference a lib for that. Great, please keep shooting more videos for classes!
Glad it helped Carl. Check out my latest video - this section(ruclips.net/video/ZYxa-Q-nSX0/видео.html) on Custom Functions uses Class Interfaces to provide a practical solution.
Hi, thank you for this and all your amaizing videos/tutorials. In this example you say you'll keep it simple but it could be very complicated, and i asking me how much complicated could be. To print the result in the debug window show how the code work, but how could we implement it to use it in all day tasks? Do plant to bring some real exaple they could be used in all day life? thank you again
Great video. I am starting to understand class modules and the implements feature. I hope to implement in the application I am working on. Thanks for your video on these topics.
Thanks for the video. It is new and exciting but I have a question. Why I can't use more than one variable in the interface? It worked perfectly when I used 1 variable but when I tried to use 2 or more, it gives me an error "Expected: =". I can't understand why. Can you please explain?
Thanks for the awesome video(I have found only this video in youtube which talking about interface in VBA)....I was learning interface but i have a question why not define two function in a class module for InterestA and InterestB and use that..What's the advantage of interface over class module ?
As always a very instructive video from you. I would have benefited with a description of the development and why classes/interface were the best solution. I will definitely watch this again and have a think of when I would use it.
So glad about this video. Thanks a lot! Would you mind to make another one about events in VBA? Maybe the last missing piece in this vba collection of gems. :)
Thank you very much, Paul. I love every video that comes from you. As I not familiar with interfaces, Could you, please, make another video about using this interfaces in the real-world. Thanks before..
Hi Paul, i really appreciate the way you teach and explain the code, can you make a video how to import Text from a particular layer of AutoCAD using VBA, it would be great help to understand. i see there are lot of code made in LSP but i want to learn how to do this in VBA.
i understand that the video is trying to showcase the power of interfaces, but wouldn't it be much easier in this scenario to handle all those cases with a 'Select Case' statement? Thanks Paul, great content.
Thank Nicu. It's not showcasing the power but rather showing how to use them. A good use is in this video(at 11.35) where they are used to filter...ruclips.net/video/ZYxa-Q-nSX0/видео.html
Thank you so much, the timing is perfect for me. I'll be able to put this in practice today. Is this as close to polymorphism / inheritance (C++ nomenclature) that we can get with VBA?
I followed your video and really understood it but when trying it I got hung up before I started it. You have a file called shtData(data) and Sheet1(example) and I can't figure out how where to code you are showing is going. A wonderful video confused on where to put the various items.
Not wanting to clutter Classes Module with 1,000 interest types. Instantiate one oInterest class with interest type, it does all if calcs. Print function is not value-added. Debug.print oInterest." in VBA main. Do you have a useful elegant real-world example? I will think more also. BTW what I really want is Excel cell formula to give VBA Object property without a module wrapper function. Thx.
can you tell me please, why I need to learn Object oriented concept before or as a prerequisite to learn VBA? Can you give me an example from real life as an analogy so I can understand it better? Thank you
👍👍 great video. interfaces are little known because of most VBA programmers learned to do things related to the workbooks and worksheets in a straight forward way, pure VB6 devs used the object oriented aspect of VB in a more advanced programmers way. still C++ dev were a bit disappointed not having all the OO features they used to have. btw not sure if the overload of operators is possible in VBA?
I’d like to see a more complex example that better explains when you realistically benefit from using an interface - this example could have been done with a simpler function.
Thanks again for the great videos. I’m a bit confused as to the necessity of such a complicated procedure. The solution could have been provided with a simple if then formula in the cell. Or even an if then or select case method in the vba code. I’m sure you’re right and intentionally chose a simple example for the video, but I am not sure I follow. It seems like an overly complex answer to what could be much simpler. I accept that I am wrong as I am 1% of the Excel user that you are, I just can’t see it. Thanks.
My thoughts exactly. I use ms access vba much more than Excel, but i think they principles are the same. As i watch these videos, i constantly try to figure how the techniques could apply to my projects. Unfortunately, the light hasn't gone on yet. I understand the need for simple examples, but it would also be useful to see a real world example. I think Paul is so far beyond my skill level that it's hard for me to understand, but i am fascinated to watch and perhaps learn.
@@jacksonmacd I agree. Im sure there are valid reasons for using this approach, but given the simple example, and my lack of expertise, I dont see it. Sometimes I go for the easiest solution, but I also appreciate correct and formal programming practices.
Thanks for your feedback Michael. I used a simple example in the video for calculating the interest to keep the code as clear as possible. Obviously it doesn't require interfaces to solve this problem. The purpose of the video is to show how interfaces work in VBA. They are part of an object oriented approach to software. I hope to show a more practical use in an upcoming video. -Paul
Hi Paul.. thanks.. great stuff! Really like how the use of Class Interfaces tightens up the code. Thanks for sharing it. Always something new and interesting at Excel Macro Mastery. Thumbs up!!
Thanks for the video! Interfaces is such an exoteric thing for me lol. My sugestion for future videos: functional programming in VBA and its close relationship with sheet formulas that uses previous value of the previous formula that use the value of the previous formula and so on.
Amazing video. :) Can you think of a way to infer class type without using factories? That's the only non scalable part of the solution. Perhaps using reflections of some sort ?
Good morning, Paul. I wonder if you might be willing to do a video on how to leverage the ReverseArrayInPlace function to handle both 1D arrays (1 x n-col and n-row x 1) as well as 2d arrays? Thank you very much for all of your excellent videos.
Hi Paul, I can see how this works. But in one of your other classes you used a class that would work like a collection and extend it with sorting for instance. I still cannot grasp on how to initiate a set MyColl = new clsMyColl without having an actual constructor. Somehow some 'internal collection" needs to be created before I can do a Mycoll.Add. Thanks
If you declare it as a private New variable it will automatically be created. You can also create it using Set in the Initialize sub which is similar to a constructor.
General question on dynamic memory allocation in VBA: If you create new instances of the class module with every iteration of the loop, where and when is it freed up?
Question: each time thru the for/next excel is creating a new object - does excel clean up the memory of the previous version/s of the object without you having to?
When an object is not longer being referenced then VBA will automatically clean the memory. See excelmacromastery.com/vba-objects/#VBA_Objects_in_Memory.
@@Excelmacromastery Next question, why create more than one of the object? Create it once before the loop and reuse it in the loop - wouldn't that be more efficient?
I had no idea and didn't expect VBA to have Interface like full-fledged OOP languages (although isn't a complete match) and I am sure too few people are aware of it's existence in VBA. Paul's enthusiasm in exploring both vertically and horizontally has touched every possible segments of VBA. Having confidence on teachers credentials/abilities important for learners and Paul has earned that very successfully.
Thanks Munim
Hi,
you're the ultimate-VBA Boss!
I program in VBA since 1998, in Excel and AutoCAD. Till now I thought Im quite good at it. Now I have to change my mind ;)
Thank you!
What? Interfaces in VBA? Man, you definitely are the Final Boss of this game called Excel VBA. Always a pleasure to watch your videos for an express refresher and to learn tiny brilliant things that make our code look more professional and our lives easier.
I haven't watched the video yet, but I don't doubt you have delivered yet another masterclass.
lol! First time I've been called the final boss.
I hope you enjoy this video on the little known subject of VBA Interfaces. Let me know in the comments if you think you could benefit from using Class Interfaces or if you find them a bit too complex.
For a practical use of Class Interfaces see how I implemented custom functions here(ruclips.net/video/ZYxa-Q-nSX0/видео.html))
Class objects are not too often used... But sure enough it comes handy at times
You are the best 👍
Lately I started to bring a more OOP approach to VBA even if I use it mainly for automations and it seems overkill most of the time. So this was well welcome, as I had no idea of this approach in VBA, thank you.
I first subscribed to your channel because I was coding in VBA at that time. Now, I'm developing a program in C# and couldn't find a better explanation of how to use class interfaces anywhere. So, although I'm not even using VBA, your video proved to be very useful to me. Thanks and congratulations.
Hi Master!, you are a CRACK!, As Charley wrote, this is the best class to understand the concept and how to implement the interfaces, this is going to be very usefull for me, thank you for your time and dedication!
Fantastic! Never expected that VBA actually offers interface class. This kind of material differentiates you from all other RUclips Excel tutorials.
Glad you like it.
i guess Im asking the wrong place but does someone know of a trick to log back into an instagram account?
I stupidly forgot the account password. I would love any help you can offer me!
This is by far the most complex video of yours... I need to go through it again and again
if you learn a about java,this woild be much easy to understand.
@@simonliang6867 i am learning c# and i remember there is interface word there.... Yet to learn it
We implemented these interface to four readings from an electronic instrument and was fantastic. I created a class interface and read current, voltage dc and ac and frequency of the signal in real time from the created object. now i will correlate all these reading implementing a reading every second calling another time object from a generic class., thanks for the tips it was very helpful.
surprised, never knew that VBA can implement interfaces or classes methods/properties although i use classes in my practice. As it's spoken: live and learn forever. Thanks a lot. The best VBA channel I've ever met
Great to hear!
Shortly after watching this video I was in need of a solution to create a class constructor with parameters, which is solved, amongst other things, with interfaces (as long as one wants a neat solution). It was then when a whole new world in VBA opened before my eyes. It was also thanks to my C# knowledge that I could understand most of the "new" concepts I had no idea existed in VBA. But here comes the best part: my familiarity with VBA has helped me better understand those concepts I only knew of in C#! In other words, I not only have better coding techniques at my disposal in VBA now but everything has fallen into place for C# too! Cool!
That's awesome Omar. Thanks for the feedback.
These longer videos which demonstrate coding entire projects are very helpful. I especially like the last one where you show how to use Enum for row/column locations and store/return multiple values in a function. Before, I was declaring public variables for all of those things. More of these types of videos and OOP concepts would be great! THANK YOU!
Thanks David.
Having coded in other languages, like Python, JavaScript, and C#, I always thought VBA was a sort of toy language...
That is, until I saw your stellar videos!
Thank you so much for your detailed and easy-to-follow tutorials on game-changing language/API features.
You've saved me more times at my job than I can count!
You're welcome Rahul.
I’m going to have to watch this more than once. 🤯🤣
Yes, it goes by pretty quickly at times. 🚀 But it does show me what I need to study in order to get up to speed. Thanks again, Sir Paul!
Finally I found someone who’s using OOP in VBA. After several tutorials on VBA I thought that there’s no OOP in VBA at all.
I never understood interfaces until you showed this example. Thank you!
Thank you for this. This is one of the few videos of yours I’ve seen, where I’m going to have to watch it a few times over, in order to understand what’s going on. I am a self-taught VBA coder who knows nothing of other languages, so the concepts are difficult to grasp after one viewing. I have no doubt that this is very powerful stuff, but as a few of your other commenters have said, in my somewhat naive approach to Excel/VBA, I would have done this using standard subs, functions, select cases, etc. Will persevere with your very helpful videos, though! Thanks again.
Glad you like it Ian. I deliberately kept the example simple so as to focus on Interfaces.
Thanks Paul - I am not sure "complex" is the right word/description. It's just a different way of approaching the problems. For me, I tried recently to get into classes/collections/properties (hadn't gotten to interfaces yet!)....and after struggling for two solid weeks - even with part -time expert help - I went "back' to good ol' arrays, functions and subs, and knocked out my fairly complex application in a couple days. I am not really proud of that, as I wanted to up my game. I learned that 30+ years of writing crappy BASIC and VBA code just doesn't prepare one for this next step up, lol. Not giving up...but this a real-world account from one amateur coder.
Thanks for the feedback. Using classes is definitely a step up from procedural coding and does take time to understand.
Sounds like me!
Master level confirmed! Just a shame there is only one like I can give!
Never had the need for an interface in VBA or I hacked my way around :-) It's clearly neater the way you showed!
Thanks Archibald.
It was not too complicated,🙂 it was perfect!!
It's going to take some experimenting to wrap my head around this. Thank you for the instruction!
I hate ElseIf. I tend to use Select Case. It's a lot cleaner and easier to understand, plus they can be used in "reverse".
For example:
Select Case True
Case intA = rngA
'do something
Case intB > fnTemp(67.5)
'do something else
Case rngC = "Excel Macro Mastery", "How to Use Class Interfaces in Excel VBA"
'do other stuff
Case blnQ (or if you'd rather, Case blnQ = True)
'do stuff if Boolean variable is T
Case Else
'oops!
End Select
In this example, it finds the first True statement in the order that you set. Using If/ElseIf would take a lot more work and be very hard to manage.
very nicely done. After first seeing these design patterns 15 years ago in Java, I am seeing them in VBA. Feels like seeing an old friend in a different country :)
I have been writing in VBA for many years and all that time I wanted to have this functionality in VBA as well as in other languages. But I thought that there were no interfaces in VBA. I have never seen them in any VBA code. Now I want to run through all of my alive projects and rewrite them all.
Thanks for more great content! The first video I ever saw from Paul was on the VBA forms and it was explained so masterfully that I immediately subscribed. This series on Classes is a great way to introduce the use of classes and the VBA community is blessed to have such a individual as Paul to provide insights.
Interfaces in VBA is something nobody thinks of and actually lot of VBA developers don't know about it as me. How do you discover all these things and hats off to your research and patience. Very very well done. Thank you and hope see more videos like these
Thanks Shiva
This does take awhile to swallow. Also it offers a huge range of possibility. But the implementation requires massive restructuring. I love this.
Your work is very helpful, when I first see your videos, I think that is very interesting, but don't necessarily have a problem that needs the solution, several months later, the light bulb goes off, Oh...that is where this is useful.
Thank you. I've had VBA interfaces explained to me a couple of times before, but this was the first time I actually clicked for me. Thank you for sharing.
You're welcome
This is a pretty good breakdown. I think the major un-intuitive part of VBA interfaces is how they require the underscore naming convention to implement the class subs/functions. There's no good feedback in that compile error message that tells you why your "Sub Calculate" needs to become "Sub iInterest_Calculate". I see that error message and think, "I *do* have a Calculate subroutine in here! What's the problem?".
One more thing that might improve the maintainability of this code would be swapping the If statement in your ClassFactory() subroutine for a Select...Case block and swapping the interestType strings for enumerables (e.g. "addedInterest", "bestInterest", etc.) if they can't change at run-time (docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/enum-statement). Even if what you call the type in the table's range changes (changing "A" to "AX", for example), you can still assign it to the same enumerable ("addedInterest") and not break any downstream functions & subs that expect a specific interestType.
This is a type of video i think i will benefit from if i watch it a couple of more times. Thank you!
OMG, I have no idea we can use Interface with vba ! It's awesome. Please continue to make video about complex topic. It is exactly why i look all your videos. And this one is by far, one of the most interesting i could see.
I don’t understand for what create clsAccount?
Just for information?
Programming on vba for 4 years
Your video very useful for me! Thank you Paul
Hi Paul - Great video as always. I have used class modules in a number of my projects since watching your videos and becoming a member. I have been revisiting some of my older projects which are still in use and recoding and improving them with things I have learnt from your website and tutorials. I am sure this latest aspect of interfaces will find its way into my coding. Keep up the good work
Thanks Phil.
Great explanation.
I’m wondering if it is necessary to set the oInterest object to Nothing at the end of the class factory or does VBA automatically do this?
Also before the code was copied to the class factory, what happens to the old oInterestA objects when a New one is created from the clsInterestA class? Do they lose scope and that area of memory then becomes reusable?
This is a great video Paul! Thank you so much! I am still learning the best project applications to use a class module for but every one of your videos helps me to further understand how class modules work and how best to use them.
Thanks Eric - Glad it was helpful!
Holly crap! I just leveled up today!
Paul's videos and articles are always good. A great demonstration of how to simplify client-side code by isolates out the selection logic based off polymorphism.
Excellent video. Old-timers like me can’t help think this replaced a lot of complexity with another lot of complexity, but the train of thought of having a simple utility module with functions like “calculate” and “display”, has long left the station. Have pity on us dinosaurs.
Glad you enjoyed it Albert!
I found this very useful as I’ve just started to use class modules, the class factory technique helped me develop a current project thank you
Very useful and interesting, thank you Paul
Great demonstration of class interface. Its a little bit a pity that VBA has not the power as JAVA in class programming. Thanks a lot. It will be nice to get more of these Videos to optimize and integrate Web as Interface from VBA.
I'm stuck on interfaces. I am very glad to see a video on this topic
Glad it was helpful!
Fascinating stuff, I love how you compress the code and the logic.
Thank you so much! It help me a lot to know more about Interface in VBA.
You're welcome.
Hello and thank you!
But if the class A must have a function of its own, we are still forced to declare it in the interface? or there is a trick?
It bothers me to have a SpecificToA() function available in the implementation of B....
Thank you, Paul, the piece for interfaces in VBA was unexpected and is useful! I just wish you could fully get rid of 'if' statement via calling appropriate interface by name. But I afraid you need to reference a lib for that. Great, please keep shooting more videos for classes!
No way around it unfortunately.
brilliant thank you very much. For me interfaces are the most complicated construct so far in VBA and there's not much to find about it on the web.
Amazing video as always. Next step please more with POLYMORPHISM, ENCAPSULATION AND INHERITANCE. 🤗
Finally something with a bit more substance...
Brilliant! I code in Microsoft Access VBA but the principles are the same. Really struggled with Interfaces before seeing this video!
Glad it helped Carl. Check out my latest video - this section(ruclips.net/video/ZYxa-Q-nSX0/видео.html) on Custom Functions uses Class Interfaces to provide a practical solution.
Love you videos, clear and informative. Thanks.
Very good topic. May i know the scenario i can use interfaces? Thank you Paul as always!
Hi, thank you for this and all your amaizing videos/tutorials. In this example you say you'll keep it simple but it could be very complicated, and i asking me how much complicated could be.
To print the result in the debug window show how the code work, but how could we implement it to use it in all day tasks? Do plant to bring some real exaple they could be used in all day life? thank you again
is it necesary the Implements statement?
Great video. I am starting to understand class modules and the implements feature. I hope to implement in the application I am working on. Thanks for your video on these topics.
You're welcome
Thanks for the video. It is new and exciting but I have a question. Why I can't use more than one variable in the interface?
It worked perfectly when I used 1 variable but when I tried to use 2 or more, it gives me an error "Expected: =". I can't understand why. Can you please explain?
So is there a abstract class also in vba??
The video is very nice and I am learning from it.
This is incredibly useful. Thanks very much.
You're very welcome!
i have a question cant we just do these things with udf why we need class
and cant we use sometimes arrays instead of class ?
thanks in advance
Have you done work with address parsing, or ordering street info that isn't parsed into street num street direction and street name?
Thanks for the awesome video(I have found only this video in youtube which talking about interface in VBA)....I was learning interface but i have a question why not define two function in a class module for InterestA and InterestB and use that..What's the advantage of interface over class module ?
thanks,how about use mvc logic to code VBA？
Thank you, Paul. Continue like this advanced subjects.
As always a very instructive video from you. I would have benefited with a description of the development and why classes/interface were the best solution. I will definitely watch this again and have a think of when I would use it.
They weren't necessarily the best solution for this problem. This video was to show how they work in VBA.
So glad about this video. Thanks a lot! Would you mind to make another one about events in VBA? Maybe the last missing piece in this vba collection of gems. :)
Thank you very much, Paul. I love every video that comes from you. As I not familiar with interfaces, Could you, please, make another video about using this interfaces in the real-world. Thanks before..
Thanks Budi.
¡Chapeau! Or if you prefer:"Me quito el sombrero"...
Thanks you Paul, it's like you know what my next question is, then you answer it and so much more 🤝👏👏
Hi Paul, i really appreciate the way you teach and explain the code, can you make a video how to import Text from a particular layer of AutoCAD using VBA, it would be great help to understand. i see there are lot of code made in LSP but i want to learn how to do this in VBA.
Great tutorial ! However, when I click on the suggested link for the code, I only get a blank page. Any thoughts ?
Try turning off any popup blockers that are running in your browser.
Excellent work!
Many thanks!
Awesome content!
Is it possible to implement more then one interface in the Class?
Yes.
i understand that the video is trying to showcase the power of interfaces, but wouldn't it be much easier in this scenario to handle all those cases with a 'Select Case' statement? Thanks Paul, great content.
Thank Nicu. It's not showcasing the power but rather showing how to use them.
A good use is in this video(at 11.35) where they are used to filter...ruclips.net/video/ZYxa-Q-nSX0/видео.html
Thank you so much, the timing is perfect for me. I'll be able to put this in practice today. Is this as close to polymorphism / inheritance (C++ nomenclature) that we can get with VBA?
Thanks. Yes - that's as close as we can get.
I followed your video and really understood it but when trying it I got hung up before I started it. You have a file called shtData(data) and Sheet1(example) and I can't figure out how where to code you are showing is going. A wonderful video confused on where to put the various items.
You're a beautiful man. Thank you so very much.
Thank you!
Not wanting to clutter Classes Module with 1,000 interest types. Instantiate one oInterest class with interest type, it does all if calcs. Print function is not value-added. Debug.print oInterest." in VBA main. Do you have a useful elegant real-world example? I will think more also. BTW what I really want is Excel cell formula to give VBA Object property without a module wrapper function. Thx.
If you need to implement more interfaces, would you need to write "Syv iInterest_iTheOther_Calculate( ... ) ?
No. You would use either iInterest or iTheOther depending on which sub your were implementing.
can you tell me please, why I need to learn Object oriented concept before or as a prerequisite to learn VBA? Can you give me an example from real life as an analogy so I can understand it better? Thank you
You don't need to learn OO first. I would actually learn VBA first and then how to use OO concepts.
👍👍 great video. interfaces are little known because of most VBA programmers learned to do things related to the workbooks and worksheets in a straight forward way, pure VB6 devs used the object oriented aspect of VB in a more advanced programmers way. still C++ dev were a bit disappointed not having all the OO features they used to have. btw not sure if the overload of operators is possible in VBA?
Great video. Thank you.
I’d like to see a more complex example that better explains when you realistically benefit from using an interface - this example could have been done with a simpler function.
Very useful Paul!
Thanks Nathan.
Thanks again for the great videos. I’m a bit confused as to the necessity of such a complicated procedure. The solution could have been provided with a simple if then formula in the cell. Or even an if then or select case method in the vba code. I’m sure you’re right and intentionally chose a simple example for the video, but I am not sure I follow. It seems like an overly complex answer to what could be much simpler. I accept that I am wrong as I am 1% of the Excel user that you are, I just can’t see it. Thanks.
My thoughts exactly. I use ms access vba much more than Excel, but i think they principles are the same. As i watch these videos, i constantly try to figure how the techniques could apply to my projects. Unfortunately, the light hasn't gone on yet. I understand the need for simple examples, but it would also be useful to see a real world example.
I think Paul is so far beyond my skill level that it's hard for me to understand, but i am fascinated to watch and perhaps learn.
@@jacksonmacd I agree. Im sure there are valid reasons for using this approach, but given the simple example, and my lack of expertise, I dont see it. Sometimes I go for the easiest solution, but I also appreciate correct and formal programming practices.
Thanks for your feedback Michael.
I used a simple example in the video for calculating the interest to keep the code as clear as possible. Obviously it doesn't require interfaces to solve this problem.
The purpose of the video is to show how interfaces work in VBA. They are part of an object oriented approach to software. I hope to show a more practical use in an upcoming video.
-Paul
Hi Paul.. thanks.. great stuff! Really like how the use of Class Interfaces tightens up the code. Thanks for sharing it. Always something new and interesting at Excel Macro Mastery. Thumbs up!!
Thanks Wayne. Glad you like it.
Very helpful. Thanks for the lesson.
You're very welcome!
Thanks for the video! Interfaces is such an exoteric thing for me lol.
My sugestion for future videos: functional programming in VBA and its close relationship with sheet formulas that uses previous value of the previous formula that use the value of the previous formula and so on.
thank you about this details
Thanks for sharing this useful videos
Amazing video. :)
Can you think of a way to infer class type without using factories? That's the only non scalable part of the solution. Perhaps using reflections of some sort ?
Unfortunately there is no reflection in VBA.
Good morning, Paul. I wonder if you might be willing to do a video on how to leverage the ReverseArrayInPlace function to handle both 1D arrays (1 x n-col and n-row x 1) as well as 2d arrays? Thank you very much for all of your excellent videos.
Hi Paul,
I can see how this works. But in one of your other classes you used a class that would work like a collection and extend it with sorting for instance.
I still cannot grasp on how to initiate a set MyColl = new clsMyColl without having an actual constructor. Somehow some 'internal collection" needs to be created before I can do a Mycoll.Add.
Thanks
If you declare it as a private New variable it will automatically be created. You can also create it using Set in the Initialize sub which is similar to a constructor.
🤩 excellent
Which font do you use in the VBE? Is that 'Consolas'?
Yes
General question on dynamic memory allocation in VBA: If you create new instances of the class module with every iteration of the loop, where and when is it freed up?
Memory is freed when the object is not longer referenced by any variable. See bit.ly/2YQ6Xde.
Another great video 👍🏻
I am going to have to watch all your videos about classes again because I unfortunately don’t get it.
Quick question, if can you use interface, is it a way that we could have a class Inherits anotherClass ? (Inheritance (Derived and Base Class))
No. Interfaces are all you can do.
Question: each time thru the for/next excel is creating a new object - does excel clean up the memory of the previous version/s of the object without you having to?
When an object is not longer being referenced then VBA will automatically clean the memory. See excelmacromastery.com/vba-objects/#VBA_Objects_in_Memory.
@@Excelmacromastery Thank you, that is quite a comprehensive covering of the subject!
@@Excelmacromastery Next question, why create more than one of the object? Create it once before the loop and reuse it in the loop - wouldn't that be more efficient?
I think I'm finally starting to understand this....famous last words.
Glad you like it Philip:-)