How to copy object collection data to worksheet?

The main topic seems to be really simple and obvious, but the truth is probably quite different than You could expect. In this article I want to show You how to copy object collection data into worksheet, but faster.

Background story

Of course, the best topics for articles are the real ones.
I had a collection, about 70k class objects and needed to write up every class object into the worksheet. Just to have a view of the inside, of the data.

So without further thinking I created simple loop For Each every class object of collection, take every property (using previously created function for that) and put inside worksheet cells. One after another.

It turns out, that simple loop took more than whole process of gathering the data, like about 10mins. What was kinda shock for me, but unfortunately in that moment I got no more time to think on this and move on.

After few days, having this issue in the back of my head, I decided to go back and speed up that last step. And this comes the part, where I want to show You the “recreation” of that case for You.

Simple & slow method: copy collection data to spreadsheet directly

Imagine the data – table with 4 headers and long till 10k row.

For the purpose of this article first of all I needed to create class object cls_abcdObject with 4 String properties – header1, header2, header3 and header4.

Option Explicit

Public header1 As String
Public header2 As String
Public header3 As String
Public header4 As String

Then I could create subroutine, new collection and start to loop from the second to the last row gathering the data for classes and put them into collection.

Option Explicit

Sub createCollectionAndWriteToExcelDirectly()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(1)
    
    Dim lastRow As Long
    
    With ws
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range(.Cells(2, 6), .Cells(lastRow, 9)).Clear
        
        Dim i As Long, abcd As cls_abcdObject, abcdCollection As Collection
        
        Set abcdCollection = New Collection
        
        For i = 2 To lastRow
            Set abcd = New cls_abcdObject
            abcd.header1 = .Cells(i, 1).Value
            abcd.header2 = .Cells(i, 2).Value
            abcd.header3 = .Cells(i, 3).Value
            abcd.header4 = .Cells(i, 4).Value
            
            abcdCollection.Add abcd, "abcdObject" & (i - 1)
        Next

After that it was the turn to loop For Each class object of collection, put the data into worksheet and measure the time of the process.

        Dim startTimer As Double
        
        startTimer = Timer
        i = 2
        For Each abcd In abcdCollection
            .Cells(i, 6).Value = abcd.header1
            .Cells(i, 7).Value = abcd.header2
            .Cells(i, 8).Value = abcd.header3
            .Cells(i, 9).Value = abcd.header4
            i = i + 1
        Next
        
        Debug.Print Format(Timer - startTimer, "0.00")
        
    End With

End Sub

The average time for this code and the collection of 10k objects was like ~2.6s.

Faster method: copy collection data using array

The second approach starts with the same code of collection creation.
But then I declared the same sized, as the data table, array and then put data of every class object into array “cells” instead of spreadsheets. Having complete array, I put the arrays value into same sized worksheet range.

Option Explicit

Sub createCollectionAndWriteToExcelUsingArray()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(1)
    
    Dim lastRow As Long
    
    With ws
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range(.Cells(2, 6), .Cells(lastRow, 9)).Clear
        
        Dim i As Long, abcd As cls_abcdObject, abcdCollection As Collection
        
        Set abcdCollection = New Collection
        
        For i = 2 To lastRow
            Set abcd = New cls_abcdObject
            abcd.header1 = .Cells(i, 1).Value
            abcd.header2 = .Cells(i, 2).Value
            abcd.header3 = .Cells(i, 3).Value
            abcd.header4 = .Cells(i, 4).Value
            
            abcdCollection.Add abcd, "abcdObject" & (i - 1)
        Next
        
        Dim startTimer As Double
        
        startTimer = Timer
        
        Dim tempTable As Variant
        ReDim tempTable(1 To lastRow - 1, 1 To 4)
        
        i = 1
        For Each abcd In abcdCollection
            tempTable(i, 1) = abcd.header1
            tempTable(i, 2) = abcd.header2
            tempTable(i, 3) = abcd.header3
            tempTable(i, 4) = abcd.header4
            i = i + 1
        Next
        
        .Range(.Cells(2, 6), .Cells(lastRow, 9)).Value = tempTable
        
        Debug.Print Format(Timer - startTimer, "0.00")
        
    End With

End Sub

It turned out, that the average time for the second approach, using array, is ~0.05s.

Comparison & winner

The second, not direct, method appears to be like 50 times faster than ‘shorter’ approach. In my real case (according the background story), instead like 10mins of additional work time of my tool, I achieved like 10seconds.

I was in shock. And proud so much… that I recreated the case for You and wrote this article. I knew already that arrays are really really fast, but still I was very surprised to achieve such result as I did!

Author: Tomasz Płociński

I'm very advanced in VBA, Excel, also easily linking VBA with other Office applications (e.g. PowerPoint) and external applications (e.g. SAP). I take part also in RPA processes (WebQuery, DataCache, IBM Access Client Solutions) where I can also use my SQL basic skillset. I'm trying now to widen my knowledge into TypeScript/JavaScript direction.

Leave a Reply

Your email address will not be published. Required fields are marked *