sexta-feira, 10 de junho de 2011

vba e excel. tutorial passo a passo

Excel VBA - Parte I: Introdução ao VBA

desenvolvendo VBA através de um exemplo prático - Parte I

Neste artigo abordo como desenvolver aplicações em VBA no Excel para iniciantes que possuam pouco ou mesmo nenhum conhecimento sobre o assunto.


This knol is part of the collection: Visual Basic for Application no Excel
« Previous (Excel VBA – Parte V: Adicionando cód...
(Excel VBA - Parte II: Gravador de ma... Next »

O Potencial

Planilha Muitas das tarefas e rotinas do dia a dia, em grande parte das empresas, são realizadas através de simples planilhas de cálculos, como o Excel. Essas planilhas são utilizadas como banco de dados; calculadoras para engenharia e até como editores de texto. A grande maioria dos usuários, no entanto, ainda não se deu conta da enorme potencialidade disponível através do VBA, ou “Visual Basic for Applications”. Esse artigo irá mostrar através de um exemplo simples, executado passo a passo, como explorar esse potencial e tornar o seu trabalho diferenciado.
O artigo completo está organizado em 5 partes:
Eu os convido a passar algumas horas comigo, explorando esse poderoso e desafiante ambiente de trabalho.

A missão

Missão Uma engenheira, estagiária do departamento de planejamento da qualidade na empresa onde trabalho, procurou-me para pedir ajuda no desenvolvimento de uma planilha em Excel que possibilitasse o lançamento do tempo despendido na medição de peças, executada na metrologia. O seu interesse não era somente em obter uma planilha que pudesse apontar o tempo gasto por cada funcionário e emitir relatórios a respeito, mas também, em aprender como desenvolver esse tipo de planilha através de macros, na verdade, através do VBA. Concordei em ajudá-la e também a envolver outras pessoas interessadas nesse mister.
Durante duas semanas, uma hora por dia, desenvolvemos o programa de forma didática. Esse interesse por parte dos jovens despertou em mim, o desejo de escrever sobre o assunto de tal modo a possibilitar que outras pessoas, igualmente interessadas, vejam no que consiste a construção de aplicações deste tipo, desmistificando um pouco a lógica da programação, que embora complexa, está totalmente ao alcance de quem, com alguma formação, se disponha a compreendê-la.

O problema

Problema Vários técnicos necessitam medir peças e comprovar se estão ou não, dentro das especificações. Essas medições, quando executadas em máquinas e instrumentos convencionais (não computadorizados), necessitam ter o apontamento do tempo despendido em relatórios manuais. Ao final de um período, um relatório gerencial é emitido reportando as peças que foram verificadas e o tempo requerido por cada um dos funcionários. O relatório é feito em Excel e possui os seguintes campos:
  • Nome do técnico
  • Número da peça verificada
  • Descrição da peça verificada
  • Tempo total despendido na verificação
Nosso propósito para essa tarefa é criarmos um pequeno programa que permita ao técnico identificar-se, informar o número e o nome da peça que está verificando, a quantidade e o tempo despendido. Esses dados devem ser apontados da maneira mais automática possível e os registros devem ser preservados para permitirem a geração dos relatórios gerencias sempre que solicitados, seja qual for o período escolhido. Planejamento
Esse desenvolvimento foi feito de forma direta, abordando as facilidades do VBA, mostrando a sua potencialidade e habilitando o interessado a iniciar-se imediatamente neste tipo de desenvolvimento.
Se continua interessado, acesse a segunda parte deste artigo: Gravador de Macros.
This knol is part of the collection: Visual Basic for Application no Excel
« Previous (Excel VBA – Parte V: Adicionando cód...
(Excel VBA - Parte II: Gravador de ma... Next »

Excel VBA - Parte II: Gravador de macros

desenvolvendo VBA através de um exemplo prático

Neste artigo abordo o gravador de macro, ressaltando não só a sua capacidade de memorizar passos repetitivos, mas também de utiliza-lo como uma ferramenta de aprendizagem do VBA.


This knol is part of the collection: Visual Basic for Application no Excel
« Previous (Excel VBA - Parte I: Introdução ao VBA)
(Excel VBA - Parte III: Planejamento ... Next »

Gravador de Macros

Gravador O propósito do gravador de macros é gerar uma macro que possa, mais tarde, reproduzir uma tarefa repetitiva, como veremos no exemplo deste artigo. No entanto, fornece um meio prático e simples de gerar código complexo que pode ser utilizado nos programas mais extensos desenvolvidos em VBA (Visual Basic for Applications), tornando-se deste modo, uma poderosa ferramenta no aprendizado dessa linguagem.

Antes de iniciar

Aviso O Excel, por padrão, apresenta segurança contra execução de macros habilitada, deste modo não teremos acesso às macros. Para resolver este problema, acesse o menu “Ferramentas > Macros > Segurança” e selecione a opção “Média”. Nesta opção, antes de uma macro ser executada é feita uma pergunta ao usuário que pode permitir ou não a sua execução.


Exemplo de aplicação

Recibos Ao voltar de uma viagem a trabalho, temos que lançar os recibos no “relatório de despesas” por ordem de data e por tipo de gasto. Para facilitar essa tarefa, à medida que vou pegando os recibos os lanço em uma planilha, sem me preocupar com a ordem que estão, e depois os classifico por data e tipo de despesa. Como viajamos constantemente, resolvemos construir uma macro para fazer essa classificação.

Passos para gravar um macro:

  • 1º passo: planejamento – o que eu quero que a Macro faça
  • 2° passo: prepare o ambiente para a gravação
  • 3º passo: Acionar o gravador de macros e executar as tarefas manualmente
  • 4º passo: Testar a macro

1° Passo: planejamento da macro:A macro deverá acessar a planilha onde os dados foram lançados conforme a figura 1, posicionar o cursor na primeira célula da tabela, no exemplo [A1], selecionar todo o conteúdo contínuo, [A1:D11] no exemplo, classificar primeiro por data e depois por tipo de despesa.

Exclamação 2° Passo: preparação antes de iniciar o gravador de macros: Sempre que abrimos o Excel ele apresenta uma série de planilhas vazias, normalmente três, com os nomes de “Plan1”, “Plan2” e “Plan3”. Vamos renomear essas planilhas para nomes mais significativos:
  • Altere o nome de “Plan1” para “DadosBrutos”
  • Lance os dados constantes na figura 1
  • Altere o nome de “Plan2” para “Despesas”

3º passo: Acionar o gravador de macros e executar as tarefas manualmente.
Exclamação Ao gravrar uma macro, o gravador não leva em conta o ponto de partida, ou seja, não identifica se a gravação iniciou-se em “Plan1”, “Plan2” ou “Plan3”, deste modo, ao executarmos a macro a partir de uma pasta diferente da utilizada como ponto de partida para gravação, ela não funcionará corretamente. Para evitar isso, sempre iniciamos a gravação em uma pasta qualquer, diferente da pasta onde estão os dados que queremos, forçando a macro a executar acesso à pasta de interesse, memorizando-a:
AcessoGravador
  • Acesse a pasta “Plan3” e clique em qualquer lugar
  • Com o Excel em execução, clicar em “Ferramentas>Macros>Gravar nova macro”
  • Dê o nome de “Ordenar” na caixa de texto que irá se abrir
  • Na caixa “tecla de atalho”, digite a letra “o”
  • Na descrição, coloque “Ordenar relação de despesas”
  • Clique em “Ok”

A partir daqui, todos os comandos que executarmos até pararmos o gravador de macros, serão gravados em um local destinado a essa finalidade, que iremos ver mais adiante. Siga a sequencia de comandos para copiar os dados da planilha "DadosBrutos", coloca-los na planilha "Despesas" e classificá-los por data e tipo de despesa:
Classificar
  • 1. Selecione a planilha “DadosBrutos”
  • 2. Selecione o intervalo entre as células “A1” e “D11”
  • 3. Clique em “Editar>Copiar”
  • 4. Selecione a planilha “Despesas”
  • 5. Selecione a célula “A1”
  • 6. Clique em “Editar>Colar”
  • 7. Com os dados ainda selecionados, clique em “Dados>Classificar”
  • 8. Se a opção “Meu intervalo de dados tem: Linha de cabeçalho” estiver desmarcada, marque-a.
  • 9. Na primeira opção de “Classificar por”, escolha “Data”
  • 10. Na segunda opção, escolha “Tipo de despesa”
  • 11. Clique em Ok
  • 12. Desative o gravador de macros
Stop
Você pode desativar o gravador de macros de dois modos: a partir do menu “Ferramentas>Macros>Parar gravação” ou simplesmente teclando no botão quadrado que aparece na tela quando o gravador de macros está ativado.


Temos agora duas planilhas: uma com os dados brutos iniciais e outra com os dados classificados em “Despesas”.
4º passo: Testar a macro:Para termos certeza de que a macro está funcionando, vamos apagar os dados da planilha “Despesas”, e executarmos a macro:
  • Selecione a planilha “Despesas”
  • Apague todo os dados desta planilha
  • Execute a macro:
    • Como definimos uma tecla de atalho ao gravarmos a macro, para executá-la podemos segurar a tecla “Control” pressionada e apertar a tecla que escolhemos para atalho, neste exemplo, a letra “o”, ou...
    • Acessar o menu “Ferramentas>Macros”, escolher a macro “Ordenar” e clicar no botão “Executar”
Se fez tudo de acordo como descrito até aqui, a sua macro deve ter funcionado direitinho e neste momento você deve estar olhando para a planilha “Despesas” observando os dados classificados, como mostrado a seguir:

Examinando o código gerado

Acesse o menu “Ferramentas > Macro > Editor do Visual Basic” na janela que irá abrir, caso você não veja a janela da figura a seguir, clique em “Visualizar> Project Explorer”.

Posicione o ponteiro do mouse sobre a palavra “Módulo1” e execute um clique duplo. Observe o código que surge na janela ao lado, como mostra a figura a seguir:

Temos um conjunto de instruções entre as palavras “Sub Ordenar ()” e “End Sub”. Tudo o que estiver entre essas duas palavras foi gerado pelo gravador de macros conforme nossas instruções.
Observe, no início, algumas linhas em verde. São linhas de comentários colocadas pelo gravador, contendo informações importantes: o nome da macro; o que a macro faz e a tecla de atalho que escolhemos ao gerar a macro. Para inserir uma linha de comentário, basta colocar um apóstrofe (‘) no início da linha, ou mesmo após um comando. Tudo o que vier a partir daí será ignorado pelo programa. Uma linha de comentário serve para documentar e é muito útil principalmente quando existir tempo considerável entre a construção da macro e análise de nossa parte. Serve também para orientar outras pessoas que irão analisar nosso programa. Como regra geral, peque por excesso: documente tudo o que puder.
Após os comentários iniciais vem a primeira linha de código: Sheets(“DadosBrutos”).Select, esta linha de código diz ao Excel para ir à planilha “DadosBrutos”, esteja onde estiver. Foi por esse motivo que iniciamos a gravação da macro a partir de “Plan3”, caso contrário o Excel passaria a selecionar as células “julgando” já estar na planilha “DadosBrutos”, o que poderia não ser verdade. Observe a sintaxe: Sheets(“DadosBrutos”).Select, como é simples. Informa qual a planilha que está referência e em seguida a seleciona, tornando-a ativa.
A segunda linha de código: Range(“A1:D11”).select, seleciona todas as células compreendidas neste intervalo. O raciocínio é o mesmo utilizado na seleção da planilha na sentença anterior.
As duas sentença seguintes:
Application.CutCopyMode = False Garante que não existe nada sendo copiado ou cortado antes da próxima instrução.
Selection.Copy Copia todo o conteúdo do intervalo selecionado na planilha e coloca na área de transferência.
A seguir, a próxima instrução seleciona a planilha onde queremos que os dados sejam colados: Sheets("Despesas").Select
Indicamos qual a célula de referência para iniciar a colagem: Range("A1").Select, colamos os dados (ActiveSheet.Paste ) e informamos ao Excel que o modo de Cópia e Corte foi encerrado (Application.CutCopyMode = False).
Finalmente, uma série de instruções do Excel configuram a forma como os dados são classificados. Podemos observar as chaves definidas em “C2” e “A2”, e a ordem da classificação ascendente:
Selection.Sort Key1 : = Range("C2"), Order1 : = xlAscending, Key2 : = Range("A2"), Order2 : = xlAscending, Header : = xlGuess, OrderCustom : = 1, MatchCase : = False, Orientation : = xlTopToBottom, DataOption1 : = xlSortNormal, DataOption2 : = xlSortNormal
Exclamação Note que a faixa de dados classificados, ou copiados, é informada no modo absoluto: Range(“A1:D11”).select Isso significa que se entrarmos com dados a partir da linha 11, os mesmos serão ignorados pela macro. Felizmente, através do VBA, essa situação é facilmente resolvida, como veremos em nossos próximos artigos desta série.
Se estiver gostando, acesse a terceira parte deste artigo:
Parte III: Parte III: Planejamento do Sistema.
This knol is part of the collection: Visual Basic for Application no Excel
« Previous (Excel VBA - Parte I: Introdução ao VBA)
(Excel VBA - Parte III: Planejamento ... Next »

Excel VBA - Parte III: Planejamento do sistema

desenvolvendo VBA através de um exemplo prático

Este artigo mostra a necessidade da definição de uma estratégia para resolução do problema. Neste caso, também aplicável na maior parte dos casos, a estratégia adotada foi partir do problema resolvido para determinarmos os passos necessários a sua resolução.


This knol is part of the collection: Visual Basic for Application no Excel
« Previous (Excel VBA - Parte II: Gravador de ma...
(Excel VBA - Parte IV:Inserindo um fo... Next »

Excel VBA - Parte III: Planejamento do sistema

desenvolvendo VBA através de um exemplo prático

Estipulando o objetivo

Nada melhor do que analisar o problema parcialmente resolvido, ou seja, se nosso objetivo é obter um relatório gerencial podemos fazer a abordagem ‘top-down’ partindo do relatório pronto para definir as tarefas necessárias para obtê-lo.

Examinando o formulário

E m nosso caso, o relatório de saída é composto de uma planilha que resume os apontamentos de duas outras planilhas cujos dados são lançados por mês, ou seja, dois tipos de planilha x 12 meses, portanto 24 planilhas.


Saida O relatório de saída, mostrado ao lado, exibe as horas por laboratório, tipo de medição, resultado e equipamento utilizado. As planilhas abrangem dois laboratórios: MHDT e LDT, cada um desses laboratórios possuem máquinas específicas que necessitam ter o tempo de utilização controlado. Além deste controle existe dois tipos de apontamentos: medições de rotina, ou seja, durante um lote de fabricação em andamento e medições para liberação do início do lote, denominadas ‘set up’. Esse tipo de medição é especial porque é feito após o funcionário preparar uma nova operação e é necessário que se tenha certeza de que os ajustes feitos nas máquinas estejam corretos antes de prosseguir com a produção. Finalmente podemos verificar que as horas apontadas têm que refletir as peças aprovadas e as peças reprovadas. Podemos observar que para cada planilha temos um resumo, no lado direito superior da figura, que mostra o total de horas apontados em “Entrada” que é a soma das horas despendidas em “Aprovadas” e “Reprovadas”.

SaidaDetalhe


O relatório final mostra os seguintes dados:
  • Laboratório de metrologia onde foi feita a medição
  • Quantidade de horas em serviço de rotina
  • Total de horas gastas nas medições
  • Número de horas em peças aprovadas
  • Número de horas em peças aprovadas



O relatório

Analisando a figura do relatório de saída fica claro que não necessitamos programar a parte fixa do formulário. Essa parte deve ser formatada manualmente, ficando como a figura a seguir:

As colunas B e C são apenas títulos formatados de forma a separar os laboratórios de MHDT e LDT. As linhas 13, 18, 23, 30, 35 e 40, assinaladas na cor cinza, contêm fórmulas para somar as duas linhas imediatamente superiores, por exemplo, em D13 colocamos a fórmula =D11+D12, e assim por diante, até a coluna O. As células na coluna P contêm fórmulas que somam as linhas horizontalmente, por exemplo, em P11 temos: = D11+E11+F11+G11+H11+I11+J11+K11+L11+M11+N11+011 No último quadro, todas as células contêm fórmulas somando os totais do laboratório entre as linhas 21 e 25 e entre as linhas 38 e 42. Por exemplo, a célula D44 contém a fórmula =D21+D38. Todas as células com fundo amarelo, no intervalo entre D11 e O42, irão receber os dados através de programação.

Definindo as telas do sistema

Todo sistema necessita de um modo de interação com o usuário. Normalmente essa interação é feita através de telas que possuem comandos e opções que podem ser acionados ou inseridos pelo usuário. Pode parecer estranho, mas antes de nos lançarmos à programação propriamente dita, temos que definir o conteúdo de cada tela que será exibida pelo sistema. Em nosso exemplo necessitamos informar ao programa, os seguintes dados:
  • Data da medição
  • Nome do técnico responsável
  • Laboratório onde foi feita a medição
  • Tipo de medição: 'set up' ou rotina
  • Resultado da medição: aprovada ou reprovada
Basicamente necessitamos de uma tela inicial onde o usuário irá se identificar pelo nome em um caixa de listagem. Uma segunda tela irá permitir que entre com os dados do local da medição, equipamento utilizado, peças medidas e resultado da medição.

Finalmente, a adição de de um botão “Atualizar”, completam o nosso exemplo.

Para inserir o botão "Atualizar", clique sobre a barra de ferramentas de menus do "Excel" com o botão direito do "mouse" e assinale a opção "Caixa de Ferramentas de Controle". Selecione o ícone que representa um esquadro e em seguida clique sobre o ícone do botão. Posicione o mouse sobre a planilha, clique e arraste, desenhando um retângulo como na figura acima.

Finalmente, clique com o botão direito do "mouse" sobre o botão que acabou de desenhar e selecione "propriedades". Altere a propriedade "Caption" para "Atualizar.

Veja a seqüencia:



Terminamos a parte mais estressante que é o planejamento. Vamos passar agora para a parte mais divertida: a programação. Venha comigo para a quarta parte deste artigo:
Parte IV:Inserindo um formulário e seus comandos.
This knol is part of the collection: Visual Basic for Application no Excel
« Previous (Excel VBA - Parte II: Gravador de ma...
(Excel VBA - Parte IV:Inserindo um fo... Next »

Excel VBA - Parte IV:Inserindo um formulário e seus comandos

desenvolvendo VBA através de um exemplo prático

Nesta parte do artigo vamos ver como inserir formulários e seus componentes.


This knol is part of the collection: Visual Basic for Application no Excel
« Previous (Excel VBA - Parte III: Planejamento ...
(Excel VBA – Parte V: Adicionando cód... Next »

O Editor do Visual Basic

SaidaDetalhe Quem leu, ou assistiu ao filme, “As crônicas de Nárnia” de C.S. Lewis, pode imaginar que a porta do guarda roupas que separa os dois universos é uma das opções do menu do Excel. Essa porta sempre esteve presente no aplicativo e tudo que temos que fazer para acessar essa outra dimensão é descobri-la e transpô-la. Faça isso através da seqüência:
Ferramentas > Macro > Editor do Visual Basic.
Você não irá encontrar seres mitológicos e encantados, mas irá encontrar um ambiente totalmente novo, onde impera a lógica e o desafio. Neste ambiente você pode tudo e tudo o que imaginar pode se tornar realidade...
Como no conto, o primeiro contato é sempre um mistério, mas à medida que nos aventurarmos nos comandos e painéis iremos adquirir a confiança e o conforto necessário para desenvolvermos nossos programas.
Por ora vamos apenas dar uma olhada nas partes essenciais e aprender como visualizar os painéis que utilizaremos com mais freqüência.
SaidaDetalhe Esse é o mapa do universo onde iremos nos aventurar. Essencialmente, se não estiver vendo os painéis mostrados na figura acima, clique em “Exibir” e selecione:
  • Janela "Verificação Imediata"
  • Project Explorer
  • Janela "Propriedades"


Inserindo um formulário

Abra o Excel e acesse: Ferramentas>Macro>Editor do VisualBasic, ou mais diretamente, segure a tecla ALT pressionada e tecle F11. Já tivemos oportunidade de conhecer o painel do editor do Visual Basic quando utilizamos o gravador de macros na segunda parte deste artigo. Desta vez vamos criar um formulário. Imagine um formulário como sendo a imagem exibida por um programa qualquer quando o executamos. Inserir UserForm1Por exemplo, o próprio Excel é um formulário composto por células e barras de menu. O Word também é um formulário.
Para inserir um formulário utilizando o VBA do Excel, clique em: Inserir> UserForm
Duas coisas devem ocorrer:
  • Um formulário de nome "UserForm1" será exibido,
  • uma nova janela, denominada "Caixa de ferramentas" deve aparecer.
Como dissemos, se a caixa de ferramentas não aparecer, selecione “Exibir” na barra de menu principal e clique sobre “Caixa de ferramentas”.
As ferramentas que aparecem na caixa de ferramentas são bastante conhecidas de todo usuário de computador. Vamos olhar essa caixa um pouco mais de perto.

A caixa de ferramentas

A caixa de ferramentas é um formulário bem ao estilo do Visual Basic, contém vários ícones correspondentes aos diversos tipos de ferramentas que podemos utilizar ao desenvolver um programa. Em nosso exemplo iremos utilizar apenas algumas dessas ferramentas:
Tools Melhor que descrever o que faz cada uma dessas ferramentas é mostrar. As próximas figuras indicam quais ferramentas foram utilizadas no programa exemplo para compor os formulários UserForm1 e UserForm2:
Tools In Forms

Criando o formulário 1

Insira um formulário. Por padrão ele irá receber o nome de “UserForm1”. Vamos manter este nome.
Tool Label Label: Acesse a caixa de ferramentas. Clique sobre o ícone “label” e clique e arraste no formulário para desenhar um retângulo como mostrado a seguir:
Label Insert Tool ComboBox ComboBox:Clique agora sobre a ferramenta “caixa de combinação”. Clique e arraste sobre o formulário, conforme a próxima figura :
ComboBox Insert Tool CommandButton CommandButton:Introduza dois botões de comando da mesma forma como fez para o Label e para o ComboBox. O seu formulário deve estar parecido com o apresentado a seguir. Se necessário, clique sobre os objetos e ajuste o tamanho e posição dos mesmos.
commandButtons Insert

Alterando as propriedades dos objetos

Existem algumas diferenças visíveis entre o formulário que acabamos de criar e o formulário que queremos que o programa exiba.








Além das descrições dos objetos também queremos alterar o tamanho e formato do texto, sublinhar o “E” em “Encerrar” e o “A” em “Acessar”. Essas alterações são feitas acessando-se as propriedades de cada objeto, como veremos a seguir.
Cada um dos objetos contidos no formulário possui propriedades que podem ser alteradas. Vamos começar por “Label1”. Clique nesse objeto e examine o painel “propriedades”. Cada uma das propriedades mostradas configura a aparência e o comportamento do objeto. Vamos alterar a propriedade “Caption”.
A propriedade “Caption” modifica o texto exibido no formulário. Substitua a palavra “Label1” que aparece na coluna ao lado da palavra “Caption” por “ Selecione o seu nome:”, como mostra a figura ao lado. Clique em qualquer lugar fora do texto que acabou de digitar para que o formulário seja atualizado.
Para fazer com que a borda ao redor do texto ajuste-se ao seu tamanho, primeiro arraste a borda com o mouse até o ponto que julgar conveniente, arrumando o texto ao seu gosto. Depois localize a propriedade “Autosize” e altere para “True”.
Outra propriedade que utilizo com muita freqüência é “Font”. Font Properties Essa propriedade permite alterarmos a maneira como o texto é apresentado, tal como o seu tamanho, cor e tipo. Com o texto selecionado no formulário, clique sobre a propriedade “Font”. Dê duplo clique sobre o botão que aparece ao lado direito para acessar o painel para alteração da fonte.
Para efeito deste artigo, sempre que tivermos que alterar uma propriedade, eu indicarei da seguinte forma:
  • Nome do Objeto (identificação na tela) 
    • Propriedade: novo valor 
Adotando essa notação para o “Label1”, como vimos acima, a notação fica da seguinte forma:
  • Label1 (Selecione o seu nome:) 
    • Caption: Selecione o seu nome: 
    • Font: Arial; Negrito;10
Para os outros elementos em “UserForm1”, altere as propriedades a seguir:
  • UserForm1 
    • Caption: Medição de peças – Tela de acesso;
    • Font: Arial; Negrito;14
  • ComboBox1
    • (Name): cbx_Usuarios
    • Font: Arial; Negrito;10
  • CommandButton1 (Encerrar)
    • (Name): cmd_Encerrar
    • Caption: Encerrar
    • Font: Arial; Negrito;10
    • Accelerator: E
  • CommandButton2 (Acessar)
    • (Name): cmd_Acessar
    • Caption: Acessar
    • Font: Arial; Negrito;10
    • Accelerator: A
Duas observações são importantes neste ponto:
  • A propriedade “Accelerator” faz com que surja um sublinhado abaixo da letra que indicamos, portanto essa letra tem que estar contida na propriedade “Caption” do objeto. Esse sublinhado significa que se o usuário, ao rodar o programa, segurar a tecla “Alt” e apertar a tecla destacada, o comando será executado da mesma forma como se tivéssemos clicado sobre o botão com o mouse.
  • A propriedade “(Name)” altera o nome do objeto, portanto ao mudarmos essa propriedade o objeto com o nome anterior “deixa” de existir e precisamos nos referenciar ao mesmo como o novo nome dado. Significa que se procurarmos o “Commandbutton1” não iremos encontrá-lo, em seu lugar teremos “cmd_Encerrar”.

Criando o formulário 2

Controles
Completamos esta parte do artigo com a inserção do segundo formulário. Insira um novo formulário (Inserir > UserForm). Ele receberá o nome padrão de UserForm2. Examine a figura ao lado e crie os controles mostrados, alterando as suas propriedades conforme o indicado mais abaixo (para ver a imagem ampliada, clique sobre a mesma).
Paletas
  • UserForm2
    • Caption: Medição de peças - tela de dados
    • BackColor: &H8000000A&Amp;
    • Font: Arial; Negrito;14
Nota: a propriedade “BackColor” altera a cor do formulário. Ao acessá-la selecione uma cor em uma das duas paletas que vão aparecer. O código acima é colocado na propriedade automaticamente, porém se preferir, pode digitá-lo diretamente.
Altere a propriedade “Font” de todos os objetos para: Arial; Negrito; 10, exceto onde indicado diferente.
  • Label2 (29/07/2009)>
    • Caption: Data
  • Label1 (João Da Silva)>
    • Caption: Nome
Altere todos os outros “Labels” dando à propriedade “Caption” o mesmo que aparece na tela. Selecione todos os “Labels”, para isso selecione um “Label” qualquer e mantenha a tecla “shift” apertada enquanto clica sobre os demais. Altere a propriedade “Font”:
  • Labels (todos)>
    • Font: Arial; Negrito; 10
    • BackColor: &H8000000A&

Nota: ao alterarmos a cor de um formulário temos que alterar também a cor de fundo dos “Labels”, “Frames” e “OptionButtons”. Se não fizermos isso o aspecto do formulário fica prejudicado.
  • ComboBox1 (abaixo de Selecione o local de trabalho:)
    • (Name): cbx_Local

  • ComboBox2
    • (Name): cbx_Equipamentos

Selecione todas as caixas de texto e altere a propriedade “Font”:
  • TextBox (todos)
    • Font: Arial; Negrito; 10
  • Frame1 (tipo de medição)
    • Caption: Tipo de medição
    • BackColor: &H8000000A&
    • Font: Arial; Negrito; 12
  • OptionButton1
    • Caption: Setup
    • BackColor: &H8000000A&
  • OptionButton2
    • Caption: Rotina
    • BackColor: &H8000000A&
  • OptionButton3
    • Caption: Aprovado
    • BackColor: &H8000000A&
  • OptionButton4
    • Caption: Reprovado
    • BackColor: &H8000000A&
  • CommandButton1 (Cancelar)
    • Caption: Cancelar
    • Accelerator: C
  • CommandButton2 (Salvar)
    • Caption: Salvar
    • Accelerator: S
Na próxima parte deste artigo irei mostrar como inserir a programação nos comandos que acabamos de criar. Acesse Excel VBA – Parte V: Adicionando código aos controles: UserForm1
This knol is part of the collection: Visual Basic for Application no Excel
« Previous (Excel VBA - Parte III: Planejamento ...
(Excel VBA – Parte V: Adicionando cód... Next »

Excel VBA – Parte V: Adicionando código aos controles: UserForm2

desenvolvendo VBA através de um exemplo prático

Este é o último artigo desta série, nele mostro como criar um "banco de dados" em forma de planilha e como recuperar e tratar esses dados para atualizar o relatório de saída.
Espero ter atingido o objetivo que é o de despertar o interesse nesse poderoso ambiente de programação, totalmente gratuito, oferecido junto com o Excel.


This knol is part of the collection: Visual Basic for Application no Excel
« Previous (Excel VBA - Parte IV:Inserindo um fo...
(Evitando erros de programação no Vis... Next »

Alterando as propriedades de "Labels"

Abra o editor do VBA, selecione o UserForm1 e dê dois cliques sobre o botão “Acessar”.
Digite o código como mostrado a seguir:

Private Sub cmd_Acessar_Click()
UserForm2.Label1 = cbx_Usuarios.Text
UserForm2.Label2 = Date
UserForm2.Show
End Sub

O formulário 1 está pronto para ser utilizado, no entanto necessitamos de um modo para fazer com que o mesmo seja exibido assim que a planilha do Excel for aberta, ou seja, quando o usuário clicar no ícone da planilha, o Excel inicia e apresenta o formulário sem intervenção do usuário.

Iniciando uma macro automaticamente ao abrir o Excel

Felizmente os desenvolvedores do Excel previram que isso seria interessante e criaram um método bastante simples: Crie uma macro com o nome de Auto_Open(). Tudo o que estiver nesta macro será executado automaticamente quando a planilha for exibida. 
Abra o módulo1, clique em: Inserir> Procedimento. Dê o nome de “Auto_Open” e digite a frase: UserForm1.show. O código deve ficar como a seguir:

Public Sub Auto_Open()
UserForm1.Show
End Sub



Salve a planilha. Feche o Excel. Localize o arquivo através do Windows Explorer e dê duplo clique sobre o nome do mesmo. Você irá notar que a planilha irá se abrir, apresentando o formulário.
Selecione um nome no ComboBox e clique no botão “Acessar”. O UserForm2 será apresentado conforme a figura ao lado:

Fica fácil de entender que ao fazermos referência ao UserForm2, estando ainda no UserForm1, podemos alterar as propriedades de seus componentes. Foi isso que fizemos com o “Label1” e “Label2” que agora apresentam o nome do usuário que acessou a rotina e a data de acesso:
UserForm2.Label1 = cbx_Usuarios.Text
UserForm2.Label2 = Date


Inserindo os dados para preenchimento dos "ComboBox"

Vamos preencher os ComboBox referentes ao local de trabalho e aos equipamentos utilizados. Acesse a planilha onde colocou o nome dos usuários (“Plan2”).
Siga cuidadosamente as instruções a seguir:
Célula                         Digite
B1                                 UsrFrm2:Laboratórios
B2                                 Sala de Medições MHDT
B3                                 Gear Lab MHDT
B4                                 Sala de Medições LHDT
B5                                 Gear Lab LHDT

Célula                         Digite
C1                                 UsrFrm2:Equipamentos
C2                                 Circularímetro
C3                                 Estudo de deformações
C4                                 Gear Lab
C5                                 Perfilômetro
C6                                 Quality Gate
C7                                 Parada de forno
C8                                 Batidas


O código para preencher os "ComboBox

Abra o Editor do Visual Basic e acesse o “UserForm2”. Selecione o método “Activate” e digite o código a seguir:
Private Sub UserForm_Activate() 
'Preenche o ComboBox cbx_Local:
    'Esvazia o ComboBox,caso já esteja preenchido:
    cbx_Local.Clear
    'Selecionamos a planilha onde estão os dados a popularem o combobox:
    Sheets("Plan2").Select
    'Selecinamos a célula onde está o ínício dos dados.
    'Note que selecionamos o cabeçalho da lista e não o primeiro nome
    Range("B1").Select
    Preencher = True
    'Iniciamos a execução da estrutura lógica
    Do While Preencher = True
        'Seleciona a célula que está na linha imediatamente inferior
        ActiveCell.Offset(1, 0).Select
        'Testa o valor da célula e decide se o laço deve ou não continuar
        If ActiveCell.Value = vbNullString Then
            'Se a célula estiver vazia, altera a condição lógica
            Preencher = False
        Else
            'se não estiver, acrescenta o item
            cbx_Local.AddItem ActiveCell.Value
    End If
    Loop
    'Coloca o ComboBox na primeira posição válida:
    cbx_Local.ListIndex = 0
'=========================================================
'Preenche o ComboBox cbx_eqptos
    'Esvazia o ComboBox,caso já esteja preenchido:
    cbx_eqptos.Clear
    'Selecionamos a planilha onde estão os dados a popularem o combobox:
    Sheets("Plan2").Select
    'Selecinamos a célula onde está o ínício dos dados.
    'Note que selecionamos o cabeçalho da lista e não o primeiro nome
    Range("C1").Select
    Preencher = True
    'Iniciamos a execução da estrutura lógica
    Do While Preencher = True
        'Seleciona a célula que está na linha imediatamente inferior
        ActiveCell.Offset(1, 0).Select
        'Testa o valor da célula e decide se o laço deve ou não continuar
        If ActiveCell.Value = vbNullString Then
            'Se a célula estiver vazia, altera a condição lógica
            Preencher = False
        Else
            'se não estiver, acrescenta o item
            cbx_eqptos.AddItem ActiveCell.Value
    End If
    Loop
    'Coloca o ComboBox na primeira posição válida:
    cbx_eqptos.ListIndex = 0
End Sub

Se estiver curioso, pode rodar o programa através da macro “Auto_Open()” e ver o funcionamento dos “ComboBox”.


Botão “Cancelar”

Acionando esse botão o usuário irá “limpar” todos os lançamentos feitos no formulário 2 e retornar ao formulário 1.
Abra o “Editor do Visual Basic”, acesse o “UserForm2” e dê dois cliques sobre o botão “Cancelar”. O evento padrão é “Click” e é esse evento que iremos utilizar. Digite o código a seguir :
Private Sub CommandButton1_Click()
'Esse comando irá limpar os dados do UserForm2 e retornar ao UserForm1
'Faz com que as informações dos text box sejam canceladas
TextBox1.Text = vbNullString
TextBox2.Text = vbNullString
TextBox3.Text = vbNullString
TextBox4.Text = vbNullString
TextBox5.Text = vbNullString
'Desativa a exibição do UserForm2
Unload Me
'Ativa a exibição do UserForm1
UserForm1.Show
End Sub

Botão “Salvar”

A função deste botão é colocar toda a informação existente no “UserForm2” em uma planilha de modo a podermos acessá-la quando for conveniente. Vamos utilizar a “Plan3” para armazenar os dados.
Acesse a “Plan3” e digite conforme a seguir:
Célula                         Digite
A1                                Data
B1                                 Usuário
C1                                 Local
D1                                Equipamento
E1                                 Peça No
F1                                 Peça Desc
G1                                Qtd
H1                                Inicio
I1                                  Fim
J1                                  Total
K1                                 Tipo
L1                                 Resultado

Abra o “Editor do Visual Basic”, acesse o “UserForm2” e dê dois cliques sobre o botão “Salvar”. O evento padrão é “Click” e é esse evento que iremos utilizar. Digite o código a seguir :
Private Sub CommandButton2_Click()
'Insere as informações no banco de dados
'Seleciona a planilha
Sheets("Plan3").Activate
'Posiciona o cursor na primeira linha da planilha
Range("3:3").Select
'Insere uma nova linha de dados (registro)
Selection.Insert Shift:=xlDown
'Seleciona a primeira célula da nova linha e informa a data
Range("A3").Select
ActiveCell.Value = Label2.Caption
'Informa o nome do usuário:
ActiveCell.Offset(0, 1).Value = Label1.Caption
'Informa o local onde foi feito o trabalho:
ActiveCell.Offset(0, 2).Value = cbx_Local.Text
'Informa o equipamento que foi utilizado
ActiveCell.Offset(0, 3).Value = cbx_eqptos.Text
'Informa o número da peça
ActiveCell.Offset(0, 4).Value = TextBox1.Text
'Informa a descrição da peça
ActiveCell.Offset(0, 5).Value = TextBox2.Text
'Informa a quantidade de peças
ActiveCell.Offset(0, 6).Value = TextBox3.Text
'Informa a hora de inicio
ActiveCell.Offset(0, 7).Value = TextBox4.Text
Inicio = CDate(TextBox4.Text)
'Informa a hora de fim
ActiveCell.Offset(0, 8).Value = TextBox5.Text
Fim = CDate(TextBox5.Text)
'Calcula o total
total = CDate(Fim - Inicio)
ActiveCell.OffSet(0,9).Value = total
'Informa o tipo de trabalho
If OptionButton1.Value = True Then
    ActiveCell.Offset(0, 10).Value = "Set-Up"
Else
    ActiveCell.Offset(0, 10).Value = "Rotina"
End If
'Informa o resultado da medição
If OptionButton3.Value = True Then
    ActiveCell.Offset(0, 11).Value = "Aprovado"
Else
    ActiveCell.Offset(0, 11).Value = "Reprovado"
End If
'Limpa os dados para nova entrada
'Limpa as caixas de texto
TextBox1.Text = vbNullString
TextBox2.Text = vbNullString
TextBox3.Text = vbNullString
TextBox4.Text = vbNullString
TextBox5.Text = vbNullString
'Posiciona cbx_local e cbx_eqptos na primeira posição
cbx_Local.ListIndex = 0
cbx_eqptos.ListIndex = 0
End Sub


Inserindo dados 

Vamos executar alguns exemplos. Saia do “Editor Do Visual Basic” e acione a macro “Auto_open”.
Selecione um usuário e clique sobre o botão “Acessar”. Selecione ou digite os dados abaixo, conforme o caso:
Clique no botão “Salvar”.
Entre com novos exemplos a sua vontade. O importante é testar todas as condições do “ComboBox” “cbx_Local” e se estiver disposto, também todas as possibilidade do “cbx_eqptos”. Se preferir utilize os dados presentes na figura a seguir.

 “Plan3” após a inserção dos dados de exemplo (clique para ampliar):
 
O programa, cada vez que um registro é salvo, insere uma linha na posição 3, deslocando as outras linhas para baixo. Deste modo, as inserções mais recentes ocupam as linhas de números mais baixos.

Atualizando o relatório

Já temos os dados salvos, tudo o que temos que fazer para finalizar o programa é atualizar o relatório de saída.
Já colocamos um botão na planilha "Plan1", abaixo do relatório, que ao ser clicado irá atualizar os lançamentos [Excel VBA - Parte III: Planejamento do sistema].
Vamos entender como a rotina neste botão irá atualizar o relatório.
  O relatório está dividido em três partes principais, a primeira corresponde ao local MHDT, a segunda ao local LHDT e a terceira parte é o resumo, ou soma, dos tempos despendidos nesses locais e equipamentos. 
Vamos traçar o fluxo passo a passo:
·         Selecionamos a planilha “Plan3”, onde estão os dados e colocamos o cursor na célula de referência “A3”.
·         Atribuímos a uma variável de controle o valor “True”.
·         Iniciamos um laço lógico para atualizar o relatório, até que encontremos uma linha vazia para mudar o estado da variável lógica e encerrar o laço.
·         Á medida que lemos os dados armazenados, verificamos o local, o tipo e o resultado do trabalho, em função dessas leituras definimos duas linhas para o lançamento do total de horas gastas: Linha1 para o tipo de trabalho e Linha2 para o resultado.
·         A data de execução define a coluna. Como “Janeiro” corresponde à coluna “D”, temos que somar 3 ao mês informado nos dados, por exemplo, se a medição foi feita em “Fevereiro”, mês 02, então somamos 2+3 = 5, ou coluna “E”. Veja que o mês é obtido através de uma função específica no Excel.
·         Definidos as linhas e a coluna, antes de lançar as horas temos que verificar se já existe algum lançamento anterior.  Fazemos isso atribuindo o valor que já estão nas células a uma variável. Esse valor é somado ao valor do registro e lançado novamente na célula.

Abra o Editor do Visual Basic, Módulo1 para inserir essa rotina.

Inserindo o código

O código completo fica conforme a seguir:

 Public Sub Atualizar()
'Atualiza os lançamentos no relatório
'Antes de iniciar os dados existentes no relatório devem
'ser zerados para evitar que sejam somados novamente
Sheets("Plan1").Select
'Limpa Rotinas e setup da Sala de Medição MHDT
Range("D11:O12").Select
Selection.Clear
'Limpa Aprovadas e Reprovadas da Sala de Medição MHDT
Range("D14:O15").Select
Selection.Clear
'Limpa Rotinas e Setup da Sala de Gear Labs MHDT
Range("D16:O17").Select
Selection.Clear
'Limpa Aprovadas e Reprovadas da Sala de Gear Labs MHDT
Range("D19:O20").Select
Selection.Clear
'Limpa Rotinas e setup da Sala de Medição LHDT
Range("D28:O29").Select
Selection.Clear
'Limpa Aprovadas e Reprovadas da Sala de Medição LHDT
Range("D31:O32").Select
Selection.Clear
'Limpa Rotinas e Setup da Sala de Gear Labs LHDT
Range("D33:O34").Select
Selection.Clear
'Limpa Aprovadas e Reprovadas da Sala de Gear Labs LHDT
Range("D36:O37").Select
Selection.Clear
'Seleciona a planilha onde estão os dados (Plan3)
Sheets("Plan3").Select
'Posiciona o cursor na célula de referência (A3)
Range("A3").Select
'Ajusta o valor da variável lógica para True
AtualizaDados = True
'Inicia o laço
Do While AtualizaDados = True
    'Lê o conteudo de "Local"
    LabLocal = ActiveCell.Offset(0, 2).Value
    'Obtem o total de horas gastas
    Total = CDate(ActiveCell.Offset(0, 9).Value)
    Trabalho = ActiveCell.Offset(0, 10).Value
    Resultado = ActiveCell.Offset(0, 11).Value
    'Em função de LOCAL determina o número de Linha1 e Linha2
    Select Case LabLocal
    '========================================================
        Case Is = "Sala de Medições MHDT."
            'Selecina por tipo de trabalho: rotina ou setup
            Select Case Trabalho
                Case Is = "Rotina"
                    Linha1 = 11
                Case Is = "Set-Up"
                    Linha1 = 12
            End Select
            'Seleciona em função do resultado: Aprovado ou reprovado
            Select Case Resultado
                Case Is = "Aprovado"
                    linha2 = 14
                Case Is = "Reprovado"
                    linha2 = 15
            End Select
    '========================================================
        Case Is = "Gear Lab MHDT"
            'Selecina por tipo de trabalho: rotina ou setup
            Select Case Trabalho
                Case Is = "Rotina"
                    Linha1 = 16
                Case Is = "Set-Up"
                    Linha1 = 17
            End Select
            'Seleciona em função do resultado: Aprovado ou reprovado
            Select Case Resultado
                Case Is = "Aprovado"
                    linha2 = 19
                Case Is = "Reprovado"
                    linha2 = 20
            End Select
    '========================================================
        Case Is = "Sala de Medições LHDT"
            'Selecina por tipo de trabalho: rotina ou setup
            Select Case Trabalho
                Case Is = "Rotina"
                    Linha1 = 28
                Case Is = "Set-Up"
                    Linha1 = 29
            End Select
            'Seleciona em função do resultado: Aprovado ou reprovado
            Select Case Resultado
                Case Is = "Aprovado"
                    linha2 = 31
                Case Is = "Reprovado"
                    linha2 = 32
            End Select
    '========================================================
        Case Is = "Gear Lab LHDT."
             'Selecina por tipo de trabalho: rotina ou setup
            Select Case Trabalho
                Case Is = "Rotina"
                    Linha1 = 33
                Case Is = "Set-Up"
                    Linha1 = 34
            End Select
            'Seleciona em função do resultado: Aprovado ou reprovado
            Select Case Resultado
                Case Is = "Aprovado"
                    linha2 = 36
                Case Is = "Reprovado"
                    linha2 = 37
            End Select
    '========================================================
    End Select
    'Em função da data, determina a coluna
    coluna = Month(ActiveCell.Value) + 2
    'Lança dados no relatorio:
    'Primeiro seleciona a planilha onde está: (Plan1)
    Sheets("Plan1").Select
    Linha1 = Linha1 - 1
    linha2 = linha2 - 1
    'Posiciona na célula de referência A1
    Range("A1").Select
    'Obtem valor do tipo de trabalho = Linha1
    Valor = CDate(ActiveCell.Offset(Linha1, coluna).Value)
    'Atualiza
    ActiveCell.Offset(Linha1, coluna).Value = CDate(Valor + Total)
    'Faz a mesma coisa para o resultado:
    Valor = ActiveCell.Offset(linha2, coluna).Value
    ActiveCell.Offset(linha2, coluna).Value = Valor + Total
    'Retorna à planilha onde estão os dados e avança uma linha
    Sheets("Plan3").Select
    ActiveCell.Offset(1, 0).Activate
    'Verifica se a célula é vazia. Se for altera a condição lógica
    If ActiveCell.Value = vbNullString Then
        AtualizaDados = False
    Else
        AtualizaDados = True
    End If
'Encerra o laço
Loop
'Formata as células
Sheets("Plan1").Select
Range("D11:P48").Select
Selection.NumberFormat = "h:mm;@"
End Sub

A estrutura lógica "Select Case"

Note a estrutura lógica “Select Case”. Essa estrutura é mais versátil que a estrutura “IF..Then...Else” quando se tem muitas opções, como é o caso. A sintaxe desta estrutura é:
Select Case VARIAVEL
            Case VARIAVEL is = Teste1
                            Codigo
            Case VARIAVEL is = Teste2
                            Codigo
            Case VARIAVEL is = Teste3
                            Codigo
End Select

Se a variável especificada coincidir com um dos valores testados, o código que segue o teste será executado e os outros ignorados. O cuidado aqui é fazer com que os nomes dados aos testes sejam exatamente iguais ao que a variável irá receber porque se a coincidência não for completa o código será pulado.

O arquivo gerado, completo pode ser baixado através do link a seguir:
[http://www.sixsigmatas.com.br/Exemplo.xls]
O fluxograma para as rotinas no UserForm2 pode ser baixado no link:
[http://www.sixsigmatas.com.br/Visio-Fluxograma.pdf]
Com esse artigo encerramos essa série. Agradeço aos que me acompanharam por essa jornada.
This knol is part of the collection: Visual Basic for Application no Excel
« Previous (Excel VBA - Parte IV:Inserindo um fo...
(Evitando erros de programação no Vis... Next »

 

Nenhum comentário:

Postar um comentário

Postar um comentário