CODEWITHSUNDEEP
vbacollections
Darren
Darren

Reputation: 793

Adding objects to Collection adds only the most recent instance over and over

When I execute the following code, everything works as expected except the very last line within the loop body. While this does add a "filmSheet" object to the Collection, it fills it only with the very last filmSheet object that was created, over and over. Is this because all of the previous filmSheet objects are destroyed each time the code exits and reenters the loop? It is really odd, though, because it is as if it is overwriting all of the previous filmSheet objects with the very last one. Anyone have some insight into what is going on here and how it can be corrected?

By the way, a filmSheet object is basically a wrapper class for an Excel worksheet object with some additional functionality.

For Each genre In genreCollection
    Dim tempFilmSheet As New filmSheet

    Call tempFilmSheet.AddSheetToWorkbook(wb)
    Call tempFilmSheet.SetGenre(genre)
    Call tempFilmSheet.CopyFilmListRangeToSheet(originalDataSheet)
    Call tempFilmSheet.DeleteUnrelatedRows
    Call tempFilmSheet.CountFilmsInGenre
    totalFilmCount = totalFilmCount + tempFilmSheet.filmCount
    filmSheetCollection.Add tempFilmSheet
Next

Upvotes: 0

Views: 36

Answers (1)

Matteo NNZ
Matteo NNZ

Reputation: 12655

You need to declare the variable only once, but then using the keyword Set to assign the same variable to a new filmSheet instance reference each time:

Dim tempFilmSheet As filmSheet 'declare variable only once 

For Each genre In genreCollection

    Set tempFilmSheet = New filmSheet 'instantiate n-times    
    Call tempFilmSheet.AddSheetToWorkbook(wb)
    Call tempFilmSheet.SetGenre(genre)
    Call tempFilmSheet.CopyFilmListRangeToSheet(originalDataSheet)
    Call tempFilmSheet.DeleteUnrelatedRows
    Call tempFilmSheet.CountFilmsInGenre
    totalFilmCount = totalFilmCount + tempFilmSheet.filmCount
    filmSheetCollection.Add tempFilmSheet

Next

To better undestand the problem, you might want to read the difference between declaring an object with Dim and setting an object to an object reference with Set.

Upvotes: 1

Related Questions