缓慢的VBA宏写入单元格 [英] Slow VBA macro writing in cells

问题描述

我有一个VBA宏，将数据写入清除的工作表，但实际上很慢！

我正在从专业版实例化Excel。 / p>

 设置xlApp =新建Excel.Application 
 xlApp.ScreenUpdating = False 
 Dim NewBook As Excel.WorkBook 
 Dim ws As Excel.Worksheet 
设置NewBook = xlApp.Workbooks.Add（）
使用NewBook 
 .Title =SomeData
设置ws = NewBook.Worksheets。 Add（）
 ws.Name =SomeData
结束
 
 xlApp.Calculation = xlCalculationManual'我将此设置为手动
 
 RowNumber = 2 
一些随机的foreach循环
 ws.Cells（RowNumber，1）.Value =某些值
 ws.Cells（RowNumber，2）.Value =某些值
 ws。单元格（RowNumber，3）.Value =某些值
 ............... 
 ws.Cells（RowNumber，12）.Value =某些值
 RowNumber = RowNumber + 1 
下一个
  

我的问题是，foreach cycl e有点大最后，我会得到29000行。在一台漂亮的电脑上花费25分钟的时间。

有没有办法加快写入单元格？
我已经完成了以下操作：

  xlApp.ScreenUpdating = False 
 xlApp.Calculation = xlCalculationManual 
  

我以错误的方式引用单元格？可以写一整行，而不是单个单元格？

这会更快吗？

我已经测试了我的代码，foreach周期经历了很快我将这些值写入一些随机变量），所以我知道，写入单元格是一直需要的。

如果需要更多信息，代码snipplets请让我知道。

感谢您的时间。

解决方案

是否可能写入一行，而不是单个单元格？
会更快吗？

是和是。这正是你可以提高性能的地方。读/写细胞是非常慢的。您正在阅读/写入的单元格数量很少，而是对COM对象进行多少个调用。因此，请使用二维数组读取和写入您的数据。

以下是将MS Project任务数据写入Excel的示例过程。我嘲弄了一个29000个任务的日程安排，并在几秒钟内运行。

  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 
设置NewBook = xlApp.Workbooks.Add（）
与NewBook 
 .Title =SomeData
设置ws = NewBook.Worksheets.Add（）
 ws.Name =SomeData
结束
 
 xlApp.ScreenUpdating = False 
 Dim OrigCalc As Excel.XlCalculation 
 OrigCalc = xlApp.Calculation 
 xlApp .Calculation = xlCalculationManual 
 
 Const BlockSize As Long = 1000 
 Dim值（）As Variant 
 ReDim值（BlockSize，12）
 Dim idx As Long 
 idx = -1 
 Dim RowNumber As Long 
 RowNumber = 2 
 Dim tsk As Task 
对于每个tsk在ActiveProject.Tasks 
 idx = idx + 1 
值（idx，0）= tsk.ID 
值（idx，1）= tsk.Name 
'popul吃了其余的值
值（idx，11）= tsk.ResourceNames 
如果idx = BlockSize  -  1然后
与ws 
 .Range（.Cells（RowNumber，1 ），.Cells（RowNumber + BlockSize  -  1，12））Value = values 
结束
 idx = -1 
 ReDim值（BlockSize，12）
 RowNumber = RowNumber + BlockSize 
 End If 
 Next 
'写最后一个块
用ws 
 .Range（.Cells（RowNumber，1），.Cells（RowNumber + BlockSize  -  1 ，12））。值=值
结束
 xlApp.ScreenUpdating = True 
 xlApp.Calculation = OrigCalc 
 
 End Sub 
  

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.

解决方案

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

这篇关于缓慢的VBA宏写入单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！