Q:

vba write data into listobject from collection (performance)

I am encountering low performance with writing data into a table (listobject)
I have a collection of objects and want to write them row by row into the table
For Each msrecord In mls.mlRecords
    Dim oNewRow As ListRow
    Set oNewRow = Sheets(mls.name).ListObjects(1).ListRows.Add(AlwaysInsert:=True)
    oNewRow.Range.Cells(1, 1).Value = msrecord.id
Next msrecord

However this is too slow. I tried Application.ScreenUpdating = False with not success.
My idea is to fill an array first and then write it all at once into the table.
Dim outputArray As Variant

Dim counter As Long
For counter = 1 To mls.mlRecords.Count

    outputArray(counter, 1).Value = msrecord.id
    outputArray(counter, 2).Value = msrecord.name
    outputArray(counter, 3).Value = msrecord.morefields

 Next

 Sheets(mls.name).ListObjects(1).DataBodyRange.Value = outputArray

However, I now run into type mismatch error when assining values in the variant array. I can't really ReDim the array since I have a collection to start with.
Any thoughts? Does it make sense to build up the array first? How can I efficiently write data into table (listobjects)?

A:

Your variable is not an array until it you call Redim. Prior to that it is Empty which is the default value of a Variant. Try adding this before your loop.
ReDim outputArray(1 to mls.mlRecords.Count, 1 to 3)

