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😄
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. You perhaps did not elaborate on this because VBA does not directly support the creation of a default member of a class. (You have to add an “Attribute Item.VB_UserMemId = 0” to the method as a compiler instruction which can only be done outside of the VBA Editor, and is not visible within it. See www.cpearson.com/excel/DefaultMember.aspx ). Best regards.
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.
@@RobertTodar You're welcome Robert! Interfaces has been on the list for a long time and I'm certain that I'll get around to it at some point. Thanks again for your input and for watching!
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 Worksheets("sheet1").range("a1").interior.color = 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 :-)
Hi! You need a class module called Film to do this. You might find the video on class modules useful - it's Part 35 in the Excel VBA Introduction playlist. I hope it helps!
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
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?
Thank you so much. This works great. Also, I'm able to vastly improve my Excel VB code. You've made a real difference. (I donated 10 £ to the cause. This has been well worth it.)
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
Hi Jay, The short answer is that yes, you could create a function (method) to return the Count. If you look at the Count member for (as far as I know) every existing VBA collection, you'll find that it's a property. If you think of existing VBA methods which return values (functions) their names tend to be verbs, implying that they perform an action and return a value or reference as a consequence of that action: Worksheets.Add - Creates a worksheet object and returns a reference to the created worksheet. Range.Find - Searches for a cell containing some information and returns a reference to the found range. Workbooks.Open - Opens a workbook and returns a reference to the opened file. This is opposed to properties whose names tend to imply that they will give you a piece of information and not much else: Worksheet.Name Range.Value Workbook.Path Count is annoying because you could treat the word as a verb or not but, as I mentioned, existing classes treat Count as a property. What makes designing classes so difficult is that there really isn't a set rule on how you do it - often you could use either a method or a property to achieve exactly the same end result. Imagine designing a generic class and you wanted to provide a way to alter the name of an object. You could use a property (as is the case for most existing classes): Object.Name = "some name" Or you could create a method with a parameter: Object.Rename "some name" Or you could create both and still have a fully-functioning class! The simplest advice I can give is look at existing classes in VBA and see how they do it.
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
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.
Collection Class Module: Option Explicit Private pPatients As Collection Public Sub add(Patient As O2Patient) pPatients.add Patient End Sub Public Property Get Count() As Long Count = pPatients.Count End Property Public Sub Remove(IndexOrName As Variant) pPatients.Remove IndexOrName End Sub Public Property Get Item(IndexOrName As Variant) As O2Patient Set Item = pPatients(IndexOrName) End Property Private Sub Class_Initialize() Set pPatients = New Collection End Sub
Class Module: Option Explicit Private pPatientName As String Private pLast4 As String Public Property Let PatientName(Text As String) Text = UCase(Text) Text = Replace(Text, "'", "") Text = Replace(Text, " ", "") Text = Replace(Text, ",", "") Text = Replace(Text, "-", "") pPatientName = Text End Property Public Property Get PatientName() As String PatientName = pPatientName End Property Public Property Let Last4(SSN As String) SSN = Right(SSN, 4) pLast4 = SSN End Property Public Property Get Last4() As String Last4 = pLast4 End Property
Regular Module: Sub TestClassCollection() Dim pColl As Collection Dim PSSNColl As Collection Dim p As O2Patient Dim strPatient As String Dim i As Long Dim n As Long Dim x As Long Set pColl = New Collection Set PSSNColl = New Collection Set p = New O2Patient With Sheet1 strPatient = "" For i = 2 To .Range("A1").CurrentRegion.Rows.Count If .Cells(i, 1) strPatient Then p.PatientName = .Cells(i, 1).Value p.Last4 = .Cells(i, 2).Value pColl.add p.PatientName PSSNColl.add p.Last4 strPatient = .Cells(i, 1).Value End If Next i End With With Sheet2 For i = 2 To .Range("A1").CurrentRegion.Rows.Count p.PatientName = .Cells(i, 1).Value p.Last4 = .Cells(i, 2).Value x = pColl.Count For n = 1 To x If InStr(1, pColl(n), p.PatientName) 0 And InStr(1, PSSNColl(n), p.Last4) 0 Then Debug.Print pColl(n), PSSNColl(n) pColl.Remove n PSSNColl.Remove n Exit For End If Next n Next i End With End Sub
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
![Noob To Max With DRAGON REWORK In Blox Fruits [FULL MOVIE]](http://i.ytimg.com/vi/LnBMOinoOvA/mqdefault.jpg)
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😄
Happy to hear that it helped you, thanks for watching!
Love it. In my opinion, this is the best VBA tutorial playlist ^_^
Couldn't agree more .... These are the best VBA tutorials that I've ever seen.... Thank you Very much!
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.
You perhaps did not elaborate on this because VBA does not directly support the creation of a default member of a class. (You have to add an “Attribute Item.VB_UserMemId = 0” to the method as a compiler instruction which can only be done outside of the VBA Editor, and is not visible within it. See www.cpearson.com/excel/DefaultMember.aspx ). Best regards.
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.
You could add Attribute Value.VB_UserMemId = 0 to set the item as the default method getting to use the shorthand syntax
Cool! Thanks for sharing that one Robert!
@@WiseOwlTutorials Hope you do a video on interfaces some day! Has always been a hard one to get my head around! =) Thanks for the videos!!
@@RobertTodar You're welcome Robert! Interfaces has been on the list for a long time and I'm certain that I'll get around to it at some point. Thanks again for your input and for watching!
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
Worksheets("sheet1").range("a1").interior.color = 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 :-)
How to work with collections and UDT?
Hi, in this video, how you have set Film as data types in variable declaration.
Hi! You need a class module called Film to do this. You might find the video on class modules useful - it's Part 35 in the Excel VBA Introduction playlist.
I hope it helps!
@@WiseOwlTutorials thanks
@@prasenjitmandal5486 You're welcome!
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
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?
Thank you so much. This works great.
Also, I'm able to vastly improve my Excel VB code. You've made a real difference.
(I donated 10 £ to the cause. This has been well worth it.)
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
Hi Jay,
The short answer is that yes, you could create a function (method) to return the Count. If you look at the Count member for (as far as I know) every existing VBA collection, you'll find that it's a property. If you think of existing VBA methods which return values (functions) their names tend to be verbs, implying that they perform an action and return a value or reference as a consequence of that action:
Worksheets.Add - Creates a worksheet object and returns a reference to the created worksheet.
Range.Find - Searches for a cell containing some information and returns a reference to the found range.
Workbooks.Open - Opens a workbook and returns a reference to the opened file.
This is opposed to properties whose names tend to imply that they will give you a piece of information and not much else:
Worksheet.Name
Range.Value
Workbook.Path
Count is annoying because you could treat the word as a verb or not but, as I mentioned, existing classes treat Count as a property.
What makes designing classes so difficult is that there really isn't a set rule on how you do it - often you could use either a method or a property to achieve exactly the same end result. Imagine designing a generic class and you wanted to provide a way to alter the name of an object. You could use a property (as is the case for most existing classes):
Object.Name = "some name"
Or you could create a method with a parameter:
Object.Rename "some name"
Or you could create both and still have a fully-functioning class!
The simplest advice I can give is look at existing classes in VBA and see how they do it.
@@WiseOwlTutorials thanks
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
Awesome video.....great learning.....thanks...
Superb
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
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.
Collection Class Module:
Option Explicit
Private pPatients As Collection
Public Sub add(Patient As O2Patient)
pPatients.add Patient
End Sub
Public Property Get Count() As Long
Count = pPatients.Count
End Property
Public Sub Remove(IndexOrName As Variant)
pPatients.Remove IndexOrName
End Sub
Public Property Get Item(IndexOrName As Variant) As O2Patient
Set Item = pPatients(IndexOrName)
End Property
Private Sub Class_Initialize()
Set pPatients = New Collection
End Sub
Class Module:
Option Explicit
Private pPatientName As String
Private pLast4 As String
Public Property Let PatientName(Text As String)
Text = UCase(Text)
Text = Replace(Text, "'", "")
Text = Replace(Text, " ", "")
Text = Replace(Text, ",", "")
Text = Replace(Text, "-", "")
pPatientName = Text
End Property
Public Property Get PatientName() As String
PatientName = pPatientName
End Property
Public Property Let Last4(SSN As String)
SSN = Right(SSN, 4)
pLast4 = SSN
End Property
Public Property Get Last4() As String
Last4 = pLast4
End Property
Regular Module:
Sub TestClassCollection()
Dim pColl As Collection
Dim PSSNColl As Collection
Dim p As O2Patient
Dim strPatient As String
Dim i As Long
Dim n As Long
Dim x As Long
Set pColl = New Collection
Set PSSNColl = New Collection
Set p = New O2Patient
With Sheet1
strPatient = ""
For i = 2 To .Range("A1").CurrentRegion.Rows.Count
If .Cells(i, 1) strPatient Then
p.PatientName = .Cells(i, 1).Value
p.Last4 = .Cells(i, 2).Value
pColl.add p.PatientName
PSSNColl.add p.Last4
strPatient = .Cells(i, 1).Value
End If
Next i
End With
With Sheet2
For i = 2 To .Range("A1").CurrentRegion.Rows.Count
p.PatientName = .Cells(i, 1).Value
p.Last4 = .Cells(i, 2).Value
x = pColl.Count
For n = 1 To x
If InStr(1, pColl(n), p.PatientName) 0 And InStr(1, PSSNColl(n), p.Last4) 0 Then
Debug.Print pColl(n), PSSNColl(n)
pColl.Remove n
PSSNColl.Remove n
Exit For
End If
Next n
Next i
End With
End Sub
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
Thanks for sharing that Tyler, I didn't know about that technique!