Итерация по классу в Excel VBA

Я создал модуль класса для сущности под названием Terminal. У меня есть метод, который заполняет этот класс, просматривая 175 отдельных листов и извлекая правильные данные из определенных ячеек. Этот процесс очень быстрый (около 2 секунд), однако когда я пытаюсь записать эти данные обратно на новый лист, это занимает гораздо больше времени (45 секунд). Казалось бы, этот процесс должен быть по крайней мере так же быстр, как заполнение класса, так как он никогда не должен покидать лист, однако, это так нет. Ниже приведен процесс, который я использую для записи данных на рабочий лист, я упускаю что-то, что заставляет это работать так медленно?

Application.ScreenUpdating = False
 Dim rowNumber As Integer
 Dim colNumber As Integer
 Dim terminalCode As String
 Dim terminal As clsTerminal

 rowNumber = 7
 colNumber = 1

 For Each terminal In terminals

        'Add hyperlink to each terminal code
        Sheets("Terminal Summary").Hyperlinks.Add Anchor:=Cells(rowNumber, colNumber), Address:="", _
            SubAddress:=terminal.terminalCode + "!A1", TextToDisplay:=terminal.terminalCode

        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 1).Value = "Current"

        'Current period
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 2).Value = terminal.iBShipments
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 3).Value = terminal.oBShipments
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 4).Value = terminal.iBNetRevenue
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 5).Value = terminal.oBNetRevenue
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 6).Value = terminal.iBWeight
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 7).Value = terminal.oBWeight
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 8).Value = terminal.iBMileage
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 9).Value = terminal.oBMileage
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 10).FormulaR1C1 = "=IFERROR(RC[-4]/RC[-8],0)"
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 11).FormulaR1C1 = "=IFERROR(RC[-4]/RC[-8],0)"
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 12).FormulaR1C1 = "=IFERROR(RC[-8]/RC[-10],0)"
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 13).FormulaR1C1 = "=IFERROR(RC[-8]/RC[-10],0)"
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 14).FormulaR1C1 = "=IFERROR(RC[-10]/(RC[-8] / 100),0)"
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 15).FormulaR1C1 = "=IFERROR(RC[-10]/(RC[-8] / 100),0)"
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 16).FormulaR1C1 = "=IFERROR(RC[-12]/RC[-8],0)"
        Sheets("Terminal Summary").Cells(rowNumber, colNumber + 17).FormulaR1C1 = "=IFERROR(RC[-12]/RC[-8],0)"

        rowNumber = rowNumber + 1
    Next terminal

Edit Я должен был заметить, что terminals-это коллекция класса terminal

3 2
excel vba performance

3 ответа:

Общим недостатком этого типа кода является то, что каждый раз, когда вы записываете данные в электронную таблицу, Excel выполняет вычисление (он оценивает все формулы в книге, чтобы увидеть, нужно ли их вычислять с новыми данными).

Если вы отключите автоматическое вычисление перед циклом, а затем снова включите его, все будет двигаться гораздо быстрее: 

Application.Calculation = xlCalculationManual
For Each terminal In terminals
...
Next terminal
Application.Calculation = xlCalculationAutomatic
4

У вас уже есть большая экономия (выключение автоматического вычисления во время записи), но есть еще несколько маленьких трюков, которые нужно иметь в виду на будущее.

Во-первых, каждый раз, когда вы пишете ячейку из VBA, возникают накладные расходы, вызванные тем, что VBA переходит к адресу workbook/wootksheet/cell и выполняет запись. Запись многих ячеек в один вызов приводит к тому, что накладные расходы возникают только один раз. Таким образом, упаковка нескольких значений в массив и запись этого массива через несколько ячеек выигрывает время. Не стоит того ради ... несколько строк, но это стоит усилий для сотен.

Далее, есть еще одна небольшая накладная для каждой "точки". Термины в "точечном" выражении, такие как Sheets("Terminal Summary").Cells(rowNumber, colNumber + 2), требуют, чтобы Excel/VBA определяли, какие объекты участвуют в каждом вызове. В некоторых случаях (особенно при обращении к удаленным объектам) эти накладные расходы могут быть существенными. VB дает нам конструкцию With...End With, чтобы уменьшить необходимость продолжать разрешать эти ссылки: каждое выражение, начинающееся с точки, автоматически ссылается на объект в следующем крайнем With.

Таким образом, мы могли бы получить что-то вроде этого: 

With Sheets("Terminal Summary")
    .Cells(rowNumber, colNumber + 2).Resize(1, 8) = terminal.InfoArray
    .Cells(rowNumber, colNumber + 10).FormulaR1C1 = "=IFERROR(RC[-4]/RC[-8],0)"
    .Cells(rowNumber, colNumber + 11).FormulaR1C1 = "=IFERROR(RC[-4]/RC[-8],0)"
    .Cells(rowNumber, colNumber + 12).FormulaR1C1 = "=IFERROR(RC[-8]/RC[-10],0)"
    .Cells(rowNumber, colNumber + 13).FormulaR1C1 = "=IFERROR(RC[-8]/RC[-10],0)"
    .Cells(rowNumber, colNumber + 14).FormulaR1C1 = "=IFERROR(RC[-10]/(RC[-8] / 100),0)"
    .Cells(rowNumber, colNumber + 15).FormulaR1C1 = "=IFERROR(RC[-10]/(RC[-8] / 100),0)"
    .Cells(rowNumber, colNumber + 16).FormulaR1C1 = "=IFERROR(RC[-12]/RC[-8],0)"
    .Cells(rowNumber, colNumber + 17).FormulaR1C1 = "=IFERROR(RC[-12]/RC[-8],0)"
End With

Я бы вставил массив в терминальный класс, что-то вроде этого: 

Public Property Get InfoArray() As Variant
    InfoArray = Array(iBShipments, oBShipments, iBNetRevenue, oBNetRevenue, iBWeight, oBWeight, iBMileage, oBMileage)
End Property

Формулы можно было бы выводить более эффективно, записывая их один раз в столбец после того, как вся терминальная информация будет выполнена.

Некоторые вещи, чтобы рассмотреть, по крайней мере...

3

У вас уже есть 90% ответа, который я бы дал, но вот еще один совет по производительности. Вместо того, чтобы делать: 

Sheets("Terminal Summary").Cells(rowNumber, colNumber + 10).FormulaR1C1 = "=IFERROR(RC[-4]/RC[-8],0)"

Можно использовать тот факт, что вы присваиваете одну и ту же формулу всем ячейкам столбца, и сделать это в одном операторе: 

Sheets("Terminal Summary").Cells(7, 11).Resize(terminals.Count, 1).FormulaR1C1 = "=IFERROR(RC[-4]/RC[-8],0)"
1