Форматирование в excel vba занимает много времени

У меня есть лист Excel (2010), где я загружаю данные из базы данных, а затем форматирую каждую строку на основе конкретной ячейки в каждой строке. Код для форматирования занимает довольно много времени. Около 4 минут для примерно 150 строк и 15 столбцов. Вот фрагмент кода. По сути, это цикл, который проверяет значение row_type и соответственно устанавливает цвет шрифта, цвет фона и т. д. для каждой строки. Есть ли лучший способ сделать это, чем использовать цикл? Любые другие улучшения, которые я могу сделать, если зацикливание - это ответ.

 J = 1
 While J <= iNumRows
    row_type = Worksheets("WaterFall").Range("WaterFallHeaders").Offset(J, -1).Cells(1, 1)

    If row_type = "main_row" Then
        With Worksheets("WaterFall").Range("WaterFallHeaders").Offset(J, 0).EntireRow.Font
            .Bold = False
            .ColorIndex = RGB(0, 0, 0)
        End With
        'yellow Description columns
        With Worksheets("WaterFall").Range("WaterFallHeaders").Offset(J, 0).Resize(1, 5).Interior
                .Color = RGB(204, 255, 204)
        End With
        'grey amount columns
        With Worksheets("WaterFall").Range("WaterFallHeaders").Offset(J, 5).Resize(1, dtCount + 2).Interior
            .Color = RGB(217, 217, 217)
        End With

        'blue action columns
        With Worksheets("WaterFall").Range("WaterFallHeaders").Offset(J, 0).Cells(1, 1).Font
            .Bold = True
        End With
        With Worksheets("WaterFall").Range("WaterFallHeaders").Offset(J, 0).Cells(1, 1).Interior
                .Color = RGB(184, 204, 225)
        End With
    Else
        With Worksheets("WaterFall").Range("WaterFallHeaders").Offset(J, 0).Resize(1, colCount - 1).Font
            .Bold = True
            .Color = RGB(51, 51, 255)
        End With
        With Worksheets("WaterFall").Range("WaterFallHeaders").Offset(J, 0).Resize(1, colCount - 1).Interior
                .Color = RGB(255, 255, 204)
        End With
    End If

    J = J + 1



Wend

Спасибо...


performance excel vba excel-2010
person Satfactor    schedule 17.04.2013    source источник
comment
Не могли бы вы создать условный формат, который можно было бы скопировать и вставить в диапазон?   -  person AxGryndr    schedule 17.04.2013
comment
Каково значение iNumRows при отладке? Возможно ли, что цикл перебирает больше строк, чем вы хотите?   -  person David Zemens    schedule 17.04.2013
comment
Другой вариант — установить Application.ScreenUpdating = false в начале и установить значение true, когда закончите. Это может сократить время отклика.   -  person AxGryndr    schedule 17.04.2013
comment
+1 @AxGryndr за Application.ScreenUpdating=False. Это обычно значительно увеличивает скорость любого кода, который манипулирует объектами рабочего листа.   -  person David Zemens    schedule 17.04.2013
comment
Application.ScreenUpdating=False сделал свое дело. Он показывает данные за пару секунд.. Спасибо !!! Я также добавил Application.Calculation = xlCalculationManual и изменения, упомянутые Дэвидом ниже.   -  person Satfactor    schedule 18.04.2013

Ответы (1)


arrow_upward
3
arrow_downward

Это не похоже на то, что должно занять 4 минуты (например, Range.Autofill для ~ 100 000 строк занимает на моей машине около 90 секунд).

Однако вы, вероятно, можете сделать код более эффективным, уменьшив количество обращений к определенным объектам, включив более эффективное использование блоков With.

Это уменьшает количество обращений компилятора к Worksheets("WaterFall").Range("WaterFallHeaders") до 1. Раньше вы обращались к этому либо 3, либо 6 раз, в зависимости от того, с какой стороны If/Else.

Не проверено, потому что у меня нет файла вашей рабочей книги, чтобы протестировать его. Дайте мне знать, если это поможет или создаст какие-либо проблемы.

J = 1
With Worksheets("WaterFall").Range("WaterFallHeaders")
    While J <= iNumRows
        row_type = .Offset(J, -1).Cells(1, 1)

        If row_type = "main_row" Then
            With .Offset(J, 0).EntireRow.Font
                .Bold = False
                .ColorIndex = RGB(0, 0, 0)
            End With

            'yellow Description columns
            With .Offset(J, 0).Resize(1, 5).Interior
                .Color = RGB(204, 255, 204)
            End With

            'grey amount columns
            With .Offset(J, 5).Resize(1, dtCount + 2).Interior
                .Color = RGB(217, 217, 217)
            End With

            'blue action columns
            With .Offset(J, 0).Cells(1, 1)
                .Font.Bold = True
                .Interior.Color = RGB(184, 204, 225)
            End With
        Else

            With .Offset(J, 0).Resize(1, colCount - 1)
                With .Font
                    .Bold = True
                    .Color = RGB(51, 51, 255)
                End With
                .Interior.Color = RGB(255, 255, 204)
            End With
        End If
    Wend
End With
person David Zemens    schedule 17.04.2013