Nos dois artigos anteriores vimos como criar uma classe para servir de
intermediário entre um formulário e uma planilha, primeiro fazendo a
classe se comunicar com a planilha e depois fazendo o formulário se
comunicar com a classe. Falta ainda tratar as listas de nomes e de
cargos dos funcionários, que veremos aqui.

Vamos começar pela lista de nomes. Ela não pode ser carregada da
planilha diretamente pelo formulário, é preciso que a classe faça o
serviço, afinal, ela foi criada justamente para não termos acesso direto
do formulário à planilha. Portanto, iremos criar uma propriedade nova na
classe clsFuncionario chamada Nomes – no plural, para
diferenciar de Nome. Podemos criar uma coleção ou um
array para colocar os nomes dos funcionários, sendo que cada tipo
teria uma abordagem diferente. Neste exemplo criarei Nomes como
um array. Também irei criar uma propriedade
Quantidade para informar a quantidade de nomes existentes nesse
array. Perceba que ambas serão somente leitura, ou seja, não será
possível guardar dados nestas propriedades, eles serão gerados pela
própria classe.

Na área de definição das variáveis da classe
clsFuncionario acrescente as seguintes linhas:

Na área de propriedades, coloque as seguintes linhas:

Perceba como é feita a leitura da propriedade quando é um array.
Nomeei o índice do array como Indice para ficar o mais
explícito possível, isso ajuda no entendimento do código no futuro,
mesmo para quem o criou.

Vejamos algumas considerações sobre a lista de funcionários. É ideal
que a lista carregada esteja em ordem alfabética para ajudar o usuário.
Como nossa planilha está ordenada pelo registro, será preciso fazer uma
ordenação pelo nome antes de carregar a lista de nomes, para depois
reordenar pelo registro. Outro detalhe é que a lista só deverá ter os
funcionários que estejam na ativa, ou seja, aqueles que foram desligados
não devem aparecer na lista (mas estes poderão ser encontrados por
pesquisa, que será feito mais adiante).

A nossa classe possui um método privado OrdenarTabela, que
ordena pelo registro. Poderíamos simplesmente copiar esse método e criar
um que ordene pelo nome, bastando alterar apenas a linha que contém o
método SortFields.Add. E se precisássemos ordenar por outra
coluna faríamos o mesmo e assim por diante, certo? Isso deixaria o
código cheio de linhas repetidas e longo à toa. Por isso vamos editar o
método OrdenarTabela, acrescentando um parâmetro de coluna, que
indicará qual coluna deve ser ordenada. E fazemos isso alterando apenas
duas linhas: o cabeçalho e a que contém o SortFields.Add. Faça as
alterações nessas linhas:

Desta maneira, a linha da função Adicionar que contém a chamada
deste método deverá acrescentar o parâmetro 1 para ordenar pelo
registro:

Veja que agora nossa classe permite classificar a planilha
Funcionários por qualquer coluna e, o que é melhor, sem
acrescentar uma única linha. Bastou fazer duas pequenas alterações no
método existente. Habitue-se a sempre analisar estas possibilidades,
elas nos poupam de retrabalho no futuro.

Agora vejamos o método CarregarNomes como fica:

Como sempre, encontramos primeiro a última linha em uso da planilha.
Depois definimos a variável Intervalo com as células da coluna
Ativo e contamos quantos verdadeiros existem, usando a
função CountIf (ou Cont.SE) do Excel, guardando em
pQuantidade. Se a quantidade for maior ou igual a 1, é feita um
redimensionamento da variável pNomes para que possa abranger a
quantidade de nomes que serão armazenados. Depois a tabela é ordenada
por nomes e uma estrutura de repetição irá armazenar os nomes no
array somente se o funcionário estiver ativo. Se não houver
funcionários ativos, o array é redimensionado para 0, limpando
eventuais nomes que possam ter existido.

Onde devemos chamar o método CarregarNomes? A primeira seria na
própria inicialização da classe. As classes podem usar ou não os eventos
Initialize e Terminate, que, como os próprios nomes
indicam, inicializam e encerram a classe. Vamos precisar da
inicialização, então vamos colocá-la no código:

Perceba que aqui redimensionei pNomes com índice zero, isto é
uma boa prática quando se tem arrays dinâmicos na classe, pois
ajuda a evitar erros ao tentar acessar algum índice do array que
não tenha valor.

Outros pontos em que será necessário chamar o método
CarregarNomes é nas funções Adicionar, Alterar,
Ativar e Desativar. A primeira porque acrescenta nomes e é
preciso adicioná-lo à lista, as duas últimas porque lidam com a coluna
Ativo e a segunda porque eventualmente pode-se alterar o nome, o
que pode tirar da ordem alfabética. Portanto, acrescente essa chamada
nas quatro funções. Na função Adicionar há uma chamada de
OrdenarTabela, que pode ser removida, pois
CarregarNomes também ordena a planilha, tornando aquela chamada
desnecessária.

Agora vamos passar para o formulário, onde será preciso fazer um método
para carregar a lista de nomes da classe clsFuncionario. Aqui
usaremos a propriedade Quantidade para saber se há conteúdo em
Nomes para carregar a caixa de listagem cmbNome:

É necessário limpar o conteúdo da caixa de listagem (método
Clear), caso contrário os nomes serão acrescentados
indefinidamente. Além disso, se desligarmos todos os funcionários a
lista deverá estar vazia. Há dois pontos do formulário que irão chamar
esse método CarregarNomes: na inicialização do formulário
(UserForm_Initialize) e no método LimparFormulario.
Coloque a chamada no final de ambos. Lembre-se que
LimparFormulario é chamado ao pressionar os botões
Cadastrar, Alterar, Desligar e Recontratar,
portanto engloba as nossas necessidades.

Agora temos a lista carregada e ela está em ordem alfabética pelo nome
dos funcionários. “Desligue” alguns funcionários e veja a lista que é
carregada. “Recontrate” e veja aparecendo de novo. Neste momento só
temos a lista carregada e não acontece nada quando selecionamos algum
nome da lista, é necessário fazer os testes digitando os números de
registro dos funcionários para “desligar” e “recontratar”. Faça os
testes necessários, inclusive desligando todos para ver que não aparece
nenhum nome na lista. Sempre é bom testar logo aquilo que codificamos,
pois caso aconteça algum erro temos o que foi feito fresquinho na nossa
memória. Se os testes forem feitos horas ou dias depois, é capaz que
você esqueça como funciona o seu próprio código.

Passamos então à pesquisa de nomes. O usuário pode escolher um nome da
lista ou então digitar um nome que não tem. Deve então clicar no botão
Pesquisar para que se faça a busca. Ao contrário do que fizemos
em OrdenarTabela, desta vez criaremos uma nova função para
pesquisar por nome. Na função Pesquisar existente na classe,
fazemos a pesquisa pelo Registro, um valor numérico, agora
precisamos procurar pelo Nome, uma string. É possível fazer uma
função que trabalhe com as duas, mas aqui no exemplo ficará um tanto
complexo, por isso acho melhor fazermos separadamente.

Vamos copiar a função Pesquisar em uma nova função chamada
PesquisarNome. Teremos algumas mudanças sutis, como alterar as
referências de Registro para Nome. Mas teremos uns
detalhes adicionais: se o nome não estiver na base, a classe deverá
sugerir um número para o campo Registro, que será o valor máximo
existente mais um. Segue o código, dê uma analisada e confira as
diferenças:

Você pode criar uma rotina de teste para verificar se está tudo
funcionando conforme o esperado. Lembre-se de sempre manter o módulo de
testes da classe para validar todas as alterações feitas e se não há
nenhum efeito colateral não esperado.

Vamos aproveitar e editar a escrita na propriedade Nome. Da
forma que está, o que o usuário inserir será guardado do jeito que for
digitado. Assim, podemos ter nomes todo escrito em maiúsculas ou em
minúsculas ou ainda em uma mistura irracional de maiúscula e minúscula.
Vamos evitar isso? Lembra do
artigo sobre strings
que explicava como usar Proper Case? Vamos aproveitar e deixar os
“de”, “da”, “do” etc em minúsculas. Veja como fica o código:

A primeira linha usa o StrConv para deixar as iniciais
maiúsculas em cada um dos nomes e o restante em minúsculas. Em seguida,
é verificado se há ” Da “, ” Das ” etc e substitui pelo texto em
minúsculas. As duas últimas linhas era o que havia antes.

Passamos agora ao formulário. Vamos tratar o evento de saída do campo
Nome para que reflita automaticamente a consistência acima. O
código é bem simples:

A primeira linha põe o valor da caixa de combinação na propriedade
Nome, que deixará o nome com as consistências que pusemos. A
segunda linha devolve o valor da propriedade à caixa de combinação, para
que a alteração seja visível para o usuário tão logo saia do campo
digitado. Você pode testar o efeito, não importa a forma que o nome for
digitado, ele sempre irá devolver o nome da forma que foi estipulado que
será guardado.

Passamos então ao botão Pesquisar. Esse botão deve verificar se
o campo Nome tem conteúdo, caso contrário não deve fazer nada. Em
seguida, deve executar a função PesquisarNome do objeto
Funcionario. Se for encontrado, deve executar o método do
formulário PreencherFuncionario e exibir uma mensagem indicando
que foi encontrado. Se não encontrar, deve exibir o número de registro
sugerido e uma mensagem informando que não foi encontrado. Como pode
ver, o código resultante é bem simples:

Lembre-se que utilizei o Find na classe para localizar o nome.
Isso significa que é possível usar caracteres coringas para localizar
nomes. “Ant?nio” pode encontrar Antonio, Antônio e
António. “João*Silva” encontrará o primeiro nome que começar com
João e terminar por Silva.

O campo Nome agora está completo e funcionando conforme o
esperado. Fizemos a lista, o tratamento do nome e a funcionalidade do
botão Pesquisar. Agora só falta o campo Cargo. Lembre-se
que faremos uma classe nova para intermediar o formulário e a planilha
com os dados dos cargos.

Antes de mais nada, vejamos as necessidades. A planilha
Listas tem uma tabela com duas colunas referentes aos cargos,
sendo a primeira coluna o código e a segunda o nome do cargo.
Precisaremos também de uma lista de cargos para colocar na caixa de
combinação, da mesma forma que fizemos com os nomes dos funcionários.
Precisaremos de duas funções de pesquisa: uma para retornar o cargo
quando fornecemos o código e outra para retornar o código quando
enviamos o cargo. Por quê? Na nossa planilha de funcionários (e na
classe) armazenaremos o código do cargo, não o nome. Por isso precisamos
obter o código para armazenar e depois precisamos do cargo quando
recebemos o código para exibir no formulário. Também precisaremos
ordenar a planilha por código e por cargo, se quisermos trazer os cargos
em ordem alfabética. Podemos adicionar um cargo novo que possa
eventualmente surgir.

Se você entendeu todo o processo até aqui deve saber fazer essa classe
sem nenhuma ajuda, bastando ver o que foi feito na classe
clsFuncionario e criar a clsCargo apenas com as partes
conforme os requisitos mencionados acima e fazendo as adaptações
necessárias. Eu lhe encorajo a tentar fazer só antes de seguir adiante
com a minha solução, pois é exercitando que aprende a fazer.

As propriedades que usaremos serão todas somente leitura, por isso não
precisaremos de criar uma propriedade para indicar inconsistência. Segue
a definição delas:

O método OrdenarTabelas pode ser copiado integralmente de
clsFuncionario, fazendo apenas a alteração de todas as
referências da planilha e limitando o intervalo para duas colunas:

Atenção que no Find alterei a pesquisa de
PlFuncionarios.Cells para PlListas.Columns(1). Se
acontecer de ter outras listas nesta planilha, a pesquisa terá de
verificar a coluna correspondente à tabela em que a pesquisa será feita.
Os outros métodos terão a mesma alteração.

Para carregar a lista de cargos, teremos de adaptar alguns detalhes do
método CarregarNomes de clsFuncionario. Além das
referências da planilha, aqui carregaremos todas as linhas, não haverá
restrição, por isso as validações não serão necessárias aqui. Também
podemos usar o método Count do Intervalo para saber
quantos cargos existem. Veja como ficou:

Os métodos para pesquisar pelo código ou pelo cargo não diferem muito
do que fizemos com clsFuncionarios, inclusive se não encontrar o
cargo na lista a classe irá gerar um código novo automaticamente para o
caso de querer adicionar esse cargo.

A função Adicionar funciona da mesma forma, mas como são menos
colunas fica mais simples. Também coloquei a função
ValidarPosicoes aqui para impedir o uso da função se não houver
valores de código e linha válidos.

Por fim, temos a inicialização da classe, que deverá carregar os cargos
automaticamente assim que a classe for gerada:

A classe está pronta. Crie rotinas de teste para validar todos os
métodos e as funções. Como dito anteriormente, habitue-se a testar o que
foi feito assim que estiver pronto. As rotinas de teste da classe devem
ficar em um módulo específico para esse fim e sugiro que mantenha no
arquivo mesmo quando tudo estiver pronto, pois nunca se sabe quando pode
haver alterações. Com essas rotinas à mão fica mais fácil testar se as
alterações interferem no que estava pronto.

Agora vamos ao formulário. Em primeiro lugar, precisamos criar um
objeto para a classe clsCargo. Na área de declarações do
formulário acrescente a seguinte linha logo abaixo da declaração do
objeto Funcionario:

Vamos criar um método privado para carregar os cargos. É bem similar ao
carregamento dos nomes:

Em seguida, na inicialização do formulário, acrescente as seguintes
linhas:

Só uma observação: se colocar no final da sub-rotina, ao carregar o
formulário a planilha de listas estará visível, não a de funcionários.
Há duas formas de ajeitar isso: acrescentando uma linha para ativar a
planilha de funcionários ou colocando essas linhas antes da chamada de
CarregarNomes, que irá ativar a planilha de funcionários. Eu
preferi usar a segunda opção.

O objeto ListaCargos só será alterado quando for digitado algum
cargo que não exista na lista e o usuário decidir acrescentá-lo à lista
de cargos da planilha. Portanto não será necessário carregar os cargos
na limpeza do formulário. Vamos tratar o evento de saída do campo
Cargo, que é a única possibilidade de acrescentar um cargo:

Assim que a pesquisa pelo nome do cargo for efetuada, a propriedade
Codigo será automaticamente preenchida. Se o cargo existir, a
propriedade Cargo do objeto Funcionario é preenchida com o
código retornado. Se o cargo não existir, esse código é preenchido pela
classe com o maior valor existente mais um e o usuário receberá uma
caixa de mensagem avisando que o cargo não existe na lista e perguntando
se deseja acrescentar. Caso o usuário clicar em Sim, o cargo será
acrescentado à lista e a caixa de listagem dos cargos será recarregada,
contendo o valor adicionado.

Passamos agora ao preenchimento do formulário, onde recebemos o código
do cargo e devemos exibir o cargo correspondente. Substitua a linha:

Por:

Do jeito que estava antes, ao carregar o funcionário aparecia o número
do código do cargo. Essa substituição pesquisa o cargo correspondente ao
código retornado pela classe clsFuncionario. Se acontecer de
retornar algum código que não estiver na lista de cargos, um texto
aparecerá e o campo será marcado como inconsistente.

Agora o formulário está completo. Foram três artigos longos, com
diversas explicações e muito, muito código. Há partes que podem ser melhoradas ou feitas de outra forma, o que é
normal em programação: há diversas formas de fazer a mesma coisa.

Espero que tenham aprendido como usar uma classe, não só para ligar o
formulário a uma planilha, mas de uma maneira geral. Classes bem-feitas
podem ser reaproveitadas em outros projetos com poucas alterações. Você
pode adaptar a classe clsFuncionario para praticamente qualquer
necessidade de ligar um formulário com uma planilha. Também pode
acrescentar outras caixas de listagem fazendo poucas alterações na
classe clsCargo. Pode criar planilhas com vários formulários,
cada um ligando a uma ou mais planilhas. Pegue um tempo livre, veja um
projeto feito de outra forma e confira se valeria a pena colocar
classes. Quanto maior a complexidade da planilha, melhor e mais simples
ficaria com o uso de classes.

Para dúvidas sobre o artigo, comentários ou sugestões, utilize os
comentários abaixo. Até o próximo artigo!