Excel VBA：将数组写入单元格非常慢 [英] Excel VBA: Writing an array to cells is very slow

问题描述

我正在使用Excel中的VBA从路透3000数据库中检索一些信息。我检索的数据是一个二维数组，由一列保存日期和其他列保存数值组成。

I am working with VBA in Excel to retrieve some information from the Reuters 3000 Database. The data I retrieve comes as a bidimensional array consisting of one column holding dates and other column holding numeric values.

在我检索到信息后，一个不超过2秒钟，我想把这些数据写入工作表。在工作表中，我有一列包含日期和其他几列数值，每列包含相同类别的值。我遍历数组的行以获取日期和数值，并将其保存在变量中，然后在工作表的日期列上搜索日期，并在找到我写入该值的日期之后。这是我的代码：

After I retrieve the information, a process that takes no more than 2 seconds, I want to write this data to a worksheet. In the worksheet I have a column with dates and several other columns with numeric values, each column containing values of a same category. I iterate over the rows of the array to get the date and numeric value and I keep those in a variable, then I search for the date on the date column of the worksheet and after I've found the date I write the value. Here is my code:

Private Sub writeRetrievedData(retrievedData As Variant, dateColumnRange As String, columnOffset As Integer)

Dim element As Long: Dim startElement As Long: Dim endElement As Long
Dim instrumentDate As Variant: Dim instrumentValue As Variant
Dim c As Variant: Dim dateCellAddress As Variant

Application.ScreenUpdating = False    
Sheets("Data").Activate
startElement = LBound(retrievedData, 1): endElement = UBound(retrievedData, 1)
Application.DisplayStatusBar = True
Application.StatusBar = "Busy writing data to worksheet"

For element = startElement To endElement
    instrumentDate = retrievedData(element, 1): instrumentValue = retrievedData(element, 2)
    Range(dateColumnRange).Select
    Set c = Selection.Find(What:=instrumentDate, After:=ActiveCell, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
        c.offset(0, columnOffset).Value = instrumentValue            
    End If
Next element

Application.DisplayStatusBar = False

End Sub

我的问题是，这个过程非常慢，即使在阵列中只有5行，大约需要15秒钟来完成任务。因为我想重复这个过程几次（从数据库检索的每组数据一次），我想尽可能地减少执行时间。

My problem is that this process is very slow, even if I have only 5 rows in the array it takes about 15 seconds to complete the task. As I want to repeat this process several times (once per each set of data I retrieve from the database), I would like to decrease the execution time as much as possible.

如您所见，我正在禁用更新屏幕，这是提高性能的最常用的操作之一。有没有人建议如何进一步减少执行时间？

As you can see, I am disabling the update of the screen, which is one of the most recurrent actions to improve performance. Does anybody have a suggestion on how I can further decrease the execution time?

PS。我知道数据检索过程并不需要很多，因为我已经测试了这个部分（在检索到数据后，在MsgBox上显示值）

PS. I know the data retrieval process does not take much because I already tested that part (displaying values on a MsgBox as soon as the data has been retrieved)

感谢高级。

推荐答案

这是我为提高性能而做的：

This is what I did to improve the performance:

• 当值写入时，避免选择单元格。这是Tim Williams的建议。


• 我将属性Application.Calculation设置为xlCalculationManual



• 而不是使用Find（）函数要搜索日期，我将工作表中的所有日期加载到数组中，并遍历此数组以获取行号。事实证明比Find（）函数更快。

• Avoid selecting the cell when the value is going to be written. This was a suggestion of Tim Williams.
• I set the property Application.Calculation to xlCalculationManual

• Instead of using the Find() function to search for the date, I loaded all the dates from the worksheet into an array and iterate over this array to get the row number. This turns out to be faster than the Find() function.

Private Function loadDateArray() As Variant

    Dim Date_Arr() As Variant

    Sheets("Data").Activate
    Date_Arr = Range(Cells(3, 106), Cells(3, 106).End(xlDown))
    loadDateArray = Date_Arr

End Function

Private Function getDateRow(dateArray As Variant, dateToLook As Variant)

    Dim i As Double: Dim dateRow As Double

    For i = LBound(dateArray, 1) To UBound(dateArray, 1)
        If dateArray(i, 1) = dateToLook Then
            dateRow = i
            Exit For
        End If
    Next i

    getDateRow = dateRow

End Function

感谢大家的帮助！

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