Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
5  
we would have to see the code you have –  datatoo Jul 10 '12 at 5:26
    
in a addition to the useful and informative answers below, are you selecting cells or ranges explicitly within your loop? This is a big performance no no NO, try just using the Range object with variables... –  Philip Jul 9 '13 at 15:43
    
have you considered copying the sheet data to an array and working on it in memory? –  Philip Jul 9 '13 at 15:44
add comment

2 Answers

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.

share|improve this answer
    
maybe even worth copying the sheet data to an array then working on it in memory –  Philip Jul 9 '13 at 15:45
    
@Philip. I agree a single load from worksheet to array is faster but I decided that without seeing the original code there was a limit to how many new techniques could/should be introduced. The links provided by tumchaaditya detailed many good techniques but I thought the true problem was likely to be garbage collection and limited myself to that issue. Without any response from the OP I was unwilling to take the matter further. –  Tony Dallimore Jul 9 '13 at 19:49
add comment

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
    
share|improve this answer
1  
You should summarize those links in your answer, to avoid link rot. –  JimmyPena Jul 23 '12 at 17:10
    
I added a summary –  nixda Jul 9 '13 at 16:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.