How can I speed up my macro in Excel 2003?

Question

I have a macro that copies data from one cell to another and uses a VLOOKUP formula, among other things. My spreadsheet contains nearly 2000 rows.

When I run it in Excel 2003, Excel starts to slow down as the macro processes rows 500 and above. It gets even worse when it reaches the 1000th row. It takes more than 5 hours to complete.

In Excel 2007, however, the macro runs for only half an hour.

Can anyone help me find a good solution?

Answer 1

I cannot diagnose the exact reason until I have a look at your code, but, for now, refer following links:

http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm
http://blogs.office.com/b/microsoft-excel/archive/2009/03/12/excel-vba-performance-coding-best-practices.aspx

They describe how to optimize the performance of any excel macro.

Answer 2

The blog suggested by tumchaaditya offers some excellent suggestions which are worth implementing but I doubt they will help here.

For me the key issue is that the macro slows down. Do you have commands like:

StrA = StrA & NewData

ReDim Preserve MyArray(1 To UBound(MyArray)+1)

These commands make StrA and MyArray slightly bigger. For each loop, the interpreter has to find space for the larger object, copy the data from the old object and then release the old object for garbage collection. Every time you make StrA or MyArray a little bigger, this process takes longer. I do not know why the problem is worse with Excel 2003; perhaps Excel 2007 has a better garbage collector.

If you are accumulating data from each row, something like this is much better:

Option Explicit
Type SRowDtl      ' The definition of a User Type must preceed any routines
  Info1 As String
  Info2 As Long
  Info3() As Double
End Type

Sub ProcessRows()

  Dim RowDtl() as SRowDtl
  Dim InxRowDtlCrntMax as Long 

  ReDim RowDtl(NumberOfRows)
  InxRowDtlCrntMax = -1 

  For Each Row ....

     ' Store data from new row
     InxRowDtlCrntMax = InxRowDtlCrntMax+1

     RowDtl(InxRowDtlCrntMax).Info1 = xxx
     RowDtl(InxRowDtlCrntMax).Info2 = yyy
     RowDtl(InxRowDtlCrntMax).Info3(5) = zzz

  Next

The syntax can look strange if you not familiar with what most languages call Structures and VBA calls User Types. But, once you are comfortable with the syntax, structures make your code much clearer and, often, much faster.