Populating a large spreadsheet table can be slow if we push data cell-by-cell. Here is a small example.
We start with creating a 1000x1000 table by directly accessing individual cells.
Private Sub run1()
This approach takes about 240 seconds on my machine.
A refinement is to turn off screen updating: Application.ScreenUpdating = False. This did not make a difference for me. Excel seems to turn off screen updating automatically after a little while.
A better approach is to create a VBA array and to write this array in one swoop to the spreadsheet. We use here a Variant array as it contains both strings and numbers. The code looks like:
Private Sub run3()
This code takes 0.46 seconds!
This strategy is also useful when calling Excel from other languages such as Delphi or C#.