I wouldn't presume to improve on Hans's CollectionRudi wrote:Is there any advantage to using a collection?
When would one use a collection over an array?
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. 
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
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.
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.
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
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.
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.
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