Este tutorial discutirá como acelerar macros VBA e outras práticas recomendadas de VBA.
Configurações para acelerar o código VBA
Abaixo você encontrará várias dicas para acelerar seu código VBA. As dicas são organizadas livremente por importância.
A maneira mais fácil de melhorar a velocidade de seu código VBA é desabilitando o ScreenUpdating e desabilitando os cálculos automáticos. Essas configurações devem ser desabilitadas em todos os procedimentos grandes.
Desativar atualização de tela
Por padrão, o Excel exibirá alterações na (s) pasta (s) de trabalho em tempo real à medida que o código VBA é executado. Isso causa uma grande desaceleração na velocidade de processamento, já que o Excel mais interpreta e exibe as alterações para cada linha de código.
Para desligar a atualização de tela:
| 1 | Application.ScreenUpdating = False |
No final de sua macro, você deve ativar novamente a atualização de tela:
| 1 | Application.ScreenUpdating = True |
Enquanto seu código está sendo executado, pode ser necessário “atualizar” a tela. Não há comando de “atualização”. Em vez disso, você precisará ativar a Atualização de tela novamente e desativá-la novamente.
Definir cálculos para manual
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 excessivamente lentas.
Para definir os cálculos como manuais:
| 1 | Application.Calculation = xlManual |
Para recalcular manualmente toda a pasta de trabalho:
| 1 | Calcular |
Observe que você também pode calcular apenas uma planilha, intervalo ou célula individual, se necessário para aumentar a velocidade.
Para restaurar os cálculos automáticos (no final do seu procedimento):
| 1 | Application.Calculation = xlAutomatic |
Importante! Esta é uma configuração do Excel. Se você não redefinir os cálculos para automáticos, sua pasta de trabalho não será recalculada até que você solicite.
Você verá as maiores melhorias nas configurações acima, mas existem várias outras configurações que podem fazer a diferença:
Desativar eventos
Os eventos são “gatilhos” que causam procedimentos de evento para correr. Os exemplos incluem: quando qualquer célula em uma planilha muda, quando uma planilha é ativada, quando uma pasta de trabalho é aberta, antes de uma pasta de trabalho ser salva, etc.
Desativar eventos pode causar pequenas melhorias na velocidade quando qualquer macro é executada, mas a melhoria na velocidade pode ser muito maior se sua pasta de trabalho usar eventos. E, em alguns casos, desabilitar eventos é necessário para evitar a criação de loops infinitos.
Para desativar eventos:
| 1 | Application.EnableEvents = False |
Para reativar os eventos:
| 1 | Application.EnableEvents = True |
Desativar PageBreaks
Desativar PageBreaks pode ajudar em certas situações:
- Você definiu anteriormente uma propriedade PageSetup 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 do PageSetup).
Para desativar PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = False |
Para reativar o PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = True |
Melhores práticas para melhorar a velocidade do VBA
Evite ativar e selecionar
Ao gravar uma macro, você verá muitos métodos Ativar e Selecionar:
| 12345678 | Sub Slow_Example ()Folhas ("Folha2"). SelecioneIntervalo ("D9"). SelecioneActiveCell.FormulaR1C1 = "exemplo"Intervalo ("D12"). SelecioneActiveCell.FormulaR1C1 = "demonstração"Intervalo ("D13"). SelecioneEnd Sub |
Ativar e selecionar objetos geralmente é desnecessário, eles adicionam desordem ao seu código e consomem muito tempo. Você deve evitar esses métodos quando possível.
Exemplo aprimorado:
| 1234 | Sub Fast_Example ()Folhas ("Folha2"). Intervalo ("D9"). FormulaR1C1 = "exemplo"Folhas ("Folha2"). Intervalo ("D12"). FormulaR1C1 = "demonstração"End Sub |
Evite copiar e colar
A cópia requer memória significativa. Infelizmente, você não pode 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 reduzir drasticamente a velocidade do seu código ou até mesmo travar o Excel.
Em vez de copiar e colar, considere definir as propriedades de valor das células.
| 123456789 | Sub CopyPaste ()'Mais devagarIntervalo ("a1: a1000"). Copiar intervalo ("b1: b1000")'Mais rápidoIntervalo ("b1: b1000"). Valor = Intervalo ("a1: a1000"). ValorEnd Sub |
Use os loops For Each em vez de Loops For
Ao percorrer os objetos, o loop For Each é mais rápido do que o Loop For. Exemplo:
Este For Loop:
| 123456 | Sub Loop1 ()dim i como alcancePara i = 1 a 100Células (i, 1). Valor = 1Proximo euEnd Sub |
| 123456 | Sub Loop2 ()Dim cell como rangePara cada célula no intervalo ("a1: a100")cell.Value = 1Próxima célulaEnd Sub |
Declarar Variáveis / Usar Opção Explícita
O VBA não exige que você declare suas variáveis, a menos que você adicione Option Explicit no topo do seu módulo:| 1 | Opção Explícita |
| 1234 | Sub OptionExplicit ()var1 = 10MsgBox varlEnd Sub |
Use com - Terminar com declarações
Se você fizer referência aos mesmos objetos várias vezes (por exemplo, intervalos, planilhas, pastas de trabalho), considere o uso da instrução With. É mais rápido de processar, pode tornar seu código mais fácil de ler e simplifica seu código.Com exemplo de declaração:| 12345678 | Sub Faster_Example ()Com folhas ("Folha2").Range ("D9"). FormulaR1C1 = "exemplo".Range ("D12"). FormulaR1C1 = "demonstração".Range ("D9"). Font.Bold = True.Range ("D12"). Font.Bold = TrueTerminar comEnd Sub |
| 123456 | Sub Slow_Example ()Folhas ("Folha2"). Intervalo ("D9"). FormulaR1C1 = "exemplo"Folhas ("Folha2"). Intervalo ("D12"). FormulaR1C1 = "demonstração"Folhas ("Folha2"). Intervalo ("D9"). Font.Bold = TrueFolhas ("Folha2"). Intervalo ("D12"). Font.Bold = TrueEnd Sub |
Dicas de melhores práticas avançadas
Proteger UserInterfaceOnly
É uma boa prática evitar que suas planilhas editem células desprotegidas para evitar que o usuário final (ou você!) Corrompa acidentalmente a pasta de trabalho. No entanto, isso também protegerá as planilhas 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.
| 12345 | Sub UnProtectSheet ()Planilhas (“planilha1”). Desproteger ”senha”'Editar Folha1Planilhas (“planilha1”). Proteger ”senha”End Sub |
Em vez disso, você pode proteger as planilhas configurando UserInterfaceOnly: = True. Isso permite que o VBA faça alterações nas planilhas, ao mesmo tempo em que as protege do usuário.
| 1 | Planilhas (“planilha1”). Senha de proteção: = "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 Thisworkbook:
| 123456 | Sub Workbook_Open ()Dim ws como planilhaPara cada ws nas planilhasws.Protect Password: = "password", UserInterFaceOnly: = TruePróximo wsEnd Sub |
ou este código em qualquer módulo regular:
| 123456 | Private Sub Auto_Open ()Dim ws como planilhaPara cada ws nas planilhasws.Protect Password: = "password", UserInterFaceOnly: = TruePróximo wsEnd Sub |
Use matrizes para editar grandes intervalos
Pode ser muito demorado manipular grandes intervalos de células (Ex. 100.000+). Em vez de percorrer intervalos de células, manipulando cada célula, você pode carregar as células em uma matriz, processar cada item na matriz e, em seguida, produzir a matriz de volta em suas células originais. Carregar as células em matrizes para manipulação pode ser muito mais rápido.
| 1234567891011121314151617181920212223242526272829303132 | Sub LoopRange ()Dim cell como rangeDim tStart As DoubletStart = TimerPara cada célula no intervalo ("A1: A100000")cell.Value = cell.Value * 100Próxima célulaDebug.Print (Timer - tStart) e "segundos"End SubSub LoopArray ()Dim arr As VariantDim item como varianteDim tStart As DoubletStart = Timerarr = Intervalo ("A1: A100000"). ValorPara cada item recebidoitem = item * 100Próximo itemIntervalo ("A1: A100000"). Valor = arrDebug.Print (Timer - tStart) e "segundos"End Sub |
