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!

[@karenjoy511]

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

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

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

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

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

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

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

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

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

[@shanewilliams7131]

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

[@reevesjim]

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

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

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

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

[@mgreg009]

Holly crap! I just leveled up today!

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

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

[@cesarbravo1035]

Very useful and interesting, thank you Paul

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

[@nguyenvu8262]

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

[@jonkirk2118]

This is incredibly useful. Thanks very much.

[@Excelmacromastery]

You're very welcome!

[@gttrongvn]

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

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

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

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

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

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

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

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

[@stevennye5075]

Excellent work!

[@Excelmacromastery]

Many thanks!

[@paraglide01]

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

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

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

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

[@nadermounir8228]

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

[@FabricioLennar]

Awesome content!

[@Alexey0795]

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

[@Excelmacromastery]

Glad it was helpful!

[@randymartin9876]

Love you videos, clear and informative. Thanks.

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

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

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

[@Luth1us]

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

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

[@iincitr]

Thank you, Paul. Continue like this advanced subjects.

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

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

[@hereb4theend]

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

[@Excelmacromastery]

Thank you!

[@nathankelvi1806]

Very useful Paul!

[@Excelmacromastery]

Thanks Nathan.

[@frikduplessis3869]

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

[@grahamparker7729]

Another great video 👍🏻

[@jagoca728]

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

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

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

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

[@MichaelBonandrini]

Finally something with a bit more substance...

[@td1138]

Very helpful. Thanks for the lesson.

[@Excelmacromastery]

You're very welcome!

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.

[@mhfs61]

Great video. Thank you.

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

[@s1ngularityxd64]

🤩 excellent

[@sedataksakal414]

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

[@eddai84]

Thanks for sharing this useful videos

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

[@Teruroom]

This your video is so excellent😆💕✨

[@Excelmacromastery]

Thank you 🤗

[@rrrprogram8667]

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

[@MrMallesh1]

very nice !

[@khalidalisawi8037]

thank you about this details

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

[@joeallansalazar9824]

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

[@rrrraaaacccc80]

Great 💯👍

[@philipmcdonnell7168]

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

[@Excelmacromastery]

Glad you like it Philip:-)

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

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

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

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

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

[@stevechow8892]

thanks,how about use mvc logic to code VBA？

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

[@marekchudy8893]

Thanks you

[@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?

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

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

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

[@JonathanExcels]

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

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

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

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

[@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?

[@sidh7023]

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

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

[@marekmagnuszewski6364]

Is it possible to implement more then one interface in the Class?

[@Excelmacromastery]

Yes.

[@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 ?

[@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?

[@juanmanzoni1354]

is it necesary the Implements statement?

[@pablovirgolini7202]

If you need to implement more interfaces, would you need to write "Syv iInterest_iTheOther_Calculate( ... ) ?

[@Excelmacromastery]

No. You would use either iInterest or iTheOther depending on which sub your were implementing.

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

[@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?