quarta-feira, 19 de janeiro de 2011

Muitas pessoas acessam o Blog Coizaradas buscando informações e tutoriais de Excel. A postagem que dá um passo-a-passo como criar Formulários no Excel 2007 fez bastante sucesso...

Nessa postagem um tutorial que fiz sobre consulta em banco de dados usando o Excel 2007 e um pouco de programação em VBA e Formulários.

Podemos fazer acesso a um banco de dados em planilhas independentes usando os recursos de formulário e alguns comandos em programação em VBA. Para utilizar esses recursos, será necessário exibir a Guia Desenvolvedor do Excel 2007. Clique no botão do Office no canto superior esquerdo do vídeo, clique em Opções do Excel, em Mais Usados, habilite a opção Mostrar Guia Desenvolvedor na Faixa de Opções.

Vamos deixar apenas duas planilhas na pasta de trabalho do Excel. Se você tem três ou mais, clique botão direito do mouse sobre as excedentes e escolha Excluir no menu flutuante.

Na primeira aba da planilha escreva Tabela e a segunda nomeie de Consulta. Digite os dados conforma a tabela abaixo na planilha Tabela. Eu utilizei nomes de cidades da região para os locais da pesquisa de preços.

Você pode alterar os nomes dos produtos e dos locais livremente.

Na segunda planilha (Consulta), selecione e escolha uma cor para o fundo. Clique na Guia Desenvolver/Inserir e selecione a Caixa de Grupo dos Controles de Formulário.


Desenhe uma caixa de grupo como mostrado na figura abaixo, começando a partir da linha 5 da planilha Consulta.

Se você precisar aumentar ou diminuir a caixa, clique numa alça de dimensionamento (bolinha) dos cantos da caixa e arraste para o centro da figura para diminuir ou para fora, para aumentar.

No topo da caixa, digite um nome para ela. Pode ser Pesquisa por produtos ou outro que você queira.

Digite os dados iniciais da planilha conforme mostrado abaixo. Produto em A7, Valor em A8 e Valor Revenda em A9, todos alinhados à direita. As caixas brancas são apenas células com contorno preto e fundo branco.

Na linha Produto, vamos inserir uma Caixa de Combinação para o vendedor escolher o produto em uma lista suspensa, sem a necessidade de digitação. Clique na Guia Desenvolver/Inserir e selecione a Caixa de Combinação, que é o segundo botão da primeira linha do Controle de Formulário. Desenhe o tamanho da Caixa de Combinação. Clique com o mouse e arraste para definir o tamanho apropriado. Para aumentar ou diminuir a caixa, clique com o botão direito do mouse sobre ela e depois com o esquerdo nas alças de dimensionamento. Em breve será feita a configuração adequada para exibição da lista.
Ao lado, crie mais uma caixa de grupo. Dê o nome de Locais de Pesquisa.

Insira nessa caixa de grupo, controles do tipo Botão de Opção.
Digite um nome de cidade para cada cidade onde será feira a pesquisa por preços de produtos. Utilizei as cidades da região do Vale do Taquari (Arroio do Meio, Lajeado, Estrela, Santa Cruz do Sul, Teutônia e Venâncio Aires).

Você pode alterar se achar conveniente. Para digitar o nome da cidade, clique sobre o item, quando abrir a caixa, digite o nome do local.

Para movimentar, clique sobre a borda da caixa com o botão direito do mouse e use as setas de direção do teclado para deslocar em qualquer direção. Você pode ainda personalizar as opções, dando um ar mais profissional como efeitos em 3D. Clique com o botão direito do mouse sobre o item e escolha Formatar Controle...

Na janela que abre, clique em Sombreamento em 3D. Crie agora mais uma Caixa de Grupo abaixo dos dados já digitados e nomeie de Valores.

Digite os dados conforma a figura abaixo. Texto alinhado á direita e células vazias pintadas de branco com contorno em preto.

Agora vamos inserir outros elementos da planilha. São os controles ActiveX para escolher Quantidade de itens comprados e também o número de parcelas da compra. Clique na Guia Desenvolvedor, escolha Inserir e nos Controles ActiveX clique em Barra de Rolagem.

Desenhe uma barra de rolagem ao lado do item Quantidade. Lembre-se que as bolinhas servem para dimensionar o tamanho da barra.

Repita o procedimento ao lado da célula “Número de Parcelas” para acrescentar mais uma barra de rolagem. Na próxima etapa iremos adicionar mais dois controles, desta vez serão botões que controlarão a visibilidade da planilha com a base de dados.

Clique na Guia Desenvolvedor, escolha Inserir e nos Controles ActiveX clique em Botão de Comando.

Esse CommandButton deve ser desenhado ao lado da célula Valor Final.

Um detalhe importante: Para modificar ou programar o botão o Modo de Design deverá estar habilitado. Quando desmarcado o botão se comporta como um botão do Windows e não há como editar seu código nem aumentar ou diminuir seu tamanho. Para acrescentar comportamentos ao botão de Controle ActiveX clique no botão Propriedades.


O que deve ser modificado para atribuir comportamentos ao botão... Digite no primeiro botão as configurações da janela abaixo:


Name: É o nome pelo qual o botão será referido na programação. Escolha um nome curto e sem espaços.
BackColor: Permite escolher cor de fundo para o botão
Caption: O nome que será exibido em cima do botão
Font: Permite escolher ou trocar a fonte para o texto do botão
ForeColor: A cor da fonte mostrada no botão
Height: Define a altura do botão em pontos
Left: Distância relacionada ao botão do lado esquerdo da tela
Locked: Quando True, bloqueia o deslocamento do botão na tela
Picture: Atribui uma figura ou foto na imagem do botão
PrintObject: Quando True, imprime o botão com a planilha
Shadow: Exibe uma sombra embaixo do botão
Top: Distância relacionada ao botão do topo da tela
Visible: Aplica o atributo de visibilidade ao botão quando a planilha for aberta
Width: Define a largura do botão em pontos
WordWrap: Quando true, atribui uma quebra na linha quando o texto do botão for muito extenso.

Repita o Procedimento para o segundo botão. Desenhe um novo botão e configure de acordo com essas sugestões:

Name: Ocultar
BackColor: Não mudar a cor do fundo
Caption: Ocultar Planilha
Font: Calibri
ForeColor: Não mudar a cor do texto
Height: 22,5
Left: 203
Locked: True
Picture: (nenhum)
PrintObject: True
Shadow: False
Top: 261
Visible: True
Width: 90,75
WordWrap: False

Para alguém ser um programador em VB são necessárias muitas horas de treinamento formal e de prática. A ideia desse post é só fazer uma pesquisa no Excel utilizando um pouco da programação em VB, por isso só tem mesmo o básico.

Maaassss....antes de iniciar a programação, vamos entender um pouco do Visual Basic:
O VB permite criar aplicativos tanto para máquina local quanto para ambiente de rede. Estes aplicativos podem tanto compartilhar banco de dados quanto acessar um banco de dados cliente-servidor. Os programas desenvolvidos em Visual Basic são orientados a eventos.

Eventos são ações geradas pelo usuário clicar o mouse ou pressionar uma uma tecla. Os eventos podem também ser gerados pelo próprio Windows. Existem eventos associados ao formulário, e cada controle inserido neste.

Agora vamos às linhas de código.Veja se o botão Modo de Design está habilitado e dê dois clicks rápidos sobre o botão Exibir tabela.

Isso deverá abrir o módulo de programação do Visual Basic. Digite as linha de código abaixo.

Private Sub exibir_Click()
Dim Exibir As String
Exibir = MsgBox("Deseja exibir a planilha tabela?", vbYesNo, "Confirmação")
If Exibir = vbYes Then
Sheets("tabela").Visible = True
Sheets("tabela").Select
Else
Exit Sub
End If
End Sub

O que significa:
Private Sub exibir_Click() - Inicia o procedimento
Dim Exibir As String - Declara Exibir como uma variável do tipo String (Uma sequência de Caracteres).

Uma variável é algum tipo de dados ao qual é reservado um espaço físico na memória do computador. Para declarar variáveis, você precisa dar a uma variável tanto um nome (Exibir) quanto um tipo (String). Os nomes de variáveis podem ter até 40 caracteres, e precisam começar com uma letra.

Declarando com o Comando Dim
Sintaxe: Dim As
Exemplo: Dim Exibir As String

- Exibir = MsgBox("Deseja exibir a planilha tabela?", vbYesNo, "Confirmação")
Aqui declaramos que a variável recebeu uma MSGBox com a informação contida nos parênteses.
- VbYesNo define os tipos de botões que aparecerão na Caixa de Mensagem: Sim ou Não
A palavra “Confirmação” aparecerá na barra de título da caixa de mensagem (MessageBox).

- If Exibir = vbYes Then - Aqui iniciam os procedimentos lógicos com os laços de repetição. SE Exibir for igual a SIM, ou seja se o usuário clicar no botão SIM, então...

- Sheets("tabela").Visible = True - Muda a propriedade de visibilidade da planilha Tabela e torne-a Visível.
- Sheets("tabela").Select - Nessa linha estamos ainda informando para torná-la ativa.
- Else - Senão
- Exit Sub - Fecha da janela
- End If - Aqui Fechamos os procedimentos lógicos
- End Sub - No final sempre é necessário fechar ou encerrar o procedimento.

Veja se o Modo de Design ainda está habilitado e dê dois clicks rápidos sobre o botão Ocultar Tabela. Isso deverá abrir o módulo de programação do Visual Basic. Digite as linhas de código abaixo:

Private Sub ocultar_Click()
Sheets("tabela").Visible = False
End Sub

Na linha de código: Sheets("tabela").Visible = False, mudamos a propriedade de visibilidade da planilha Tabela e tornando-a Invisível ou oculta.
Desligue o Modo de Design e teste os botões para confirmar a programação.

Para que o Excel calcule a margem de lucro em cada produto, crie uma Caixa de Seleção...

e ao lado digite Lucro.

Na parte superior da planilha, onde existem as linhas vazias, digite Cidade em A2, Lucro em A3 e Produto em C2, todos alinhados à direita.

Clique com o botão direito do mouse sobre a primeira cidade listada no Formulário e escolha Formatar Controle.
Na guia Controle, no campo Vínculo de célula, digite $B$2 (com $ para Referências Absolutas). Essa célula deve exibir um número que indica qual cidade está selecionada na área Local de Pesquisa no Formulário.
Observe que selecionando outra cidade, o valor da B2, também muda.

A Caixa de Combinação onde o produto será pesquisado, também deve ser configurada a fim de exibir os produtos da lista da planilha Tabela. Acesse a planilha Tabela e dê um nome para o intervalo dos produtos. Veja no exemplo abaixo. Selecione de A2 até A12 e na Caixa de nome digite Prod e pressione Enter para nomear.


Repita o mesmo processo para nomear o intervalo do Local da Pesquisa. Selecione de B2 até G12, clique na Caixa de Nome e digite para esse intervalo o nome de Base.

Volte à planilha Consulta, clique com o botão direito do mouse sobre a Caixa de Combinação e escolha Formatar controle...


Na Guia Controle, digite Prod no Intervalo de entrada. O Excel reconhecerá o intervalo nomeado na planilha Tabela. Em vínculo com a célula escolha $D$2, em Linhas Suspensas não há necessidade de alteração e para um efeito mais elegante marque Sombreamento 3D. Clique OK.

Agora, clicando na seta da Caixa de Combinação, os produtos já aparecem listados.

Para continuar nosso trabalho, vamos configurar a Caixa de Seleção Lucro. Clique com o botão direito sobre ela e escolha Formatar controle.

Em Vínculo de célula escreva $B$3. Assim, a célula B3 irá exibir a resposta VERDADEIRO quando a caixa de seleção estiver marcada e FALSO quando estiver desmarcada.

E para concluir nosso trabalho, as fórmulas!

Para que o valor do produto seja encontrado, será necessário utilizar uma função do Excel que faz um cruzamento entre o local da pesquisa selecionado na caixa de Grupo de Formulário Locais da Pesquisa e o item selecionado na Caixa de Combinação dos Produtos. A função apropriada para esse caso é a INDICE que busca uma informação num banco de dados pela interseção de informações através de linha e coluna.

Na célula para o valor: digite a fórmula: =ÍNDICE(base;D2;B2).
- Indice é a função que executa a pesquisa
- base é o local ou a base de dados para a pesquisa
- D2 é a célula onde está relacionado o vínculo com o produto escolhido na Caixa de Combinação
- B2 é a célula onde está relacionado o vínculo com o local escolhido no Botão de Opção do Formulário

Na célula para o Valor de Revenda: digite a fórmula: =B8*150%
O valor contido em B8 será multiplicado por 150% dando uma margem de 50% sobre o valor original

Na célula para o Lucro: digite a fórmula: =SE(B3=VERDADEIRO;B9-B8;"")
- Se o valor da célula B3 for VERDADEIRO, então calcule o Valor de revenda MENOS o valor da compra, SENÃO deixe a célula VAZIA.

A célula da Quantidade terá o valor definido pela barra de rolagem. Clique no botão Modo de Design e depois um clique na Barra de Rolagem da Quantidade e após em Propriedades. Vamos configurar a rolagem da barra.



Clique no botão Modo de Design e depois um clique na Barra de Rolagem da Quantidade e após em Propriedades.

Apenas mude o valor Max para 20 para limitar em 20 peças por cliente e o valor Min em 1, que é a quantidade mínima da compra.

Para o número de parcelas, repita todo o procedimento para tornar a barra ativa, clique no botão Modo de Design e depois um clique na Barra de Rolagem das parcelas e após em Propriedades. Mude o valor Max para 12 para limitar as parcelas e o valor Min em 2.

O Valor Total será a multiplicação da quantidade pelo Valor Unitário: =B13*B9

A porcentagem de juros depende do número de parcelas, daí você digita essa fórmula com a função SE que calcula os juros dependendo da quantidade de parcelas

=SE(B15<4;0;se(b15>10;3,5%;2,5%))

O valor final da Parcela é calculado assim: =(B14/B15)*B16+B14/B15

O valor final da compra: =B15*B17

Para finalizar a planilha, você poderá acrescentar uma barra com o nome do trabalho sobrescrevendo as células de controle usadas no formulário e na função índice.

Poderá também realizar pequenos ajustes de alinhamentos, exibindo a grade do Excel (Guia Layout da Página, grupo Opções de Planilha, Linha de Grade, Exibir).
Espero que seja útil a quem gosta de aprender mais sobre o Excel que é uma fabulosa ferramenta de trabalho. I hope you enjoy it.

Categories:

2 comentários:

JOSE ROBERTO disse...

Ótimo tutorial, parabéns e obrigado por compartilhar conhecimento.

Solano disse...

Obrigado José Roberto, pelo seu comentário e por acessar o blog Coizaradas. E é verdade mesmo, conhecimento sem publicidade serve pra pouca coisa.

Abraço

Solano Bouvie

Subscribe to RSS Feed Follow me on Twitter!