VBA -  Criando um formulário de Pesquisas (Excel)

 Neste artigo vou mostrar como podemos automatizar o Microsoft Excel usando a linguagem VBA criando um formulário básico de pesquisas.

Os recursos usados no projeto deste artigo foram:

Apresentando o cenário

Iremos criar uma planilha Excel usando a versão 2007 com dados de produtos como : Código, Produto, Valor, Quantidade, Total; e usando código VBA vamos criar um formulário que permita realizar buscas com base no código do produto, onde no formulário de pesquisa o usuário digita o código do produto e os demais valores preenchem de forma automática os campos do formulário.

Preparando o ambiente

Antes de iniciar temos que realizar duas configurações no Microsoft Excel 2007:

Obs: A necessidade de habilitar a execução de macros é que por padrão, o Excel coloca um nível de segurança mais elevado pois códigos maliciosos podem ser executados em scripts através de macros no Excel.

Vamos ativar a guia do desenvolvedor e logo  em seguida habilitar a execução de macros.

Para ativar a guia do desenvolvedor (se ainda não estiver ativa) siga os seguintes passos:

Abra o Excel 2007 e ative o Menu principal de opções clicando no botão superior a esquerda, conforme a figura abaixo;

A seguir clique na guia Opções do Excel para abrir a janela - Opções do Excel;

Nesta janela, selecione o item - Mais Usados - e a seguir marque o item - Mostrar guia Desenvolvedor na Faixa de Opções e clique no botão OK;

No menu do Excel selecione a guia do Desenvolvedor e em seguida clique em Segurança de Macro;

Na janela Central de Confiabilidade selecione a opção - Configurações de Macro - e marque o item para habilitar todas as macros conforme a figura abaixo:

Pronto , feito isso já podemos iniciar o desenvolvimento da aplicação VBA no Excel 2007.

Abra então o Excel 2007 , será aberta uma planilha em branco. Clique então no botão no canto superior a esquerda para abrir o menu principal de opções;

Selecione a opção Salvar como e clique no item : Pasta de Trabalho Habilitada para Macro do Excel;

Em seguida informe o nome do arquivo : Excel_FormulárioPesquisas

Será aberta o arquivo Excel exibindo as três planilhas padrão: Plan1, Plan2 e Plan3;

Vamos definir o papel de cada planilha em nosso projeto Excel;

Vamos começar com a planilha Plan1

A primeira coisa que iremos fazer será renomear a planilha Plan1 para Estoque.

Selecione a planilha Plan1 e clique no menu Formatar e a seguir em Renomear Planilha e altere o nome para Estoque:

Nesta planilha teremos 5 colunas : Código, Produto, Valor, Quantidade e Total

A coluna Valor Total deverá ser o valor do produto entre  coluna Valor e a coluna Quantidade.

Cada coluna conterá informações dos produtos em estoque de uma pequena empresa.

Criando o formulário de Pesquisas com VBA

Vamos agora criar o nosso formulário de pesquisa usando VBA.

A seguir pressione ALT+F11 para abrir o Editor Visual Basic (VBE);

Do VBE você pode ir para o Excel clicando no botão Excel no topo/esquerdo de sua tela . Assim usando os dois botões você pode navegar no VBE para o Excel e, vice-versa.

Nota: Você também pode abrir o Editor Visual Basic clicando no menu Desenvolvedor e a seguir no ícone Visual Basic.

Vamos incluir no projeto VBA um formulário ou UserForm, para isso, clique no menu Inserir -> UserForm;

Podemos agora a partir da Caixa de Ferramentas selecionar controles e arrastar e soltar no formulário.

Altere a propriedade Name do formulário para FormularioPesquisa.

Vamos selecionar os seguintes controles  arrastando cada um  e soltando no FormularioPesquisa :

Obs: Eu estou usando os nomes padrões dos controles neste exemplo por pura preguiça de alterar a propriedade name. O correto em um projeto real é sempre dar nomes significativos aos controles. Ex: lbDados, txtNome, txtValor, txtQuantidade, txtTotal, etc.

Vamos dispor os controles conforme o leiaute abaixo usando o mouse para dimensionar os controles do tamanho desejado.

 

Altere também as seguintes propriedades do UserForm diretamente na janela de propriedades:

Quando o formulário FormularioPesquisa for aberto o usuário poderá digitar o código do produto e os demais valores serão preenchidos automaticamente com os dados da planilha Excel.

Este formulário será aberto a partir da planilha Excel onde iremos definir uma macro que iremos atribuir ao botão. Então primeiro temos que criar a macro.

Definindo a Macro para criar o formulário automático

O que é uma macro?

Uma macro é uma coleção de comandos que você pode aplicar com um único clique. As macros podem automatizar quase tudo que seja possível executar no programa que você está usando e até mesmo permitem fazer coisas que talvez você não soubesse que fossem possíveis.

As macros são programação, mas para usá-las, você não precisa ser um desenvolvedor e nem mesmo ter conhecimento de programação. A maioria das macros que você pode criar nos programas do Office são escritas em uma linguagem chamada Microsoft Visual Basic for Applications, ou VBA.

Em muitos programas do Office, você pode criar uma macro gravando uma série de ações ou escrevendo a macro.

Vamos escrever uma macro que será usada para abrir gerar um formulário automático com base na nossa planilha ativa.

Clique no menu Desenvolvedor e a seguir em Visual Basic para ir para o Editor Visual Basic ou pressione as teclas ALT + F11;

A seguir clique no menu Inserir e a seguir em Módulo para inserir um módulo no projeto:

Após essa operação digite o código abaixo no módulo inserido:

Sub Botão1_Clique()
  FormularioPesquisa.Show
End Sub

Este código usa o método Show do formulário FormularioPesquisa que iremos criar a seguir.

Criando um botão de comando na planilha para chamar a Macro

Retorne à planilha Excel e ativando a guia do desenvolvedor vamos incluir um controle Botão na planilha clicando na opção Inserir e selecionando o controle Botão:

Após selecionar o Botão coloque-o na planilha Excel. Fazendo isso de imediato será aberta a janela atribuir macro, exibindo a macro que criamos no  Módulo.

Selecione a macro e clique em OK;

Com isso atribuímos a macro Botão1_Clique criada ao botão de comando da planilha:

A seguir selecione o botão e altere o texto do Botão para Pesquisa Automática;

Pronto ! Estando na planilha Excel e clicando no botão  Pesquisa Automática teremos o formulário carregado conforme abaixo:

Agora basta digitar o código do produto a pesquisa no formulário e Teclar ENTER para preencher os demais controles TextBox com os valores do produto.

Mas como a mágica acontece ???

Definindo o código VBA do formulário

Vamos voltar ao ambiente de desenvolvimento VBA. Pressione ALT+F11 para abrir o Editor Visual Basic (VBE);

Selecione o controle TextBox1 e a seguir clique com o botão direito do mouse selecionando Exibir Código:

A seguir na caixa de evento selecione o evento AfterUpdate do controle TextBox1:

Neste evento digite o código a seguir:

Private Sub TextBox1_AfterUpdate()

Dim intervalo As Range
Dim texto As String
Dim codigo As Integer
Dim pequisa
Dim mensagem

codigo = TextBox1.Text
Sheets("Estoque").Select
Set intervalo = Range("A2:E200")

On Error GoTo trataErro

pesquisa = Application.WorksheetFunction.VLookup(codigo, intervalo, 2, False)
pesq1 = Application.WorksheetFunction.VLookup(codigo, intervalo, 3, False)
pesq2 = Application.WorksheetFunction.VLookup(codigo, intervalo, 4, False)
pesq3 = Application.WorksheetFunction.VLookup(codigo, intervalo, 5, False)

TextBox2.Text = pesquisa
TextBox3.Text = pesq1
TextBox4.Text = pesq2
TextBox5.Text = pesq3
TextBox1.SetFocus

Exit Sub

trataErro:
   texto = "Produto não localizado!"
   mensagem = MsgBox(texto, vbOKOnly + vbInformation)

End Sub

A função VLookup() no código acima recebe o código do produto , o intervalo de busca e o valor booleano False que indica que a função encontrará somente correspondências exatas.

Esta função localiza um valor na primeira coluna à esquerda de uma tabela(intervalo de células) e retorna um valor na mesma linha de uma coluna especificada na tabela (intervalo).

Sintaxe:

expression .VLookup(Arg1, Arg2, Arg3, Arg4)

Onde expression é uma variável que representa um objeto WorksheetFunction .

Parâmetros

Nome Obrigatório/opcional Tipo de dados Descrição
Arg1 Required Variant Valor_procurado - o valor a ser pesquisado na primeira coluna da matriz.Valor_procurado pode ser um valor ou uma referência.Se valor_procurado for menor do que o menor valor na primeira coluna de matriz_tabela, PROCV retornará o valor de erro #N/A.
Arg2 Required Variant Matriz_tabela - duas ou mais colunas de dados.Use uma referência a um intervalo ou nome de intervalo.Os valores na primeira coluna de matriz_tabela são os valores procurados por valor_procurado.Esses valores podem ser texto, números ou valores lógicos.Texto de letras maiúsculo e minúsculo são equivalentes.
Arg3 Required Variant Núm_índice_coluna - o número da coluna em matriz_tabela a partir do qual o valor correspondente deve ser retornado.Um núm_índice_coluna de 1 retornará o valor na primeira coluna em matriz_tabela; um núm_índice_coluna de 2 retornará o valor na segunda coluna em matriz_tabela e assim por diante.
Arg4 Optional Variant Procurar_intervalo - um valor lógico que especifica se você deseja que o método de VLookup para encontrar uma correspondência exata ou uma correspondência aproximada:

Valor retornado : Variant

fonte: http://msdn.microsoft.com/pt-br/library/office/ff194701%28v=office.15%29.aspx

Os valores retornados são atribuídos às variáveis pesquisa, pesq1, pesq2 e pesq3 que em seguida são atribuídos à respectiva caixa de texto no formulário para exibir os valores.

Pegue a planilha com o código VBA aqui: Excel_FormularioPesquisa.zip

Salmos 5:9 Porque não há fidelidade na boca deles; as suas entranhas são verdadeiras maldades, a sua garganta é um sepulcro aberto; lisonjeiam com a sua língua.
Salmos 5:10 Declara-os culpados, ó Deus; que caiam por seus próprios conselhos; lança-os fora por causa da multidão de suas transgressões, pois se revoltaram contra ti.


Veja os Destaques e novidades do SUPER DVD VB (sempre atualizado) : clique e confira !

Quer migrar para o VB .NET ?

Veja mais sistemas completos para a plataforma .NET no Super DVD .NET , confira...

Quer aprender C# ??

Chegou o Super DVD C# com exclusivo material de suporte e vídeo aulas com curso básico sobre C#
 

   Gostou ?   Compartilhe no Facebook    Compartilhe no Twitter

Referências:

José Carlos Macoratti