Collections vs. Arrays
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Collections vs. Arrays
Hi,
In this post, Hans uses a variable declared as a New Collection to store (and then populate) a combo box control.
I have not seen this before and have usually used loops or Array variables to store multiple values.
Is this just another way to store multiple values for later use?
Is there any advantage to using a collection?
When would one use a collection over an array?
I'm just trying to get a feel for this new option.
TX
In this post, Hans uses a variable declared as a New Collection to store (and then populate) a combo box control.
I have not seen this before and have usually used loops or Array variables to store multiple values.
Is this just another way to store multiple values for later use?
Is there any advantage to using a collection?
When would one use a collection over an array?
I'm just trying to get a feel for this new option.
TX
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Collections vs. Arrays
A collection resembles a one-dimensional array somewhat, but there are some differences:
- Each item has a unique key.
- Items can be added anywhere in the collection (inserting an item in the middle of an array requires work).
- Items can be deleted from the collection.
The UserForm_Initialize code of the frmFind userform in the thread you refer to uses a collection object to create a list of unique values from a range with many duplicates. The line
col.Add Item:=.Range("A" & r).Value, Key:=.Range("A" & r).Value
adds the value of a cell to the collection col, and uses that value as the key of the new item. If the same value has already been added, an error occurs and the value is not added again. There is no error message because of the line
On Error Resume Next
above the loop.
This is a fairly common use of a collection. Creating a list of unique items in an array would require nested loops: for each potential new item, you'd have to loop through the array to check whether it already occurs. So that is much more work.
- Each item has a unique key.
- Items can be added anywhere in the collection (inserting an item in the middle of an array requires work).
- Items can be deleted from the collection.
The UserForm_Initialize code of the frmFind userform in the thread you refer to uses a collection object to create a list of unique values from a range with many duplicates. The line
col.Add Item:=.Range("A" & r).Value, Key:=.Range("A" & r).Value
adds the value of a cell to the collection col, and uses that value as the key of the new item. If the same value has already been added, an error occurs and the value is not added again. There is no error message because of the line
On Error Resume Next
above the loop.
This is a fairly common use of a collection. Creating a list of unique items in an array would require nested loops: for each potential new item, you'd have to loop through the array to check whether it already occurs. So that is much more work.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Collections vs. Arrays
Very nice...
I can see why/how that can be of use.
This is a very exciting find. TX for revealing this.
BTW: What is the significance of "New" (Obviously to create a New Collection). IOW: If I do not use New, what collection is used if I just say: Dim myC as Collection?
Also, does the collection get destroyed at End Sub, or must you clear it specifically to avoid hogging up memory (in larger projects)?
I can see why/how that can be of use.
This is a very exciting find. TX for revealing this.
BTW: What is the significance of "New" (Obviously to create a New Collection). IOW: If I do not use New, what collection is used if I just say: Dim myC as Collection?
Also, does the collection get destroyed at End Sub, or must you clear it specifically to avoid hogging up memory (in larger projects)?
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Collections vs. Arrays
A collection is an object. If you simply declare
Dim myC As Collection
the object myC is Nothing. You can't add anything to it. To initialize it, you can use
Set myC = New Collection
The line
Dim myC As New Collection
is shorthand for
Dim myC As Collection
Set myC = New Collection
When the code executes End Sub or Exit Sub, VBA will automatically destroy all non-static local variables, so it isn't necessary to use a line
Set myC = Nothing
above End Sub (although it doesn't hurt). If you wish to recover the memory used by a collection somewhere in the middle of a procedure, you can set it to Nothing there, of course.
Dim myC As Collection
the object myC is Nothing. You can't add anything to it. To initialize it, you can use
Set myC = New Collection
The line
Dim myC As New Collection
is shorthand for
Dim myC As Collection
Set myC = New Collection
When the code executes End Sub or Exit Sub, VBA will automatically destroy all non-static local variables, so it isn't necessary to use a line
Set myC = Nothing
above End Sub (although it doesn't hurt). If you wish to recover the memory used by a collection somewhere in the middle of a procedure, you can set it to Nothing there, of course.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Collections vs. Arrays
Excellent.
Appreciate the tuition...as usual!
Appreciate the tuition...as usual!
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- PlutoniumLounger
- Posts: 15655
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Collections vs. Arrays
I wouldn't presume to improve on Hans's Collection of tips-n-tricks, but I recall from my early days that I seemed to cause myself problems with collections when I was looping through them to delete (or move, or re-assign) them. deleting while looping forwards can cause short spells of head-scratching amazement, whereas looping backwards clears up many problems.Rudi wrote:Is there any advantage to using a collection?
When would one use a collection over an array?
HTH; I may have mis-understood your use of the term "collections"; and right now I can't come up with a concrete example of what I'm talking about (still on first coffee ...
I think a good programming definition of a collection would be "A (possibly unordered) set of things which share a common type of characteristic". By which definition a set of MP3 files can be a collection, as can a set of Documents or a set of Integers.
He who plants a seed, plants life.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Collections vs. Arrays
I will have to experiment with collections when the opportunity presents itself. Hans has clarified the difference quite nicely with the example he set forth and it is clear to me that they are similar but distinctly different and each have their own advantages. Its now just a matter of identifying when to use which in the best situation. Similar to an IF block and a Select Case block...
I like options, as long as they don't confuse me. I have this one wrapped (for now)!
I like options, as long as they don't confuse me. I have this one wrapped (for now)!
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Microsoft MVP
- Posts: 1320
- Joined: 24 May 2013, 15:33
- Location: Warminster, PA
Re: Collections vs. Arrays
Just to throw another idea on the pile, you may have worked with VBA collection objects before, without realizing it. Taking the Word object model as an example, built-in collections include the document's .Characters, .Words, .Paragraphs, .Sections, .InlineShapes, .Shapes and many others. Wherever you see an object type whose name is singular, and the same name as plural, the plural one is a collection. The "custom" Collection that Hans discussed generally works the same way as these built-in collections.
One specific advantage of a collection over an array is that the collection supports the For Each loop, which doesn't apply to the array.
A bit more about Chris's comment on deleting items from a collection in a loop: A loop requires a loop counter or index, and in each pass through the loop you use the index to identify which member of the collection you want to process. (In a For Each loop the index is hidden, but it's still used "behind the scenes".) If you delete the current item, VBA automatically reindexes all the items that follow it. Let's say you start with item 1 = A, 2 = B, 3 = C, and 4 = D, and you delete item 2. Then you're left with 1 = A, 2 = C, and 3 = D. The next time around the loop, the index is 3, so it will never process the item whose value is C. The correct way, as Chris said, is to run the loop For index = Collection.Count To 1 Step -1, so any reindexing affects only items that have already been processed.
By the way, some but not all of the built-in collections seem to have special coding in the Delete method that makes the For Each loop operate "correctly" and not skip items. I've never tried to pin down exactly which ones do or don't skip.
One specific advantage of a collection over an array is that the collection supports the For Each loop, which doesn't apply to the array.
A bit more about Chris's comment on deleting items from a collection in a loop: A loop requires a loop counter or index, and in each pass through the loop you use the index to identify which member of the collection you want to process. (In a For Each loop the index is hidden, but it's still used "behind the scenes".) If you delete the current item, VBA automatically reindexes all the items that follow it. Let's say you start with item 1 = A, 2 = B, 3 = C, and 4 = D, and you delete item 2. Then you're left with 1 = A, 2 = C, and 3 = D. The next time around the loop, the index is 3, so it will never process the item whose value is C. The correct way, as Chris said, is to run the loop For index = Collection.Count To 1 Step -1, so any reindexing affects only items that have already been processed.
By the way, some but not all of the built-in collections seem to have special coding in the Delete method that makes the For Each loop operate "correctly" and not skip items. I've never tried to pin down exactly which ones do or don't skip.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Collections vs. Arrays
Hi Jay, thanks for your useful additions!
One small correction: arrays support For Each too. The following code will run without error:
It will return
a
b
c
d
One small correction: arrays support For Each too. The following code will run without error:
Code: Select all
Sub Test()
Dim a(1 To 2, 1 To 2) As String
Dim v As Variant
a(1, 1) = "a"
a(2, 1) = "b"
a(1, 2) = "c"
a(2, 2) = "d"
For Each v In a
Debug.Print v
Next v
End Sub
a
b
c
d
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Collections vs. Arrays
Thanks for those pointers of clarification. I have learned the value of looping from the bottom up when it comes to processing and deleting rows in Excel and your explanations make a sense in the context of looping an "invisible" collection of values too. Appreciate the tuition.Jay Freedman wrote:A loop requires a loop counter or index, and in each pass through the loop you use the index to identify which member of the collection you want to process. (In a For Each loop the index is hidden, but it's still used "behind the scenes".) If you delete the current item, VBA automatically reindexes all the items that follow it. Let's say you start with item 1 = A, 2 = B, 3 = C, and 4 = D, and you delete item 2. Then you're left with 1 = A, 2 = C, and 3 = D. The next time around the loop, the index is 3, so it will never process the item whose value is C. The correct way, as Chris said, is to run the loop For index = Collection.Count To 1 Step -1, so any reindexing affects only items that have already been processed.
Many TX.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Collections vs. Arrays
Hans, your above example is very interesting as I see the For Each loop is looping through a string array.
I have always been under the impression that a For Each loop is designed for objects in a collection (only), and here you are using it with a string array...unless the array itself is seen as an object?? (Is my observation correct?)
TX
I have always been under the impression that a For Each loop is designed for objects in a collection (only), and here you are using it with a string array...unless the array itself is seen as an object?? (Is my observation correct?)
TX
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Collections vs. Arrays
No, I don't think an array is a kind of object - it's seen as a kind of collection. Or, stated in another way, For Each ... Next has been implemented for arrays as well as for collections.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Collections vs. Arrays
Noted... TX
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Microsoft MVP
- Posts: 1320
- Joined: 24 May 2013, 15:33
- Location: Warminster, PA
Re: Collections vs. Arrays
Somehow, in all the years since <mumble> that point has escaped my attention. The oldest reference I have available, the Office 2003 VBA help, does say "arrays and collections". Yes, I do learn something new almost every day!HansV wrote:One small correction: arrays support For Each too.
Oddly, the reference page for the same topic in VB.Net (http://msdn.microsoft.com/en-us/library ... 10%29.aspx" onclick="window.open(this.href);return false;) mentions only collections. I've never done much in VB.Net, preferring C# and C++.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Collections vs. Arrays
It mentions collections but it does show examples where it is looping through arrays of integers and strings...
Some very interesting code examples in this page. TX Jay!
Sample from the website above...
Some very interesting code examples in this page. TX Jay!
Sample from the website above...
Code: Select all
' Create lists of numbers and letters
' by using array initializers.
Dim numbers() As Integer = {1, 4, 7}
Dim letters() As String = {"a", "b", "c"}
' Iterate through the list by using nested loops.
For Each number As Integer In numbers
For Each letter As String In letters
Debug.Write(number.ToString & letter & " ")
Next
Next
Debug.WriteLine("")
'Output: 1a 1b 1c 4a 4b 4c 7a 7b 7c
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Microsoft MVP
- Posts: 1320
- Joined: 24 May 2013, 15:33
- Location: Warminster, PA
Re: Collections vs. Arrays
Rudi, read the following only if you don't mind being a bit confused by possibly irrelevant detail...Rudi wrote:Thanks for those pointers of clarification. I have learned the value of looping from the bottom up when it comes to processing and deleting rows in Excel and your explanations make a sense in the context of looping an "invisible" collection of values too. Appreciate the tuition.Jay Freedman wrote:A loop requires a loop counter or index, and in each pass through the loop you use the index to identify which member of the collection you want to process. (In a For Each loop the index is hidden, but it's still used "behind the scenes".) If you delete the current item, VBA automatically reindexes all the items that follow it. Let's say you start with item 1 = A, 2 = B, 3 = C, and 4 = D, and you delete item 2. Then you're left with 1 = A, 2 = C, and 3 = D. The next time around the loop, the index is 3, so it will never process the item whose value is C. The correct way, as Chris said, is to run the loop For index = Collection.Count To 1 Step -1, so any reindexing affects only items that have already been processed.
Many TX.
The For Each loop doesn't necessarily use a numeric index the way I described it, although the effect is the same.
A collection has an associated object called an enumerator, and the enumerator has a method called MoveNext. That method can be written (in this case by the developers at Microsoft who created VBA) in a general way or specially for each collection. When the For Each loop code calls the MoveNext method and passes in the current item, the return value is the "next" item -- whatever that means for the collection. It could be a numeric index, or the Key of an item, or a pointer to some object in memory. The special treatment for deletion of items in certain collections is done by writing a special MoveNext method.
There's some additional info in the Technical Implementation section of http://msdn.microsoft.com/en-us/library ... 10%29.aspx" onclick="window.open(this.href);return false; . That's in the reference for VB.Net, in which you could write your own enumerator for a custom collection.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Collections vs. Arrays
TX Jay... I am a sponge when it comes to new knowledge. I love absorbing facts and useful info.
Your insights and info can only enlighten me more, esp. when it comes to this topic...
Cheers
Your insights and info can only enlighten me more, esp. when it comes to this topic...
Cheers
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Collections vs. Arrays
Another area where I use a collection is when I have to keep multiple instances of a class in memory.
For instance when I add controls to a userform during runtime and their events are to be handled by a class module.
To make that work, I instantiate a copy of that class for every control I add and then add that instance to a module-level collection in the userform.
Once it is time to close the userform, you simply set the collection to nothing to ensure all instances of the class are destroyed.
Clear as mud?
For instance when I add controls to a userform during runtime and their events are to be handled by a class module.
To make that work, I instantiate a copy of that class for every control I add and then add that instance to a module-level collection in the userform.
Once it is time to close the userform, you simply set the collection to nothing to ensure all instances of the class are destroyed.
Clear as mud?
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Collections vs. Arrays
I have had VERY little exposure to class modules and can only theoretically comprehend your use of collections.
It certainly sounds like sound advice though.
TX JK
It certainly sounds like sound advice though.
TX JK
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- BronzeLounger
- Posts: 1545
- Joined: 26 Jan 2010, 11:36
- Location: Melbourne, Australia
Re: Collections vs. Arrays
It's probably just me, but I've always been disturbed at the idea of knowing an error is going to be thrown and pre-empting with a Resume Next.HansV wrote:If the same value has already been added, an error occurs and the value is not added again. There is no error message because of the line
On Error Resume Next
above the loop.
I developed this technique when working with the (Document) Variables collection in Word. It allows you to Add a Name,Value item to the collection without throwing an error. It achieves this by checking for an already existing collection member, without having an error thrown. It is part of a class encapsulation, so not all the variable names will make sense.
Code: Select all
Public Function Add(Name As String, Optional Value As String) As Boolean
'Adds a new docvar to the collection, optionally with a specified value;
'otherwise with a single space placeholder value.
'Does nothing if the docvar already exists. Used also by SetDV()
Dim var As Variable, num As Long
With m_docRef
For Each var In m_Vars
If UCase(var.Name) = UCase(Name) Then _
num = var.Index
'Exists = True: Exit Function
Next var
If num = 0 Then
m_Vars.Add Name:=Name, Value:=Value
Else
m_Vars.Item(num) = Value
End If
End With
Add = True
End Function
No error thrown throughout.
Alan