Pertanyaan Tulisan makro VBA lambat dalam sel


Saya memiliki makro VBA, yang menulis data ke dalam lembar kerja yang dihapus, tetapi sangat lambat!

Saya membuat Instanasi Excel dari Profesional Proyek.

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

Masalah saya adalah, bahwa siklus foreach agak besar. Pada akhirnya, saya akan mendapatkan sekitar 29.000 baris. Dibutuhkan lebih dari 25 menit untuk melakukan ini pada komputer yang cukup OK.

Apakah ada trik untuk mempercepat penulisan ke sel? Saya sudah melakukan yang berikut:

xlApp.ScreenUpdating = False
xlApp.Calculation = xlCalculationManual

Apakah saya merujuk sel dengan cara yang salah? Mungkinkah, menulis dalam satu baris penuh, bukannya sel tunggal?

Apakah itu lebih cepat?

Saya telah menguji kode saya, siklus foreach berjalan melalui cukup cepat (saya menulis nilai-nilai ke dalam beberapa variabel acak), jadi saya tahu, bahwa menulis ke dalam sel adalah apa yang diperlukan selama ini.

Jika Anda membutuhkan informasi lebih lanjut, snipplet kode tolong beri tahu saya.

Terima kasih atas waktu Anda.


4
2018-06-16 15:18


asal


Jawaban:


Mungkinkah, menulis dalam satu baris penuh, bukannya sel tunggal?   Apakah itu lebih cepat?

Ya dan ya. Di sinilah Anda dapat meningkatkan kinerja. Membaca / menulis ke sel sangat lambat. Sangat penting berapa banyak sel yang Anda baca / tulis, tetapi lebih banyak berapa banyak panggilan yang Anda buat ke objek COM untuk melakukannya. Oleh karena itu, baca dan tulis data Anda dalam blok menggunakan array dua dimensi.

Berikut ini contoh prosedur yang menulis data tugas MS Project ke Excel. Saya mengejek jadwal dengan 29.000 tugas dan ini berjalan dalam beberapa detik.

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

6
2018-06-16 23:55



Lakukan seperti ini:

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

Dimana arr adalah array dari Anda some value nilai mis.

Dim arr(1 to 100) as Long

Atau jika mungkin (lebih cepat):

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

Dimana twoDimensionalArray adalah array 2 dimensi dari Anda some value nilai mis.

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

3
2018-06-16 15:30



Saya berada dalam situasi di mana saya mengisi meja besar dan saya harus pergi sel demi sel, lalu baris demi baris. Sangat lambat. Saya masih tidak yakin mengapa tetapi sebelum loop saya, saya menambahkan:

cells(1,1).select

(Itu sel di luar meja saya jika itu penting-idk) dan kecepatannya secara signifikan ditingkatkan. Maksud saya dari 10 menit hingga sekitar 30 detik. Jadi jika Anda menulis ke sel di dalam tabel, coba saja.

Saya harus menambahkan bahwa hal pertama yang selalu saya lakukan adalah menonaktifkan acara, pembaruan layar, dan beralih ke perhitungan manual. Itu tidak membantu sebelum saya mencoba solusi ini


0
2017-09-05 16:28



Jawaban sebelumnya menyebutkan melakukan cells(1,1).select.

Saran saya adalah melakukannya Worksheets("Sheet2").Activate sebelum loop pembaruan Anda.

  • menggantikan Sheet2 di atas dengan sheet apa pun yang bukan sel yang diperbarui. Ini menghasilkan peningkatan yang sangat substansial.

  • meskipun Anda dapat mengatur tampilan aplikasi yang salah, mengubah lembar yang diaktifkan benar-benar menghapus biaya tambahan.


0
2017-09-19 18:15