Esta é uma dica de performance um pouco mais avançada.
Existem várias funções no VBA que também existem no Excel, e
são chamadas por um comando do tipo appplication.worksheetfunction. Alguns
exemplos são funções como arredondar, mínimo, máximo, etc…
Porém, nos casos que precisamos de alta performance, faz
sentido essas funções ou utilizar a versão VBA delas, se houver.
Isto porque o worksheetfunction faz o equivalente a jogar na
planilha, aplicar a função e voltar para o código.
Um exemplo simples.
A macro1 utiliza application.worksheetfunction.round 25
milhões de vezes. No meu computador, demora 139 segundos, ou seja, mais de 2
minutos.
For i = 1 To 5000
For j = 1 To 5000
y =
Application.WorksheetFunction.Round(i / j, 0)
Next j
Next i
Já a macro2 utiliza math.round (função nativa VBA),
demorando 2 segundos.
O Excel é uma ferramenta de análise de dados
extremamente poderosa. Porém, quando ele fica grande demais, começa a
travar, os cálculos ficam demorados, etc.
Há algumas dicas a respeito, que podem ajudar a melhorar bastante a performance.
1) Restringir o Procv
Para fazer cruzamento entre bases, usa-se bastante o
procv. Um procv irrestrito é quando o critério pega a coluna inteira,
como a seguir.
=PROCV(C2;F:F;1)
Embora seja fácil e garanta que uma nova base com
mais linhas entre na conta, a performance diminui. Isso porque o Excel
vai tentar procurar desde a primeira linha (1) até a última linha
(1.058.576).
Deve-se limitar o range de buscas, para um número
suficientemente grande para a base. Por exemplo, se 5000 linhas forem
suficientes:
=PROCV(C2;F1:F5000;1)
2) Restringir Somase, somases, contses, e outros do tipo.
A mesma dica de restringir o range vale para fórmulas como somases e outras do tipo, pela mesma razão descrita acima.
3)
Excluir (não é apagar, é excluir) linhas vazias. Às vezes, uma
planilha pode estar com 10 linhas com informação, mas estar utilizando 1
milhão de linhas! Isto ocorre, por exemplo, se alguém utilizar as 1
milhão de linhas, e apagar o conteúdo (com a tecla
del). Isto vai apagar o valor da célula, mas o Excel vai continuar
considerando que o range útil é de 1 milhão de linhas. O mesmo vale para
colunas vazias.
Deve-se excluir (não apagar) esse range (selecionar as linhas, clicar
com o botão direito e excluir – ou utilizar o atalhao ctrl -).
Uma forma de conferir o range utilizado é através do atalho ctrl end. Isto vai levar o cursor à última célula livre.
4)
Vínculos: algumas vezes, a tabela pode ter vínculos, como o da imagem
a seguir. Isto pode dificultar muito a manipulação da planilha, se o
vínculo for de milhares de células, por exemplo.
Este vínculo é mesmo necessário? Se não for, em Dados -> Editar links (no Excel 2010) há uma opção para excluir o vínculo.
5)
Nomes. Atrelado ao tema vínculos. De vez em quando, há um nome
vinculando outra fonte de informações, e pesando muito. Pode ser que nem
o usuário tenha criado, mas algum outro Addin que manipula o excel.
Uma forma de deletar tudo o quanto é nome é rodando o código a seguir.
Publiquei um artigo na revista Mundo Logística deste mês, sobre a nossa Central de Monitoramento Logístico, com o apoio do time do projeto.
Foi
um mega projeto que envolveu criar toda a infraestrutura de comunicação
(torres e repetidoras), o software de gerenciamento de eventos e a
inteligência matemática para distribuição dos veículos.
Estamos à disposição para compartilhar ideias a respeito.
Exercício na forma de teste. Dada uma tabela, tirar a média dos valores por região, apenas se o campo “Considerar” for igual a 1. Três resoluções abaixo.
1) O método mais simples é usar a função média.ses.
Conforme o próprio nome indica, a função retorna a média, para vários critérios.
=médiases (B:B; A:A;E4; C:C;1)
B:B – Porque os valores estão na coluna B
A:A – primeiro critério, região
E4 – a região escolhida, digamos Curitiba
C:C – segundo critério, considerar ou não
1 – considerar apenas quem é 1
2 – O segundo método é criar uma tabela dinâmica.
A única “pegadinha” é que a tabela dinâmica normalmente faz a soma. Tem que mudar para média.
3 – Nas versões antigas
de Excel, a função média.ses não existia. Mas dava para fazer com
somases e cont.ses, e depois dividindo um pelo outro. É até mais
didático.
