ansaurus

Question

Answer 1

+4 A:

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

e.James 2009-11-17 15:32:39

Dead on. 95.5% increase in performance. Thanks

Irwin M. Fletcher 2009-11-17 15:38:51

You are welcome :)

e.James 2009-11-17 15:39:18

Answer 2

+2 A:

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...

Mike Woodhouse 2009-11-17 16:22:57

+1 These are great tips, performance aside the code is so much cleaner. I am a C# guy hacking around in VBA, it looks like I still have a lot to learn

Irwin M. Fletcher 2009-11-17 16:34:14

+1 and thank you. I had no idea you could write entire blocks of cells at once. That's going to come in handy.

e.James 2009-11-18 06:56:07

Answer 3

+1 A:

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)"

Gary McGill 2009-11-17 20:44:15

This brings me to one last question that I have had, is it signficantly faster to add all the formulas (or formatting for that matter) in one column at once, rather then each time you pass throug the column when you are going line by line adding other data

Irwin M. Fletcher 2009-11-17 21:16:04

Yes. If you can trade doing stuff in VBA for getting Excel to do it natively, then you'll see a huge speed increase. So while the single assignment to the column might take a bit longer than the single assignment to one cell, it'll be much faster than a single assignment to 100 cells.

Gary McGill 2009-11-18 08:26:12

...oops, I wish I could edit that comment. What I meant to say was, much faster than 100 assignments to one cell each.

Gary McGill 2009-11-18 08:27:13

ansaurus

tags:

views:

answers:

Iterating Through a Class in Excel VBA

related questions