VBA在单元格中写入数据非常慢 [英] VBA to write data in cells very slow

问题描述

我编写了一个非常简单的脚本，该脚本将数据写入excel单元格.基本上，这是一个遍历数组的循环，它将数据写入特定的单元格或公式中.

I have written a very straightforward script which writes data into excel cells. Basically, this is a loop over an array and it writes data into specific cells or formulas.

问题在于脚本的这一部分非常慢.关于如何改善那个问题有什么想法吗?

The problem is that this part of the script is extremly slow. Any ideas on how to improve thath?

谢谢.

    For j = 0 To i - 1
    'Insère nouvelle ligne
    Rows(startRow & ":" & startRow).Select
    Selection.Copy
    Rows(startRow + 1 & ":" & startRow + 1).Select
    Selection.Insert Shift:=xlDown

    'Insère données
    If roomType(j) <> "" Then
        Feuil3.Cells(startRow, 1).Value = roomName(j)
        Feuil3.Cells(startRow, 2).Value = roomSurface(j)
        Feuil3.Cells(startRow, 7).Value = roomPeople(j)
        Feuil3.Cells(startRow, 12).Value = roomPeople(j)
        Feuil3.Cells(startRow, 5).Value = dict.Item(roomType(j))
        Feuil3.Cells(startRow, 3).Value = roomHeight(j)

        Feuil3.Range("F" & startRow).Formula = "=IFERROR(IF($E" & startRow & "=Data!$A$55,,ROUNDUP($B" & startRow & "/VLOOKUP($E" & startRow & ",Data!$A$3:$E$55,4,FALSE),0)),)"
        Feuil3.Range("H" & startRow).Formula = "=$C$25"
        Feuil3.Range("I" & startRow).Formula = "=IF($E" & startRow & "=Data!$A$55,$B" & startRow & "*$E$55,(MAX(F" & startRow & ",G" & startRow & ")*H" & startRow & "))"
        Feuil3.Range("N" & startRow).Formula = "=IFERROR(VLOOKUP($K" & startRow & ",$M$22:$O$26,3,FALSE),)"
        Feuil3.Range("O" & startRow).Formula = "=IFERROR(IF(ISBLANK(M" & startRow & ")=TRUE,L" & startRow & "*N" & startRow & ",L" & startRow & "*M" & startRow & "*N" & startRow & "),)"
        Feuil3.Range("Q" & startRow).Formula = "=MAX(I" & startRow & ",O" & startRow & ")"
        Feuil3.Range("T" & startRow).Formula = "=IFERROR(MAX(R" & startRow & ",S" & startRow & ")/(B" & startRow & "*C" & startRow & "),)"
    End If

    startRow = startRow + 1
Next j
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

推荐答案

将数组分配给一定范围的单元比循环中分配给单个单元更快.您可以尝试如下操作: Worksheets("MySheet").Range("A1:D100").Value = myArray .这是因为VBA和Excel之间的通信需要一些时间.这里有更多信息: VBA Excel大数据操作需要永远的时间

It is quicker to assign an array to a range of cells than to assign to individual cells in a loop. You could try sometihing like: Worksheets("MySheet").Range("A1:D100").Value = myArray. It is because communication between VBA and Excel takes some time. A little bit more on that here: VBA Excel large data manipulation taking forever

使用 Cells 而不是 Range 的速度快2.6倍.这是一个问题: Range()VS Cells()-运行时间

Also using Cells instead of Range is aboout 2.6 times faster. Here is a question on that: Range() VS Cells() - run times

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