VBA – Aumentar a Velocidade e Outras Práticas Recomendadas

Este tutorial abordará como acelerar as macros do VBA e outras práticas recomendadas do VBA.

Configurações para Acelerar o Código VBA

Abaixo você encontrará várias dicas para acelerar seu código VBA. As dicas estão organizadas de forma livre por importância.

A maneira mais fácil de aumentar a velocidade do código VBA é desativar o ScreenUpdating e os cálculos automáticos. Essas configurações devem ser desativadas em todos os procedimentos grandes.

Desativar a Atualização de Tela

Por padrão, o Excel exibirá as alterações na(s) pasta(s) de trabalho em tempo real à medida que o código VBA for executado. Isso causa uma grande lentidão na velocidade de processamento, pois o Excel interpreta e exibe as alterações para cada linha de código.

Para desativar a atualização de tela:

Application.ScreenUpdating = False

No final da macro, você deve ativar novamente a atualização de tela:

Application.ScreenUpdating = True

Enquanto o código estiver em execução, talvez seja necessário “atualizar” a tela. Não existe um comando “atualizar”. Em vez disso, você precisará ativar novamente a atualização de tela e desativá-la novamente.

Definir os Cálculos Como Manuais

Sempre que um valor de célula é alterado, o Excel deve seguir a “árvore de cálculo” para recalcular todas as células dependentes. Além disso, sempre que uma fórmula for alterada, o Excel precisará atualizar a “árvore de cálculo”, além de recalcular todas as células dependentes. Dependendo do tamanho da pasta de trabalho, esses recálculos podem fazer com que as macros sejam executadas com uma lentidão excessiva.

Para definir os cálculos como manuais

Application.Calculation = xlManual

Para recalcular manualmente a pasta de trabalho inteira:

Calculate

Observe que você também pode calcular apenas uma planilha, um intervalo ou uma célula individual, se necessário, para aumentar a velocidade.

Para restaurar os cálculos automáticos (no final do procedimento):

Application.Calculation = xlAutomatic

Importante! Essa é uma configuração do Excel. Se você não redefinir os cálculos como automáticos, a pasta de trabalho não recalculará até que você diga para fazê-lo.

Você verá as maiores melhorias com as configurações acima, mas há várias outras configurações que podem fazer a diferença.

Desativar Eventos

Os eventos são “acionadores” que fazem com que procedimentos de eventos especiais sejam executados. Os exemplos incluem: quando qualquer célula em uma planilha é alterada, quando uma planilha é ativada, quando uma pasta de trabalho é aberta, antes de uma pasta de trabalho ser salva, etc.

A desativação de eventos pode causar pequenas melhorias de velocidade quando qualquer macros é executada, mas a melhoria de velocidade pode ser muito maior se a pasta de trabalho usar eventos. Em alguns casos, a desativação de eventos é necessária para evitar a criação de loops infinitos.

Para desativar os eventos:

Application.EnableEvents = False

Para ativar os eventos novamente:

Application.EnableEvents = True

Desativar Quebra de Página

A desativação do PageBreaks pode ajudar em determinadas situações:

• Você definiu anteriormente uma propriedade de Configuração de Página para a planilha relevante e seu procedimento VBA modifica as propriedades de muitas linhas ou colunas.
• OU Seu procedimento VBA força o Excel a calcular as quebras de página (exibindo a Visualização de Impressão ou modificando quaisquer propriedades de Configuração de Página).

Para desativar a Quebra de Página:

ActiveSheet.DisplayPageBreaks = False

Para reativar a Quebra de Página:

ActiveSheet.DisplayPageBreaks = True

Práticas Recomendadas para Aumentar a Velocidade do VBA

Evite Ativar e Selecionar

Ao gravar uma macro, você verá muitos métodos Activate e Select:

Sub Exemplo_Lento()
    Sheets("Planilha1").Select
    Range("D9").Select
    ActiveCell.FormulaR1C1 = "exemplo"
    Range("D12").Select
    ActiveCell.FormulaR1C1 = "demonstração"
    Range("D13").Select
End Sub

A ativação e a seleção de objetos geralmente são desnecessárias, adicionam desordem ao seu código e consomem muito tempo. Você deve evitar esses métodos sempre que possível.

Exemplo aprimorado:

Sub Exemplo_Rapido()
    Sheets("Planilha1").Range("D9").FormulaR1C1 = "exemplo"
    Sheets("Planilha1").Range("D12").FormulaR1C1 = "demonstração"
End Sub

Evitar Copiar e Colar

A cópia requer muita memória. Infelizmente, não é possível dizer ao VBA para limpar a memória interna. Em vez disso, o Excel limpará sua memória interna em intervalos (aparentemente) específicos. Portanto, se você realizar muitas operações de copiar e colar, corre o risco de consumir muita memória, o que pode tornar seu código drasticamente mais lento ou até mesmo travar o Excel.

Em vez de copiar e colar, considere a possibilidade de definir as propriedades de valor das células.

Sub CopiarColar()

 'Mais lento
 Range("a1:a1000").Copy Range("b1:b1000")

 'Mais rápido
 Range("b1:b1000").Value = Range("a1:a1000").Value

End Sub

Use os Loops For Each em vez de For Loops

Ao percorrer objetos, o loop For Each é mais rápido do que o For Loop. Exemplo:

Este loop For:

Sub Loop1()
    dim i as Integer
    For i = 1 To 100
       Cells(i, 1).Value = 1
    Next i
End Sub

Sub Loop2()
    Dim celula As Range
    For Each celula In Range("a1:a100")
       celula.Value = 1
    Next celula
End Sub

Declarar Variáveis / Usar Option Explicit (Opção Explícita)

Option Explicit

Sub OpcaoExplicita()
 var1 = 10
 MsgBox varl
End Sub

Uso de Declarações With – End With

Sub Exemplo_Rapido()
    With Sheets("Planilha2")
      .Range("D9").FormulaR1C1 = "exemplo"
      .Range("D12").FormulaR1C1 = "demontração"
      .Range("D9").Font.Bold = True
      .Range("D12").Font.Bold = True
    End With
End Sub

Sub Exemplo_Lento()
    Sheets("Planilha2").Range("D9").FormulaR1C1 = "exemplo"
    Sheets("Planilha2").Range("D12").FormulaR1C1 = "demontração"
    Sheets("Planilha2").Range("D9").Font.Bold = True
    Sheets("Planilha2").Range("D12").Font.Bold = True
End Sub

Dicas Avançadas de Melhores Práticas

Proteger UserInterfaceOnly (Somente Interface do Usuário)

É uma boa prática proteger suas planilhas contra a edição de células desprotegidas para evitar que o usuário final (ou você!) corrompa acidentalmente a pasta de trabalho. No entanto, isso também protegerá a(s) planilha(s) de permitir que o VBA faça alterações. Portanto, você deve desproteger e proteger novamente as planilhas, o que consome muito tempo quando feito em muitas planilhas.

Sub DesprotegerPlanilha()
    Sheets("Planilha1").Unprotect "senha"
    'Editar Planilha1
    Sheets("Planilha1").Protect "senha"
End Sub

Em vez disso, você pode proteger as planilhas com a configuração UserInterfaceOnly:=True. Isso permite que o VBA faça alterações nas planilhas e, ao mesmo tempo, as proteja do usuário.

Sheets("Planilha1").Protect Password:="senha", UserInterFaceOnly:=True

Importante! UserInterFaceOnly é redefinido como False sempre que a pasta de trabalho é aberta. Portanto, para usar esse recurso incrível, você precisará usar os eventos Workbook_Open ou Auto_Open para definir a configuração sempre que a pasta de trabalho for aberta.

Coloque este código no módulo EstaPastaDeTrabalho

Private Sub Workbook_Open()
    Dim ws As Worksheet
    For Each ws In Worksheets
       ws.Protect Password:="senha", UserInterFaceOnly:=True
    Next ws
End Sub

ou este código em qualquer módulo normal

Private Sub Auto_Open()
    Dim ws As Worksheet
    For Each ws In Worksheets
       ws.Protect Password:="senha", UserInterFaceOnly:=True
    Next ws
End Sub

Uso de Matrizes para Editar Intervalos Grandes

Pode ser muito demorado manipular intervalos grandes de células (por exemplo, 100.000+). Em vez de percorrer os intervalos de células, manipulando cada célula, você pode carregar as células em uma matriz, processar cada item da matriz e, em seguida, enviar a matriz de volta para as células originais. Carregar as células em matrizes para manipulação pode ser muito mais rápido.

Sub LoopIntervalo()

    Dim celula As Range
    Dim tInicio As Double

    tInicio = Timer

    For Each celula In Range("A1:A100000")
       celula.Value = celula.Value * 100
    Next celula

    Debug.Print (Timer - tInicio) & " segundos"

End Sub

Sub LoopMatriz()

    Dim arr As Variant
    Dim item As Variant
    Dim tInicio As Double

    tInicio = Timer

    arr = Range("A1:A100000").Value
    For Each item In arr
       item = item * 100
    Next item
    Range("A1:A100000").Value = arr

    Debug.Print (Timer - tInicio) & " segundos"

End Sub