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
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: