Melhorar o Desempenho do PROCV

Todos que usam Excel já passaram por esse cenário uma vez: há uma pasta de trabalho que possui uma tabela muito grande e há diversos PROCV e demora muito tempo para recalcula-la. Neste artigo vou mostrar como tornar seu PROCV 1000 vezes mais rápido. Não estou exagerando: em alguns casos, pode ser mais que 1000 vezes. Se você acha que fiquei doido, vou fazer uma afirmação mais ousada ainda: farei isso substituindo cada PROCV da planilha por uma fórmula com dois PROCV!

Estudo de Caso

Observem o cenário abaixo:

A tabela Banco de Dados (que chamei de tblBancoDeDados), possui aproximadamente 25000 linhas. A Tabela de Testes possui o mesmo tamanho, registros aleatórios de tblBancoDeDados na coluna ID, e possui uma fórmula de PROCV para retornar os valores correspondentes na coluna Um PROCV Linear dada por:

=PROCV([@ID];tblDados;2;0)

No meu computador, foram necessários 11,74 segundos para calcular todos esses PROCV. Uma eternidade, se você precisa alterar com frequência os dados da tabela tblBancoDeDados.

Utilizando uma técnica curiosa, reduzi esse tempo de 11,74 segundos para 0,078 segundos, ou melhor: míseros 78 milissegundos!

A fórmula utilizada para alcançar esse resultado é:

=SE(PROCV([@ID];tblDados;1)=[@ID];PROCV([@ID];tblDados;2);NÃO.DISP())

A primeira observação que chama atenção é que os PROCV utilizados não são do tipo de correspondência exata, mas sim o de busca aproximada (omitir o último argumento do PROCV é o mesmo que considera-lo como VERDADEIRO ou 1).

Vejam que interessante o resultado do teste:

Afinal o que é um PROCV Linear e um PROCV Binário?

Algoritmos de Busca

Busca Linear

O PROCV mais utilizado pelas pessoas é aquele cujo último argumento é 0 ou FALSO. Chamo esse PROCV de linear por causa da forma incremental de como ele busca os dados.

Para efeito de estudo, considere a tabela abaixo de apenas 20 registros. Suponha que a fórmula esteja buscando pelo registro 1310 para retornar Ana. A função procura esse registro na primeira célula da coluna ID. Se não encontrar 1310, procura na segunda, e assim por diante, fazendo um total de 13 iterações até retornar o valor correspondente a 1310, que é Ana:

Busca Binária

O princípio de funcionamento de uma busca linear é dividir um vetor pela metade sucessivas vezes até encontrar o valor procurado. No exemplo a seguir, o tamanho do vetor é 20. Então, a busca linear verifica se o registro na posição 10 é maior ou menor que o valor ( 1310) procurado. Se for menor, procura 1310 na metade do vetor formado pelos registros 1 a 10, caso contrário, de 11 a 20, que é nosso caso.

No próximo passo, o algoritmo procura verifica se o 15o registro (metade entre 11 e 20) é maior ou menor que o valor procurado. Como é menor, o novo vetor de busca se restringe aos registros de 11 a 15. Por fim, o algoritmo verifica se o registro da metade desse intervalo (13o registro) é igual ao valor procurado, que é verdade, e então retorna sua correspondência, que é José.

Há um ponto de atenção importantíssimo no que se refere ao algoritmo binário: o vetor de busca deve estar classificado em ordem crescente! Essa é sua única limitação. Se essa condição não for atendida, você irá obter resultados errados nas suas fórmulas. Em contrapartida, a busca linear funciona inclusive em vetores que não estejam ordenados. Como essa condição é muito importante e traz resultados errados se não for atendida, vou destaca-la para ficar bem claro:

O PROCV na forma binária só funciona em tabelas cuja coluna de procura esteja classificada em ordem crescente!

Comparação entre PROCV Binário e PROCV Linear

Abaixo podemos ver como o algoritmo binário é muito mais rápido que o linear. Veja que à medida que aumentamos a quantidade de linhas do vetor de procura, a quantidade de iterações no algoritmo linear aumenta na mesma proporção. Por outro lado, no caso do algoritmo binário soma-se aproximadamente apenas 4 passos ao multiplicar o vetor de busca por 10:

Como se trata de caso médio, dividi o total de iterações do PROCV linear por 2 e mantive o mesmo valor do binário, uma vez que para fazer uma fórmula PROCV binário, precisamos usar a função PROCV duas vezes em sua composição. No entanto, mesmo avaliando o PROCV duas vezes, a forma binária é incomparavelmente mais rápido que o linear.

A Fórmula do PROCV Binário

=SE(PROCV([@ID];tblDados;1)=[@ID];PROCV([@ID];tblDados;2);NÃO.DISP())

O fato da fórmula do PROCV binário necessitar usar a função PROCV duas vezes não possuem relação direta. A explicação se dá porque as duas formas do PROCV não são diferentes apenas no que se refere na forma de como o algoritmo procura os dados, mas também na forma como consideram válido o resultado de uma busca. Então:

  • PROCV com último argumento FALSO: Faz busca linear e retorna uma correspondência exata. Se não for encontrada uma correspondência exata, retorna o erro #N/D.
  • PROCV com último argumento VERDADEIRO: Faz busca binária e retorna uma correspondência aproximada. Se não for encontrada uma correspondência exata, retorna o valor imediatamente menor que o termo procurado.

O que faz precisarmos de usar dois PROCV no caso binário é a segunda característica do PROCV com último argumento VERDADEIRO. Como ele pode trazer um resultado que seja diferente do que é buscado, é necessário primeiro testar se o valor retornado pela função é igual ao valor buscado:

PROCV([@ID];tblDados;1)=[@ID]

Se essa condição for satisfeita, sabemos que o registro existe na nossa tabela e então podemos retornar a coluna desejada na função:

PROCV([@ID];tblDados;2)

Se o teste condicional falhar, a fórmula irá retornar o erro #N/D, representado por NÃO.DISP().

Repetindo o que foi escrito na seção anterior, mesmo utilizando a função duas vezes, a fórmula como todo pode ser mais de 1000 vezes mais rápida que um único PROCV linear.

Melhorando o Desempenho de Outras Funções Além do PROCV

O Excel possui outras funções além do PROCV que fazem busca binária como a CORRESP, PROC e PROCH. Para o exemplo inicial das ~25000 mil linhas, as fórmulas seriam:

Para CORRESP:

=SE(ÍNDICE(tblDados[ID];CORRESP([@ID];tblDados[ID];1))=[@ID];ÍNDICE(tblDados[Valor];CORRESP([@ID];tblDados[ID];1));NÃO.DISP())

Para PROC:

=SE(PROC([@ID];tblDados[ID])=[@ID];PROC([@ID];tblDados[ID];tblDados[Valor]);NÃO.DISP())

Pessoalmente, a forma que mais uso é o PROC, pois é a mais enxuta. No entanto, decidi escrever o artigo destacando o PROCV por ser uma função mais conhecida do Excel.

Referências

Charles Williams

Para fazer download do arquivo de exemplo deste artigo, clique aqui.
Publicado em Dicas | Com a tag , , , , , , , | Deixar um comentário

Introdução a Módulos de Classe no VBA

Introdução

O VBA suporta o uso de classes através de componentes chamados módulos de classe. Classes são usadas para criar objetos.

Uma analogia bastante utilizada é dizermos que se um bolo é um objeto, sua receita é uma classe. Seguindo o raciocínio, é possível fazer vários bolos a partir de uma receita, ou vários objetos a partir de uma classe. Uma classe não aloca memória em tempo de execução, e um objeto sim, já que a classe possui apenas a definição do objeto que cria.

Uma classe descreve as propriedades e métodos de um objeto. Propriedades podem ser entendidas como características de um objeto, e métodos, ações que o mesmo promove. Por exemplo, considere o objeto Carro. Entre suas propriedades, podemos citar cor, quilometragem, chassi, marca, modelo, etc. Os métodos poderiam ser ações como dar a partida, acionar o para-brisa, frear, buzinar. Se fizermos a analogia que uma propriedade é um adjetivo, certamente um método é um verbo.

Declarar, Instanciar e Destruir um Objeto a Partir de uma Classe

Os objetos possuem um ciclo de vida. Primeiro, devem ser declarados. Então, são criados (tecnicamente é melhor falar instanciados), depois são utilizados (ou consumidos) e, for fim, são destruídos.

No VBA, um objeto é instanciado no momento em que a palavra chave New é utilizada.

oCarro é o objeto e clsCarro é a classe. Repare que quando trabalhamos com objetos, as atribuições devem ser feitas com o uso da palavra chave Set, ao contrário de tipos de dados simples como Long, Integer, Date, etc., em que o uso da igualdade dá valor à variável.

Para que o exemplo acima funcione, é necessário que criemos a classe clsCarro no nosso projeto VBA:

Embora não seja obrigatório, é altamente recomendável destruir todos os objetos criados ao término da execução do seu programa. O gerenciamento de memória e coletor de lixo do VBE não são bons, e ao adotar essa prática você minimiza erros inesperados e até crashes no Excel.

Consumir Objetos

O exemplo a seguir mostra como acessar membros de um objeto de uma classe chamada clsEmpregado. Pelo código, podemos ver que a classe define três propriedades: Nome, Endereço e Salário. Cole o num Módulo de Classe chamado clsEmpregado:

Para cada uma das propriedades, há um procedimento Get e outro Let. Get é chamado quando se deseja ler o valor de uma propriedade e Let é chamado quando se deseja atribuir um valor a uma propriedade.

Para vermos nosso programa em funcionamento, devemos criar num módulo (comum, e não de classe) o seguinte código:

Observe que ao escrever oEmpregado aparece o intellisense com todas as propriedades do objeto:

A vantagem imediata em usar classes é ter uma visualização completa dos membros de um objeto, além do ganho obtido em tempo de desenvolvimento.

Ao executar essa rotina, teremos como resultado na janela de verificação imediata (Ctrl+G):

Uso do With

Ao trabalhar com suas classes personalizadas, você pode usar o bloco With com os objetos que você criar:

Atributos vs Propriedades vs Parâmetros

Dentro da classe, podemos observar, por exemplo Endereço, aEndereço e pEndereço. O que está havendo? Para definir uma simples propriedade, utilizei três membros diferentes com nomes semelhantes. Eles são necessários. Vamos rever a definição de atribuição de propriedade Endereço:

Endereço é o nome da propriedade do objeto. A declaração de uma propriedade se assemelha bastante com a declaração de um subprocedimento e, como sabemos, um procedimento por si só não conseguem armazenar um valor após o término de sua execução. É para isso que utilizamos uma variável para armazenar o valor da propriedade, que é representada aqui por aEndereço (A letra a representa atributo). Ela é uma variável de nível de módulo, e retém os valores de um objeto enquanto o objeto estiver alocado na memória. Então, a propriedade Endereço é uma espécie de canal de comunicação entre a variável interna aEndereço e o ambiente externo.

pEndereço (A letra p representa parâmetro) é nada mais que o parâmetro de entrada do procedimento objeto. Ele possui o valor externo que será processado pela procedimento e atribuído à variável aEndereço. Por fim:

Endereço, nesse contexto, funciona igual uma função, que retorna o valor do atributo aEndereço ao procedimento que chamou a propriedade Endereço.

Propriedades Somente Como Leitura

No exemplo anterior, todas as propriedades possuíam a declaração Let e Get.

É possível criar uma propriedade somente como leitura. Para tal, poderíamos definir, por exemplo, uma propriedade chamada SalárioAnual, que seria dada pelo produto do salário mensal e 12:

Note que poderíamos ter utilizado a variável de módulo (que aqui tem a função de atributo) aSalário ao invés da propriedade Salário. No entanto, as boas práticas de programação dizem que quando estamos numa classe, devemos utilizar o valor retornado pela propriedade, e não a variável que armazena o valor da propriedade. Essa boa prática tem um custo que é tornar a depuração do código mais trabalhosa, uma vez que todo acesso à propriedade Salário desvia o código para seu procedimento respectivo. Por outro lado, ao usar o valor da propriedade, você terá garantido que o valor atribuído já foi processado e validado por sua classe.

O fato dessa propriedade não possuir a instrução Let é o que a caracteriza somente como leitura, tornando impossível fazer uma atribuição direta a ela.

Restringindo Valores de Entrada de uma Propriedade

Uma grande vantagem em usar classes é possibilidade de tratar os dados de entrada e saída da classe. Para o exemplo da classe clsEmpregado, vamos adicionar código para ser impossível atribuir um valor negativo à propriedade Salário. Poderíamos adaptar sua propriedade Let na forma mostrada abaixo:

Criar Métodos na Classe

Até agora foi mostrado apenas como trabalhar com propriedades nas classes. Você pode definir métodos também. Acrescente o bloco de código abaixo na classe clsEmpregado:

Agora, coloque o código abaixo num módulo comum e execute:

O resultado será:

Referenciando Propriedades e Métodos da Classe

Quando escrevemos código dentro de uma classe, ao invés de escrever Nome, Salário e SalárioAnual, podemos escrever Me.Nome, Me.Salário e Me.SalárioAnual. Em outras palavras, no contexto dentro de uma classe, Me se refere a ela mesma. Uma vantagem de se usar o Me é o fato de aparecer o intellisense com todos os membros (isto é, propriedades, métodos, constantes e enumerações) da classe:

Note que ícones de propriedades são diferentes de ícones de métodos.

Eventos Padrões de uma Classe

Toda classe no VBA possui dois eventos padrões, de nome fixo. Um chama-se Class_Initialize, e é executado quando um objeto é instanciado. O outro, Class_Terminate, é executado quando o objeto é destruído. Você não é obrigado a colocar código nesses dois eventos.

Para exemplificar, crie uma classe chamada clsCasa com o código abaixo:

Se você retirar a instrução Set oCasa = Nothing do código acima verá que o ponto de execução desviará de End Sub para o procedimento destrutor do objeto (ou seja, foi destruído implicitamente). Pode-se argumentar então que destruir um objeto explicitamente é desnecessário. Volto a insistir que a melhor forma de destruir um objeto é explicitamente, e que em muitos casos você, como desenvolvedor, terá menos problemas em sistemas mais complexos e melhor gerenciamento de memória. A título de exemplo, existem alguns cenários de crashes no Excel em formulários quando os mesmos não são destruídos explicitamente. Infelizmente, pelo fato do problema ser também de design do VBE, não consigo descrever um passo a passo para reproduzir esse tipo de problema. Além disso, um bom programa é aquele que encerra quando a instrução End Sub do método pai é executada, sem disparar toneladas de coletores de lixo dos objetos pendurados na memória.

Normalmente uso o evento Class_Initialize para definir valores iniciais e padrões de um objeto. No exemplo acima, ao criar um objeto clsCasa, define-se automaticamente que a propriedade Endereço do mesmo é Rua das Flores, 105.

Nesse sentido, você pode usar esse evento para definir propriedades padrão ao criar um objeto. Suponha que você crie vários objetos de uma classe clsVeículo para usar num ambiente em que os veículos são, predominantemente, carros. Se existir uma propriedade chamada QuantidadeRodas, você poderia atribuir 4 à essa propriedade dentro do evento Class_Initialize, e atribuir explicitamente oVeículo.QuantidadeRodas = 2 fora da classe apenas nos casos de quando o veículo é uma moto.

O Class_Terminate é utilizado para colocar códigos de limpeza no ato da destruição de um objeto. No nosso exemplo, mostra-se apenas uma notificação de que o objeto foi destruído.

Os Problemas da Auto Instanciação

Alternativamente, você pode criar um objeto dessa forma:

Foi feita a declaração e criada uma instância do objeto numa única instrução. O nome dessa técnica é auto instanciação de variável. No VBA, não é recomendável utilizá-la por dois motivos:

  • Aumenta o overhead do código porque cada chamada a um objeto criado dessa classe irá disparar o evento de inicialização do mesmo. Ao criar um objeto dessa forma e fazer uma simples atribuição como, por exemplo, oCarro.Cor = "Verde", o evento Class_Initialize será disparado, e isso é altamente indesejável.
  • Não há como testar se uma variável criada desse tipo é Nothing porque a própria instrução de teste irá criar uma instância do objeto, retornando, então, sempre False para o teste. Nesse exemplo, o teste If oCarro Is Nothing Then... sempre irá passar.

Criar mais de um Objeto com uma Classe

No exemplo a seguir, foram criados três objetos do mesmo tipo, mas com propriedades diferentes:

O VBA não mistura os valores das propriedades (na verdade atributos) dos objetos criados. Cada objeto aloca espaço na memória para guardar os próprios valores de cada propriedade.

Criar uma Coleção de Objetos

Você pode adicionar objetos em coleções. Suponha que você tenha a tabela abaixo:

Use o código a seguir para criar um objeto por linha da tabela, povoar as propriedades da tabela de acordo as colunas respectivas e, em seguida, mostrar os dados dos objetos na janela de verificação imediata:

Você pode remover um item da coleção se desejar. A instrução abaixo remove o terceiro item da coleção do nosso exemplo:

Classes com Várias Propriedades

Em classes que definem muitos objetos, temos também que escrever todas suas propriedades dentro da classe. Normalmente, várias dessas propriedades tem a simples forma Get e Let de atribuição e leitura:

Costumo simplificar essa grande quantidade de linhas para:

Ao invés de cada propriedade ocupar 7 linhas, agora ocupa 2.

Como faço isso muitas e muitas vezes, criei “código para gerar código”. Por exemplo, execute o código a seguir:

Se você entrar Dim aNome As String na janela, obterá as declarações Get e Let na janela de verificação imediata e uma quebra extra de linha:

Então, simplesmente escrevo as declarações dos atributos e rodo essa macro para cada gerar cada uma das declarações das propriedades. Você pode criar versões mais sofisticadas desse código, como por exemplo: obter o esquema de uma tabela de um banco de dados e gerar classes no VBE mapeando cada um dos campos a uma propriedade, por exemplo. Use a criatividade.

Referências

Chip Pearson

Para fazer download do arquivo de exemplo deste artigo, clique aqui.
Publicado em Tutoriais | Com a tag , , , , , , , , , | Deixar um comentário