title: How to make a function to return a list of objects in VBA
body: <p>I have a table, and I want to convert each row from that table in an object. 
  The object will have properties for each column. </p>
  
  <p>I made this function: </p>
  
  <pre><code>Public Function GetProducts() As Object
     Set GetProducts = New Collection
  
      Dim p As New ProductInfo
      Dim rng As Range
      Dim xRow As Range
  
      Set rng = Sheet2.Range("A2:I" &amp; Sheet2.Range("A2").End(xlDown).Row)
  
          For Each xRow In rng.Rows
  
              p.Id = xRow.Cells(1, 1).Value
              p.Cod = xRow.Cells(1, 2).Value
              p.Name = xRow.Cells(1, 3).Value
              p.productType = xRow.Cells(1, 4).Value
              p.norma = xRow.Cells(1, 5).Value
              p.masina = xRow.Cells(1, 6).Value
              p.masinaType = xRow.Cells(1, 7).Value
              p.operatori = xRow.Cells(1, 8).Value
              p.sectie = xRow.Cells(1, 9).Value
  
              GetProducts.Add Item:=p, Key:=CStr(p.Id)
  
          Next xRow
  
  End Function
  </code></pre>
  
  <p>than I tried to check the function with this Sub: </p>
  
  <pre><code>Public Sub CheckProducts()
      Dim products As Collection
      Dim p As ProductInfo
      Set products = GetProducts()
  
          For Each p In products
              MsgBox p.Id
          Next p
  
  End Sub
  </code></pre>
  
  <p>The msgbox is returning always 20 (I have 20 items in my table, and last ID is 20). </p>
  
  <p>When I checked the number of items in collection, I got 20, as I was expected. </p>
  
  <p>Can anyone help me understand why I cannot iterate the collection and get the id of each item? </p>