Excel 2016 VBA Speed ​​Tips - dummies

O VBA para o Excel 2016 é rápido, mas nem sempre é rápido o suficiente. (Os programas de computador nunca são rápidos o suficiente.) Continue lendo para descobrir alguns exemplos de programação que você pode usar para acelerar suas macros.

Desligando a atualização da tela

Ao executar uma macro, você pode sentar e assistir todas as ações na tela que ocorrem na macro. Embora isso possa ser instrutivo, depois de ter a macro funcionando corretamente, muitas vezes é irritante e pode retardar consideravelmente o desempenho de sua macro. Felizmente, você pode desativar a atualização da tela que normalmente ocorre quando você executa uma macro. Para desligar a atualização da tela, use a seguinte declaração:

Aplicação. ScreenUpdating = False

Se você deseja que o usuário veja o que está acontecendo em qualquer ponto durante a macro, use a seguinte declaração para ativar a atualização da tela novamente:

Aplicação. ScreenUpdating = True

Para demonstrar a diferença de velocidade, execute esta macro simples, que preenche um intervalo com números:

Sub FillRange () Dim r como Long, c Como número Dim longo como Long Number = 0 Para r = 1 a 50 Para c = 1 a 50 número = número + 1 células (r, c). Selecione as células (r, c). Valor = Número Próximo c Próximo r End Sub

Você vê cada célula sendo selecionada eo valor que está sendo inserido nas células. Agora, insira a seguinte declaração no início do procedimento e execute-a novamente:

Aplicação. ScreenUpdating = False

O intervalo é preenchido muito mais rápido e você não vê o resultado até a macro terminar e a atualização da tela (automaticamente) é definida como True.

Quando você está depurando o código, a execução do programa às vezes termina em algum lugar no meio, sem que você tenha ativado a atualização da Tela novamente. Isso às vezes faz com que a janela de aplicativos do Excel se torne totalmente insensível. A saída deste estado congelado é simples: volte para o VBE e execute a seguinte declaração na janela Imediato:

Aplicação. ScreenUpdating = True

Desligando o cálculo automático

Se você tiver uma planilha com muitas fórmulas complexas, você pode achar que você pode acelerar as coisas, configurando o modo de cálculo para o manual enquanto a macro está sendo executada. Quando a macro terminar, ajuste o modo de cálculo de volta para automático.

A seguinte declaração define o modo de cálculo Excel como manual:

Aplicação. Cálculo = xlCalculationManual

Execute a próxima instrução para definir o modo de cálculo como automático:

Aplicação. Cálculo = xlCalculationAutomatic

Se seu código usa células com resultados de fórmulas, desligar o cálculo significa que as células não serão recalculadas a menos que você indique explicitamente ao Excel fazê-lo!

Eliminando as mensagens de alerta difíceis

Como você sabe, uma macro pode executar automaticamente uma série de ações. Em muitos casos, você pode iniciar uma macro e, em seguida, sair na sala de interrupção enquanto o Excel faz o que é. Algumas operações do Excel, no entanto, exibem mensagens que exigem uma resposta humana. Esses tipos de mensagens significam que você não pode deixar o Excel desacompanhado enquanto ele executa sua macro - a menos que você conheça o truque secreto.

Você pode instruir o Excel para que não exiba esses tipos de alertas durante a execução de uma macro.

O truque secreto para evitar estas mensagens de alerta é inserir a seguinte declaração VBA na sua macro:

Aplicação. DisplayAlerts = False

O Excel executa a operação padrão para esses tipos de mensagens. No caso de excluir uma folha, a operação padrão é Excluir. Se você não tem certeza do que é a operação padrão, execute uma prova para ver o que acontece.

Quando o procedimento termina, o Excel redefine automaticamente a propriedade DisplayAlerts para True. Se você precisar ativar os alertas antes do término do procedimento, use esta instrução:

Aplicação. DisplayAlerts = True

Simplificando referências de objeto

Como você provavelmente já sabe, as referências a objetos podem se tornar muito longas. Por exemplo, uma referência totalmente qualificada para um objeto Range pode ser assim:

Workbooks ("MyBook. Xlsx"). Folhas de trabalho ("Folha1") _. Range ("InterestRate")

Se sua macro usar esse intervalo com freqüência, você pode querer criar uma variável de objeto usando o comando Set. Por exemplo, a seguinte instrução atribui esse objeto Range a uma variável de objeto chamada Taxa:

Set Rate = Workbooks ("MyBook. Xlsx") _. Folhas de trabalho ("Folha1"). Range ("InterestRate")

Depois de definir esta variável de objeto, você pode usar a taxa variável em vez da longa referência. Por exemplo, você pode alterar o valor da célula chamada InterestRate:

Taxa. Valor =. 085

Isso é muito mais fácil de digitar do que a seguinte declaração:

Workbooks ("MyBook. Xlsx"). Folhas de trabalho ("Folha1"). _ Range ("InterestRate") =. 085

Além de simplificar sua codificação, o uso de variáveis ​​de objeto acelera consideravelmente suas macros.

Declarando tipos de variáveis ​​

Você geralmente não precisa se preocupar com o tipo de dados que você atribui a uma variável. O Excel lida com todos os detalhes por trás dos bastidores. Por exemplo, se você tem uma variável chamada MyVar, você pode atribuir uma quantidade de qualquer tipo a essa variável. Você pode até mesmo atribuir uma seqüência de texto a ele mais adiante no procedimento.

Se você deseja que seus procedimentos sejam executados o mais rápido possível, diga ao Excel que tipo de dados serão atribuídos a cada uma de suas variáveis. Isso é conhecido como declarando um tipo de variável.

Em geral, você deve usar o tipo de dados que requer o menor número de bytes, mas ainda pode manipular todos os dados atribuídos a ele. Quando o VBA funciona com dados, a velocidade de execução depende do número de bytes que a VBA tem à sua disposição. Em outras palavras, quanto menos uso de bytes usa, o VBA mais rápido pode acessar e manipular os dados.Uma exceção para isso é o tipo de dados Integer. Se a velocidade for crítica, use o tipo de dados Long.

Se você usa uma variável de objeto, você pode declarar a variável como um tipo de objeto específico. Aqui está um exemplo:

Dim Rate como Range Set Rate = Workbooks ("MyBook. Xlsx") _. Folhas de trabalho ("Folha1"). Range ("InterestRate")

Usando o With-End With structure

Você precisa definir uma série de propriedades para um objeto? Seu código corre mais rápido se você usar a estrutura With-End With. Um benefício adicional é que seu código pode ser mais fácil de ler.

O código a seguir não usa With-End With:

Selection. HorizontalAlignment = xlCenter Selection. VerticalAlignment = xlCenter Selection. WrapText = True Selection. Orientação = 0 Seleção. ShrinkToFit = False Selection. MergeCells = False

Aqui está o mesmo código, reescrito para usar With-End With:

With Selection. HorizontalAlignment = xlCenter. VerticalAlignment = xlCenter. WrapText = True. Orientação = 0. ShrinkToFit = False. MergeCells = False End Com

Quando você usa With-End With, verifique se cada declaração começa com um ponto.