Gerar Árvore de Arquivos e Diretórios no Excel

Você pode usar o Excel para varrer um caminho local ou de rede e mostrar os arquivos e/ou diretórios (que chamarei neste artigo de itens) numa tabela, criando uma árvore de itens. Na pasta de trabalho que preparei, você pode configurar como quer exibir os resultados:

O resultado da execução do programa pode ser visto abaixo:

Muitas vezes, mesmo com permissão de administrador, não é possível varrer todos os itens de um diretório. Isso pode ocorrer em função de atributos de um item ou até mesmo na incapacidade do VBA em ter as mesmas permissões que um usuário tem. Para esses casos e de outros erros, há uma planilha chamada Erros na pasta de trabalho que lista os casos em que não foi possível ler informações de um item.

Se for de seu interesse, há como tornar a rotina mais rápida: elimine todas as referências a intervalos nomeados como [ListFolders], substituindo por variáveis de nível de módulo, ou então simplesmente elimine do código testes condicionais e tratamentos de erros que você julgar desnecessários.

Existem dois métodos de busca disponíveis: Dir e FileSystemObject. Ambos tem vantagens e desvantagens. Fiz um teste varrendo todos os itens do meu computador e notei resultados curiosos:

  • FileSystemObject: demorou 190s, encontrou 340.000 itens e obteve erro no acesso a 375 itens.
  • Dir: demorou 62s, encontrou 270.000 itens e obteve erro no acesso a 55 itens.

O método Dir é mais rápido que o FileSystemObject, no entanto, sua implementação com recursão é um pouco complicada, como pode ser visto no código. Além disso, Dir encontra menos arquivos que FileSystemObject. Essa grande diferença se dá pelo fato de eu não ter utilizado argumentos vbReadOnly, vbHidden e vbSystem na função Dir. Não sei se é possível afirmar que mesmo usando esses argumentos, eu alcançaria a mesma quantidade de itens varridos pelo FileSystemObject. Decidi deixar esses argumentos de fora porque a cada novo teste aparecia erros diferentes que precisaria eu mudar bastante a rotina.

Num ambiente de rede bastante lento, eu certamente utilizo Dir. No entanto, se eu quiser fazer uma análise minuciosa que inclui arquivos de sistema e ocultos, escolheria FileSystemObject.

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

AmbienteXL

O AmbienteXL (que antes se chamava ExpressXL) é um suplemento gratuito para Excel 2007 ou superior que desenvolvi em VBA e expande as funcionalidades do aplicativo. Esta página explica as ferramentas presentes no suplemento. Ele não é compatível com Excel para Mac e nem funciona no Excel para Windows RT.

As ferramentas do AmbienteXL podem ser acessadas através de uma guia que é criada quando o suplemento é executado ou instalado:

Vale ressaltar que este é um projeto que está em contínuo desenvolvimento, e novas ferramentas poderão surgir com o tempo.

Embora eu tenha testado o AmbienteXL, não me responsabilizo por danos ou resultados inesperados que o mesmo possa causar em seu computador, embora tais efeitos sejam improváveis.

Instalação

Para usar o AmbienteXL somente numa seção do Excel, basta baixar o indicado no final desta página, extrair e executar o arquivo AmbienteXL.xlam.

No entanto, se você quiser que o AmbienteXL carregue toda vez que abrir o Excel, descompacte-o num diretório local ou num caminho da rede e vá à guia Arquivo >> Opções:

Agora, na janela Opções do Excel, escolha o menu Suplementos , na caixa de combinação Gerenciar escolha Suplementos do Excel e em seguida clique no botão Ir…:

Na janela Suplementos que aparecer, clique em Procurar e localize o local onde você extraiu o suplemento, e em seguida clique no botão OK:

Se você tiver extraído o suplemento num caminho da rede, pode ser que o Excel te pergunte se deseja criar uma cópia local do mesmo. O recomendado é escolher Não para essa opção, para não criar uma cópia local do suplemento.

Se gravar o suplemento na rede, disponibilize-o com a propriedade Somente para Leitura habilitada. Dessa forma, você não terá problemas em retirar ou substituir o arquivo da rede futuramente, uma vez que o Excel de outras máquinas não irá bloquear o acesso ao suplemento ao abri-lo.

Desinstalar

Para desinstalar o AmbienteXL, basta desabilitar a caixa de seleção que você habilitou através dos passos anteriores e clicar em OK.

Grupo Células

Nesse grupo há comandos para transformar células e tabelas:

Maiúsculas, Minúsculas e Proper

Este comando transforma o texto de células de uma seleção para capitalização maiúscula, minúscula ou apropriada (isto é, primeira letra de cada palavra maiúscula e o resto em minúsculas). Para utiliza-la, basta selecionar um intervalo e clicar num dos comandos:

Substituir Texto

Utilize esse comando para fazer uma substituição em massa de células numa planilha. Primeiramente, crie uma planilha De-Para com as substituições de sua necessidade e salve num determinado caminho. É obrigatório colocar o De na coluna A e o Para na coluna B, a partir da primeira linha, como mostrado na figura abaixo:

No exemplo abaixo, desejo substituir os valores da coluna Nome. Ao selecionar as células desejadas e clicar no comando Substituir Texto, a janela de opções da ferramenta será mostrada:

Ao escolher a pasta de trabalho que possui o De-Para, escolha a planilha adequada. Você verá uma previsão das substituições:

Clique em OK para realizar a substituição em massa:

Inserir Texto / Remover Texto

Esses comandos são práticos quando se deseja transformar o texto de várias células sem precisar criar colunas com fórmulas auxiliares. O exemplo abaixo mostra uma aplicação do comando Inserir Texto. Vale ressaltar que, para tal, deve-se selecionar o intervalo ao qual se deseja transformar:

A janela que foi mostrada ao usuário ao clicar no comando é mostrada a seguir:

Observe que há várias opções de inserir um texto, seja no início, fim ou no meio do texto da célula.

O comando Remover Texto é parecido com o de inserir, com os mesmos tipos de opção e com a diferença, obviamente, de remover texto ao invés de inserir:

Tanto para o comando de inserir ou remover texto, você pode considerar a ação do fim ao início do texto das células.

Normalizar

A normalização (também conhecida como unpivot) é uma série de passos que se seguem no projeto de um banco de dados que permitem um armazenamento consistente e um eficiente acesso aos dados. O comando Normalizar do AmbienteXL é capaz de normalizar uma matriz. Veja um exemplo:

Ao clicar no comando Normalizar, uma janela perguntará ao usuário qual intervalo ele deseja normalizar. Faça uma seleção incluindo os cabeçalhos de linha e coluna e em seguida clique em OK:

Outra janela perguntará se você quer listar valores em branco ou igual a zero. Após fazer sua escolha, seu resultado ficará como mostrado a seguir:

Coluna e Linha correspondem a, respectivamente, os cabeçalhos verticais e horizontais.

Planilhas

Nesse grupo há comandos que operam em planilhas.

Desproteger

Este comando protege/desprotege várias planilhas de uma vez. Vale ressaltar que para desproteger as planilhas, você deverá saber a senha (você não encontrará no meu site nenhuma técnica para quebrar senhas do produto Office).

Ainda não terminei de escrever o código desse comando, então ele ainda não possui funcionalidade.

Visibilidade

Este comando mostra uma janela interativa que permite ocultar e exibi pastas de trabalho e planilhas de forma fácil:

Você pode tomar ações de ocultar ou reexibir várias planilhas ou pastas de trabalho ao mesmo tempo.

Comparar

Este comando é útil para comparar duas tabelas. Dados os dois cenários abaixo (um de informações 1994 e outro de 2014), quais são as diferenças?

Para este exemplo, a ferramenta mostra dados que estão presentes em 1994, mas ausentes em 2014 e vice versa, e também mostra dados que alteraram. Ao clicar no comando Comparar, uma janela se abrirá para configurar a comparação:

Algumas observações:

  • Os cabeçalhos devem estar na primeira coluna, seus nomes não podem repetir e não pode haver nenhum em branco.
  • O Campo chave relaciona os registros de uma tabela com outra. Esta coluna não aceita registros em branco ou duplicados na coluna.
  • Você pode adicionar quantos campos a comparar que quiser. Apenas aparecerão campos que possuem o mesmo nome de cabeçalho entre duas tabelas. Se quiser saber simplesmente quais registros entrarão e quais saíram, desmarque todos os campos a comparar.

Veja o resultado da execução desse comando com a configuração mostrada na janela anterior:

Além dessa tabela, é gerada também uma planilha com uma tabela dinâmica que resume as diferenças entre as duas tabelas:

Mesclar

A expressão mesclar planilhas é também o que informalmente chamamos de empilhar planilhas. Suponha que você tenha uma pasta de trabalho com quatro planilhas: uma capa chamada Folha de Rosto, e outras três planilhas chamadas Lixadeiras, Máquinas de Soldar e Furadeiras, que possuem dados de equipamentos de uma empresa:

Planilha Lixadeiras:

Planilha Máquinas de Soldar:

Planilha Furadeiras:

Seu objetivo é criar uma única planilha que relacione todos os dados dessas três planilhas. A premissa para este comando funcionar é que as planilhas possuam a mesma estrutura, isto é, os cabeçalhos devem ter a mesma quantidade de linhas e suas colunas devem representar a mesma informação em cada tabela.

Ao clicar no comando Mesclar do grupo Planilhas, a janela a seguir abrirá:

Desmarquei a Folha de Rosto porque ela não contém dados para mesclar. Desconsiderei os cabeçalhos, exceto o primeiro, decidi manter a formatação e permiti que o comando crie uma coluna à direita dos dados para eu saber quais são suas planilhas de origem. Veja o resultado final dessa configuração criado pelo comando:

Ordenar

Suponha que você tenha uma pasta de trabalho com várias planilhas e deseja reorganizar suas posições:

Ao clicar no comando Ordenar, será mostrada uma janela que permite ordenação de planilhas, inclusive a opção de classifica-las em ordem alfabética clicando no botão Ordenar A-Z:

Miscelânea

Este grupo possui comandos de funcionalidades diversas no Excel:

Somente Leitura

Clique neste botão para alternar uma pasta de trabalho para o modo somente para leitura ou gravação. Útil para quando você abre uma pasta de trabalho e, para evitar que a salve acidentalmente, a torna somente para leitura antes.

Mostrar Nomes Ocultos

Nomes Definidos ocultos só podem ser reexibidos através de macros. Este comando mostra todos os nomes definidos da pasta de trabalho ativa.

Tabela Dinâmica para Valores

Selecione uma tabela dinâmica e use este comando para transformar uma tabela dinâmica num intervalo regular do Excel.

MailXL

O MailXL possui comandos para enviar de forma fácil intervalos, planilhas ou pastas de trabalho por e-mail como anexo ou no corpo da mensagem. Além disso, você pode enviar um ou mais gráficos como imagem.

O formato do anexo pode ser xls, xlsb, xlsx, xlsm e até pdf. Você pode converter as fórmulas das planilhas para valores antes de enviar o e-mail.

O MailXL só funciona se o seu computador estiver com o Outlook instalado e com uma conta de e-mail configurada. Posteriormente será adicionada uma opção para enviar e-mails via CDO.

Intervalo

Ao clicar neste comando, você será solicitado para selecionar um intervalo. Veja o exemplo a seguir:

Uma janela com opções aparecerá. Para este exemplo, resolvi enviar o intervalo selecionado no corpo do e-mail:

Veja o resultado final:

Planilha(s)

Este comando é semelhante ao de enviar um intervalo, exceto que o intervalo a ser enviado é da planilha inteira. Inicialmente, aparece uma janela perguntando qual planilha deseja enviar:

Depois, você cairá na mesma janela do comando de enviar um intervalo e então poderá enviar o e-mail no formato desejado.

Pasta de Trabalho

Este comando é semelhante ao de enviar uma planilha, exceto que a pasta de trabalho inteira é enviada. Normalmente, utiliza-se essa opção para enviar uma pasta de trabalho como anexo. No exemplo a seguir, optei por enviar a pasta de trabalho no formato PDF:

Gráfico(s)

Este comando permite enviar gráficos como imagem por e-mail. Suponha que sua pasta de trabalho possua uma planilha chamada Análises com dois gráficos:

Suponha também que há uma folha de gráfico chamada Comparativo:

Ao clicar no controle Gráfico(s), você poderá escolher quais imagens e o formato que deseja enviar por e-mail, através da janela a seguir:

O resultado é mostrado abaixo:

CADXL

O CADXL é um conjunto de ferramentas que permite interagir com elementos do AutoCAD. Com ele, você pode editar tabelas escritas com objetos do AutoCAD e pode também listar e editar todos os textos presentes num desenho.

Para usar os comandos do CADXL, você deverá estar com o AutoCAD aberto e um desenho carregado.

Obter Tabela

Suponha que você tenha uma tabela como mostrada abaixo:

Clique no botão Obter Tabela. O foco será dado ao AutoCAD, você deverá selecionar a tabela inteira (inclusive bordas) e pressionar Enter:

O resultado é mostrado a seguir:

Você pode editar os valores dessa planilha e clicar no comando Executar Alterações para atualizar os textos da tabela no AutoCAD.

Fique atento porque a informação que vincula cada um dos textos da tabela do AutoCAD a uma célula é um identificador único chamado Handle, e este fica armazenado como comentário em cada uma das células. Em outras palavras, se você usar operações Ctrl+V para colar ao invés de colar apenas valores, o comentário da célula de destino será perdido e, consequentemente, seu vínculo com o elemento respectivo na tabela do AutoCAD.

Obter Texto

Esta ferramenta permite obter textos de uma seleção do AutoCAD. Ao clicar no comando Obter Texto, o foco será dado ao AutoCAD para você fazer sua seleção e pressionar Enter. Você pode incluir objetos que não são texto que o comando ignora-os. Veja um exemplo abaixo:

O resultado no Excel é mostrado a seguir:

O CADXL disponibiliza a coordenada dos textos do AutoCAD, o conteúdo e um hyperlink que ao ser clicado, foca a posição do objeto no AutoCAD.

Você pode usar essa ferramenta para diversos fins, como localizar textos duplicados e auditar desenhos.

Observações e Limitações

O CADXL não é capaz de ler textos dentro de blocos, isto é, você deverá explodi-los se quiser usar a ferramenta.

Os textos capturados e reconhecidos pelo CADXL são MTEXT, TEXT e atributos ATTDEF. Para o comando de tabela, as linhas devem ser objetos do tipo LINE, isto é, não funciona polyline e outros.

Não faça uma seleção demasiadamente grande. Pode ser demore muito para o comando importar os dados da tabela, embora devolvê-los ao AutoCAD seja bastante rápido. Se estiver demorando muito para processar uma importação, pressione Ctrl+Break para cancelar.

O texto importado de objetos MTEXT é mostrado com seus códigos de formatação. Suponha que você importe um MTEXT Felipe sublinhado no AutoCAD. Ele será mostrado como %%uFelipe no Excel, pois o código %%u representa a formatação de sublinhado no AutoCAD.

Para que o CADXL busque o texto na tabela ou em parte da tabela, é necessário que a mesma possua linhas internas e de contorno (ou seja, bordas internas e externas) e que elas estejam incluídas na seleção. Se o texto de uma célula da tabela do AutoCAD for grande a ponto de sair dos limites de borda, ele não será considerado pelo CADXL.

Ainda sobre importação de tabelas, se numa célula contiver mais de um objeto de texto, o CADXL irá considerar apenas o primeiro valor de texto que encontrar.

VBA

Para usar qualquer ferramenta desta seção, você tem que habilitar a opção Confiar no acesso ao modelo de objeto do projeto VBA e reiniciar o Excel.

O AmbienteXL integra ao VBE através de uma janela suspensa que possui comandos para indentar código VBA. Para ver essa janela, basta clicar com o botão da direita na janela de código de algum projeto VBA e escolher se quer indentar um módulo ou um projeto inteiro:

Download e Termos de Uso

O AmbienteXL está liberado para uso próprio e comercial. Esta ferramenta não pode ser vendida ou distribuída com alterações sem minha autorização. Fique à vontade para sugerir novas funcionalidades ou relatar bugs na seção de comentários. A senha do projeto VBE é FrankKabel.

Para baixar o AmbienteXL, clique aqui.
Publicado em Ferramentas | Com a tag , | 2 comentários

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 , , , , , , , | 2 comentários

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