This is the best example and explanation of VBA classes I have found yet on what is a challenging concept to grasp for a beginner. Thanks for taking the time and effort to make this excellent video. I hope your channel grows to gain more subscriber which you deserve.
The collection "animals" only needs to be filled with the elements of the class specified in "choice" this simplifies the code and reduces the time and memory requirements.
Great video, just one question though: What happens if new data arrived that included a new type of animal (say owl) which not is listed already? Would you need to create a new class for it? If so, that doesn't seem to make sense.
Hi NP, yes you would need to create an Owl class. This tutorial assumes there are limited pet types and therefore only these classes. An alternative for dynamic data would be to create one Animal class and have the type as an attribute and assign all pets to that class. So all pets are Animals with Animal.type = cat/dog/owl etc.
I'm a relatively experienced programmer, and I've been googling around past couple of days to try and find a practical application for classes (custom objects), but couldn't find any. My own line of thinking is creating objects is ever only useful if you are coding a lot of similar stuff and you want to put it all in a neat little box with a ribbon on top... but even then justifying objects over just functions seems difficult to me. Is there something I'm missing? (I work as an internal dev for all things accounting at a medium large corp.)
Heya Moe, my background is similar and I too used mostly functions. I did occasionally use classes for when I wanted to convert some identifiable objects from spreadsheets into VBA objects and then do what I wanted with them in more detail (e.g. 'Employee'). I think I just touched on what is possible though and I'm open to the idea some people are doing or have done some very neat things with Classes and objects in VBA. I can understand getting to a point where you hit the wall of lack of features for classes in VBA and get frustrated. I do wonder if Python for Excel then comes into it's own at this point, but then that's not so practical in a corporate environment where you cannot just install python on all the machines.
@@databen7194 Thanks for reply. I've been mentioning Python as a thing that exists to my boss, but considering the mainstream direction of using "robots" *sigh* which are outsourced projects, I fear an end is nigh for in-house developers. We will all be degraded to code janitors.
I have been stepping through your code and can't figure out the following: Sub PrintCollection(coll As Collection, name As String) Debug.Print vbNewLine & "Printing " & name & ":" On Error Resume Next Dim item As Variant
For Each item In coll Debug.Print item.PrintOut() Next item
On Error GoTo 0 End Sub in particular, when you reach this line: Debug.Print item.PrintOut() it immediately jumps to the specific class, be it Cat, Dog, Fish or Hamster. How does the program know which class to go to?
Sure, so the key line here is that 'item' is declared as a Variant (basically 'anything') so when each item variant is looped over in the Collection it morphis into it's type with it, and luckily for us this includes class types! Cat, Dog or Fish. It could be any data type even a basic one for example String, Integer or Long would work in a Variant loop too, give it a try. :)
I guess he missed the use of the AnimalClassFactory: within the AnimalClassFactory Function the classType is extracted from the range. Then an object of this class is created and returned to the Sub CreateReport, where this object is added to the collection. This collection of objects will be then handed over to your Sub PrintCollection. And now that happens what Ben already said. Item is now one object in the collection. So when calling item.PrintOut this Sub is performed on the object. So if you look in Sub PrintOut there it is defined to Debug.Print TypeName(Me), where Me is the object, so TypeName(Me) gives you the class name
It doesn't seem as if you are using the implemented animal class. None of those methods are triggered and if you comment out the implement line then everything still works... No disrespect, just trying to understand
Important: VBA does not implement interfaces, it exposes the methods that need to be implemented. An interface is like a contract. When you implement an interface class you agree to include all the interface's public properties and methods in that class module.
14:28 What would be the difference if I would've implemented daysHoused not as a Public Property but as a Public Function DaysHoused() As Integer DaysHoused = Now() - arrivalDate_ End Function And wouldn't the call be the same? aMaxStayTime = animal.DaysHoused() So, why does Property saves us many lines of code?
it's the first time is see some1 in vba use classes with interfaces, Great Video
Finally got it. Great job!
I have seen numerous class videos but I have to admit that only yours made sense to me.
Thanks Noam! I'm glad my video helped you
This is the best example and explanation of VBA classes I have found yet on what is a challenging concept to grasp for a beginner. Thanks for taking the time and effort to make this excellent video. I hope your channel grows to gain more subscriber which you deserve.
Thanks Daniel !
Great tutorial, I love it. I'm subscribing now.
Just found your channel.
What an awsome video.
Thanks, thanks, thanks!!
Liked and subscribed! Well done!
Outstanding! (please improve the audio). Best regards from Limón, Costa Rica!
Thanks, will do!
The collection "animals" only needs to be filled with the elements of the class specified in "choice" this simplifies the code and reduces the time and memory requirements.
Great video!
Could you please cover working with customXMl part
Very good explanation of how to use classes in vba. You wouldn’t share either the file, would you by any chance?
Link added to description :)
Thanks ever so much! Much obliged 😀
Great video, just one question though:
What happens if new data arrived that included a new type of animal (say owl) which not is listed already?
Would you need to create a new class for it?
If so, that doesn't seem to make sense.
Hi NP, yes you would need to create an Owl class. This tutorial assumes there are limited pet types and therefore only these classes.
An alternative for dynamic data would be to create one Animal class and have the type as an attribute and assign all pets to that class. So all pets are Animals with Animal.type = cat/dog/owl etc.
@@databen7194 Thanks for clarifying.
I'm a relatively experienced programmer, and I've been googling around past couple of days to try and find a practical application for classes (custom objects), but couldn't find any. My own line of thinking is creating objects is ever only useful if you are coding a lot of similar stuff and you want to put it all in a neat little box with a ribbon on top... but even then justifying objects over just functions seems difficult to me. Is there something I'm missing? (I work as an internal dev for all things accounting at a medium large corp.)
Heya Moe, my background is similar and I too used mostly functions. I did occasionally use classes for when I wanted to convert some identifiable objects from spreadsheets into VBA objects and then do what I wanted with them in more detail (e.g. 'Employee'). I think I just touched on what is possible though and I'm open to the idea some people are doing or have done some very neat things with Classes and objects in VBA.
I can understand getting to a point where you hit the wall of lack of features for classes in VBA and get frustrated. I do wonder if Python for Excel then comes into it's own at this point, but then that's not so practical in a corporate environment where you cannot just install python on all the machines.
@@databen7194 Thanks for reply. I've been mentioning Python as a thing that exists to my boss, but considering the mainstream direction of using "robots" *sigh* which are outsourced projects, I fear an end is nigh for in-house developers. We will all be degraded to code janitors.
I've always spelt Fishes with an 'es' not Fishs, so your formula for me would return Fishe ?
I have been stepping through your code and can't figure out the following:
Sub PrintCollection(coll As Collection, name As String)
Debug.Print vbNewLine & "Printing " & name & ":"
On Error Resume Next
Dim item As Variant
For Each item In coll
Debug.Print item.PrintOut()
Next item
On Error GoTo 0
End Sub
in particular, when you reach this line:
Debug.Print item.PrintOut()
it immediately jumps to the specific class, be it Cat, Dog, Fish or Hamster.
How does the program know which class to go to?
Sure, so the key line here is that 'item' is declared as a Variant (basically 'anything') so when each item variant is looped over in the Collection it morphis into it's type with it, and luckily for us this includes class types! Cat, Dog or Fish. It could be any data type even a basic one for example String, Integer or Long would work in a Variant loop too, give it a try. :)
I guess he missed the use of the AnimalClassFactory:
within the AnimalClassFactory Function the classType is extracted from the range. Then an object of this class is created and returned to the Sub CreateReport, where this object is added to the collection. This collection of objects will be then handed over to your Sub PrintCollection.
And now that happens what Ben already said. Item is now one object in the collection. So when calling item.PrintOut this Sub is performed on the object. So if you look in Sub PrintOut there it is defined to Debug.Print TypeName(Me), where Me is the object, so TypeName(Me) gives you the class name
Ben, good class example and explanation! Can you share the file?
Link added to description :)
It doesn't seem as if you are using the implemented animal class. None of those methods are triggered and if you comment out the implement line then everything still works... No disrespect, just trying to understand
Important: VBA does not implement interfaces, it exposes the methods that need to be implemented. An interface is like a contract. When you implement an interface class you agree to include all the interface's public properties and methods in that class module.
There's a small bug:
if (...) then aMaxStayTime = aStayTime
-> if (...) then aMaxStayTime = animal.daysHoused
14:28 What would be the difference if I would've implemented daysHoused not as a Public Property but as a
Public Function DaysHoused() As Integer
DaysHoused = Now() - arrivalDate_
End Function
And wouldn't the call be the same?
aMaxStayTime = animal.DaysHoused()
So, why does Property saves us many lines of code?