VBA: Difference in two ways of declaring a new object? (Trying to understand why my solution works)
- by Matt
I was creating a new object within a loop, and adding that object to a collection; but when I read back the collection after, it was always filled entirely with the last object I had added. I've come up with two ways around this, but I simply do not understand why my initial implementation was wrong.
Original:
Dim oItem As Variant
Dim sOutput As String
Dim i As Integer
Dim oCollection As New Collection
For i = 0 To 10
Dim oMatch As New clsMatch
oMatch.setLineNumber i
oCollection.Add oMatch
Next
For Each oItem In oCollection
sOutput = sOutput & "[" & oItem.lineNumber & "]"
Next
MsgBox sOutput
This resulted in every lineNumber being 10; I was obviously not creating new objects, but instead using the same one each time through the loop, despite the declaration being inside of the loop.
So, I added Set oMatch = Nothing immediately before the Next line, and this fixed the problem, it was now 0 to 10. So if the old object was explicitly destroyed, then it was willing to create a new one? I would have thought the next iteration through the loop would cause anything declared within the loop do be destroyed due to scope?
Curious, I tried another way of declaring a new object: Dim oMatch As clsMatch: Set oMatch = New clsMatch. This, too, results in 0 to 10.
Can anyone explain to me why the first implementation was wrong?
