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

by

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 Collection
End 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.

More Related Contents:

  1. What are the rules governing usage of parenthesis in VBA function calls?
  2. How does one decompile and recompile a database application?
  3. Autonumber value of last inserted row – MS Access / VBA
  4. Code to loop through all records in MS Access
  5. Bang Notation and Dot Notation in VBA and MS-Access
  6. How do I force VBA/Access to require variables to be defined?
  7. How to diagnose MS access crashes
  8. Equivalent cURL in VBA?
  9. How do I access the selected rows in Access?
  10. Run-time error ‘3061’. Too few parameters. Expected 1. (Access 2007)
  11. Prevent enabling of shift bypass – Access 2010
  12. VBA procedure to import csv file into access
  13. Transform multiple rows into columns with unique key
  14. how to show query results in a datasheet in the same form?
  15. Access VBA: Find max number in column and add 1
  16. MS Access prepared statements
  17. Create sequential ID value based on the year that a record is added
  18. UrlDownloadToFile in Access 2010 – Sub or Function not Defined
  19. Null values for variables in VBA
  20. Using a centralized VBA module in multiple Access databases
  21. How to execute a query in ms-access in VBA code?
  22. How to add a menu item to the default right click context menu
  23. VBA – Run Time Error 3271 using DAO object
  24. Trouble with InputBoxes
  25. Get Name of Current VBA Function
  26. What does the Call keyword do in VB6?
  27. Is there any way to create multiple insert statements in a ms-access query?
  28. Working with Unicode file names in VBA (using Dir, FileSystemObject, etc.)
  29. Custom row source for combo box in continuous form in Access
  30. Is it possible to convert VBA to C#?

Leave a Comment