Excel VBA: Using Class Modules with Collections (5/5)
Vložit
- čas přidán 5. 09. 2024
- 👉 Ready to master VBA?
- Check out my full courses: courses.excelm...
- Subscribe to the channel here: bit.ly/36hpTCY
- FREE CHEAT SHEET: Get the free cheat sheet on VBA arrays here: bit.ly/2MXsnz9
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
In this Excel VBA video, we see how to use Class modules objects with Collections. One of the problems with collections is that it can only store one item from a row of data.
So how to we store data from multiple columns? We use class module objects.
In this video you will see how to create class module objects to store our data and how to add the modules to a collection. Finally we will see how to read the data from these class module objects to a worksheet.
Table of Contents:
00:42 Reading from a worksheet to a collection
03:11 Creating a Class Module
04:43 How to use the Class Module
07:15 Using the Class Object with a Collection
10:57 Write from the class objects to a worksheet
Related links:
The Ultimate Guide to Collections in Excel VBA: (excelmacromast...)
Get your free Arrays/Dictionary/Collections cheat sheet here: (bit.ly/2MXsnz9)
The Excel VBA Handbook Course(TheExcelVBAHan...)
Webinar Archives - 60+ Hours of VBA training(excelmacromast...)
Shortcut Keys:
Ctrl + R: View the Project Properties Window.
Ctrl + Shift + 8(or Ctrl + *): Get the current region on a worksheet.
F5: Run the code from the current sub.
F9(or click left margin): Add a breakpoint to pause the code.
Tab: To move lines of code to the right(Indent)
Shift + Tab: To move lines of code to the left(Outdent).
I went through the collection series over and over again.... And this is the best i have ever seen explained about the collection
Thanks. Glad you like it.
I have watched all the 5 tutorials in series and I am very impressed about how good you did it. I am 59 years old, have some knowledge about VBA coding. Your tutorials are so compact, efficient and valuable. Thank you so much to you, living somewhere in the world. Sharing could be not better to me then your examples, thank you again, I appreciate your efforts.
true indeed
I finally get it. The other examples I've seen of this were only storing 1 data item per row in the collection (e.g. Name) so it wasn't clear why using a class was a benefit. Now that I see you can store multiple data items it makes sense. I guess if you were working with multiple classes you could store that as dimensions in an array?
Absolutely awesome. I work with vba for a few years now and have been avoiding classes until now you made it so clear and simple to understand.
Thank you very much. I will be looking for new videos.
Glad you like the explanation.
I've just discovered mixing collections with classes and it ma be the most powerful tool to make clear, easy to maintain code in large VBA applications. I am pretty sure you are using this a lot in your programs. One more (or maybe two..?) videos with practical implementing this concept would be incredibly helpful. E.g. for education purpose I am trying now to code something like relational database using Excel tables (a little like CRUD in SQL)- I am tired for of filtering, inserting, deleteting rows if condition is met, updating values all the time (copy-pasting macros all the time and adapting) in Excel tables. Just an idea. Thanks a lot, you're legend.
Class module was allien for me until now. You are perfect. i am looking for fast way to filter main data with criteria and pasta to another sheet. thanks again.
This was an outstanding tutorial for this beginner VBA Coder to learn about Class Modules.
Thanks Paul!
I've wanted know what the class modules are and why & when we use them. And the search brought me here. It's still vague and confusing to me but......
Your teaching videos are very helpful for me. And even though I'm not good at English(especially listening) it's relatively easy to understand. I think you are a excellent teacher. Two thumbs up! Thank you!
i was just using array for everything, this make all of what i was doing 100 times more flexible and easier to code holy shit, thank you.
why i didnt thought of using collection with classes before, seems so obvious now.
Great example of a real-world use for classes in VBA. Looking forward to a series on class modules, methods and properties!
glad you like it. Classes are coming soon.
Excellent series. I appreciated it very much.
Really enjoyed this little set of 5 videos. Very informative on how to use collections and classes.
i love how you add animations, makes it even clearer ! thanks for sharing and helping out ;)
Great video Paul. The advanced filter is fast but does not provide any control. and that's the main disadvantage of this method. therefore I prefer the array method.
That's a very interesting point Stranno. What do you mean "does not provide any control"?
Excel Macro Mastery
Sorry. Wrong subject. My comment refers to your latest video about optimizing code with regard to increase the speed. if you want to change the the data on the fly in a particular column for instance, the advanced filter is not a suitable method.
@@strannostrannovasrr That's true. There are some situations where it is not suitable.
Very helpful, it's kinda object oriented collection, much easily to handle and avoid further errors
Excellent video. I am your fan
Thanks Vikram.
Awesome Paul. Very helpful and easy to follow.
Glad you like it Wyatt.
Thank you, I struggled a lot with my MS Access application for a little mistake. Thankful your video I found and correct it.
it is a beautiful demo about Collection and Class Module. Thank you !
Again, beautifully described Paul. I finally believe I might be able to get to grips with Excel VBA. Cheers
Thanks Ronan.
Excellent video again. Great explanation and exactly the sort of task I have at hand.
Only issue I had was the swimming pool effect on the mouse click was giving me a headache. (I'm pretty reactive to strobing and scrolling effects 😞) Just something you might want to note.
Thanks Paul.. so in this case, the class module is like a collection inside a collection.. meaning the class module is a collection of data specific to a single record within a collection of records meeting specific criteria.. yes? Thanks for the intro to using class modules.. very helpful. Thanks for sharing your knowledge. Looking forward to more. Thumbs up!!
Exactly Wayne. The collection is like the rows and then the class module contains the fields in that row.
@@Excelmacromastery Awesome.. thanks.. you are unlocking some things that I did not well understand prior. Thumbs up!
Exactly what I was looking for, thank you very much, your tutorials are very easy to understand
Perhaps a video on best practice/examples when a class object is altered by a method and when not would be useful. A bit like ByRef and ByVal. Your explanations are excellent and code always clean. Thanks.
Thank you, extremely well chosen examples with extremely clear explanation.
Honestly, your channel is the best I found on CZcams about VBA. It is up to date and always useful for my projects. You should teach on Udemy.
Thanks, I actually do have a course on Udemy.
@@Excelmacromastery thanks I will take a look
I have been thinking (for a long time!) if there is a way to manipulate like "VLOOKUP" in VBA, using "class" ideas. Because 1) VLOOKUP is a worksheet function, not VBA function. 2) I could emulate VLOOKUP by using "FIND" or something, but it gets messy. 3) I want to hold all the data in the range during the entire procedure.
This is the answer!! THANK YOU VERY MUCH!!
Thanks Paul....Thank you very much ..... I have learned the high end concepts like Class,collection,dictionary and arrays from your videos..Thanks a lot........
You're very welcome!
Thx. Very good Video! It helped me a lot
You're welcome
Dear, Today I see your list about the collection. it was interesting and easy to understand
Hi Paul, thanks for your detailed Tutorials. They are great! I will directly incorporate this in my macros.
You're welcome.
Hi Paul,
Excellent video, thanks for sharing and making so simple to understand.
No problem. Glad you found it useful.
If you are using class module just to store basic variables like in your example, you could use "Type" keyword as well .. thanks man for this great video
Thanks Paul. Great video. I found this information very very useful and look forward to incorporating this into my macros. Seems like it would be very fast with larger data sets.
that's great you found it useful. Let me know how it works with your macros.
thanks for the videos. really it is interesting and I have learned a lot every day. I ask you to introduce an advanced video about classes
Hi Paul,
Thanks for brilliant videos and awesome explanations
You're welcome Frik.
Hi Paul,
Please don't stop with this awesome short videos, I can't stop watching them, I have also tried out your techniques they are very helpful.👍
Thanks Excellent !
Really it baffles me why should you get a single thumb down on any of your videos. The way you explain your stuff is really impressive though I wish you go little bit slower. Thanks a ton
Thanks Fahad.
Ahh! Just had the light bulb switch on! Great section Paul.
So lemme get this straight...
The first part to specify filter..i.e. Australia, easy enuf. Then use the class to fill collections. Then use the collection again to "fill the class" back for the write out.? I thought you were gonna write out what was in the collections directly. But I see what you did now.
I can see doing this with just loops, ranges etc would take a lot more doing. Very cool.
What great series of a collection usage. It’s very very useful technique to handle database in Excel! Thanks a lot!
You're very welcome!
awsome thks :-)
From Montreal (Quebec)
Thanks Mario
Great video,Thanks
Thanks for your support.
Paul,
Thanks for your video.
One of the problems I find with learning class modules (and with it, collections), is why do we need it?
In your video, I agree it's very clear to readers what is going on.
However I feel (and do correct if I'm wrong!) that classes slow things down. Certainly in your video, using a collection to store the class, you had to loop at the end to return the results back onto Sheet1.
This is how I usually do it (because I'm no class module expert)!
Dim CountAus As Long
CountAus = Application.WorksheetFunction.CountIf(Sheet1.Columns(3), "Australia")
Dim DataArray() As Variant
DataArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
Dim DataArrayRows As Long
DataArrayRows = UBound(DataArray(), 1)
Dim AusArray() As Variant
ReDim AusArray(1 To CountAus, 1 To 2) As Variant
Dim i As Long, j As Long
j = 1
For i = 1 To DataArrayRows
If DataArray(i, 3) = "Australia" Then
AusArray(j, 1) = DataArray(i, 1)
AusArray(j, 2) = DataArray(i, 4)
j = j + 1
End If
Next i
Sheet1.Cells(1, 8).Resize(j - 1, 2).Value = AusArray()
Can you please let me know your thoughts about the shortcomings of my code, other than it's not as clear as yours because mine does not include any headings?
I would be grateful if you could post a video of a situation where ONLY a class module would do the trick.
Thanks
I think there is not exist a problem that only class module would resolve. But using objects like these make life way more blissfull :)
Say your class has lots of variables, now say your class variables result in additional variables which you’ll need to iterate through to get the result you’re looking for. Having a hierarchy will make your code much more readable for anyone helping you, or fixing your code after you’ve moved up the latter.
Excellent stuff... Thanks for the videos
You're welcome
Fantastic, very well explained . Thanks
Very nice video thanks for sharing 👍👍👍
you're very welcome.
Very good, thank you so much.
Thank you too!
Thank you very much
Paul, supper done. Thanks a lot. You are the best.
You are welcome!
Excellent video Sir, thank you very much, this is the best video I've ever seen. Cheers from Colombia
Glad you enjoyed it!
Very helpful and detailed explanation. Thank you!
You're very welcome!
Thanks Paul, love you so much, thank for sharing your knowledge. I got what i want from lecture
cảm ơn Phu
I always used a temporary collection with all my information I want to collect (so for this example first and last name) and after getting this information I stored it in another collection (looks about the same in your video) with a key. Its close to a class module, not the same but also handy. At least in my opinion. Now I know another way. Thank you. :D
Thanks for the feedback Marcel.
Excellent video; thank you very much
Nice explanation Paul. Is it possible to have the collection managed from within the class ? e.g. obviously the Collection must be declared (public) in the calling program. Then the Class (constructor, destructor) can simply call "coll.add customer" on each object instantiation. That way it only ever needs to be coded up once ?
I know it’s been 2 years since your comment/question. As I start exploring use of classes, I have the same question. Have you learned the answer to your question? If so, would you please share it?
Thanks,great video as always!
I would also tested custom Type along with Collections, although I'm a big fan of class modules 🙂
This is good. Thank you for the sharing
Thanks Chong
This was a new way for me, this will save some time, Thanks!
You're welcome.
Спасибо
good described but it should have been in the playlist within classes thats my feedback to you
tks very much
Thank you sir. Do you have any recommendations if you want to “group by” a particular collection’s item?
Thanks Paul , Great Video, can you make one more video on get and let method in class module, and their advantage if any Thanks.
Thanks Mallesh. I plan to cover Class Modules in a series of videos.
In the meantime, you can check out this article which cover the get and let properties:
excelmacromastery.com/vba-class-modules/#Class_Module_Properties
Nice Video. Thank you for explaining in very simple language.
I tried to adopt this method to transfer filtered data to another sheet. Everything was fine, but numbers with decimals got rounded off in the destination sheet. In your video, if the Total Items of Joseph Velasquez were 40.65, it became 41.
Kindly suggest me a solution. Thanks.
Collection is only 2 dimensional whereas array can be multi dimensional
Hi Paul
I have learned quite a bit from this video
it will streamline the code in my current projects
I was just wondering for a simple example like this would be easier to use custom types instead of class modules and when would it be better not to
Custom types predate Classes. We normally use classes to make the code follow OOP methods. That said, I haven't compared them speed wise so it's possible there is a speed advantage.
Hi Paul, a big thanks for this set of insightful videos. I've got a question though and I hope it's not off-base regarding what you taught us in this last tuto. Is it possible to create collections in a bigger collection (like if I were to create one main collection and then, multiple sub collections that would be stored in the first main one) ?
If I explain my project a little bit more : I work within a digital team for a Railway company, so the data I have to handle is a list of trips that each have a specific (and variable) list of points (stations). So I'd want to create a first collection that'll allow me to store all my trips objects. And then I'd want to create one collection of "stations" for each one of my trip. Could that be possible ? I don't know how to start :
- Should I create a class object named "trip" and then put a collection named "stationList" as one of the variables of my object trip ?
--> But I can't find a way to handle a collection as a variable of a class Object that will be also stored into another collection itself
Thanks in advance for your help
Kind regards, I look forward to listening to another video of yours
Very well explained. I have a query: could you please explain code to create a new excel workbook and write the data to the new workbook??. Thanks in advance👍👍
Many Thx for such an excellent tutorial. Would there be chance on how to incorporate COLLECTION, CLASS and ADO (ACE.OLEDB.12.0) together?
Thanks. You don't really use them together. ADO reads the data and places it in a recordset which is then written to the worksheet.
Collections and Classes are used as interim places to store data. Between reading and writing.
Well, I have had to learn a bit of VBA the "Hard Way', ... just watching some of your material has helped immensely..
When digging around for information, (when you have no background in this stuff) is a little overbearing to say the least.
But with help from different ends it is starting to make sense.
I'm to the point now where writing little macros isn't cutting it, and watching your explanation on the layout of Subs, Classes Modules etc has me looking at things totally different.
Its not completely understood,.... but I'm heading in the right direction. Thanks.
true
Thanks for the video .its very helpful.
Can we store them into array instead of collection .and what is the cons and pros of using either.
Appreciate your answer mate.
Yes you can
Thanks bro.. actually i went back to the previous series and realise that you did actually explained that..
Keep it up ..you are a legend
Hello kelly after this informative upload , on which subject of vba you gonna make tutorial.??
The Dictionary is up next😀
Nice video. Just to check. Is it possible to autopopulate the variables in the class?
Very nice teaching. Thanks for ur efforts.
Can u also give us a multi level (7 combo box) with depended unique data. I tried but getting only up 3 level. Next shows error.
Thanks
Paul what if I wanted to add another worksheet of data to make the collection like a 3 dimensional array? How would that be done?
Paul what is the difference between locals window and watch window? And which do you recommend to follow colls/arrays/dicts? Thnk u very much
locals automatically adds variables that are active. You cannot add variables or edit the locals window.
The watch window allows you to add variables and edit them. You can add function calls, formulas and be very creative.
Personally i only use the watch window as the locals window is limitedq.
@@Excelmacromastery Thanks Paul. Also i found Locals w doesn't follow dict variables as objects. Now I'm using only Watch w. Thnk u very very much.
Only a question, can we do something like this with UDT.?
Great video! subscribed. Thanks! Could do some pivot table and pivot chart programming videos when you get a chance ?
Thanks Glenn.
Hello Paul,
Is there a way to access the four different values in the collection item directly? Something like coll(i,).item (3) for the "Country"?
you can use Coll (i).Country.
Coll (i) returns the class module object.
Hi Paul. Thank you for the great tutorial. What is the whole point of "CustomerOut" at the end? I've tried it out without it and it works. I just used the original "Customer"
To avoid confusion. In this situation it will work but if the code gets more complex it is better not to have the same variable doing two different jobs.
Quick question: In this example we have set the condition "Australia" to create the collection. Let's say that I want to create a collection for each country, but I want VBA to recognise the number and name of the counties form the range, how should we set the condition? Thanks!
I have a little question: my teacher gave me a code with variables and i should put the variables in a classmodule and the button should work the same way like it did before. Is it the same way like you did in the video?
There 7:22 i understood, why we do create every time a new object "set o = new clsCustomer" but why doenst it work if we just insert new values into our already existing o object? I tried it but it doenst work and i dont unterstand why
You say that= For Each it's faster than just For. So i f i use => For Each costumer in coll (then paste all into a sheet with an i=i+1) still be faster?
Dear Sir, thank you for sharing. I'm retrieving response text using html inside a class module. Given a column of web page links to web scrape, can we input the response text (in this case stock prices) immediately to a range of cells without using collection ? At the moment, my code only retrieve the stock prices on C2, so only cell C2 get updated over and over again with different prices. Kindly need your help.
What would be the advantage of this over using an advanced filter?
I think that was just for learning purpose. I'd use an array.
Nice! I'm trying to copy the data from 46 worksheets and combine them into one. I used another coding before, but it capped out at 25 worksheets. I was told to try to complete the task using a collections. The problem is, I'm not sure how to construct the coding. Is this something you can assist with?
Hello, you can do it with the help of collections, but i guess it would work faster with arrays. Still, if you prefer collections, first create class module for your data type. First you create collection, then first loop goes through all source sheets, secondly you loop data inside your sheet row by row , creating instance of your class, filling it with data from row and finally adding it to the collection. Then when you have filled your collection, loop through it filling out the target sheet.
Does the clsCustomer has to be a Public Data Class or can it be Private Data Class and then Public Property Class, or would the latter interfere with the Add to Collection?
You can use properties if you prefer. This is the proper way to do OO code. However I have never found any benefit to using properties for classes that only hold data. It adds an extra abstraction for no benefit.
Paul,
Normally when you declare a class or collection, you write:
Dim Coll As Class / Collection
This is normally followed by
Set Coll = New Class / Collection
In your video, (towards the end when you write out the data to column H), you put:
Dim CustomerOut As ClsCustomer
and a few lines later, you wrote:
Set CustomerOut = Coll(I)
I expected to see
Set CustomerOut = New ClsCustomer
So my question is: when do you NOT have to follow up:
Dim SomeVariable As SomeClass
with
Set somevariable = New SomeClass?
Also instead of using a class to write out the data, you could have written
Sheet1.Cells(i, "H").Value = Coll(I).FirstName
Sheet1.Cells(I,"I").Value = Coll(I).TotalItems
I want to write a function where I have multipe columns with undefined number of values, but I don't know it beforehand since it will change per scenario. For instance
column A Column B ... Column Z
1 2 4
2 4
8
I want to scan the columns and then Add them into an Array or Collection, but problem is I don't know beforehand how much arrays I need , in this case it goes from A to Z , so I need then 26 arrays . So I have to create in the program 26 arrays how to do that or collections. i tried in a for loop dim array&i() as array but I get error message. Or do you need to make an array of an array, where array(1) points to ArrayA. Hope somebody can help.
at 5:25 in the UseCustomers sub, after you declare and set the variable O to clsCustomer, you type o. and intellisense displays the headings defined in clsCustomer. When I type o. intellisense doesn't seem to be working. What might I be doing wrong. (PS I am loving your series of CZcams contributions on VBA.)
Of Intellisense isn't working it's normally because there is an error in your code or a variable isn't declared correctly. Use debug.compile to remove errors and try again.
@@Excelmacromastery You hit the nail on the head. After your quick and timely response, I noticed that I left public off of two of the declarations. correcting that solved the propblem. Thanks so much for being willing to help out an older newbie.
i tried using the collection function below which is useful for me as currently i am using a long array formula to get the result but when i run the below with different module it give me a correct result but when i run it in a cell as =abc(2) it gives #Value error
Function abc(x As Variant)
Dim coll As New Collection
Dim Rg As Range
Set Rg = Worksheets("Sheet1").Range("A1").CurrentRegion
Dim i
For i = 1 To Rg.Rows.Count
If Rg.Cells(i, 1).Value = "Sales" Then
coll.Add Rg.Cells(i, 2).Value
End If
Next i
abc = coll(x)
End Function
Question: Given that Surname is a unique identifier, how can I refer to the item with Surname "Benton" without having to loop through the entire collection?
Solved it, the line to add the class object has to change to: coll.add customer, customer.Surname
This will add Surname as the key.
Glad you got it sorted.
Hi Paul Why use collection. Array do just the same.
Hi Ole. They are quite different as you can see in video on collection vs arrays in the collections playlist.
For example, an array requires code to resize but a collection does it automatically.
I agree, look at my comment.
Can we use user defined data types
instead of Class module?
You can but it's considered better practice to use Class Modules.
Hi Paul
This is great
it will make my coding life much easier
Just wondering for a simple example like this
Would it be better to use custom types and when would it be best not to
Normally we use classes when we are storing multipe pieces of data about something. For example a company, sales records, persons details.
Thank you for your top video', very clear explained.
I have a question? Is it possible to sum or substract collections?
For example: you have collection A = {1,2,3} and collection B = {2,3,5)
So, A-B is then {1}
or A intersection B is {2,3} or A union B = {1,2,3,5}
I wonder if VBA can do this? ..... maybe an idea for you next video?.... Tnx again
No. It can't do anything like this.
You have to write your own function for it.
Thank you very much, Paul. It's very much useful. I watch most of videos and learnt a lot from those. In addition, Could you share codes that can send messages from Excel VBA to WhatsApp number or group. Thank you
What is it with you and Jenny Jones
Very ignorant question here... why use "i" as a variable for row in the For Next Loop rather than use "r" as the Variable for Row?
Watt: It's not a rule. But it is a decades-old practice in numerous programming languages when defining and using loops.
The only ignorant question is the question that you didn't ask.
When writing out the results, instead of declaring a new class as per the video:
Dim customerOut As clsCustomer
For i = 1 to coll.Count
Set customerOut = Coll(I)
Sheet.cells(I, "H").Value = customerOut.Firstname
Why not just write:
For i = 1 to coll.Count
Sheet.cells(I, "H").Value = Coll(I).Firstname
To make the code clearer for less advanced users.
@@ExcelmacromasteryThanks