Como posso acelerar minha macro no Excel 2003?

Question

Eu tenho uma macro que copia dados de uma célula para outra e usa uma fórmula VLOOKUP , entre outras coisas. Minha planilha contém quase 2000 linhas.

Quando eu o executo no Excel 2003, o Excel começa a desacelerar à medida que a macro processa as linhas 500 e posteriores. Fica ainda pior quando atinge a milésima linha. Demora mais de 5 horas para ser concluído.

No Excel 2007, no entanto, a macro é executada por apenas meia hora.

Alguém pode me ajudar a encontrar uma boa solução?

    

Answer 1

O blog sugerido por tumchaaditya oferece algumas excelentes sugestões que valem a pena ser aplicadas, mas duvido que elas ajudem aqui.

Para mim, a questão principal é que a macro fica mais lenta. Você tem comandos como:

StrA = StrA & NewData

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

Esses comandos tornam o StrA e o MyArray um pouco maiores. Para cada loop, o interpretador precisa encontrar espaço para o objeto maior, copiar os dados do objeto antigo e liberar o objeto antigo para a coleta de lixo. Toda vez que você torna o StrA ou MyArray um pouco maior, esse processo leva mais tempo. Eu não sei porque o problema é pior com o Excel 2003; talvez o Excel 2007 tenha um coletor de lixo melhor.

Se você está acumulando dados de cada linha, algo assim é muito melhor:

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

A sintaxe pode parecer estranha se você não estiver familiarizado com o que a maioria das linguagens chamam de tipos de usuário de chamadas de estruturas e VBA. Mas, uma vez que você esteja confortável com a sintaxe, as estruturas tornam seu código muito mais claro e, frequentemente, muito mais rápido.

    

Answer 2

Não consigo diagnosticar o motivo exato até que eu dê uma olhada no seu código. Mas, por enquanto, veja os links a seguir: Office.Com - Boas Práticas de Codificação de Desempenho do Excel VBA
VBA da Ozgrid.com Acelerando o Código VBA

Eles descrevem como otimizar o desempenho de qualquer macro do Excel.

1. Acelere o código e pare a tela piscando

   Application.ScreenUpdating=False
   Application.ScreenUpdating=True
   

2. Evitar cálculo ao executar o código

   Application.Calculation = xlCalculationManual
   Application.Calculation = xlCalculationAutomatic
   

3. Acelerando o código se você tiver planilha ou eventos de pasta de trabalho.

   Também interrompe loops infinitos em eventos

   Application.EnableEvents = False
   Application.EnableEvents = True
   

4. Use a instrução With ao trabalhar com objetos

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

5. Use VbNullString ao invés de="" Ao precisar padronizar uma variável String de volta para o padrão ""

   strWords = "Cats"
   strWords = vbNullString
   

6. Inserindo uma fórmula relativa em um intervalo de células: mais rápido que o preenchimento automático ou cópia

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

7. Evite o uso de Copiar e Colar sempre que possível (passa a área de transferência)

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

8. Sempre declare suas variáveis corretamente!

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