Geek Answers Handbook

Slow VBA macro in cells

I have a VBA macro that writes data to a cleaned worksheet, but it is very slow!

I am creating Excel from 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 loop is very large. In the end, I will take 29,000 rows. It takes more than 25 minutes to do this on a pretty good computer.

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

xlApp.ScreenUpdating = False
xlApp.Calculation = xlCalculationManual

Am I referencing cells incorrectly? Is it possible to write an entire row, rather than individual cells?

Will it be faster?

I tested my code, the foreach loop runs pretty fast (I wrote values ​​to some random variables), so I know that writing to cells is something that takes all this time.

If you need more information, code snippets, please let me know.

Thank you for your time.

+4
2

, ? ?

. , . / , , . , /, , COM-, . .

, MS Project Excel. 29 000 , .

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
+5

:

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

arr - some value, .

Dim arr(1 to 100) as Long

, ( ):

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

twoDimensionalArray - 2- some value, .

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

More articles:


All Articles