Trabalhando programao VBA no EXCEL

VBA uma linguagem de programao disponvel em todo pacote Office, (Word, Excel, Access) que disponibiliza ao usurio tcnicas de programao para agilizar melhor seus trabalhos. Para ativar o editor VB do Excel, clique no Boto Office escolha Opes do Excel e verifique se a guia Mostrar Guia Desenvolvedor esta ativa.

Aps ativar a guia desenvolvedor acesse o ambiente do VBA.

Para entender uma linguagem de programao voc primeiro dever aprender seus conceitos, como Desvios, Laos, Variveis etc....

Vamos comear fazendo uma simples conta de multiplicao. Para isso iremos criar um formulrio e utilizar seus componentes. Para criar um formulrio clique no menu Inserir opo UserForm. Para montar nosso formulrio usaremos a caixa de ferramentas.

Para Alterar as propriedades de cada componente utilize a caixa de propriedades. Clique no componente e escolha o item a ser alterado.

Monte conforme abaixo, nosso primeiro projeto.

Aps montar toda a imagem do projeto precisaremos programar suas funes. Para isso iremos utilizar variveis e operadores aritmticos. O objetivo do projeto que quando o usurio digitar os dados nas caixas de texto (entrada) e pressionar qual operao deseja realizar, o resultado seja expresso no rotulo resultado. Para que isso funcione clique duas vezes no boto SOMA e ao abrir a tela de programao, digite os comandos abaixo.

DIM VALOR1,VALOR2 as DoubleVALOR1= TEXTBOX1.TEXTVALOR2=TEXTBOX2.TEXTLABEL1.CAPTION=VALOR1+VALOR2

Nota: O comando DIM utilizado para declararmos as variveis do projeto especificando seu nome e tipo (Double significa valor numrico).Valor1 e Valor2 foram os nomes dados as variveis.O sinal de = significa atribuio exemplo: Valor1=textbox1.text (valor1 recebe o valor da caixa de texto textbox1)

Para testar seu projeto clique no boto Executar

Para que os outros botes funcione programe conforme o boto soma s mudando o operador aritmtico.Para testa seu aprendizado tente montar seu segundo formulrio conforme abaixo

Comando do Boto ANTECIPADODIM MENSALIDADE AS DOUBLEMENSALIDADE=TEXTBOX1.TEXTLABEL2.CAPTION=MENSALIDADE-(MENSALIDADE*5/100)

Comando do Boto ATRASADODIM MENSALIDADE AS DOUBLEMENSALIDADE=TEXTBOX1.TEXTLABEL2.CAPTION=MENSALIDADE+(MENSALIDADE*5/100)

DesviosAgora vamos criar um programa utilizando os conceitos de desvios. Estrutura de questionamento de um programa If ...Then...Else, com esta podemos determinar qual cdigo vai ser executado se o resultado for verdadeiro.Sua sintaxe a seguinte:If condio Then Comando 1 Else Comando 1.... End if

Se a condio for verdadeira ira executar parte do cdigo que contm o comando, caso seja falsa executar a outra parte. Para que voc possa compreender melhor, nada melhor que utilizar-mos um exemplo prtico.Para testar vamos criar um projeto, onde digitaremos um nome e se o nome for igual a ERICO, o sistema chama um outro formulrio. Primeiro crie o formulrio 1(Userform1) conforme abaixoDigite o comando abaixo do boto Acessar:Dim nome As Stringnome = Textbox1.textIf textbox1 = "VBA" ThenUserForm2.ShowElseMsgBox "Nome esta incorreto"End If

Segundo crie o formulrio 2 (Userform2) que ser chamado pelo formulrio 1.

Execute e veja seu funcionamento.Exercicios de Reviso1-ELABORE UM PROGRAMA PARA RECEBER A NOTA DA PROVA E DO TESTE DE UM ALUNO E VERIFIQUE SE O MESMO ESTA APROVADO OU REPROVADO. Construa o formulrio abaixo:

DIM PROVA,TESTE AS DOUBLEDIM MEDIA AS DOUBLEDIM ALUNO AS STRINGMEDIA=0ALUNO=TEXTBOX1.TEXTPROVA=TEXTBOX2.TEXTTESTE=TEXTBOX3.TEXTMEDIA=(PROVA+TESTE)/2

IF MEDIA>=7 THEN LABEL4.CAPTION='APROVADO...'&MEDIAELSE LABEL4.CAPTION='REPROVADO...'&MEDIAEND IF

2-ELABORE UM PROGRAMA PARA RECEBER A IDADE DE UM NADADOR E INFORMA EM QUE SALA ELE SERA INCLUIDO. Construa o formulrio abaixo:

DIM IDADE AS INTEGERIDADE=TEXTBOX1.TEXTIF (IDADE >=5) AND (IDADE =8) AND (IDADE =11) AND (IDADE =14) AND (IDADE =18) THEN LABEL3.CAPTION='ADULTO';End IF

3-ELABORE UM PROGRAMA PARA TRS NUMEROS E INFORMA O MAIOR DELES. Construa o formulrio abaixo:

DIM N1,N2,N3 AS DOUBLEN1=TEXTBOX1.TEXTN2=TEXTBOX2.TEXTN3=TEXTBOX3.TEXTIF (N1>N2) AND (N1>N3) THEN LABEL5.CAPTION=N1ELSE IF (N2>N1) AND (N2>N3) THEN LABEL5.CAPTION=N2ELSE IF (N3>N1) AND (N3>N2) THEN LABEL5.CAPTION=N3End IF

Exercicios

A)Elabore um algoritmo que receba o valor de um produto em dlar e converta para Real.B)Elabore um algoritmo que receba o Nome e Salrio de um funcionrio e fornea o valor do desconto do Vale Transporte (6%) e Vale refeio (3%)C)Elabore um Algoritmo que receba o nome do curso e valor desejado por um aluno e fornece, quanto o mesmo vai pagar se for a vista (10% desconto) 2 vezes (7% de acrscimo fornea a valor das parcelas) -3 vezes (12% acrscimo fornea o valor das parcelas)

Laos de RepetioFor...Next, em determinadas situaes precisamos fazer a repetio de um ou mais comandos, quase sempre utilizamos o lao de repetioFor, quando j sabemos o nmeros de vezes que o cdigo deve ser executado.Sua sintaxe a seguinte:For contador = inicio do fim do incrementoComando1Next

Vamos fazer um exemplo prtico e simples para que voc entenda melhor a utilizao desta estrutura.

Neste projeto iremos somar todos os numero de 1 a 10 e mostrar seu resultado.

Construa o form conforme abaixo:

Clique no boto Iniciar e digite o cdigo abaixo.

Neste projeto iremos usar um componente COMBOBOX (Caixa de Combinao), construa o forme abaixo :

Programe o boto conforme abaixo.

Para adicionar itens no componente caixa de combinao utilize o comando AddItem e o nome da cidade que voc deseja adicionar.Para visualizar a lista que voc criou clique no boto criar lista, e depois no componente caixa de combinao.

Outra forma de carregar o combobox a partir da prpria planilha do Excel utilizando para isso a propriedade Rowsorce, digite na planilha na coluna A os mesmos nomes das cidades.Crie outro form e coloque apenas o componente Combobox

Feche o VBA e acesse a planilha do Excel Plan1, na Coluna A digite as cidades.

Volte ao VBA e clique no combobox e na caixa de propriedades altere conforme abaixo.

Na propriedade ROWSOURCE informe a planilha e as coordenadas (colunas e linhas) onde esto as informaes.

Neste projeto vamos aprender um cdigo para inserir planilhas no Excel via cdigo.Crie um formulrio conforme a tela abaixo:

Programe o boto adicionar planilhas. O comando SHEETS.ADD vai adicionar no Excel uma planilha a cada clique.

Para deletar crie um segundo boto e adicione o comando abaixo.

Sheets("nome planilha").Delete, onde inserirmos sheets que representa planilha, entre parnteses colocamos o nome da planilha e por fim delete.

Reviso de Algoritmo SeqenciaisElabore os algoritmos abaixo codificando para a linguagem VBA. Crie um menu e chame cada algoritmo atravs de um boto.a)Faa um algoritmo que leia a idade de uma pessoa expressa em anos, meses e dias e mostre-a expressa apenas em dias.b)Escreva um algoritmo para ler o salrio mensal e o percentual de reajuste. Calcular e escrever o valor do novo salrio.c)Escreva um algoritmo para ler um valor inteiro e escrever o seu antecessor (anterior a ele)d)Escreva um algoritmo para ler o nmero de eleitores de um municpio, o nmero de votos brancos, nulos e vlidos. Calcular e escrever o percentual que cada um representa em relao ao total de eleitores.e)Uma revendedora de carros usados paga a seus funcionrios vendedores, um salrio fixo por ms, mais uma comisso tambm fixa para cada carro vendido e mais 5 % do valor das vendas por ele efetuadas. Escrever um algoritmo que leia o nmero de carros por ele vendidos, o valor total de suas vendas, o salrio fixo e o valor que recebe por carro vendido. Calcule e escreva o salrio mensal do vendedor.f)A turma C composta de 60 alunos, e a turma D de 20 alunos. Escreva um algoritmo que leia o percentual de alunos reprovados na turma C, o percentual de aprovados na turma D, calcule e escreva:a. O nmero de alunos reprovados na turma C.b. O nmero de alunos reprovados na turma D.c. A percentagem de alunos reprovados em relao ao total de alunos das duas turmas.g) Faa um algoritmo que o usurio digite um nmero e obtenha como resultado o dobro e o triplo do nmero digitadoh)Faa um algoritmo em que o usurio ir fornecer a receita e as despesas de uma empresa e obtenha como resultado o lucro e o percentual das despesas em relao a receita i)Faa um algoritmo que receba o ano de nascimento de uma pessoa e o ano atual. Em seguida, calcule e mostre a idade da pessoa e quantos anos essa pessoa ter em 2008.j)Sabe-se que o quilowatt de energia custa um quinto do salrio mnimo. Faa um algoritmo que receba o valor do salrio mnimo e a quantidade de quilowatts consumida por uma residncia. Calcule e mostre o valor de cada quilowatt, o valor a ser pago por essa residncia e o valor a ser pago com desconto de 15%.Reviso de Algoritmo com Desvios (IF)

1. Faa um algoritmo que o usurio ir digitar o nome e o sexo de uma pessoa. Dever ser impresso o nome e se esta pessoa um homem ou uma mulher 2. Faa um algoritmo que o usurio digitando o nome e a respectiva idade, retorne se esta pessoa ou no maior de idade 3. Faa um algoritmo que leia o nome, cargo e o salrio de um funcionrio. Se o cargo for operador, o funcionrio dever receber um aumento de 30%, se o cargo for programador, o funcionrio dever receber um aumento de 18%. Escreva o nome e o novo salrio 4. Faa um algoritmo que leia 3 valores. Determine e imprima o maior 5. Para um aluno ser aprovado deve-se: a. se obter um mdia final maior ou igual a 7 b. se obter uma freqncia superior ou igual a 80% c. faa um algoritmo que leia: nome, 1 nota, 2 nota, 3 nota, quantidade de aulas dadas e o nmero de presenas Determine e imprima o nome, se o aluno foi aprovado ou reprovado e a mdia

6. Uma empresa possui a seguinte poltica de auxlio: se um funcionrio ganha menos de 2 salrios mnimos, ele recebe um auxlio de 35% de auxlio se recebe de 2 salrios mnimos at 4 salrios, recebe um auxlio de 20% nos demais casos, recebe um auxlio de 10% Faa um algoritmo em que o operador informando o nome e o salrio obtenha como resultado: o nome, o salrio e o valor do auxlio

7. Sabendo-se que: a. uma caixa de laranja contm 60 unidades b. uma caixa de limo contm 80 unidades c. uma caixa de morango contm 20 unidades Faa um algoritmo em que o usurio ir informar o fruto e o respectivo preo unitrio e obtenha como resultado a fruta e o preo da caixa

8. Faa um algoritmo que leia nome, sexo, salrio. Determine e imprima o nome e o novo salrio, sabendo-se que os homens devem receber um aumento de 20% e as mulheres um aumento de 22% 9. Faa um algoritmo para ler nome da mercadoria, o valor pago ao fornecedor, a categoria da mercadoria (A ou B). Sabendo-se que a categoria A deve possuir uma margem de lucro de 5%, a categoria B 3% e as demais 12%. Obtenha como resultado: o nome da mercadoria, o preo da mercadoria ao consumidor para obter o lucro desejado.

1

Reviso de Algoritmo com Laos (For - While)

a)Criar um algoritmo para receber um valor e escrever sua tabuada.

Cdigos do boto soma Sair e Limpar. Siga o exemplo do boto somar e faa os botes de Mult div sub.

b) Elabore um algoritmo para receber a nota da prova e teste de um aluno e calcule a sua media.

Cdigo do boto Calcular, limpar e Sair

c) Elabore os algoritmos abaixo:1) Apresentar o total da soma dos cem primeiros nmeros inteiros ( 1 + 2+ 3 ...+ 100).2) Elaborar um programa que apresente no final o somatrio dos valores pares existentes na faixa de 1 at 500.3) Apresentar todos os nmeros divisveis por 4 que sejam menores que 200. Para verificar se o nmero divisvel por 4,

Cdigos:

4) Neste algoritmo iremos utilizar o lao de repetio Infinito (While). O algoritmo calcula a media de varias notas at o usurio finalizar digitando o nome fim no lugar do nome do aluno.

Cdigos:

Trabalhando com SubRotinas e Funes

Chamada de um funo dentro de outra funo:

a)Primeiro crie um modulo e insira um procedimento do tipo funo.

Digite o comando:

Depois crie a segunda funo : VALOR_LIQUIDO

Digite o comando:

Exemplo com funo e Sub

a)Abra um modulo e programe conforme abaixo:Digite os cdigos no modulo, observe que ser criado um procedimento e uma funo. O procedimento chama a funo sorteio e solicita um calculo para gerar valores aleatrios.

Criando um formulrio de Pesquisa PersonalizadoNesta Etapa iremos trabalhar mais com a linguagem VBA direcionando para a utilizao de formulrios.Para comear voc precisa criar um formulrio semelhante a esse aqui:

Esse formulrio possui os seguintes controles: Um rtulo, uma caixa de texto, uma caixa de seleo, e dois botes de comando. Depois que voc ajeitar esses controles no formulrio vamos alterar as seguintes propriedades dos controles:

Caixa de Texto(Name): txtLocalizar

Caixa de Seleo(Name): chkCelulaInteiracaption: Coincidir Contedo da Clula Inteira

Boto de opo 1(Name): cmdLocalizarCaption: Localizar

Boto de opo 2(Name): cmdCancelCaption: Cancelar

Propriedades do formulrio(Name): frmLocalizarCaption: Localizar

Programando o boto LocalizarDe dois cliques no boto localizar, para comear a inserir o cdigo.

A lgica simples, quando o formulrio carregado o usurio insere um texto na caixa de texto e clica no boto localizar, necessrio que as clulas nas quais se quer fazer a busca estejam selecionadas, caso contrrio no encontrar nada. Quando o boto localizar clicado, preciso pegar o valor que estiver escrito na caixa de texto, e procurar na planilha, quando encontrar precisa ativar a clula. Caso no ache, ele precisa mostrar uma mensagem, avisando o usurio de que o valor no foi encontrado na seleo. preciso tambm considerar o fato de que o usurio possa no colocar nenhum contedo na caixa de texto, e clique no boto localizar, nesse caso, precisamos tratar a situao e exibir uma mensagem de erro. Primeiro vamos criar uma varivel, para armazenar o contedo da caixa de texto.Vamos cham-la de varLocalizar,e ela vai ser uma varivel do tipo variant, para que possa possuir tambm valores do tipo data. Dim varLocalizar As Variant Vamos tratar o erro caso o texto procurado pelo usurio no seja localizado, vamos fazer assim: On Error GoTo Erro:Erro: MsgBox "A Expresso no Pde Ser Localizada"

Pronto, vamos agora supor que o usurio no coloque nada na caixa de texto e clique no boto localizar, vamos exibir uma mensagem de texto avisando que a caixa de texto localizar no pode ficar vazia.Entre as linhas On error goto erro: e Erro escreva o seguinte cdigo: If txtlocalizar.Text = "" Then Me.Hide MsgBox "Digite a expresso a ser Localizada", vbCritical Me.Show 'muda o foco para a caixa de texto txtlocalizar.SetFocus Exit Sub End If

Explicando os comandos

If txtlocalizar.Text = "" Then - Se a caixa de texto estiver vazia Me.Hide - Esconde o nosso formulrio. MsgBox "Digite a expresso a ser Localizada", vbCriticalMsgbox mostra a mensagem que ns colocamos entre aspas na tela, o vbCritical, diz que o formato da mensagem uma mensagem de erro. Me.Show - Como ns escondemos o formulrio, com o me.hide, agora ns voltamos a mostrar o formulrio. txtlocalizar.SetFocus - faz com que o foco fique na caixa de texto quando o formulrio volta a ser exibido, isso faz com que o formulrio voltando a aparecer j permita ao usurio escrever a nova expresso a ser localizada, sem a necessidade de clicar na caixa de texto antes. Exit sub sai da rotina e volta para o formulrio, sem executar o restante do cdigo End IF, fecha o IF que ns abrimos para ver se a caixa de texto no estava vazia.Agora ns j sabemos se a caixa de texto tem alguma coisa para ser procurada ou no, ento vamos gravar o contedo da caixa de texto, na varivel strLocalizar. Para fazer isso basta colocar o seguinte cdigo logo embaixo do end IF. varLocalizar = txtlocalizar.Text Agora precisamos verificar se o contedo da caixa de texto no uma data caso seja uma data precisamos converter o valor que na caixa de texto texto para data dentro da varivel. If IsDate(varLocalizar) = True Then varLocalizar = CDate(varLocalizar) End IfPronto, agora podemos comear a procurar o texto na seleo. Mas antes, precisamos ver se a caixa de checagem no est marcada, porque se estiver marcada, ento precisamos procurar a expresso exata dentro da clula e no apenas parte dela, alm disso precisamos verificar se o contedo da caixa de texto no uma data, porque se for uma data ela precisa ser convertida internamente em nosso programa para uma data, caso contrrio o nosso programa no vai conseguir localizar.Para isso colocamos as seguintes linhas. varLocalizar = txtlocalizar.Text If IsDate(varLocalizar) = True Then varLocalizar = CDate(varLocalizar) End If 'se a caixa de coincidir clula inteira estiver marcada If ChkCelulaInteira.Value = True Then 'procura a expresso exata Selection.Find(what:=varLocalizar, after:=ActiveCell, lookat:=xlWhole).Activate Else 'caso contrrio, procura por partes da clula Selection.Find(what:=varLocalizar, after:=ActiveCell, lookat:=xlPart).Activate End If 'sai do cdigo Exit SubO cdigo acima verifica se o contedo da caixa de texto corresponde uma data, se corresponder data, ento o texto convertido em data "(cDate( varlocalizar)". Em seguida checa se a caixa de checagem est marcada, se estiver, ele procura a expresso exata se no estiver marcada, ento ele procura por qualquer parte nas clulas. as linhas que fazem isso so muito parecidas, o que muda apenas uma "palavra".Procura a expresso exata: Selection.Find(what:=varLocalizar, after:=ActiveCell, lookat:=xlWhole).Activate Procura a por partes nas clulas: Selection.Find(what:=varLocalizar, after:=ActiveCell, lookat:=xlPart).ActivateAgora colocamos um exit sub, para evitar que a mensagem do tratamento de erro no seja exibida desnecessariamente.O cdigo completo (inclusive com todos os comentrios) fica assim: Private Sub cmdLocalizar_Click() Dim varLocalizar As Variant 'caso no encontre a expresso a ser localizada 'trata o erro On Error GoTo Erro: 'checa se a caixa de texto no est vazia If txtlocalizar.Text = "" Then Me.Hide MsgBox "Digite a expresso a ser Localizada", vbCritical Me.Show 'muda o foco para a caixa te texto txtlocalizar.SetFocus Exit Sub End If 'pega o valor da caixa de texto e armazena numa varvel varLocalizar = txtlocalizar.Text If IsDate(varLocalizar) = True Then varLocalizar = CDate(varLocalizar) End If 'se a caixa de coincidir clula inteira estiver marcada If ChkCelulaInteira.Value = True Then 'procura a expresso exata Selection.Find(what:=varLocalizar, after:=ActiveCell, lookat:=xlWhole).Activate Else 'caso contrrio, procura por partes da clula Selection.Find(what:=varLocalizar, after:=ActiveCell, lookat:=xlPart).Activate End If 'sai do cdigo Exit SubErro: 'se no achar, exibe a mensagem MsgBox "A Expresso no Pde Ser Localizada"End Sub

Pronto, j podemos localizar dados na nossa planilha, lembre-se no entanto que o nosso cdigo s procura nas clulas selecionadas. Programando o boto CANCELAR Private Sub CmdCancel_Click() 'exconde o formulrio Me.Hide 'descarrega o formulrio Unload MeEnd Sub

Para colocar esse cdigo no boto, de dois cliques no boto cancelar e escreva o cdigo.

Vamos adicionar um mdulo ao nosso projeto para chamar o nosso formulrio LOCALIZAR no Excel.Clique em inserir mdulo.

Um mdulo padro chamado Mdulo1 ser adicionado ao projeto.Dentro desse mdulo vamos criar o cdigo que vai chamar o formulrio frmlocalizar.Sub CarregaForm() 'carrega o frmLocalizar frmLocalizar.ShowEnd Sub

Aps criar o modulo vamos inserir um boto no Excel para ativar esta macro.

Aps adicionar o item CarregaForm clique em Modificar e escolha um smbolo conforme abaixo.

Teste seu form localizar.

Entendendo EVENTOS no VBAEventos so aes que se executam automticamente em determinadas situaes definidas pelo programador.

O Evento OnTime, possui parmetros que recebem uma data e hora especificada e qual procedimento deve ser executado. bom lembrar que voc no est especificando uma data e hora para executar um procedimento, ento essa data e hora no pode ser menor que a data e hora atual.

Utilizando o Evento ON TIMEAbra o excel, e acesse a interface do VBA, insira um Modulo. Declare as seguinte variveis publicas e os Procedimentos IniciaTimer( ) Mensagem( ) ParaTimer() As variveis publicas so declaradas para serem usadas em todos os procedimentos, a declarao Publica indica que elas esto disponveis para todo o projeto.

Public ExecQuando As Double Public Const cExecIntervaloSegundos = 20 'executa a cada 20 segundosPublic Const cProcedimento = "Mensagem" 'procedimento que deve ser executado

O procedimento IniciaTimer, busca a hora do sistema operacional e dispara uma mensagem a cada 20 segundos.

Sub IniciaTimer() ExecQuando = Now + TimeSerial(0, 0, cExecIntervaloSegundos) Application.OnTime earliesttime:=ExecQuando, procedure:=cProcedimento,schedule:=True End Sub

O Procedimento mensagem dispara uma caixa com uma mensagem que ser mostrada a cada 20 segundos. Ela chama o procedimento IniciaTimer

Sub mensagem()MsgBox "Esta mensagem aparece a cada " & cExecIntervaloSegundos & " Segundos"'agenda o procedimento novamenteIniciaTimerEnd Sub

A funo ParaTimer ira desativar o evento On Time.

Sub ParaTimer()On Error Resume NextApplication.OnTime earliesttime:=ExecQuando, procedure:=cProcedimento, schedule:=FalseEnd Sub

Insira as 3 macros e teste seu funcionamento.

Criando Menu Personalizados no ExcelAcesse o VBA e crie o Mdulo a seguir:

Digite a rotina de criao do menu. A idia criar um menu novo e a cada escolha de opo disparar uma mensagem para o usurio.

Salve o modulo e adicione ao Excel a opo para disparar a macro.

Clique em......