title: How to write a VBA collection to an Excel sheet
body: <p>I have some existing code that I am modifying.  This code creates a collection of rows from preexisting worksheet tables.  It creates a large 2-D collection, with distinct information in each column.  There is a separate class module that declares the data type for each column.</p>
  
  <p>The code writes the 2-D collection to a new sheet by looping through each item in turn.  I have never used a collection before, and would like to write the collection to the sheet in a single pass.  The current code takes quite a long time when the table has lots of records.</p>
  
  <p>Is there a way to convert the entire collection to a 2-D array, or so that I can then write the 2-D array in a single go?  Or is there a way to write the entire collection to the sheet, just like with a 2-D array?  I have tried to search for this and have so far been unsuccessful.  Any general points would be appreciated!</p>
  
  <p>Here is some example code, with comments in bold, to illustrate how the collection is being used.</p>
  
  <p><strong>Define the Class Module, Named as TableEntry</strong></p>
  
  <pre><code>Public Item1 As String
  Public Item2 As String
  Public Item3 As String
  Public Item4 As Integer
  Public Item5 As Integer
  </code></pre>
  
  <p><strong>Main Routine - Create the Collection, Fill the Collection, Write Collection to Sheet</strong></p>
  
  <pre><code>Sub MainRoutine()
  
  Dim table As Collection
  Set table = New Collection
  
  Call FillCollection(File As String, ByRef table As Collection)
  
  Call WriteCollectionToSheet(ByRef table As Collection)
  </code></pre>
  
  <p><strong>Sub Routine 1 - Fill the Collection</strong></p>
  
  <pre><code>Dim wb As Workbook
  Set wb = Workbooks.Open(File)
  
  Dim ws As Worksheet
  For Each ws In ActiveWorkbook.Worksheets
  
  Dim R As Range
  Set R = ws.Range("A2")
  
    Dim e As TableEntry
    For i = 1 To 20
  
      Set e = New TableEntry
  
      e.Item1 = R.Offset(i + 1, 0).Offset(0, 0)
      e.Item2 = R.Offset(i + 1, 0).Offset(0, 1)
      e.Item3 = R.Offset(i + 1, 0).Offset(0, 2)
      e.Item4 = R.Offset(i + 1, 0).Offset(0, 3)
      e.Item5 = R.Offset(i + 1, 0).Offset(0, 4)
  
      table.Add e
  
    Next i
  
  Next ws
  </code></pre>
  
  <p><strong>Sub Routine 2 - Write Collection to Sheet</strong></p>