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, see the following links: Office.Com - Excel VBA Performance Coding best practices
Ozgrid.com VBA Speeding Up VBA Code

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

1. Speed up code and stop screen flickering

   Application.ScreenUpdating=False
   Application.ScreenUpdating=True
   

2. Preventing calculation while executing code

   Application.Calculation = xlCalculationManual
   Application.Calculation = xlCalculationAutomatic
   

3. Speeding up code if you have Worksheet or Workbook Events.

   Also stops endless loops in Events

   Application.EnableEvents = False
   Application.EnableEvents = True
   

4. Use the With Statement when working with Objects

   With Range("A1")
       .Font.Bold = True
       .Interior.ColorIndex = 6
   End With
   

5. Use VbNullString instead of = "" When needing to default a String variable back to it's default of ""

   strWords = "Cats"
   strWords = vbNullString
   

6. Inserting a Relative formula into a range of cells: Faster than AutoFill or Copy

   Range("A1:A200").FormulaR1C1 = "=SUM(RC[1]:RC[5])"
   

7. Avoid the use of Copy and Paste whenever possible (By-passes the Clipboard)

   Sheet1.Range("A1:A200").Copy Destination:=Sheet2.Range("B1")
   

8. Always declare your variables correctly!

   Dim wSheet as Worksheet
   Set wSheet = Sheet1
   Set wSheet = Nothing
   

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.