Iterating Through a Class in Excel VBA

I have created a class module for an entity called Terminal. I have a method that populates this class by going through 175 seperate worksheets and pulling the correct data from specific cells. This process is very quick (about 2 seconds), however when I try to write this data back out to a new worksheet, it is taking much longer (45 seconds). It would seem that this process should be atleast as fast as populating the class since it never has to leave the worksheet, however, it is not. Below is the process that I am using to write the data to the worksheet, am I overlooking something that is causing this to run so slowly?

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 I should have noted terminals is a collection of the terminal class开发者_JAVA技巧

---

A common pitfall with this type of code is that every time you write data to the spreadsheet, Excel runs through a calculation (it evaluates all of the formulas in the workbook to see if they need to be computed with the new data).

If you disable automatic calculation before your loop and then re-enable it afterwards, things will move much quicker:

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

---

You already have the big saving (switching off auto-calculation while writing) but there are a few other little tricks to keep in mind for the future.

Firstly, every time you write a cell from VBA there's an overhead caused by VBA going to the workbook/wootksheet/cell address and performing the write. Writing many cells in one call incurs that overhead only once. So packing several values into an array and writing that array across several cells gains time. Not worth it for a few lines, but well worth the effort for hundreds.

Further, there's another little overhead for each "dot". The terms in a "dotted" expression such as Sheets("Terminal Summary").Cells(rowNumber, colNumber + 2) require Excel/VBA to determine which objects are involved in each call. In some cases (particularly when referencing remote objects) that overhead can be substantial. VB gives us the With...End With construct to reduce the need to keep resolving those references: every expression that starts with a dot automatically references the object in the next outermost With.

So we might get something like this:

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

I'd tuck the array thing into the Terminal class, something like this:

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

The formulae might be output more efficiently by writing them once per column after the Terminal information is all done.

Some things to consider, at least...

---

You already have 90% of the answer I would give, but here's a further performance tip. Instead of doing:

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

You could use the fact that you're assigning the same formula to all the cells in the column, and do it in one statement:

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