Excel VBA Introduction Part 35 - Class Modules 

You have, by far, the most comprehensive instructional videos on VBA on the internet. I always find myself coming back to your channel for a refresher or new information. Keep up the excellent work!

This dude made collision detection with VBA? *Stands and applauds*

This in my top 10 best tutorials in terms of Usefulness. I come back here all the time, and use this example frequently when talking with ADO sources.

Incredibly helpful! The level of expertise and the detailed breakdown of individual concepts made this the best lecture available for class module and property. Thanks you so much!

It feels illegal to have access to such high quality learning content for free.

This is the first time that I post a comment in any tutorial, but this one deserves. Knowledgeable and excellent presenter, clear and comprehensive amazing wok, congratulations! Thank you very much for sharing.

I would like to create an infinite loop that would give you likes until the end of time! You are absolutely GENIUS teacher!

The best tutorial on class modules out there by far! Thank you Andrew as always!

This is stunning. I can't tell you how many times I've come across knowledgeable people/presenters who can't really explain the "What" and "Why" about their code on RU-vid. But you, you're magic!! Thank you, I'm inspired. Oh, apparently, I've been programming in Excel the hard and long way (writing code EVERY SINGLE TIME I WANT SOMETHING DONE). I learned how to use VBA "head first" and I have the bruises to prove it! Your presentation changes the way I approach automation in my projects! I'm sorry, I ramble when I'm excited...

Jezus man. You're a great teacher. I actually understood everything in your video although I'm a rookie in class modules. Great job!

Great video. Very clear and easy to follow.

I got lost at around 40 minutes :O Need to watch this multiple times.

Excellent presentation. Best I've found on RU-vid to date. Many thanks.

Thank you, thank you, thank you! All of your tutorials are exceedingly helpful, but implementing Classes will take my VBA projects to the next level.

That lesson is the most usefull for start to work with ClassModule ! Thanks a lot !

Far superior explanation to other I watched, thank you sir!

No words can describe your coverage of the topic, Well done

This is simply brilliant video.. Thanks for sharing

Great video! Very informative and practical. Thanks so much for posting this great resource. Six years ago (!) I proposed a way to simplify the GenreText() Property Get procedure. As an alternative solution to the issue of the Genre property, in which an enum is used to assign values and then a separate Property (GenreText) is used to get a string corresponding to the enum, I now have a solution that uses just the original Property Get/Let Genre procedures. There is no need for a separate GenreText() Property Get procedure. I set the data type of the Genre Property procs to Variant. The Genres enum is still used with the Let procedure. Any other values not corresponding to one of the enum members will cause the Property Get() procedure to return "Invalid genre." I use a module level dictionary whose keys correspond to the Long values from the Genres enum and whose items are the string representations of the various genres. Here is the code for the clsFilm class module: Option Explicit Private Const MODULE_NAME = "clsFilm" Private m_dictGenres As Dictionary Private m_Genre As Long Private Const INVALID_GENRE = -1 Public Enum Genres ActionAndAdventure Animation Comedy Drama HistoricalDocumentary Romance SciFi End Enum Public Property Get Genre() As Variant 'The string return corresponds to the 'dictionary key held in the module level 'variable. If m_Genre INVALID_GENRE Then Genre = m_dictGenres(m_Genre) Else Genre = "Invalid genre" End If End Property Public Property Let Genre(ByVal vNewValue As Variant) 'vNewValue is a Long value corresponding to a member 'of the FilmGenres enum. It corresponds to a key in 'the module level dictionary. If m_dictGenres.Exists(vNewValue) Then m_Genre = vNewValue Else m_Genre = INVALID_GENRE End If End Property Private Sub Class_Initialize() 'Populate dictionary to translate enum values to text 'for use with the Genre Property Get/Let procedures Set m_dictGenres = New Dictionary With m_dictGenres .Item(Genres.ActionAndAdventure) = "Action And Adventure" .Item(Genres.Animation) = "Animation" .Item(Genres.Comedy) = "Comedy" .Item(Genres.Drama) = "Drama" .Item(Genres.HistoricalDocumentary) = "Historical Documentary" .Item(Genres.Romance) = "Romance" .Item(Genres.SciFi) = "SciFi" End With 'Set the default value for the Genre property m_Genre = INVALID_GENRE End Sub ------------------------------------------------------------------------------------------ Here is the testing code in a standard module: Public Sub TestFilmClass() Dim objFilm As clsFilm Set objFilm = New clsFilm With objFilm .Genre = Genres.ActionAndAdventure Debug.Print .Genre .Genre = Genres.Drama Debug.Print .Genre .Genre = Genres.SciFi Debug.Print .Genre .Genre = Genres.HistoricalDocumentary Debug.Print .Genre .Genre = "Not a valid genre" Debug.Print .Genre End With End Sub ----------------------------------------------------------------------------- Here is the output generated by the testing code: Action And Adventure Drama SciFi Historical Documentary Invalid genre Thank you kindly.

Thanks for this insightful video. I was following you step by step, and I got excited to complete others. Please if possible, try to add closed captions on your videos. Regards

best channel for vba learning

Awesome tutorial! Thanks for this informative lesson! For the read only GenreText property, you can get the same functionality with far less code. If you start the FilmGenres enum with a literal 1, like this Public Enum FilmGenres Action = 1 Adventure = 2 Animation = 3 Comedy = 4 Romance = 5 SciFi = 6 End Enum then in your property procedure you can simply write: Public Property Get GenreText() As String Dim vntChoice as Variant 'in case pGenre has been assigned a value that is not a FilmGenres member, check if Choose() 'returns Null vntChoice = Choose(pGenre, "Action", "Adventure", "Animation", "Comedy", "Romance", "SciFi") GenreText = IIf(IsNull(vntChoice), "", vntChoice) End Property If you write validation code in your Property Let Genre() procedure so that it accepts only valid enum values, then Choose() will never return Null, and therefore the GenreText property procedure becomes simply: GenreText = Choose(pGenre, "Action", "Adventure", "Animation", "Comedy", "Romance", "SciFi")

Explains it better than the five books I have on VBA. Well done sir! You are a scholar and a gentleman :) Keep up the good work!

You're a great teacher. Thank you so much for the effort. I will gladly donate.

Very good and briefly explain action. I like it

Amazing explanation, with great examples!

Fantastic tutorial as always, a great first step into the world of OOP. Will try to incorporate this into all future VBA projects!

Well done! Excellent teacher. Great tutorial.

Amazing work!! This really helped me trying to figure out classes - which are very confusing to begin with.

Thanks a lot for this video. It is hard to find some well declared stuff about VBA class modules. This tutorial was amazingly helpful as introduction to the topic. I appreciate your teaching skills very much.

I have been looking for this! Its the missing piece to a problem I've been working around, sadly, for years. Thank you for sharing!!! P.S. I watch your ads ALL the way through.

Thanks for your video. I found your explanation of the different concepts very helpful :)

Wise Owl tutorial on using sound effects would be pretty awesome.

Awesome! Great teaching skills! Thumbs up!

Great Video, many thanks for your efforts.

Great Video and great explanation. Thank you.

Thanks a lot for a very useful and detailed lecture.

Dear Wise🦉, thank you so so much for this wonderfull video! ❤

Thank you, this video help to me to understand class modules in VBA

Great video, helped a lot ! Thanks wise Owl :)

Thank you very much for these videos.

Superbly clear. Thank you so much.

I thank you for your video, really it best video to learn classes in vba

Greetings and thanks from Brazil fort he good job !

Amazing! Thank you so much!

First class explanation! Pun intended :D

Dude you are a genius, thank you so much!

1:15 What is a class 8:35 Designing a Class 10:58 Creating a Class Module 13:21 Creating an Instance of a class 16:27 Destroying an Instance of a class 17:47 Class Module Events 19:37 Stepping Through Class Module Code 20:31 Creating Fields in a Class Module 23:19 Limitations of Fields 24:02 Creating Properties 25:36 The Property Let Statment 27:44 Writing a value to a property 29:30 The Property Get Statment 31:21 The Advantages of Properties 32:25 Quickly Copying Properties 35:36 Declaring an Enumeration 37:13 Using Enumerations in properties 38:56 Using a Property with an enumeration 40:39 Read-Only Properties 44:44 Default Values for Properties 49:03 Creating Methods 54:17 More complex Methods

Very Good! Thanks you so much!

Thanks again. I m still trying to grasp why classes are used but you are starting to lift the fog.

Again thank you for such an explanatory helpful video, my request is to make a tutorial on interface class.

Thank for the tutorial. Your Kung Fu is strong sensei!

Man can't thank you enough.

Thank you

thank you so much

This is a great video. How do you explain to folks the difference between classes and Types? The best I can come up with is controlling the information going in or not, while type is full write access?

you're amazing, thank you

Hi wiseowl, i am trying the same thing out. In let properties, when I am assigning the value to Releasedate it has already value 12.00 AM and perhaps not taking value through argu passed. Please advice

Hello Grand Owl! Thanks for all your incredible work. Big Fan of yours and apprentice. Can you please load a brief video elaborating more on SET Property?

Hi Andrew, I have a question: is not fully clear to me when to use types (part 34) vs classes (part 35). I looked over both the tutorials and I still have some doubts about pros and cons. Could you please help me with that? Thanks you

Is there a way to preserve objects created by instantiating a class for later use in other subs? You can make the class "Public Class1 as MyClass" but cannot "set Class1 = new MyClass" outside a sub so the object is destroyed once the sub is finished. Any way to get around this?

Love these Videos, still trying to get my head around this Class malarkey, finally set up my first class to store my colour pallets. My question is, I work in finance and therefore have to do a lot of borders in my code, is this something I should consider setting up a class for?

Hello, It is nice

I've tried the Class module in different workbooks, but I can't get it, so it just works in the same workbook. How to define the Class module for all excel

Hi, First i want to thank you for all this great tutorials about VBA. I learned so much of it an use it daily in my office. But here a small question. Why use a "get" or "let" instead of a simple "sub" or "function". I even hesitate in normal modules if i shall define a code as "sub" or "function". I guess i missed a point somewhere. Can anybody tell me ? Thanks

Hi Another quick question on the genretext part You have select case genre.action. When do you use .action rather than genre = action? Thank you so much

Thx for this video, explained in accessible way. Andrew, are you going to make a video about ByVal ByRef in VBA ?

Finally succeeded in creating my own class module after watching you video. Thank you for this great lesson! Now Im struggling to fill a userform combobox with the enum textvalues in the initialize event … probably I have to move the enum from the class module to a „normal“ module as the class is not instanced yet?

Great video bro, very helpful, wii you please make a video of class interface

When you are create a property are you able to apply it to any new object you create?? In other words if you create a object called "Books" would you be able to apply the film length property to it? Can you restrict what properies can be applied to an objects?

Thank you so much !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

I used FastKeys to write both property let and property get at the same time

Awesome. 👍🏻😎m

When generating the genre strings, couldn't you create the string as part of the instance when setting the genre for each film?

can we write type statement like enum statement in a class module?

Hi sir can u share me link from where i can download full vba course videos or advise me.

Excellent Video as always! Could you please explain how to incorporate a Function within a Class Module? Say I wanted to pass in arguments to this Function and return a value. How would I go about doing this?

Hey there, I was really amazed ho you could build a real game inside excel using VBA, I had no idea one could achieve it at all. Could you please give some clues how do you deal with those .dll's and how to discover its functions and all of its functionalities? If you give any reference it would very helpful and nice of you. Thank you very much!

What is difference of Objects and Classes? Can you tell me their roles respectively? Or just sth that Object|Class can do, which Class|Object can not..Thanks

This may be beyond the scope of the VBA videos, and it may not even make that much of a difference, but I was curious on optimization of the code. These videos are great, and I figured this was the best place to ask the question. I created a custom data type, and I stored it in an array, and I also had other classes I created stored in a collection. I was curious how everything was stored and accessed. I realize the collection is really a reference to an object, so I'm assuming there is only one instance of the object itself; however, I'm not too sure about adding custom data types to an array. Is there a reference to that one instance of that data type, or is that data type basically created over and over? I could see that if the collections/arrays store/access data in this manner, that the data types would end up using a lot more memory to store information. Also, is there any more processing power needed to access the objects than a data type. I realize that it may not really matter, or it may depend on the situation, or there may not even be a definitive answer. Thanks.

Very clear. But still, I am so far from knowing why and when would I create a class module instead of normal sub

So does it mean with a public variable users are able to both read values from and write values into the variable, while with a public property can let the vb developer controllers whether he/she wants to let the user read values from (property let) or write values into (property get) the variable?

ok.... this is where you loose me completely. My coding has been described as " belt and braces.." sort of "gets the job done" if you like. I know it is possible to create all this, of course, but your second question is very valid to me. Why?. Would this make me a better programer? Great videos BTW.

Excelent! can u c/p some short example when to use SET methods?

Hi I have one question why didn't you make even a single video MS Access macro?

This is brilliant. Thank you. This is a long shot but are you available for private lessons in VBA and C# in London over a number of months please? Thanks again

Hi! I come from Vietnam. This video is useful. Can you make a video about RaiseEvent in ClassModule? Thank you!

so did I get this right? A library stores some specific classes, and classes store objects with methodes which can be used for those objects. And by setting a reference to a library for example to MS Outlook 14.0 Object Library I make all the methodes and objects of the stored classes available to my current project.

why not just set the film genre as String?? then there will be no need to write the select case statement?? i am just asking cuz i din get why we went that way??

Twilight Saga's genre killed me 😂

Hi Andrew, I follow your tutorials with admiration. The subtitle option is disabled in 35,36,37 training videos. I would be very happy if there is a way to solve this.

You should get a knighthood for creating these VBA series!

Hello I don't understand the property function completely. I need a macro to transfer some data from one workbook to another, but the data has to be of a proper format. For exmaple one of the data has to 8 digits. Could I test for such things in the property? And if so, how do I go about showing the users of my program, that they have not entered a proper value? I hope you can help! :) And thank you so much for the tutorials, they helped me out a lot on my job. Sebastian

i wish you make a lesson about API fuction in Excel

Liked this video. First attempt at using class modules. However, still not clear on how you make something read-only. I have set up an enumertion list for departments (IT, R&D, Finance, etc) but not sure how to restrict user to only selecting from that list. I can escape the drop-down list and type in something that's not in the list but I get an "variable not defined" compile error at run-time.

Nice video, Can you add few more videos on Class Module

Please make a video on adding custom tab in UI

Hi Andrew, an excellent video, thank you:-) Perhaps could you do a tutorial how to write the code for that bird game :-)?

Ok, pros out there, I have a good one for you. I have 28 textboxes; I've created a class module named grpTextbox, and declared my textbox group with the WithEvents keyword. Now, I want to set an event, BeforeUpdate on my textbox group. Here's the thing, when I try to invoke this event, out does not show it on the dropdown list; how the hell do I access the damn event for the group of textboxes; if I declare it as MsForms.TextBox I'm fudged, if I declare it as MsForms.Control, bam!! There's the freaking event, but does not work; at runtime I get a stupid 459 error!! I've seen countless threads about this and no one can figure it out... Can someone help please!!!! Cool vifs, by the way.

One small correction Public Property Let Title(Value as String) pTitle = Value (instead of Title) End Property If you keep it as Title it won't set the title variable.