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)
NextAfter 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
