10 maneiras de acelerar o seu Macros

Como as macros do Excel tornam-se cada vez mais robusto e complexo, você pode achar que eles perdem desempenho. Ao discutir macros, a palavra desempenho

é geralmente sinónimo de velocidade. A velocidade é a rapidez com que os procedimentos de VBA executar suas tarefas pretendidas. A seguir estão dez maneiras de ajudar a manter seus macros do Excel funcionando em seu nível ótimo desempenho.

cálculos de folha vacilantes

Você sabia que cada vez que uma célula que afeta qualquer fórmula na planilha é alterado ou manipulado, Excel recalcula a planilha inteira? Em planilhas que têm uma grande quantidade de fórmulas, esse comportamento pode diminuir dramaticamente suas macros.

Você pode usar a propriedade Application.Calculation para dizer Excel para mudar para o modo de cálculo manual. Quando uma pasta de trabalho está no modo de cálculo manual, o livro não irá recalcular até que você acionar explicitamente um cálculo pressionando a tecla F9.

Lugar do Excel no modo de cálculo manual, execute o código, e depois voltar para o modo de cálculo automático.

Sub Macro1 () Application.Calculation = xlCalculationManual'Place sua macro código hereApplication.Calculation = xlCalculationAutomaticEnd Sub

Definir o modo de cálculo de volta para xlCalculationAutomatic irá acionar automaticamente um novo cálculo da planilha, por isso não há necessidade de pressionar a tecla F9 após as corridas de macro.

a atualização da tela folha de desativação

Você pode notar que quando as macros executar, a tela faz uma boa quantidade de cintilação. Esta cintilação é Excel tentar redesenhar a tela para mostrar o estado atual da planilha. Infelizmente, cada vez que Excel redesenha a tela, que ocupa recursos de memória.

Você pode usar a propriedade Application.ScreenUpdating para desativar as atualizações de tela até que a sua macro foi concluída. Desativando a atualização da tela economiza tempo e recursos, permitindo que a sua macro para executar um pouco mais rápido. Após o código de macro terminar a execução, você pode girar a tela atualizar novamente.

Sub Macro1 () Application.Calculation = xlCalculationManualApplication.ScreenUpdating = False'Place seu código de macro hereApplication.Calculation = xlCalculationAutomaticApplication.ScreenUpdating = TrueEnd Sub

Depois de definir a propriedade ScreenUpdating volta para True, Excel irá acionar automaticamente um redesenho da tela.

Desligar as atualizações da barra de status

A barra de status do Excel, que aparece na parte inferior da janela do Excel, normalmente exibe o progresso de certas ações no Excel. Se a sua macro está trabalhando com grandes quantidades de dados, a barra de status irá ocupar alguns recursos.

É importante notar que desligar a atualização da tela é separado desligar a exibição da barra de status. A barra de status continuará a ser atualizado mesmo se você desativar a atualização da tela. Você pode usar a propriedade Application.DisplayStatusBar para desativar temporariamente quaisquer atualizações da barra de status, melhorando ainda mais o desempenho de sua macro:

Sub Macro1 () Application.Calculation = xlCalculationManualApplication.ScreenUpdating = FalseApplication.DisplayStatusBar = False'Place seu código de macro hereApplication.Calculation = xlCalculationAutomaticApplication.ScreenUpdating = TrueApplication.DisplayStatusBar = TrueEnd Sub

Dizendo Excel para ignorar eventos

Você pode implementar macros como procedimentos de evento, dizendo Excel para executar determinado código quando uma planilha ou pasta de trabalho muda.

Às vezes, macros padrão fazer alterações que irão desencadear um procedimento de evento. Por exemplo, se você tem um macro padrão que manipula várias células em Sheet1, cada vez que uma célula em que a folha é alterada, a macro tem de fazer uma pausa enquanto o evento Worksheet_Change é executado.

Você pode adicionar outro nível de desempenho impulsionar usando a propriedade EnableEvents para dizer Excel para ignorar eventos enquanto a sua macro é executada.

Defina a propriedade EnableEvents como False antes de executar a macro. Após o código de macro é concluída em execução, você pode definir a propriedade EnableEvents de volta para True.

Sub Macro1 () Application.Calculation = xlCalculationManualApplication.ScreenUpdating = FalseApplication.DisplayStatusBar = FalseApplication.EnableEvents = False'Place seu código de macro hereApplication.Calculation = xlCalculationAutomaticApplication.ScreenUpdating = TrueApplication.DisplayStatusBar = TrueApplication.EnableEvents = TrueEnd Sub

Escondendo quebras de página

Cada vez que seu macro modifica o número de linhas, modifica o número de colunas, ou altera a configuração de página de uma planilha, Excel é forçado a tomar tempo de recalcular as quebras de página na folha.

Você pode evitar este comportamento simplesmente esconder as quebras de página antes de iniciar a sua macro.

Defina a propriedade DisplayPageBreaks folha para Falso para ocultar as quebras de página. Se você quiser continuar a mostrar quebras de página após as corridas macro, defina a propriedade DisplayPageBreaks folha de volta para True.

Sub Macro1 () Application.Calculation = xlCalculationManualApplication.ScreenUpdating = FalseApplication.DisplayStatusBar = FalseApplication.EnableEvents = FalseActivesheet.DisplayPageBreaks = False'Place seu código de macro hereApplication.Calculation = xlCalculationAutomaticApplication.ScreenUpdating = TrueApplication.DisplayStatusBar = TrueApplication.EnableEvents = TrueActivesheet.DisplayPageBreaks = TrueEnd Sub

Suspender atualizações da tabela de pivô

Se os seus manipula macro pivô tabelas que contêm grandes fontes de dados, poderá detectar um fraco desempenho quando fazendo coisas como a adição ou movimentação campos pivô dinamicamente.

Você pode melhorar o desempenho do seu macro suspendendo o recálculo da tabela dinâmica até que todas as alterações de campo pivot foram feitas. Basta definir a propriedade PivotTable.ManualUpdate como True para adiar o recálculo, execute o código de macro e, em seguida, defina a propriedade PivotTable.ManualUpdate volta para Falso para acionar o recálculo.

ActiveSheet.PivotTables Sub Macro1 () ( "PivotTable1"). ManualUpdate = True'Place seus hereActiveSheet.PivotTables código de macro ( "PivotTable1"). ManualUpdate = FalseEnd Sub

Orientação clara de copiar e colar

É importante lembrar que, embora Macro Recorder economiza tempo escrevendo código VBA para você, nem sempre escrever o código mais eficiente. Um bom exemplo é a forma como Macro Recorder capta qualquer ação de copiar-e-colar de executar durante a gravação.

Você pode dar a seus macros um ligeiro impulso por cortar o intermediário e realizar uma cópia direta de uma célula para uma célula de destino. Este código alternativo utiliza o argumento de destino para contornar a área de transferência e copiar o conteúdo da célula A1 diretamente a célula B1.

. Range ( "A1") Cópia Destination: = Range ( "B1")

Se você precisa copiar apenas valores (não a formatação ou fórmulas), você pode melhorar o desempenho ainda mais, evitando o método Copy todos juntos. Basta definir o valor da célula de destino para o mesmo valor encontrado na célula de origem. Este método é de cerca de cerca de 25 vezes mais rápido do que usando o método de cópia:

Range ( "B1"). Value = Range ( "A1"). Value

Se você precisa copiar apenas fórmulas de uma célula para outra (não valores ou formatação), você pode definir a fórmula da célula de destino para a mesma fórmula contido na célula de origem:

Range ( "B1"). Formula = Range ( "A1"). Formula

Usando a instrução Com

Ao gravar macros, muitas vezes você vai manipular o mesmo objeto mais de uma vez. Você pode economizar tempo e melhorar o desempenho usando a instrução Com a executar várias ações em um determinado objeto em um tiro.

A declaração Com utilizada no exemplo a seguir informa ao Excel para aplicar todas as alterações de formatação de uma só vez:

Com Range ( "A1"). Font.Bold = True.Italic = True.Underline = xlUnderlineStyleSingleEnd com

Metendo o hábito de chunking ações em Com declarações não só manter suas macros correr mais rápido, mas também torná-lo mais fácil de ler o código de macro.

Evitando o método Select

Macro Recorder gosta de usar o método Select para selecionar explicitamente os objetos antes de tomar ações sobre eles. Geralmente não há necessidade de selecionar objetos antes de trabalhar com eles. Na verdade, você pode melhorar drasticamente o desempenho macro por não usar o método Select.

Depois de gravar suas macros, torná-lo um hábito de alterar o código gerado para remover os Selecionar métodos. Neste caso, o código otimizado seria parecido com o seguinte:

Sheets ( "Sheet1"). Range ( "A1"). FormulaR1C1 = "1000" Sheets ( "Sheet2"). Range ( "A1"). FormulaR1C1 = "1000" Sheets ( "Sheet3"). Range ( "A1" ) .FormulaR1C1 = "1000"

Note que a nada está sendo selecionado. O código simplesmente usa a hierarquia de objetos para aplicar as ações necessárias.

Limitando viagens para a planilha

Outra maneira de acelerar seus macros é limitar a quantidade de vezes que você faz referência a dados da planilha em seu código. É sempre menos eficiente para pegar os dados da folha de trabalho do que de memória. Ou seja, as macros vai correr muito mais rápido se eles não têm para interagir repetidamente com a planilha.

Por exemplo, o seguinte código simples força VBA para retornar continuamente para Sheets ( "Sheet1") Range ( "A1") para obter o número necessário para a comparação que está sendo executada na declaração If.:

Para ReportMonth = 1 To 12Se Range ( "A1"). Value = ReportMonth ThenMsgBox 1000000 / ReportMonthEnd IfNext ReportMonth

Um método muito mais eficiente é salvar o valor na Sheets ( "Sheet1"). Range ( "A1") para uma variável chamada MyMonth. Desta forma, o código faz referência a variável MyMonth em vez da planilha:

Dim MyMonth como IntegerMyMonth = Range ( "A1"). Valuefor ReportMonth = 1 To 12Se MyMonth = ReportMonth ThenMsgBox 1000000 / ReportMonthEnd IfNext ReportMonth

Considere alavancar variáveis ​​para trabalhar com dados na memória ao invés de referenciar diretamente planilhas.