How to Use Class Interfaces in Excel VBA

[@mrashid229]

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.

[@Excelmacromastery]

Thanks Munim

[@aszarek]

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!

[@rdchenweb]

Fantastic! Never expected that VBA actually offers interface class. This kind of material differentiates you from all other KZbin Excel tutorials.

[@Excelmacromastery]

Glad you like it.

[@brunoeugene9293]

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!

[@oz7075]

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.

[@Excelmacromastery]

lol! First time I've been called the final boss.

[@davidlinesq]

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!

[@Excelmacromastery]

Thanks David.

[@rrrprogram8667]

This is by far the most complex video of yours... I need to go through it again and again

[@simonliang6867]

if you learn a about java,this woild be much easy to understand.

[@rrrprogram8667]

@@simonliang6867 i am learning c# and i remember there is interface word there.... Yet to learn it

[@philipmcdonnell7168]

I’m going to have to watch this more than once. 🤯🤣

[@houstonvanhoy7767]

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!

[@karenjoy511]

I never understood interfaces until you showed this example. Thank you!

[@cessromer7078]

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.

[@GanovAlex]

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

[@Excelmacromastery]

Great to hear!

[@rahuldevstech]

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!

[@Excelmacromastery]

You're welcome Rahul.

[@thearchibaldtuttle]

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!

[@Excelmacromastery]

Thanks Archibald.

[@phat80]

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.

[@oz7075]

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!

[@Excelmacromastery]

That's awesome Omar. Thanks for the feedback.

[@iankr]

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.

[@Excelmacromastery]

Glad you like it Ian. I deliberately kept the example simple so as to focus on Interfaces.

[@carybrown6919]

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.

[@nuwanhettiarachchi3023]

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 :)

[@shanewilliams7131]

It was not too complicated,🙂 it was perfect!!

[@Excelmacromastery]

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(kzbin.info/www/bejne/kIrbkmCHYtOGjpI))

[@rrrprogram8667]

Class objects are not too often used... But sure enough it comes handy at times

[@Ajtiger1984]

You are the best 👍

[@jxggxr_dxv]

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.

[@CharleyGR]

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.

[@gerardochaparro5265]

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!

[@peterswinoff1886]

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.

[@WTC_2U]

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.

[@Excelmacromastery]

Thanks for the feedback. Using classes is definitely a step up from procedural coding and does take time to understand.

[@jacksonmacd]

Sounds like me!

[@reevesjim]

This is a type of video i think i will benefit from if i watch it a couple of more times. Thank you!

[@TheSmileyCoder]

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.

[@Excelmacromastery]

You're welcome

[@BenjaminHouot]

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.

[@nguyenvu8262]

This does take awhile to swallow. Also it offers a huge range of possibility. But the implementation requires massive restructuring. I love this.

[@JDalmasca]

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.

[@hotflashfoto]

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.

[@genli3641]

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.

[@philhamilton6926]

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

[@Excelmacromastery]

Thanks Phil.

[@stevennye5075]

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.

[@EricHartwigExcelConsulting]

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.

[@Excelmacromastery]

Thanks Eric - Glad it was helpful!

[@shiva11112]

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

[@Excelmacromastery]

Thanks Shiva

[@mgreg009]

Holly crap! I just leveled up today!

[@cleverr7]

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

[@albertbatfinder5240]

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.

[@Excelmacromastery]

Glad you enjoyed it Albert!

[@paraglide01]

Fascinating stuff, I love how you compress the code and the logic.

[@cesarbravo1035]

Very useful and interesting, thank you Paul

[@gttrongvn]

Thank you so much! It help me a lot to know more about Interface in VBA.

[@Excelmacromastery]

You're welcome.

[@danielp1569]

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

[@Luth1us]

Amazing video as always. Next step please more with POLYMORPHISM, ENCAPSULATION AND INHERITANCE. 🤗

[@Alexey0795]

I'm stuck on interfaces. I am very glad to see a video on this topic

[@Excelmacromastery]

Glad it was helpful!

[@Leonardo_A1]

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.

[@aNDy-qh1em]

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!

[@Excelmacromastery]

No way around it unfortunately.

[@jonkirk2118]

This is incredibly useful. Thanks very much.

[@Excelmacromastery]

You're very welcome!

[@ClintEJr]

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.

[@Excelmacromastery]

You're welcome

[@randymartin9876]

Love you videos, clear and informative. Thanks.

[@MichaelBonandrini]

Finally something with a bit more substance...

[@nadermounir8228]

The video is very nice and I am learning from it.

[@joeallansalazar9824]

Very good topic. May i know the scenario i can use interfaces? Thank you Paul as always!

[@RungeCarl]

Brilliant! I code in Microsoft Access VBA but the principles are the same. Really struggled with Interfaces before seeing this video!

[@Excelmacromastery]

Glad it helped Carl. Check out my latest video - this section(kzbin.info/www/bejne/kIrbkmCHYtOGjpI) on Custom Functions uses Class Interfaces to provide a practical solution.

[@adamonjourney3726]

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. :)

[@budiyanto1531]

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

[@Excelmacromastery]

Thanks Budi.

[@iincitr]

Thank you, Paul. Continue like this advanced subjects.

[@billsimoni]

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.

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.

[@williamspiers4692]

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.

[@Excelmacromastery]

They weren't necessarily the best solution for this problem. This video was to show how they work in VBA.

[@frikduplessis3869]

Thanks you Paul, it's like you know what my next question is, then you answer it and so much more 🤝👏👏

[@KLiCuk1]

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?

[@tknaved]

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.

[@eiger67]

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

[@gahshunker]

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.

[@Excelmacromastery]

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...kzbin.info/www/bejne/kIrbkmCHYtOGjpI

[@jagoca728]

¡Chapeau! Or if you prefer:"Me quito el sombrero"...

[@hereb4theend]

You're a beautiful man. Thank you so very much.

[@Excelmacromastery]

Thank you!

[@StarOfAthenry]

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.

[@homejonny9326]

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.

[@stevennye5075]

Excellent work!

[@Excelmacromastery]

Many thanks!

[@cormackeenan8175]

This video was as easy to follow as it is to solve the Riemann hypothesis.

[@Excelmacromastery]

Class Interfaces is an advanced topic. If you're not familiar with OOP then it will be hard to understand.

[@stevechow8892]

thanks,how about use mvc logic to code VBA？

[@philipmcdonnell7168]

I think I'm finally starting to understand this....famous last words.

[@Excelmacromastery]

Glad you like it Philip:-)

[@wayneedmondson1065]

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!!

[@Excelmacromastery]

Thanks Wayne. Glad you like it.

[@FabricioLennar]

Awesome content!

[@MrTyrellcorp]

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?

[@Excelmacromastery]

Thanks. Yes - that's as close as we can get.

[@mdismailhosen8280]

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 ?

[@eddai84]

Thanks for sharing this useful videos

[@mhfs61]

Great video. Thank you.

[@td1138]

Very helpful. Thanks for the lesson.

[@Excelmacromastery]

You're very welcome!

[@hadibq]

👍👍 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?

[@JonathanExcels]

I am going to have to watch all your videos about classes again because I unfortunately don’t get it.

[@anoriginalnick]

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 ?

[@Excelmacromastery]

Unfortunately there is no reflection in VBA.

[@khalidalisawi8037]

thank you about this details

[@michaelturner9673]

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.

[@jacksonmacd]

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.

[@michaelturner9673]

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

[@Excelmacromastery]

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

[@sidh7023]

Have you done work with address parsing, or ordering street info that isn't parsed into street num street direction and street name?

[@anandhrajtube]

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?

[@juanmanzoni1354]

is it necesary the Implements statement?

[@nathankelvi1806]

Very useful Paul!

[@Excelmacromastery]

Thanks Nathan.

[@onyx6D]

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

[@jrcryo]

Hello Paul, Nice tutorial, i just don't understand the benefit of the interface.

[@antetunce3986]

the class factory would be huge of it could return an instance based on required classname then create an instance based on classname. Once the classname required is defined, if the corresponding module exists then returns an instance of it. You no more have to maintain this if stamement that could grows potentialy too much depending on your app. vba does not provide this kind of tool natively (as far as i know) but there are ways to generate the code on the fly in order to get the class instance based on its classname dynamicaly.

[@albertbatfinder5240]

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?

[@Excelmacromastery]

Memory is freed when the object is not longer referenced by any variable. See bit.ly/2YQ6Xde.

[@sedataksakal414]

Teşekkür ederim. Harikasınız :)

[@shep7484]

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.

[@scouter-xn6zi]

Great tutorial ! However, when I click on the suggested link for the code, I only get a blank page. Any thoughts ?

[@Excelmacromastery]

Try turning off any popup blockers that are running in your browser.

[@grahamparker7729]

Another great video 👍🏻

[@rrrprogram8667]

So is there a abstract class also in vba??

[@rrrprogram8667]

I expected more videos during this lockdown times.... Anyways... Keep making more videos

[@Iosens]

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

[@Excelmacromastery]

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.

[@Teruroom]

This your video is so excellent😆💕✨

[@Excelmacromastery]

Thank you 🤗

[@ouzytheoriginal]

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

[@mariodinoia9586]

Hi Paul Thank you for your ongoing superior Excel VBA Tutorials Is it possible to do a series on Object Oriented Programming in relation to VBA I for one would be very grateful I'm sure there are many Excel VBA users that would be happy to pay for a tutorial similar to VBA Handbook But for Object Oriented VBA So how about it community, please reply if you would like Paul to do this

[@Excelmacromastery]

That's a great suggestion Mario. It's something I've considered and I may create a course like this but it would be sometime in 2021.

[@mariodinoia9586]

@@Excelmacromastery Hi Paul Its 2021

[@mariodinoia9586]

@@Excelmacromastery Hi Paul, 2022 is almost over, any progress?

[@s1ngularityxd64]

🤩 excellent