Блог

Я пытаюсь ускорить цикл For, так как для некоторых листов с большим количеством строк / столбцов требуется много времени. Уже пробовал что-то с массивом и словарем, но я не понимаю. Как можно оптимизировать этот код для проверки содержимого ячеек и вставки, если верно, нового вычисленного содержимого в другие ячейки? Большое спасибо!

     Pos = 1
    lngWS = ws_Table.ListRows.Count
    
    For Pos = Pos To lngWS
        If ws_Table.DataBodyRange(Pos, Column_Date).Value2 <> vbNullString And _
           ws_Table.DataBodyRange(Pos, Column_Verify).Value2 <> "OK" Then

            ws_Table.DataBodyRange(Pos, Column_Year).Value2 = Year(ws_Table.DataBodyRange(Pos, Column_Date).Value2)
            ws_Table.DataBodyRange(Pos, Column_Month).Value2 = Format(ws_Table.DataBodyRange(Pos, Column_Date).Value2, "MMMM")

            ws_Table.DataBodyRange(Pos, Column_Compare_1).FormulaLocal = "=1 1"
            ws_Table.DataBodyRange(Pos, Column_Compare_1).Value2 = ws_Table.DataBodyRange(Pos, Column_Compare_1).Value2
            ws_Table.DataBodyRange(Pos, Column_Compare_2).FormulaLocal = "=2 2"
            ws_Table.DataBodyRange(Pos, Column_Compare_2).Value2 = ws_Table.DataBodyRange(Pos, Column_Compare_2).Value2

        End If
    Next Pos
 

Чтобы ускорить работу с данными в диапазоне, гораздо быстрее скопировать данные из диапазона в массив, поработать с массивом, а затем скопировать данные обратно в диапазон.

Это работает для диапазонов с более чем одной ячейкой:

 Dim Lst as Variant ' The array
Dim Idx as Long ' The row index

' First copy the range to an array
Lst = ws_Table.DataBodyRange
For Idx = LBound(Lst) To UBound(Lst)
    ' Change the rows in the array here, for example:
    '     Lst(Idx, 1) = Lst(Idx, 1) * 3
    '
    ' Be aware that you might need to recreate your formulas, as 
    ' it is the results of the forumalas that are copied to the array:
    '     Lst(Idx, 2) = "=1 1"
Next Idx
' Then copy the array back to the range
ws_Table.DataBodyRange = Lst
 

Причина, по которой работа с массивом выполняется быстро, заключается в том, что это структура данных в памяти, а объект listobject, диапазон или ячейка являются COM-объектом. Для ссылки на COM-объект требуется много времени.

Метод, который я рекомендую, ссылается только на COM-объект 2 или 3 раза, в то время как метод, используемый в вашем примере, или решение, предложенное Тимом Уильямсом, ссылается на COM-объект несколько раз для каждой строки в диапазоне.

Это может быть немного быстрее:

 Dim dtVal, verVal, pos As Long
'...
'...

Application.ScreenUpdating = False
For pos = 1 To ws_Table.ListRows.Count
    With ws_Table.DataBodyRange.Rows(pos)
        
        dtVal = .Cells(Column_Date).Value2
        verVal = .Cells(Column_Verify).Value2
        
        If Len(dtVal) > 0 And verVal <> "OK" Then

            .Cells(Column_Year).Value2 = Year(dtVal)
            .Cells(Column_Month).Value2 = Format(dtVal, "MMMM")

            With .Cells(Column_Compare_1)
                .FormulaLocal = "=1 1"
                .Value = .Value
            End With
            With .Cells(Column_Compare_2)
                .FormulaLocal = "=2 2"
                .Value2 = .Value2
            End With
        End If 'have date and not "OK"
    End With
Next pos