VBA: Difference in two ways of declaring a new object? (Trying to understand why my solution works)

Fink's answer gets your main problem right, which is that your first loop is adding multiple references to the same instance of 'clsMatch' to your collection. I'll just elaborate on why your fix works.

In VBA, a line like:

Dim c As New Collection

doesn't actually create a new collection. The 'Dim' statement is always just a declaration. Think of the 'As New' form as being shorthand for this:

Dim c As Collection'...'(later, when you're about to use 'c')If c Is Nothing Then    Set c = New CollectionEnd If'...

That is why destroying your reference by setting the variable that contained it to 'Nothing' was working. [NOTE: to whomever edited this to say "was not" - that changes the meaning of the answer and makes it incorrect. Please read the original question. The OP found that setting the variable to Nothing did work, and I was explaing why that was the case.] When the loop came back around to the 'oMatch.setLineNumber' line, VBA "helpfully" created a new instance of 'clsMatch' for your 'oMatch' variable to refer to, and then you got multiple different instances in your collection.

It would probably be better to do this explicitly:

Dim oMatch As clsMatch   For i = 0 To 10                    Set oMatch = New clsMatch                    oMatch.setLineNumber i                    oCollection.Add oMatch                Next  

Note that (unlike in C/C++ or ??.NET) it doesn't matter where the 'Dim' declaration goes. It's not being "executed" multiple times inside the loop, and the scope of what it declares is procedure-wide even though it appears inside the loop.

When your adding the oMatch object to the collection, its passing the variable By Memory Reference. When you are declaring oMatch again as a new clsMatch, its not destroying the first objects local memory pointer you had created. Its simply giving you the same local memory location as the first oMatch object you had created even though you have declared it as a new object. VBA uses ByRef as the default memory passing technique. The collection memory locations are then updated, both pointing to the same memory location, with the newly updated line number. Thus all collection memory pointers are going to point to the same last object you had created.

When you set oMatch = nothing, it resets the local memory pointer, and will create a new oMatch object with a new local memory pointer, and the collection's pointers will all point to their correct objects.

VBA's default memory passing is ByRef, as apposed to VB where the default is ByVal, so you might run into this caveat every now and again.

There is a valid use for "as new" within class modules. Consider this:

module a:

Dim mUbelow as myClassX       ' do not use "as new" here set mUbelow = new myClassX    ' mUbelow instanciation also instanciates subClass                               ' as a referencedClass object                              ' so you can not forget to do thismUbelow.subClass.someThing = "good news"  ' without the "as new" below: ==> error

class myClassX:

Public subClass as new referencedClass ' automatic instanciation of subclass:

class referencedClass:

Public someThing as string