10 Formas de acelerar suas macros - manequins

À medida que suas macros do Excel se tornam cada vez mais robustas e complexas, você pode achar que elas perdem o desempenho. Ao discutir macros, a palavra desempenho geralmente é sinônimo de velocidade . Velocidade é a rapidez com que seus procedimentos VBA executam as tarefas pretendidas. Seguem-se dez maneiras de ajudar a manter suas macros do Excel em execução no seu melhor nível de desempenho.

Cálculos da planilha de interrupção

Você sabia que cada vez que uma célula que afeta qualquer fórmula em sua planilha é alterada ou manipulada, o Excel recalcula toda a planilha? Nas planilhas que possuem uma grande quantidade de fórmulas, esse comportamento pode reduzir drasticamente suas macros.

Você pode usar o aplicativo. Propriedade de cálculo para indicar ao Excel que mude para o modo de cálculo manual. Quando uma pasta de trabalho está no modo de cálculo manual, a pasta de trabalho não será recalculada até que você desencadeie explicitamente um cálculo pressionando a tecla F9.

Coloque o Excel no modo de cálculo manual, execute seu código e volte ao modo de cálculo automático.

Sub Macro1 () Aplicação. Cálculo = xlCalculationManual 'Coloque seu código de macro aqui Aplicação. Cálculo = xlCalculationAutomatic End Sub

Configurando o modo de cálculo de volta para xlCalculationAutomatic irá ativar automaticamente um recálculo da planilha, portanto, não há necessidade de pressionar a tecla F9 depois que sua macro é executada.

Desativando a atualização da tela

Você pode notar que, quando suas macros são executadas, sua tela faz uma certa quantidade de cintilação. Essa cintilação é o Excel tentando redesenhar a tela para mostrar o estado atual da planilha. Infelizmente, cada vez que o Excel redescreva a tela, ela ocupa recursos de memória.

Você pode usar o aplicativo. ScreenUpdating propriedade para desativar as atualizações da tela até que sua macro tenha sido concluída. Desativar a atualização da tela economiza tempo e recursos, permitindo que sua macro seja executada um pouco mais rápido. Depois que seu código de macro terminar de ser executado, você pode voltar a atualizar a tela.

Sub Macro1 () Aplicação. Cálculo = xlCalculationManual Application. ScreenUpdating = False 'Coloque seu código de macro aqui Aplicação. Cálculo = xlCalculationAutomatic Application. ScreenUpdating = True End Sub

Depois de configurar a propriedade ScreenUpdating novamente em True, o Excel irá automaticamente acionar um redraw da tela.

Desativando 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 determinadas ações no Excel.Se a sua macro estiver trabalhando com muitos dados, a barra de status ocupará alguns recursos.

É importante notar que desligar a atualização da tela é separado de desligar a exibição da barra de status. A barra de status continuará a ser atualizada mesmo se você desativar a atualização da tela. Você pode usar o aplicativo. Propriedade DisplayStatusBar para desabilitar temporariamente todas as atualizações da barra de status, aprimorando ainda mais o desempenho da sua macro:

Sub Macro1 () Application. Cálculo = xlCalculationManual Application. ScreenUpdating = False Application. DisplayStatusBar = False 'Coloque seu código de macro aqui Aplicação. Cálculo = xlCalculationAutomatic Application. ScreenUpdating = True Application. DisplayStatusBar = True End Sub

Dizer ao Excel para ignorar eventos

Você pode implementar macros como procedimentos de eventos, informando o Excel para executar determinado código quando uma planilha ou uma pasta de trabalho mudar.

Às vezes, macros padrão fazem alterações que desencadearão um procedimento de evento. Por exemplo, se você tiver uma macro padrão que manipule várias células na Sheet1, cada vez que uma célula naquela folha for alterada, sua macro deve pausar enquanto o evento Worksheet_Change é executado.

Você pode adicionar outro nível de aumento de desempenho usando a propriedade EnableEvents para indicar ao Excel que ignore os eventos enquanto a macro é executada.

Defina a propriedade EnableEvents como False antes de executar a macro. Depois que seu código de macro terminar, você pode definir a propriedade EnableEvents de volta para Verdadeiro.

Sub Macro1 () Aplicação. Cálculo = xlCalculationManual Application. ScreenUpdating = False Application. DisplayStatusBar = False Application. EnableEvents = False 'Coloque seu código de macro aqui Aplicação. Cálculo = xlCalculationAutomatic Application. ScreenUpdating = True Application. DisplayStatusBar = Aplicação Verdadeira. EnableEvents = True End Sub

Ocultando quebras de página

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

Você pode evitar esse comportamento simplesmente escondendo as quebras de página antes de iniciar sua macro.

Defina a propriedade da folha DisplayPageBreaks como False para ocultar quebras de página. Se você quiser continuar a mostrar quebras de página depois que a macro é executada, defina a propriedade SheetPageBreaks para True.

Sub Macro1 () Aplicação. Cálculo = xlCalculationManual Application. ScreenUpdating = False Application. DisplayStatusBar = False Application. EnableEvents = False Activesheet. DisplayPageBreaks = False 'Coloque seu código de macro aqui Aplicação. Cálculo = xlCalculationAutomatic Application. ScreenUpdating = True Application. DisplayStatusBar = Aplicação Verdadeira. EnableEvents = True Activesheet. DisplayPageBreaks = True End Sub

Suspendendo atualizações da tabela dinâmica

Se sua macro manipular tabelas dinâmicas que contenham grandes fontes de dados, você pode ter um desempenho fraco ao fazer coisas como adicionar ou mover dinamicamente campos dinâmicos.

Você pode melhorar o desempenho da sua macro, suspendendo o recálculo da tabela dinâmica até que todas as mudanças no campo pivô tenham sido feitas. Basta configurar a tabela dinâmica. ManualUpdate propriedade para True para adiar o recálculo, executar seu código de macro e, em seguida, definir a tabela dinâmica. ManualUpdate propriedade de volta para False para acionar o recálculo.

Sub Macro1 () ActiveSheet. Tabelas dinâmicas ("Tabela dinâmica1"). ManualUpdate = True 'Coloque seu código de macro aqui ActiveSheet. Tabelas dinâmicas ("Tabela dinâmica1"). ManualUpdate = False End Sub

Direção clara de copiar e colar

É importante lembrar que, embora o Macro Recorder economize tempo escrevendo o código VBA para você, nem sempre escreve o código mais eficiente. Um excelente exemplo é como o Macro Recorder captura qualquer ação de copiar e colar que você executa durante a gravação.

Você pode dar a suas macros um ligeiro impulso cortando o intermediário e realizando uma cópia direta de uma célula para uma célula de destino. Este código alternativo usa o argumento de Destino para ignorar a área de transferência e copiar o conteúdo da célula A1 diretamente para a célula B1.

Gama ("A1"). Copiar destino: = intervalo ("B1")

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

Faixa ("B1"). Valor = intervalo ("A1"). Valor

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 na mesma fórmula contida na célula de origem:

Faixa ("B1"). Formula = Range ("A1"). Fórmula

Usando a instrução With com

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

A instrução With, utilizada no exemplo a seguir, solicita ao Excel que aplique todas as alterações de formatação ao mesmo tempo:

Com intervalo ("A1"). Fonte. Negrito = verdadeiro. Itálico = Verdadeiro. Sublinhado = xlUnderlineStyleSingle End Com

Entrando no hábito de fazer ações com as declarações não só manterá suas macros funcionando mais rápido, mas também facilitam a leitura do seu código de macro.

Evitando o método de seleção

O Gravador de Macro gosta de usar o método Selecionar para selecionar objetos de forma explícita antes de tomar ações neles. Geralmente, não é necessário selecionar objetos antes de trabalhar com eles. Na verdade, você pode melhorar drasticamente o desempenho da macro ao não usar o método Select.

Depois de gravar suas macros, torne-se o hábito de alterar o código gerado para remover os métodos Selecionar. Nesse caso, o código otimizado seria o seguinte:

Folhas ("Folha1"). Gama ("A1"). FormulaR1C1 = "1000" Folhas ("Folha2"). Gama ("A1"). FormulaR1C1 = folhas "1000" ("Folha3"). Gama ("A1"). FormulaR1C1 = "1000"

Observe que o 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 suas macros é limitar a quantidade de vezes que você faz referência aos dados da planilha em seu código. É sempre menos eficiente capturar dados da planilha do que da memória. Ou seja, suas macros serão executadas muito mais rápido se não tiverem que interagir repetidamente com a planilha.

Por exemplo, o seguinte código simples força o VBA a retornar continuamente para Folhas ("Folha1"). Range ("A1") para obter o número necessário para a comparação sendo realizada na instrução If:

Para ReportMonth = 1 a 12 If Range ("A1"). Value = ReportMonth Then MsgBox 1000000 / ReportMonth End If Next ReportMonth

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

Dim MyMonth como Integer MyMonth = Range ("A1"). Valor para ReportMonth = 1 a 12 Se MyMonth = ReportMonth, em seguida, MsgBox 1000000 / ReportMonth End If Next ReportMonth

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