Excel VBA Introduction Part 37 - Custom Collections

[@janezklun]

Great explanation, thank you Andrew

[@WiseOwlTutorials]

Thanks Janez! Glad you enjoyed it.

[@MrBebopbob]

Hi Andrew. Thank you very much for the excellent training videos. I have watched a dozen or so of your Excel VBA videos, and I am certain that the quality of these tutorials is much better than the material I have seen in other Excel VBA tutorials. Each subject is well thought out and methodically developed. Your explanation of the differences (e.g. early binding versus late binding, class fields versus properties, auto-instancing, etc.....) is awesome. I feel like I am getting a free lunch (and I know I should not complain). If you have a chance to create a tutorial (or 2) about Excel Tables and List Objects in VBA, I would be very interested (and grateful, as listobjects seems to be an area where only superficial information is available on-line). Meanwhile, I will continue studying your current library. I highly recommend these tutorials to anyone who wants professional concise (yet enjoyable) instruction. Thanks Again Bob

[@shailendranr2756]

The best Tutorials for VBA

[@phillipeharada1062]

Good job!! Best vba tutorial ever... More videos, pls...

[@BidurPokhrelpobitan]

Well explained ... Loved your series :)

[@ashrafkader1972]

excellent, thanks alot

[@WiseOwlTutorials]

You're very welcome, thanks for watching!

[@patricknyamu6110]

Great work as usual. Thanks

[@wattjock2405]

@20:42 ~ I have written this procedure and the Class Module "Item", to loop over a table of 3,135 items. The first column in the Table is [ID], it is filled numbers from 1 to 3,135. I used the following line ~ Items.Add I, I.ID ~ for the key and VBA returns an Error 13 Type Mismatch, however; when I use the forth column ~ Items.Add I.Description ~ it runs fine. Each record in the database has a unique # for it's ID. Is there something that I don't understand that is preventing me from using the I.ID as the Key for the Items Collection? Do I need to convert the ID # to a string using Cstr(r.value)? Coincidentally, I get the same Error 13 when using the films.add f,FilmID line in the Top Movies 2012 example in the video.

[@WiseOwlTutorials]

Hi there! Yes that's correct: the Key must be a string. It's a little unusual that VBA isn't prepared to perform the implicit type conversion that it's happy to do in so many other places but a quick addition of the CStr function solves that as you suggested: Films.Add f, CStr(f.FilmID) docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/add-method-visual-basic-for-applications I hope that helps!

[@wattjock2405]

@@WiseOwlTutorials ~ thanks for the reply and ... coincidentally enough, that is exactly the same answer I finally came up with! Worked great afterwards!

[@tugaric]

Awesome! Best series on youtube, and I've watched alot of them :D

[@DagarArun]

I'm your fan Sir !!!!

[@BaneDataSolutionsLtd]

Wonderful stuff.

[@emailuznow]

Thank you I'm still slightly unsure. Maybe in your next video you can hopefully try to explain that bit as seeing visually will clear things up more. Your videos are awesome

[@wattjock2405]

After running a Sub that uses the Public Films Collection, would you necessarily not want to set Films=Nothing to free the memory? I think it would depend on if you are going to have other Sub Routines do more to the Films Collection than just what that one Sub Procedure does, yes?

[@WiseOwlTutorials]

Agreed! Don't set it to Nothing if you need to do more stuff with it.

[@alberthema]

Great Work, Hope you the best

[@alberthema]

I did a class with properties as arrays Private pPosition() As Double Private pVelocity() As Double Private pSelfBest() As Double Public Property Let Position(Value() As Double) pPosition = Value End Property Public Property Let Velocity(Value() As Double) pVelocity = Value End Property Public Property Let SelfBest(Value() As Double) pSelfBest = Value End Property Public Property Get Position() As Double Position = pPosition End Property Public Property Get Velocity() As Double Velocity = pVelocity End Property Public Property Get SelfBest() As Double SelfBest = pSelfBest End Property But when I'm trying to initiate this proprieties in the module , I don't find a pop up list of such property . Can you help me find the reason, I will be grateful.

[@alberthema]

It works, Many thanks. the resize of the array after added to a collection will be the same or there is any notes. Allah Teach you as you teach us

[@emailuznow]

Thank you Andrew Does this mean by returning as TEAM you can only access properties and methods of the TEAM class or does it mean you can still reference other classes. The reason i ask is because with typed collection you can only put in properties and methods from the class/object passed in Is this the same case as when using the property get where you can only get properties n methods from TEAM when returning As Team? And am i right in saying if i return as an object for the get property then i would have to use Set Ie Public property get item(value as variant) as TEAM SET item = End property

[@emailuznow]

Hi, great tutorials Why are you setting f to nothing and creating new instance each time in the loop? What will happen without creating instance each time?

[@sriram1701]

well explained but can collection used to merge two duplicate records

[@emailuznow]

Thank you When you say public property get teamagent() as agent What does (returned as agent) mean? Ie as string makes the result value a string or if defined as long then returns long. This is returning a class/object agent. Does this mean its going to return an object or does this mean it can return any of the properties or property types as the result

[@دراساتاجتماعية-ز9ل]

Thank you for all you provide us with valuable information and excellent lessons that we can benefit from in our lives... There is a question that I have been very tired of finding solutions to... How do I make an array of tables with different names in different sheets?

[@WiseOwlTutorials]

Hi! What are you trying to do exactly? Do you mean that you want to create an array and populate it with existing table (ListObject) objects? Or do you want to create tables from existing data on your worksheets?

[@دراساتاجتماعية-ز9ل]

@@WiseOwlTutorials 5 sheets in each sheet Excel Table ,every excel table is all in the same design I want to sort all the tables at the same time with one button using VBA.

[@WiseOwlTutorials]

OK, you don't need an array for that. You can loop through the Worksheets collection, on each worksheet you can refer to the ListObjects collection to return a reference to your table and then use properties of the Sort object to apply your sorting. Here's a basic example: Sub SortTables() Dim ws As Worksheet Dim tbl As ListObject For Each ws In ThisWorkbook.Worksheets If ws.ListObjects.Count > 0 Then Set tbl = ws.ListObjects(1) With tbl.Sort .SortFields.Clear .SortFields.Add _ Key:=ws.Range("B1"), _ Order:=xlAscending .Apply End With End If Next ws End Sub

[@دراساتاجتماعية-ز9ل]

@@WiseOwlTutorials Thank you very much for your interest and quick response to our request and we are still learning a lot from you

[@stephenhammond1745]

I've just started using collections with classes. I think I saw some video where they defined the collection inside the class module and also created an "Add" method. What would be the advantage, if any, of doing it that way? Would the collection be Public or Private? I assume Private which is why you need to define an Add method but I can't find the video again or anything online about doing this.

[@WiseOwlTutorials]

Hi Stephen, Ordinarily you'd do this if you wanted to control the type of object that you can add to the collection. The custom Add method would restrict the type it accepts. The collection variable would be private so the class module would need to expose properties to allow access to its items. Hope it helps!

[@emailuznow]

Hi Your videos are awesome Is there any chance you could do a video where you use windows API? The codes involving API looks complicated but hopefully you could do a video on this Many thanks

[@emailuznow]

Sorry im not sure what you mean. What i meant was each time you looped over the collection, you set f to nothing. Why did you not just set f to nothing after the loop had finished rather than creating an instance within each loop? Sorry to ask question but im just trying to fully understand it as normally i just set the objects to mothing right at the end of my code

[@JeffBradley-s2n]

One thing I miss in Excel VBA that was really good in Visual Basic 6 are control arrays. I feel it should be possible to emulate them using control collections but can't see how.

[@WiseOwlTutorials]

Hi Jeff! I had to read up on control arrays as I wasn't familiar with the topic. I don't think that there's a UI way to create them in VBA but you can make your own collections of controls programmatically. Here's a basic example: Option Explicit Private TxtBoxCollection As New Collection Private Sub CommandButton1_Click() Dim ctl As Control For Each ctl In TxtBoxCollection ctl.BackColor = rgbRed Next ctl End Sub Private Sub UserForm_Initialize() TxtBoxCollection.Add UserForm1.Controls("TextBox1") TxtBoxCollection.Add UserForm1.Controls("TextBox2") TxtBoxCollection.Add UserForm1.Controls("TextBox3") End Sub

[@JeffBradley-s2n]

@@WiseOwlTutorials - thanks for the reply. I was beginning to suspect this was the case after beating my head against it for a while !! Just like to thank you for an awesome collection of training videos which have managed to drag this crusty old VB6 amateur programmer kicking and screaming into the 2020's

[@WiseOwlTutorials]

@@JeffBradley-s2n My pleasure Jeff! Wait until you get to Office Scripts!

[@emailuznow]

Hi i have a few Qs and i wanted some clarification to see if i was on the right track 1) With custom classes (say i have a class called teams) with properties and methods called Name, Age and method (adddept) and a Typed collection class called addteams. Am i right in saying that if i pass in the object ( teams) to the collection addteams, that would mean i could only pass in properties and methods of the team classs to this collection? ie Name, Age and adddept and cannot be anything else? 2) With properties and methods. With properties of an object, am i right in saying this will always be = to something i.e it can only be sheet.1. Value = "a" or a = sheet1.value what about with methods because you could have worksheets.add or sheets1.move (to somewhere). i ask this because in a collection as a key, can you only pass in a property of an object or method also? 3) how can i create sub classes so if i wanted to create something like this team.agent.name = something rather than team.name = something so you could have a name assigned to agent also

[@krishnad2348]

How to get values of rows in VBA

[@joem8251]

Thank you, wiseowl... thank you. Remember that a User Defined Data Type (UDT) cannot be added to a collection. When I pass a UDT into a collection, I get error message: "Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions" after executing: myCollection.Add myUDT, where myCollection is a new Collection I try and add one UDT to and myUDT is defined using a function. The UDT is public and defined above the sub which executes the line which generates an error (above). To help clarify, the structure of my code looks like: Public Type myUDT X As Integer Y As String Z As Currency End Type Public Sub MYSUB() Dim ThisUDT As myUDT Dim myCollection As Collection Set myCollection = New Collection ThisUDT = myFunction() myCollection.Add ThisUDT End Sub Public Function myFunction() As myUDT Dim X_UDT As myUDT ' ' some code to define X_UDT elements X_UDT.X, X_UDT.Y, X_UDT.Z... ' myFunction = X_UDT End Function This post has been edited (but left rather than deleted) in order to help anyone else that may have had a similar problem I had. This issue was resolved by building a class similar to my UDT. In any case, I have enjoyed your videos very much!

[@joem8251]

+Joe M If anyone else has this issue, remember that a user-defined data type may not be added to a collection.

[@emailuznow]

Re: creating instances Im aware of how the instance works but sorry still unsure why its created in the loop and not out Ie Set f = new Film For i = 1 to 20 F.title Films.add Next i Set f = nothing

[@shahimkhlaifat]

isn't the index thing always starts by zero ... !!!!!!!