How to Use Class Interfaces in Excel VBA

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

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

thank you about this details

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

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!

Nice video, but to much jumping around.

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.

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.

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.

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.

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.

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

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.

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

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

Nice

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

Mind = blown

Holly crap! I just leveled up today!

Cool, except that the classfactory should be a class itself.

No need for classes no need for interface, just ointerest=result(amount,interestType) and a function "result" with all the interest types, no 3 times the same printing routine needed.

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

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

Confused. You said "VBA will go to the correct type, whether it's A or B." But Excel doesn't magically "figure it out". That only happens because you EXPLICITLY DEFINE oInterest to be clsInterestA or clsInterestB. Also, the Class Factory doesn't "create a class", you created the class. The factory creates an object instance of a class.

very nice !

Whenever I use classes, I prefer not pass pass arguments.

Instead, I make use of the properties.

So in your code, you have:

' Calculate the interest

Sub iInterest_Calculate(ByVal amount As Double)
m_Amount = amount * 1.1
End Sub

I have adapted it like this:

iInterest looks like this:

Private m_Amount As Double

Sub Calculate()
End Sub

Sub PrintResult()
End Sub

Public Property Get Amount() As Double
Amount = m_Amount
End Property

Public Property Let Amount(ByVal A As Double)
m_Amount = A
End Property

clsInterestA looks like this:

Implements iInterest

Private m_Amount As Double

' Calculate the interest

Sub iInterest_Calculate() '*** THE MAIN DIFFERENCE, DOESN;T TAKE ARGUMENT.
m_Amount = iInterest_Amount * 1.1
End Sub

' Print the result to the Immediate Window

Sub iInterest_PrintResult()
Debug.Print TypeName(Me) & ": " & iInterest_Amount
End Sub

Public Property Get iInterest_Amount() As Double
iInterest_Amount = m_Amount
End Property

Public Property Let iInterest_Amount(ByVal A As Double)
m_Amount = A
End Property

The For Next loop in modMain is now:

Dim i As Long, result As Double
For i = 2 To rg.Rows.Count

' read the current row to variables

Amount = rg.Cells(i, 1).Value
interestType = rg.Cells(i, 2).Value

' Get the interest object

Set oInterest = ClassFactory(interestType)

' Calculate the interest

oInterest.Amount = Amount '*** ADDED THIS, SO DOESN'T PASS ARGUMENT TO CALCULATE

oInterest.Calculate

' some code

' Print the interest to the Immediate Window

oInterest.PrintResult

Next i

It returns the same results as yours.

Can you tell me if my way is just different (not passing arguments to Calculate) or is it more work for no extra benefit?

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?

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

This is incredibly useful. Thanks very much.

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

I've learnt a lot from your videos but this one seems odd. Wouldn't it be better to have 1 class that handles all interest types? Then adding in more types means just adding to 1 class module.

Wouldn't it be better to just create one class and move the if statements to the class function?

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.

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.

And here I was thinking I knew a lot about VBA. Man was I wrong.

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

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

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

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

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

All your videos are too fast. When explain a part of the code you often go from one part of the window to another and the audience has just about a second to focus on the code and listen to the explanation which mean you’ve missed just about everything. You know the code and techniques by heart but for the many in the audience, we are seeing both for the first time. I think if you take a little more time to pause at different screen shots it would be much easier to follow. These actions happen so fast and repeatedly that it even makes it impossible to pause the video and keep track of what’s going on. I don’t know how successful you are with these instructional videos but I find them took quick, and I have been programming in VBA for quite some time.

Your methods are great and really help to creat good code. I’ve worked on VBA programs with basically thousands of line of code in different workbooks including VBA add-ins and I can’t tell you how many hours were spent to sift through jumbled code to make improvement, to change functionality, or fix problems. Thanks for your fine work in conveying these lessons.

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 ?

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

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?

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

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.