10 Dicas de Como Aumentar a Velocidade do VBA Excel

Veja neste artigo 10 dicas de como aumentar a velocidade do VBA no Excel. Artigo com exemplo em planilha e vídeo-aula passo-a-passo.

10 Dicas de Como Aumentar a Velocidade do VBA Excel

O VBA é uma ferramenta incrível do Excel, mas ele pode ser um tanto lento se não aplicado da forma correta. Veja 10 dicas de como aumentar a velocidade do seu VBA Excel neste artigo, tenho certeza de que irá lhe ajudar.

10 Dicas para aumentar a velocidade do VBA Excel:

Mudar para o cálculo manual
Desabilitar Refresh da tela
Desabilitar animações
Evite intercalar VBA e Excel
Não use célula como variável
Use arrays ao invés de células
Use Value2
Não copie e cole
Declaração obrigatória das variáveis
Use o With

Mudar o Excel para Cálculo Manual no VBA

Como aumentar a velocidade do VBA Excel 1

O cálculo automático é o primeiro recurso que deve ser desabilitado ao iniciar um código VBA, pois ele faz com que a cada alteração nas células todas sejam recalculadas, fazendo então com que o processamento pese muito mais.

Para desabilitar o cálculo automático no VBA o código é:

Application.Calculation = xlCalculationManual

Este código acima realiza a mudança do tipo de cálculo do Excel para manual, o mesmo que você faria mudando via Excel.

Algo muito importante é lembrar de voltar ao cálculo automático após o processamento, se assim estivesse antes. Para isso use o código abaixo:

Application.Calculation = xlCalculationAutomatic

Veja que no exemplo usamos o GoTo para que caso haja erro o sistema envie para o tratamento de erro e force a saída pelo Sair, fazendo com que sempre passe pela linha que retorna o cálculo para automático.

Abaixo o código do exemplo:

Public Sub lsDesabilitarManual()
    On Error GoTo TratarErro

    Application.Calculation = xlCalculationManual
    'Aqui seu Processamento

Sair:
    Application.Calculation = xlCalculationAutomatic
    Exit Sub
TratarErro:
    GoTo Sair
End Sub

Desabilitar Refresh da Tela para aumentar velocidade do VBA

O refresh da tela, ou screenUpdating no VBA Excel faz com que a tela fique “piscando”, isso acontece porque cada movimentação que você fizer na planilha ela irá realizar um refresh da tela do Excel para apresentar o resultado.

O problema disso é que isso consome muito processamento e leva muito mais tempo desta forma o seu processamento.

Então a dica é desabilitar o screenUpdating para evitar que a tela fique sendo atualizada durante o VBA.

Para fazer isso use o seguinte código:

Application.ScreenUpdating = False

Lembre também de voltar como estava antes, pois senão não atualizará mais a tela do Excel enquanto não fechar e abrir o Excel novamente.

Para isso use o mesmo código mudando para True:

Application.ScreenUpdating = True

Como o anterior, é uma prática recomendada usar o tratamento de erro para garantir que o screenUpdating retorne após o processamento:

Public Sub lsDesabilitarRefreshTela()
    On Error GoTo TratarErro

    Application.ScreenUpdating = False
    
    'Processamento
Sair:
    Application.ScreenUpdating = True
    Exit Sub
TratarErro:
    GoTo Sair
End Sub

Desabilitar as animações no VBA Excel

As animações na tela também podem consumir recursos, assim como o screenUpdating, fazendo com que este processamento visual seja realizado a cada alteração que tenha animações no Excel.

O código para desabilitar as animações no VBA é o seguinte:

Application.EnableAnimations = False

Não esqueça de retornar como estava antes, para isso use o código abaixo:

Application.EnableAnimations = True

O código abaixo é um exemplo de como utilizar este código com o tratamento de erro:

Public Sub lsDesabilitarAnimacao()
    On Error GoTo TratarErro

    Application.EnableAnimations = False
    
    'Processamento
Sair:
    Application.EnableAnimations = True
    Exit Sub
TratarErro:
    GoTo Sair
End Sub

Evite Intercalar VBA e Excel

É comum vermos códigos aonde realizamos interação do VBA com o Excel realizando alterações de células, planilhas, etc.

A questão é que realizar esta mudança entre Excel e VBA faz com que o tempo de processamento fique muito maior.

Evite ao máximo fazer chamadas, a células do VBA, faça a chamada apenas uma vez e depois faça o processamento com esta variável.

Veja um exemplo de código que você deve evitar:

lValorIncrementar = lValorIncrementar + Planilha1.Range(“lParametro”).Value2

Realize a passagem deste valor da célula para uma variável antes de utilizar o mesmo, ficando muito mais rápido o seu código. Faça isto para aumentar velocidade vba.

lValor = Planilha1.Range(“lParametro”).Value2

lValorIncrementar = lValorIncrementar + lValor

Este é um exemplo do código VBA Excel sugerido:

Public Sub lsEviteIntercalar()
    On Error GoTo TratarErro

    Dim lValor, lValorIncrementar, i    As Long
    Dim lInicio, lFinal                            As Date
    
    lValor = Planilha1.Range("lParametro").Value2
    lInicio = Now
       
    For i = 1 To 1000000
        lValorIncrementar = lValorIncrementar + lValor
    Next i
    
    lFinal = Now
    MsgBox Format(lFinal - lInicio, "hh:mm:ss")
       
Sair:
    Exit Sub
TratarErro:
    GoTo Sair
End Sub

Não use Célula como Variável

Da mesma forma que vimos que não devemos usar uma célula como passagem de parâmetros no VBA e sim variáveis por exemplo, você pode ver um exemplo do código abaixo um exemplo que não devemos seguir.

For i = 1 To 100000

Planilha1.Range(“lValor”).Value2 = lValorIncrementar + Planilha1.Range(“lParametro”).Value2

Next i

No código acima você pode notar que a célula está sendo alterada diretamente na célula, e isso afeta muito o processamento, o código acima por exemplo demorar 00:02:28 para executar.

Uma forma de fazer com que tenha melhor performance faça conforme no código abaixo, passando o valor para a célula somente ao final do processamento, reduzindo demais os processamentos.

Public Sub lsValorNaVariavel()
    On Error GoTo TratarErro

    Dim lValor, lValorIncrementar, i    As Long
    Dim lInicio, lFinal                            As Date
    lInicio = Now
    lValor = Planilha1.Range("lParametro").Value2

    For i = 1 To 200000
        lValorIncrementar = lValorIncrementar + lValor
    Next i
    
    Planilha1.Range("lParametro").Value2 = lValorIncrementar
    lFinal = Now
    MsgBox Format(lFinal - lInicio, "hh:mm:ss")
       
Sair:
    Exit Sub
TratarErro:
    GoTo Sair
End Sub

Use arrays ao invés de células

Caso você tenha interações que altere muitas células, você pode aplicar este intervalo de células para um array, ou seja, um conjunto de variáveis de vetor, alterando ou lendo os dados deste array e depois retornando para as células.

Este é um exemplo de uma loop no qual as células são alteradas diretamente no loop, você pode substituir este código pelo uso de arrays ganhando muito tempo

For i = 1 To 10000

Planilha1.Cells(i, 7).Value2 = Planilha1.Cells(i, 6).Value2 * 10

Next i

Veja uma substituição do código acima usando array no VBA:

Dim lValorArray As Variant

lValorArray = Planilha1.Range(“F1:F10000”).Value2

For i = 1 To 10000

lValorArray(i, 1) = lValorArray(i, 1) * 10

Next i

Planilha1.Range(“G1:G10000”).Value2 = lValorArray

Como pode notar no código acima, você tem a passagem do intervalo diretamente para a variável lValorArray, é feito o processamento neste vetor e em seguida os valores do vetor são passados do vetor para o range de células.

Abaixo um exemplo de código no qual é aplicado array.

Public Sub lsArrays()
    On Error GoTo TratarErro

    Dim lValorIncrementar, i    As Long
    Dim lInicio, lFinal                As Date
    Dim lValorArray                As Variant

    lInicio = Now
    lValorArray = Planilha1.Range("F1:F10000").Value2
    
    'Tempo 00:00:00
    For i = 1 To 10000
        lValorArray(i, 1) = lValorArray(i, 1) * 10
    Next i
    Planilha1.Range("G1:G10000").Value2 = lValorArray
    lFinal = Now
    MsgBox Format(lFinal - lInicio, "hh:mm:ss")
Sair:
    Exit Sub
TratarErro:
    GoTo Sair
End Sub

Use Value2 no VBA Excel

Uma outra forma de aumentar a velocidade do seu Excel VBA é utilizar sempre que possível Value2 ao invés de Text e Value, veja porque:

.text = Retorna o valor e a formatação

.value = retorna sem formatação maioria exceto data ou moeda(pode truncar)

.value2 = Sem formatação + rápido

Você terá ganhos pequenos, mas ganhos, porque tudo o que copia vai pra memória do computador e isto afeta diretamente a performance do VBA Excel.

Não copie e cole no VBA Excel para aumentar a velocidade do VBA

É comum ao gravar macros que o código gerado tenha muitas partes com .COPY, .SELECT e .PASTE.

Evite ao máximo fazer uso destes eventos porque o VBA irá fazer chamadas ao Excel e afetar muito a performance.

Este é um exemplo de como não fazer:

For i = 1 To 101

Planilha1.Cells(i + 1, 10).Copy

Planilha1.Cells(i, 10).Select

ActiveSheet.Paste

Next i

E abaixo um exemplo de como pode fazer. Lembrando que no exemplo abaixo substitua também por uso de arrays como vimos anterioramente, mas deixei para que fique claro o exemplo.

For i = 1 To 101

Planilha1.Cells(i, 10).Value2 = Planilha1.Cells(i + 1, 10).Value2

Next i

Abaixo o código completo de um exemplo:

Public Sub lsNaoCopiarColar()
    On Error GoTo TratarErro

    Dim lValorIncrementar, i    As Long
    Dim lInicio, lFinal                As Date

    lInicio = Now
    'Tempo 00:00:00
    For i = 1 To 101
        Planilha1.Cells(i, 10).Value2 = Planilha1.Cells(i + 1, 10).Value2
    Next i
    
    lFinal = Now
    MsgBox Format(lFinal - lInicio, "hh:mm:ss")
       
Sair:
    Exit Sub
TratarErro:
    GoTo Sair
End Sub

Veja abaixo substitutos a este código de copiar e colar aonde você precisa copiar e colar de diferentes formas no VBA Excel com velocidade:

‘Valor, fórmula e formatação

Planilha1.Range(“A2”) = Planilha1.Range(“a1”)

‘Apenas valores

Planilha1.Range(“A2”).Value2 = Planilha1.Range(“A1”).Value2

‘Apenas fórmulas

Planilha1.Range(“A2”).Formula = Planilha1.Range(“a2”).Formula

Declaração Obrigatória de Variáveis no VBA Excel

As variáveis ocupam lugar na memória e obrigar a declaração das variáveis faz com que o tipo seja também declarado.

Isto faz com que o código rode mais rápido até por conta do tipo de variável já ser definido na sua declaração, reduzindo assim o tamanho alocado de memória no VBA e aumentando a velocidade do VBA Excel.

Para isso basta usar o Option Explicite no topo do seu código fonte para aumentar a velocidade do vba.

Option Explicit

Public Sub lsDesabilitarManual()
    On Error GoTo TratarErro

    Application.Calculation = xlCalculationManual
    'Aqui seu Processamento

Sair:
    Application.Calculation = xlCalculationAutomatic
    Exit Sub
TratarErro:
    GoTo Sair
End Sub

Aumente a velocidade do VBA Excel usando With para dar mais velocidade no VBA

O With faz com que o código fique mais limpo e também que a chamada de um objeto do Excel seja realizada apenas uma vez, economizando assim o tempo de chamá-los várias vezes

Este é um exemplo de código que não deve ser usado ao mudar as propriedades de objetos no Excel VBA.

Public Sub lsSemWith()
    On Error GoTo TratarErro
  
    Planilha1.Range("A10").Value2 = 10
    Planilha1.Range("A10").Font.Size = 15
    Planilha1.Range("A10").Font.Color = vbRed
  
Sair:
    Exit Sub
TratarErro:
    GoTo Sair
End Sub

Abaixo um exemplo de realizar a mesma instrução de uma forma mais rápida no VBA Excel:

Public Sub lsComWith()
    On Error GoTo TratarErro
  
    With Planilha1.Range("A10")
        .Value2 = 10
        .Font.Size = 15
        .Font.Color = vbRed
    End With
Sair:
    Exit Sub
TratarErro:
    GoTo Sair
End Sub

Conclusão VBA mais rápido no Excel

Como você pode notar ao longo deste artigo tivemos 10 dicas de como aumentar a velocidade do seu código VBA no Excel.

Na vídeo-aula no topo do artigo você também pode ver vários testes de tempo comparando os códigos não otimizados com os códigos otimizados para velocidade no VBA Excel.

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:

Avalie este post