Excel VBA Introduction Part 35 - Class Modules

[@ChrisNewsome]

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!

[@chimerablack4913]

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

[@WiseOwlTutorials]

Yeah but it's really wonky :D

[@OneWhoWas]

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.

[@WiseOwlTutorials]

Ah thanks! Happy to hear that you find the videos helpful and thanks for taking the time to leave a comment!

[@ufmart16]

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!

[@iberealves830]

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.

[@WiseOwlTutorials]

Thank you so much for the kind comments and for watching the video!

[@CavalierNSN]

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

[@HoangNguyen-pv6fz]

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

[@WiseOwlTutorials]

😀thanks!

[@quickDash-agba]

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

[@WiseOwlTutorials]

Thanks so much Gustav!

[@jaca47]

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

[@waynekiely4137]

Excellent presentation. Best I've found on KZbin to date. Many thanks.

[@Bartnick81]

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

[@WiseOwlTutorials]

Thanks, I'm happy to hear that it helped!

[@edsonmatheus7976]

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

[@ukaszpawlak4854]

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

[@stelsovich1]

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

[@rrrprogram8667]

This is simply brilliant video.. Thanks for sharing

[@johnhickton2922]

Great video. Very clear and easy to follow.

[@WiseOwlTutorials]

Thanks John!

[@robinleen4305]

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

[@WiseOwlTutorials]

Ahh thank you Robin, that's very generous of you! I'm really pleased to hear that you've found the videos useful and thank you for the kind words!

[@smbossco]

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.

[@alberthema]

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

[@ouzytheoriginal]

best channel for vba learning

[@WiseOwlTutorials]

Thank you for the comments!

[@GaryHutsonVBA]

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!

[@serdip]

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

[@gustavanderson4633]

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

[@WiseOwlTutorials]

Thanks Gustav, happy to hear that you enjoyed it and thanks for taking the time to comment!

[@MostafaMahmoud-wz6gx]

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

[@WiseOwlTutorials]

Thanks Mostafa happy to hear that you enjoyed the video!

[@szita2000]

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

[@WiseOwlTutorials]

That's around 35 minutes better than many people Ernie!

[@Daniel-ro4se]

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

[@WiseOwlTutorials]

You're very welcome Daniel! Thanks for watching!

[@DimitriBoyarski]

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

[@WiseOwlTutorials]

Hi Dimitri! I wrote an article on how to play sound effects a few years ago which you can see here www.wiseowl.co.uk/blog/s415/flappy-bird-excel-vba-sounds.htm I hope it gives you some ideas!

[@3DssMarketing]

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

[@WiseOwlTutorials]

Happy to hear that it helped! Thanks for watching!

[@brionreid7316]

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.

[@serdip]

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.

[@abufun1718]

Very good and briefly explain action. I like it

[@WiseOwlTutorials]

Thanks for watching!

[@dwwilliams21]

Amazing explanation, with great examples!

[@loveroflife2157]

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.

[@MissionTaken]

Well done! Excellent teacher. Great tutorial.

[@IgnacioAguilarToledo]

Great tutorial. Thank you!

[@WiseOwlTutorials]

Thanks for watching and taking the time to leave a comment!

[@khalidalisawi8037]

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

[@WiseOwlTutorials]

Thank you Khalid, I'm happy that you found it useful!

[@hadireg]

Awesome! Great teaching skills! Thumbs up!

[@DarrenSaw]

First class explanation! Pun intended :D

[@WiseOwlTutorials]

😀pun appreciated!

[@pullaiahmamidala6754]

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

[@ElWiwif]

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?

[@WiseOwlTutorials]

Hi Brian! I'll add this to the list but it's likely to be some time before I get chance to do this! Thanks for the suggestion and for watching!

[@mrbenny28]

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

[@pbassociates9168]

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

[@WiseOwlTutorials]

Glad you enjoyed it and thank you for the suggestion!

[@nigelriza4814]

Superbly clear. Thank you so much.

[@danielrestreporuiz6074]

Dude you are a genius, thank you so much!

[@WiseOwlTutorials]

:D I wouldn't go that far but you're welcome! Thanks for watching!

[@CyrusPieris]

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

[@geotermiaineel7478]

@@WiseOwlTutorials It is a basic idea of OOP. Actually, there was a problem of hiding data in the procedural programming. The class permits us to hide data to some users and permit to other users.

[@geotermiaineel7478]

Well, there are more. A book on OOP will be helpful.

[@ceoleo]

Thank you very much for these videos.

[@MesutAkcan]

Very Good! Thanks you so much!

[@WiseOwlTutorials]

You're very welcome, Mesut! Thank you for watching!

[@scotolivera8207]

Man can't thank you enough.

[@WiseOwlTutorials]

You're welcome and thanks for watching!

[@claudiajanson9141]

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?

[@WiseOwlTutorials]

Happy to hear it was useful! If it's a public enum you want to use elsewhere it would probably make sense to declare it in a separate module. I usually have a separate PublicDeclarations module for that sort of thing.

[@claudiajanson9141]

@@WiseOwlTutorials thanks a lot. I‘ll try that. 🙂

[@DevinderKumar]

Thanks a lot for a very useful and detailed lecture.

[@alpwi]

Great Video and great explanation. Thank you.

[@alivali7263]

Hello, It is nice

[@mohamedradwan7170]

Great Video, many thanks for your efforts.

[@rogeriopalma2386]

Greetings and thanks from Brazil fort he good job !

[@TomasStibtoStibal]

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

[@ulayaz]

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

[@WiseOwlTutorials]

Glad you enjoyed it! I have Interfaces on my list of topics to create but it's not likely to happen in the near future.

[@bondniko]

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

[@manueloscarariasrodriguez8810]

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

[@WiseOwlTutorials]

Nice!

[@houss_yi7084]

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

[@WiseOwlTutorials]

You're very welcome, thank you for watching!

[@erixD]

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!

[@phapkhuong5279]

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

[@m74w]

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

[@100verdik]

you're amazing, thank you

[@WiseOwlTutorials]

Thank you for watching!

[@elkapitan20]

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

[@srider33]

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?

[@turankocman6096]

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.

[@WiseOwlTutorials]

Hi! It seems that the language had not been set for these videos. I've done this now but it may take some time to KZbin to generate the subtitles. Thanks for bringing this to my attention!

[@turankocman6096]

Hello Andrew, I had the opportunity to learn a lot with your trainings, thank you for providing us with these trainings.@@WiseOwlTutorials

[@faiz.ahmad65]

Please make a video on adding custom tab in UI

[@prestondukes4956]

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.

[@Kurt1968]

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?

[@KidNapPingNo1]

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.

[@KidNapPingNo1]

Great. Thank you for the fast answer :)

[@krzysztofmaciak1611]

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

[@markbocsor3704]

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

[@WiseOwlTutorials]

Thanks Mark! I did write a detailed tutorial on creating the game which you can see here www.wiseowl.co.uk/blog/s398/flappy-bird-excel-vba-index.htm I didn't quite finish the last two parts and modern versions of Excel give terrible performance compared to Excel 2010 so it's unlikely that I'll ever finish it off. Do take a look if you're interested, there are some pretty cool techniques to learn in the 13 parts that I did write!

[@markbocsor3704]

@@WiseOwlTutorials Thanks Andrew, I will definitely watch it

[@dukestt]

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.

[@dukestt]

***** Thank you for your reply....I obviously have to do more reading. As for a career, I just do this for fun but if I could do it for a job that would be good, I guess.

[@JS-ys2uk]

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

[@everything_is_on_fire3155]

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

[@daveblake6407]

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?

[@data-science-ai]

Amazing! Thank you so much!

[@MrMallesh1]

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

[@WiseOwlTutorials]

Thanks Mallesh! I'm not sure if we will have some more videos on VBA class modules in the future but if you have some specific questions about class modules I might be able to answer them.

[@Sebastian71732]

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

[@Sebastian71732]

Sebastian Specht I guess my question is, if these properties are inteted for the programmer, which designs / changes the program, or for the actual user of the program.

[@davidhansen527]

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?

[@stephenhammond1745]

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.

[@WiseOwlTutorials]

Hi Stephen! Yeah, enumerations don't have any built-in validation unfortunately, they're more of a convenience for a developer rather than an end-user feature. It's true of the native enumerations too - ActiveCell.End() - you can use xlDown, xlToLeft, xlToRight or xlUp, but there's nothing to stop you from writing whatever you want! The read-only feature is related to the Property procedures that you create. If you only create a Property Get procedure, that property is read-only. Hope that helps!

[@stephenhammond1745]

@@WiseOwlTutorials Yes helpful, thanks. I was hoping that there was way to only create the Get Property but still be able to access the enumeration list. Thanks for replying.

[@WiseOwlTutorials]

No problem Stephen!

[@yashsomaiya8141]

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

[@VinodKRamachandra]

Thank you

[@thomasmcallister2543]

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

[@thomasmcallister2543]

No problem. Thanks for the reply and the great videos. :)

[@thomasmcallister2543]

Your videos are incredibly helpful. No prob at all. Cheers, T

[@tedtdu]

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

[@andreibaraboi5074]

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

[@emailuznow]

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

[@bunc11]

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

[@bunc11]

Thanks :)

[@adaptronankidu2611]

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.

[@adaptronankidu2611]

WiseOwlTutorials Nevertheless, best tutorials on VBA. I have never seen anybody delving into details this much for such an important programming language.

[@kewalchandrajoshi7094]

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

[@alivali7263]

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

[@sethhyde5439]

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?

[@manhle85]

i wish you make a lesson about API fuction in Excel

[@manhle85]

thank you so much

[@ahrorkuldashev9603]

You should get a knighthood for creating these VBA series!

[@mehedihasan99]

Twilight Saga's genre killed me 😂

[@WiseOwlTutorials]

Watching five minutes of the first film had the same effect on me!

[@vinitmanerikar5444]

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

[@thomasfergusen5144]

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

[@vuminhduc2011]

I'm not an English, why doesn't your video have subtitle?

[@iamsopure1043]

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?

[@iamsopure1043]

***** Thanks :)