9 Dicas para deixar a macro mais rápida
Um dos artigos mais lidos aqui do Função Excel é o 7 Dicas para deixar a macro mais rápida, que você pode acessar clicando aqui. Pensando nisso vamos dar continuidade a estas dicas para melhorar ainda mais o desempenho de suas macros.
Para uma versão em inglês deste post, clique aqui.
1 – Desabilitar a atualização da barra de status
A barra de status normalmente exibe o progresso de certas ações no Excel. Por exemplo, se você copiar / colar um intervalo, o Excel irá mostrar o progresso dessa operação na barra de status. Muitas vezes, a ação é executada tão rápida que você não vê o progresso na barra de status. No entanto, se a 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 é diferente de desligar a exibição da barra de status. Ou seja, a barra de status continuará a ser atualizado mesmo se você desativar a atualização da tela.
Application.DisplayStatusBar = False 'Desabilita atualização da barra de status 'Seu código aqui Application.DisplayStatusBar = True 'Habilita atualização da barra de status
2 – Ignore eventos do Excel
Vamos dizer que você tem um evento Worksheet_Change implementado para Plan1 da pasta de trabalho. Toda vez que uma célula ou intervalo é alterado em Plan1, o evento Worksheet_Change dispara. Então, se você tem uma macro padrão que manipula várias células em Plan1, cada vez que uma célula da planilha é alterada, a macro tem de fazer uma pausa enquanto o evento Worksheet_Change é executado. Você pode imaginar como esse comportamento pode pesar a sua macro. Assim, você pode dizer ao Excel para ignorar eventos enquanto a sua macro é executada.
Application.EnableEvents = False 'Desativa os eventos no excel 'Seu código aqui Application.EnableEvents = True 'Ativa os eventos no Excel
3 – Desabilitar quebras de página
Outra oportunidade para um aumento de desempenho pode ser encontrado em 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 de página de uma planilha, o Excel será forçado a tomar um tempo para recalcular onde as quebras de página são mostrados na folha. Você pode evitar isso simplesmente escondendo as quebras de página antes de iniciar a sua macro.
Activesheet.DisplayPageBreaks = False 'Desabilita as quebras de páginas 'Seu código aqui Activesheet.DisplayPageBreaks = True 'Habilita as quebras de páginas
4 – Desabilite a atualização de tabelas dinâmicas
Se as suas macros interferem em tabelas dinâmicas que contêm grandes fontes de dados, é possível que você tenha uma queda de desempenho quando estas são executadas. Isso ocorre porque cada mudança que você faz com a estrutura da tabela dinâmica requer que o Excel recalcule todos os valores na tabela dinâmica para cada campo. Você pode melhorar o desempenho do seu macro suspendendo o recálculo da tabela dinâmica até que todas as alterações sejam concluídas pela macro.
ActiveSheet.PivotTables(“PivotTable1”).ManualUpdate=True 'Seu código aqui ActiveSheet.PivotTables(“PivotTable1”).ManualUpdate=False
5 – Utilize o With sempre que possível
Ao gravar macros, não é incomum manipular o mesmo objeto mais de uma vez. Por exemplo, o código pode alterar a formatação da célula A1 para que ela seja sublinhado, itálico e negrito. Se você utilizar a função de gravar macro e aplicar estas opções de formatação para a célula A1, você teria algo parecido com isto.
Range(“A1”).Select Selection.Font.Bold = True Selection.Font.Italic = True Selection.Font.Underline = xlUnderlineStyleSingle
Infelizmente, este código não é tão eficiente quanto poderia ser porque força o Excel para selecionar e, em seguida, alterar cada propriedade separadamente. Você pode economizar tempo e melhorar o desempenho usando o With e executar várias ações em um determinado objeto em de uma só vez. Assim, o mesmo efeito que teríamos com o código acima pode ser obtido com o código abaixo.
With Range(“A1”).Font .Bold = True .Italic = True .Underline = xlUnderlineStyleSingle End With
6 – Evite acessar dados da 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 buscar os dados da planilha de trabalho do que de memória. Ou seja, a macro vai rodar muito mais rápido se ela não tiver que interagir repetidamente com a planilha. Por exemplo, este código simples força a macro a retornar continuamente para Range ( “A1”) para obter o número necessário para a comparação.
For RelatoMês = 1 To 12 If Range(“A1”).Value = RelatoMês Then MsgBox 1000000 / RelatoMês End If Next RelatoMês
Uma maneira muito mais eficiente é salvar o valor na Range ( “A1”) em uma variável. Desta forma, o código faz referência a variável em vez da planilha. Conforme abaixo.
Dim MeuMês as Integer MeuMês = Range(“A1”).Value For RelatoMês = 1 To 12 If MeuMês= RelatoMês Then MsgBox 1000000 / RelatoMês End If Next RelatoMês
7 – Declare variáveis como variáveis e constantes como constantes
O mais comum é usarmos sempre as variáveis, que são objetos que armazenam um determinado valor que pode ser alterado ao longo da execução da macro. Estes valor normalmente servem como controladores ou auxiliares. Porém em alguns casos criamos um objeto para guardar um valor que será constante, que não precisará ser alterado em nenhum momento do código, nestes casos devemos declarar este objeto como uma constante. A diferença é que uma variável precisa ter o seu valor verificado a todo momento em que ela é utilizada no código, pois ele pode variar, enquanto que a constante é sempre o mesmo valor desde o momento em que é criada.
Assim ao invés de usar:
Dim Pi As Double Pi = 3.14159
Utilize algo como:
Const Pi = 3.14159
8 – Utilize variáveis do tipo Boolean sempre que possível
As variáveis do tipo Boolean podem variar em apenas 2 valores, VERDADEIRO e FALSO. Isso faz com que este típo de variável seja excelente para uso como interruptores, quando precisamos apenas verificar se uma condição é verdadeira ou não para dar sequência ao código. E por serem tão simples são muito leves e rápidas, portanto tente evitar código como o abaixo.
Dim Chave as Integer Chave = 1 If Chave = 1 Then MsgBox "Função Excel" ElseIf Chave = 0 Then MsgBox "Tchau!" End If
E utilize algo como:
Dim Chave as Boolean Chave = True If Chave = True Then MsgBox "Função Excel" Else MsgBox "Tchau!" End If
9 – Evite selecionar planilhas e células
Esta dica já havia aparecido no artigo 7 Dicas para deixar a macro mais rápida, mas agora volta um pouco mais detalhada.
Evite sempre que possível usar o .select isso diminui drasticamente a velocidade de sua macro. Evite códigos como:
Worksheets("Plan1").Select
Variável = Range("A1").ValueE procure algo como:
Variável = Worksheets("Plan1").Range("A1").ValueConheça mais dicas para acelerar sua macro clicando aqui.