Excel VBA Introduction Part 37 - Custom Collections
ฝัง
- เผยแพร่เมื่อ 23 ก.ย. 2024
- If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link www.wiseowl.co... to make a donation. Thanks for watching!
You can buy our Introduction to Excel VBA book here www.lulu.com/s...
By Andrew Gould
www.wiseowl.co.uk - Excel VBA is full of collections and you can even create your own custom collections using the Collection object. This video teaches you how to create a new custom collection, how to add items to it, how to reference those items individually and how to loop over all of the items in the collection. You'll also see how you can add items based on a custom class module to a collection.
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
Great explanation, thank you Andrew
Thanks Janez! Glad you enjoyed it.
The best Tutorials for VBA
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
Good job!!
Best vba tutorial ever... More videos, pls...
Great work as usual. Thanks
Well explained ... Loved your series :)
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
excellent, thanks alot
You're very welcome, thanks for watching!
Awesome! Best series on youtube, and I've watched alot of them :D
I'm your fan Sir !!!!
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?
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?
@@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.
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
@@WiseOwlTutorials Thank you very much for your interest and quick response to our request and we are still learning a lot from you
well explained but can collection used to merge two duplicate records
Wonderful stuff.
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?
Agreed! Don't set it to Nothing if you need to do more stuff with it.
Great Work, Hope you the best
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.
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
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?
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
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
@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.
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!
@@WiseOwlTutorials ~ thanks for the reply and ... coincidentally enough, that is exactly the same answer I finally came up with! Worked great afterwards!
How to get values of rows in VBA
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.
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!
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!
+Joe M If anyone else has this issue, remember that a user-defined data type may not be added to a collection.
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
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.
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
@@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
@@JeffBradley-s2n My pleasure Jeff! Wait until you get to Office Scripts!
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
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
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
isn't the index thing always starts by zero ... !!!!!!!