Slow VBA macro writing in cells

Question

I have a VBA macro, that writes in data into a cleared out worksheet, but it's really slow!

I'm instantiating Excel from a Project Professional.

Set xlApp = New Excel.Application
xlApp.ScreenUpdating = False
Dim NewBook As Excel.WorkBook
Dim ws As Excel.Worksheet
Set NewBook = xlApp.Workbooks.Add()
With NewBook
     .Title = "SomeData"
     Set ws = NewBook.Worksheets.Add()
     ws.Name = "SomeData"
End With

xlApp.Calculation = xlCalculationManual 'I am setting this to manual here

RowNumber=2
Some random foreach cycle
    ws.Cells(RowNumber, 1).Value = some value
    ws.Cells(RowNumber, 2).Value = some value
    ws.Cells(RowNumber, 3).Value = some value
             ...............
    ws.Cells(RowNumber, 12).Value = some value
    RowNumber=RowNumber+1
Next

My problem is, that the foreach cycle is kinda big. At the end, I'll get around 29000 rows. It takes more than 25 minutes to do this on a pretty OK computer.

Are there any tricks to speed up the writing to the cells? I've done the following:

xlApp.ScreenUpdating = False
xlApp.Calculation = xlCalculationManual

Am I referencing the cells in a wrong way? Would it be possible, to write in a whole row, instead of the single cells?

Would that be faster?

I've tested my code, the foreach cycle goes through pretty quicky (i wrote the values into some random variables), so I know, that writing into the cells is what takes up all this time.

If you need further information, code snipplets please let me know.

Thank you for your time.

Do it like this `ws.Range(Cells(1, RowNumber), Cells(12, Number))=arr` where `arr` is an array of `some value` values e.g. `Dim arr(1 to 100) as Long`. Or if possible `ws.Range(Cells(firstRow, RowNumber), Cells(lastRow, Number))=twoDimensionalArray` — AnalystCave.com, Jun 16 '15 at 15:27
Might be worth thinking about implementing a progress bar. Well sie things down slightly, but give the user reassurance that the routine is progressing. — Andrew - Eversight Ltd, Jun 17 '15 at 05:36
I don't know if this applies to you, but for me I was setting `.Value = ""`. Each call was eating up 30-40ms (small, but put that in a loop of hundreds of items!). I changed it to `.ClearContents` and the call now takes 0ms. — Drew Chapin, Jan 30 '18 at 05:05

Rachel Hettinger · Accepted Answer · 2015-06-17T14:20:26.197

Would it be possible, to write in a whole row, instead of the single cells? Would that be faster?

Yes and yes. This is exactly where you can improve performance. Reading/writing to cells is notoriously slow. It matters very little how many cells you are reading/writing, but rather how many calls you are making to the COM object to do so. Therefore read and write your data in blocks utilizing two-dimensional arrays.

Here is an example procedure that writes MS Project task data to Excel. I mocked up a schedule with 29,000 tasks and this runs in a few seconds.

Sub WriteTaskDataToExcel()

Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
xlApp.Visible = True

Dim NewBook As Excel.Workbook
Dim ws As Excel.Worksheet
Set NewBook = xlApp.Workbooks.Add()
With NewBook
     .Title = "SomeData"
     Set ws = NewBook.Worksheets.Add()
     ws.Name = "SomeData"
End With

xlApp.ScreenUpdating = False
Dim OrigCalc As Excel.XlCalculation
OrigCalc = xlApp.Calculation
xlApp.Calculation = xlCalculationManual

Const BlockSize As Long = 1000
Dim Values() As Variant
ReDim Values(BlockSize, 12)
Dim idx As Long
idx = -1
Dim RowNumber As Long
RowNumber = 2
Dim tsk As Task
For Each tsk In ActiveProject.Tasks
    idx = idx + 1
    Values(idx, 0) = tsk.ID
    Values(idx, 1) = tsk.Name
    ' populate the rest of the values
    Values(idx, 11) = tsk.ResourceNames
    If idx = BlockSize - 1 Then
        With ws
            .Range(.Cells(RowNumber, 1), .Cells(RowNumber + BlockSize - 1, 12)).Value = Values
        End With
        idx = -1
        ReDim Values(BlockSize, 12)
        RowNumber = RowNumber + BlockSize
    End If
Next
' write last block
With ws
    .Range(.Cells(RowNumber, 1), .Cells(RowNumber + BlockSize - 1, 12)).Value = Values
End With
xlApp.ScreenUpdating = True
xlApp.Calculation = OrigCalc

End Sub

Really nice code sample. The only problem is, that this would only write in rows by the multiplies of 1000. So it would write in 29000 rows, but not 29300, because it will build 300 rows and the idx will not be equal with BlockSize - 1, so the last 300 will be dumped. — Laureant, Jun 17 '15 at 07:52
@Laureant Oops, forgot that part! The code has been updated--just needed to repeat the 3-line With ws statement after the For loop. — Rachel Hettinger, Jun 17 '15 at 14:22
Would this also work for "disconnected" cells, e.g. A3, A7, A13, ...? I have a list of cells as strings (think of "A3", "A7", "A13", ...) that I need to update. — Onur, Oct 12 '16 at 16:33
@Onur No, you cannot set values on a disjointed range with a single call as the `Value` property only applies to the first `Area` of the range object. — Rachel Hettinger, Oct 12 '16 at 19:00
@RachelHettinger Thanks for the information. I assume I have to stick with single value assignments then... — Onur, Oct 13 '16 at 11:24

score 4 · Answer 2 · answered Jun 16 '15 at 15:30

4

Do it like this:

ws.Range(Cells(1, RowNumber), Cells(12, Number))=arr

Where arr is an array of your some value values e.g.

Dim arr(1 to 100) as Long

Or if possible (even faster):

ws.Range(Cells(firstRow, RowNumber), Cells(lastRow, Number))=twoDimensionalArray

Where twoDimensionalArray is a 2 dimensional array of your some value values e.g.

Dim twoDimensionalArray(1 to [your last row], 1 to 12)  as Long

answered Jun 16 '15 at 15:30

AnalystCave.com

4,884
2
22
30

Rachel sample is great, but it reduces to this. Either for single row update or using a 2 dim array or a multi row update. Using blocks is more like a decoration that creates much noise for the same of an example making this a better answer. – Noman_1 Oct 06 '21 at 11:06
This answer is fine for moving moderate amounts of data to Excel. That was not the OP's question, however. Writing data in blocks is not merely decoration when the amount of data is large. In this case, writing all 29000 tasks at once takes twice as long as breaking it up into chunks. – Rachel Hettinger Oct 06 '21 at 14:56

score 3 · Answer 3 · answered Sep 05 '18 at 16:28

I was in a situation where I was populating huge table and I had to go cell by cell, then row by row. Painfully slow. I'm still not sure why but before my loop I added:

cells(1,1).select

(that is cell outside my table if that matters -idk) and speed was significantly improved. I mean from 10 minutes to about 30 sec. So if you are writing to cells in a table give it a go.

I should add that first thing I always do is disabling events, screen updates and switching to manual calculations. That did not helped before I tried this workaround

I just tested and it really works! Does anyone knows why? – Rafael Furquim Mar 30 '22 at 17:44 — Rafael Furquim, Mar 30 '22 at 17:44

score 2 · Answer 4 · edited Sep 19 '18 at 18:43

2

A previous answer mentions doing cells(1,1).select.

My suggestion is to do Worksheets("Sheet2").Activate before your update loop.

replace Sheet2 above with any sheet that isn't the one having cells updated. This results in a really substantial improvement.
even though you can set application displayupdating false, changing the activated sheet truly removes the overhead.

edited Sep 19 '18 at 18:43

Adil B

14,635
11
60
78

answered Sep 19 '18 at 18:15

watermelon nfo

29
4

Slow VBA macro writing in cells

4 Answers4

Linked