缓慢的VBA宏写入单元格 [英] Slow VBA macro writing in cells
问题描述
我正在从专业版实例化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屋!