Excel 2016 dicas de velocidade vba

VBA para Excel 2016 é rápido, mas nem sempre é rápido o suficiente. (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 seus macros.

Desligar atualização da tela

Ao executar uma macro, você pode sentar e assistir toda a ação na tela que ocorre no macro. Apesar de fazer isso pode ser instrutivo, depois que você começa a macro funcionando corretamente, muitas vezes é irritante e pode retardar o desempenho do seu macro consideravelmente. Felizmente, você pode desativar a atualização da tela que normalmente ocorre quando você executar uma macro. Para desativar a atualização da tela, use a seguinte declaração:

Application.ScreenUpdating = False

Se você deseja que o usuário veja o que está acontecendo em qualquer ponto durante a macro, use a seguinte instrução para virar tela atualizar novamente:

Application.ScreenUpdating = True

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

Sub FillRange () Dim r como longo, c Como LongDim Número como LongNumber = 0For r = 1 Para 50For c = 1 Para 50Number = Número + 1Cells (r, c) .SelectCells (r, c) .Value = NumberNext cNext Rend sub

Você vê cada célula sendo selecionado eo valor a ser introduzido nas células. Agora insira a seguinte declaração no início do procedimento e executá-lo novamente:

Application.ScreenUpdating = False

A faixa é preenchido muito mais rápido, e você não vê o resultado até que a macro seja concluída em execução e atualização da tela é (automaticamente) definida como True.

Quando você está depurando código, a execução do programa, às vezes termina em algum lugar no meio sem ter de tela voltou a atualização novamente. Isso às vezes causa janela do aplicativo do Excel para se tornar totalmente indiferente. A maneira de sair deste estado congelado é simples: Volte para o VBE, e execute a seguinte instrução na janela Immediate:

Application.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 consideravelmente, definindo o modo de cálculo para manual, enquanto a macro está em execução. Quando a macro termina, defina o modo de cálculo de volta para automático.

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

Application.Calculation = xlCalculationManual

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

Application.Calculation = xlCalculationAutomatic

Se o seu código utiliza células com os resultados das fórmulas, desligando cálculo significa que as células não será recalculado menos que você diga explicitamente Excel para fazê-lo!

Eliminando essas mensagens de alerta traquinas

Como você sabe, a macro pode executar automaticamente uma série de ações. Em muitos casos, você pode iniciar uma macro e depois ir sair na sala de descanso, enquanto Excel faz sua coisa. Algumas operações do Excel, no entanto, exibir mensagens que exigem uma resposta humana. Estes tipos de mensagens significa que você não pode deixar Excel sem vigilância enquanto executa sua macro - a menos que você sabe o truque secreto.

O truque segredo para evitar estas mensagens de alerta está inserindo a seguinte declaração VBA em sua macro:

Application.DisplayAlerts = False

Excel executa a operação padrão para esses tipos de mensagens. No caso de exclusão de uma folha, a operação padrão é Excluir. Se você não tiver certeza de que a operação padrão é, realizar um teste para ver o que acontece.

Quando o procedimento termina, Excel redefine automaticamente os DisplayAlerts propriedade para True. Se você precisa para transformar os alertas de volta antes de o procedimento termina, utilize esta declaração:

Application.DisplayAlerts = True

Simplificar referências de objeto

Como você provavelmente já sabe, referências a objetos podem se tornar muito longa. Por exemplo, uma referência totalmente qualificado para um objeto Range pode ser parecido com este:

Pastas de trabalho ( “MyBook.xlsx”). Worksheets ( “Sheet1”) _.range ( “TaxaDeDesvio”)

Se a sua macro usa freqüentemente esta faixa, você pode querer criar uma variável de objeto usando o comando Set. Por exemplo, a seguinte instrução atribui o objeto Range para uma taxa variável objeto nomeado:

Definir Velocidade = Workbooks ( “MyBook.xlsx”) _.Worksheets ( “Sheet1”). Range ( “TaxaDeDesvio”)

Depois de definir esta variável de objeto, você pode usar a taxa variável em vez da referência demorado. Por exemplo, você pode alterar o valor do TaxaDeDesvio celular chamado:

Rate.Value = 0,085

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

Pastas de trabalho ( “MyBook.xlsx”). Worksheets ( “Sheet1”). _Range ( “TaxaDeDesvio”) = 0,085

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

Declarando tipos de variáveis

Você normalmente não precisa se preocupar com o tipo de dados que você atribuir a uma variável. Excel lida com todos os detalhes para você nos bastidores. Por exemplo, se você tem uma variável chamada MyVar, você pode atribuir um número de qualquer tipo para essa variável. Você pode até atribuir uma cadeia de texto a ele mais tarde no procedimento.

Se você quer que seus procedimentos para executar o mais rápido possível, diga Excel que tipo de dados será atribuído a cada uma de suas variáveis. Isto é conhecido como declarando tipo de uma variável.

Em geral, você deve usar o tipo de dados que requer o menor número de bytes, mas ainda pode lidar com todos os dados que lhe são atribuídas. Quando VBA trabalha com dados, velocidade de execução depende do número de bytes VBA tem à sua disposição. Em outras palavras, os dados menos bytes utiliza, a VBA mais rápido pode aceder e manipular os dados. Uma exceção a isso é o tipo de dados Integer. Se a velocidade é fundamental, use o tipo de dados Long em seu lugar.

Se você usar uma variável de objeto, você pode declarar a variável como um tipo de objeto particular. Aqui está um exemplo:

Dim Taxa como RangeSet Rate = Workbooks ( “MyBook.xlsx”) _.Worksheets ( “Sheet1”). Range ( “TaxaDeDesvio”)

Usando o com-extremidade com uma estrutura

Você precisa definir um número de propriedades para um objeto? Seu código é executado mais rápido se você usar o Com-End Com estrutura. Um benefício adicional é que seu código pode ser mais fácil de ler.

Video: HYPERLINK + VBA + MACRO // Feras do Excel // Dicas de Excel // Excel Avançado

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

Selection.HorizontalAlignment = xlCenterSelection.VerticalAlignment = xlCenterSelection.WrapText = TrueSelection.Orientation = 0Selection.ShrinkToFit = FalseSelection.MergeCells = False

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

Video: Excel VBA Avançado - Barra de Progresso - Dica 5

Com Selection.HorizontalAlignment = xlCenter.VerticalAlignment = xlCenter.WrapText = True.Orientation = 0.ShrinkToFit = False.MergeCells = FalseEnd com

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