Excel VBA Introduction Part 38 - Typed Collections

By Andrew Gould

[giangpham]

Love it. In my opinion, this is the best VBA tutorial playlist ^_^

[CowboyCola]

Very good video, I even strengthened my learning of other languages like c# by watching this video. This video made me learn more about the underlying workings of the program😄

[brianbrill]

Couldn't agree more .... These are the best VBA tutorials that I've ever seen.... Thank you Very much!

[sakmsb]

I'm really excited to see new videos on VBA. I have seen each video of yours in VBA, SQL, SSRS.

I recommend everyone to watch your videos if they need to learn to code. 

Hope to see more.

I'm awaiting your videos on advance array in VBA and C++

God bless you and Thanks for all awesome videos.

[devexpost]

Hi Andrew,
You are a very generous Training God. Thanks immensely.
I suspect that this video starting at 13:45 needs one clarification however... I believe your 14:45 statement that, with Custom Collections you “lose the ability to reference an Item using the shorthand method” is not strictly speaking correct. It’s not that you “lose” that ability; it’s just that you haven’t set it up.

I believe the shorthand ability of “Item” in the generic Collection class is a matter of it being defined as the “default member” of the class. If you define a member of a Custom Collection class as the default member, then the same “shorthand” functionality would be available.

[RobertTodar]

You could add Attribute Value.VB_UserMemId = 0 to set the item as the default method getting to use the shorthand syntax

[emailuznow]

Hi Andrew i think iv figured it out now

The syntax i think that is correct after watching it again would be

Set f = Films("TMNT") 'set a ref to TMNT using the key

Or to set another value

Fims("TMNT").genre = scifi

What would be awesome is a vid on objects within objects like how the we have in vba with worksheets and range etc (these are objects which have collections and will be so useful going forward

Like

= vbred

This has

This gives a ref to sheet1 from the worksheets collection then
Accesses a1 object (ref to a1) from the range collection then
Accesses the interior object which has a collection also and then accesses the color property and set to red.

Similarly it would be the same or practical to do this with our own custom class

Ie

Teams collection which holds teams
Add players to players collection and also assign to a team etc

So

Set t = cTeams(teamname)
Set p = t.addplayer(playername)

So add teams to teams dictionary
Add players to players dictionary
Add player to team also

These are the kind of things that will be so useful to understand accessing multiple classes, collections, properties etc and adding to an object within an object
Really look forward to your response

Your videos are awesome and easy to follow and your a neighbour also :/) from derby :-)

[prasenjitmandal]

Hi, in this video, how you have set Film as data types in variable declaration.

[orbitaldynamicsla]

Thanks so much for this video (and the other 37+).

I created a typed collection but have noticed that I can't loop through the typed collection with a "For Each" loop.

Is there a way I can make this work?

[jayjayf]

Can't you use get count in a function as apposed to using it in a property, the reason I ask is I am still ambiguous of when/what situations to use either functions, properties or subs in class modules

[emailuznow]

Hi i know how reference an item but how do i add to it?

So how do i add to this collection that has the key guardians of the galaxy

[emailuznow]

Hi Andrew

Once again thank you

I have a couple of more questions

In the example where you create a loop to add properties and then add that instance and the create a new instance to a collection

Say i didnt add values to all the properties and i wished to do so after, can i do that?

Ie

Dim clsfilms as films
Dim f as film

Set clsfilms = new films

For i = 1 to 5
Set f = new film
f.title = sheets("sheet1").cells(i, 1).value
f.length = sheets("sheet1").cells(i, 2).value
clsfilms. Add (f 'item/instance), f.title 'key)
Next i

'More code

Now say i wanted to add the genre now to the key guardians of the galaxy, can i add an extra item now

'So

f.genre = scifi but this should be added to the guardians of the galaxy key

Im not sure how to do this

Im thinking

Set fkey = clsfilms("guardians of the galaxy")

fkey.genre = scifi but this dont work

What would be awesome also if you could do a more complex vid which involved class within a class
Like
Premleague.team.player etc

[emailuznow]

Sorry Andrew i was using a dictionary instead of a collection
So set t = cteams("teamname")
Would be using the key from dictionary
I guess if i was using a collection i would use
Set t = cteams.item("teamname")?
Is that right

[data-science-ai]

So first of all, thank you. I am however experiencing a problem. I am attempting to add a number of items to my collection using a for loop. When I try to reference any of the items in the collection using the .item(#) method, it returns the last item in the collection.

[AttackOnTyler]

I know this video is 8 years old now but along with the default way of interacting with collections using coll() opposed to coll.item(), you lose the for each item in coll: next item syntax.

adding the Attribute Value.VB_UserMemId = 0 as @RobertTodar mentioned may work

I've made a Items property which returns the private collection maintaining the custom typed add and item as a workaround