Trabalhando programação VBA no EXCEL

VBA é uma linguagem de programação disponível em todo pacote Office, (Word, Excel, Access) que disponibiliza ao usuário técnicas de programação para agilizar melhor seus trabalhos.

Para ativar o editor VB do Excel, clique no Botão Office escolha Opções do Excel e verifique se a guia Mostrar Guia Desenvolvedor esta ativa.

 

1

Após ativar a guia desenvolvedor acesse o ambiente do VBA.

Para entender uma linguagem de programação você primeiro deverá aprender seus conceitos, como Desvios, Laços, Variáveis etc....

 Vamos começar fazendo uma simples conta de multiplicação. Para isso iremos criar um formulário e utilizar seus componentes. Para criar um formulário clique no menu Inserir opção UserForm.

2

Para montar nosso formulário 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.

3

Monte conforme abaixo, nosso primeiro projeto.

Após montar toda a imagem do projeto precisaremos programar suas funções. Para isso iremos utilizar variáveis e operadores aritméticos. O objetivo do projeto é que quando o usuário digitar os dados nas caixas de texto (entrada) e pressionar qual operação deseja

4

realizar, o resultado seja expresso no rotulo resultado. Para que isso funcione clique duas vezes no botão SOMA e ao abrir a tela de programação, digite os comandos abaixo.

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

Nota: O comando DIM é utilizado para declararmos as variáveis do projeto especificando seu nome e tipo (Double significa valor numérico).Valor1 e Valor2 foram os nomes dados as variáveis.O sinal de “=” significa atribuição exemplo: Valor1=textbox1.text (valor1 recebe o valor da caixa de texto textbox1)

Para testar seu projeto clique no botão Executar

Para que os outros botões funcione programe conforme o botão soma só mudando o operador aritmético.

Para testa seu aprendizado tente montar seu segundo formulário conforme abaixo

Comando do Botão ANTECIPADO

DIM MENSALIDADE AS DOUBLE

5

MENSALIDADE=TEXTBOX1.TEXTLABEL2.CAPTION=MENSALIDADE-(MENSALIDADE*5/100)

Comando do Botão ATRASADO

DIM MENSALIDADE AS DOUBLEMENSALIDADE=TEXTBOX1.TEXTLABEL2.CAPTION=MENSALIDADE+(MENSALIDADE*5/100)

Desvios

Agora vamos criar um programa utilizando os conceitos de desvios. Estrutura de questionamento de um programa If ...Then...Else,  com esta podemos determinar qual código vai ser executado se o resultado for verdadeiro. Sua sintaxe é a seguinte:  If  condição Then Comando 1 Else Comando 1.... End if

Se a condição for verdadeira ira executar parte do código que contém  o comando, caso seja falsa executará a outra parte.  Para que você possa compreender melhor, nada melhor que utilizar-mos um exemplo prático.

Para testar vamos criar um projeto, onde digitaremos um nome e se o nome for igual a “ERICO”, o sistema chama um outro formulário.  

Primeiro crie o formulário 1(Userform1) – conforme abaixo

 

Digite o comando abaixo do botão Acessar:

6

 Dim nome As String nome = Textbox1.text

 If  textbox1 = "VBA" Then UserForm2.Show Else MsgBox "Nome esta incorreto" End If

Segundo crie o formulário 2 (Userform2) – que será chamado pelo formulário 1.

Execute e veja seu funcionamento.

Exercicios de Revisão

1-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 formulário abaixo:

DIM PROVA,TESTE AS DOUBLEDIM MEDIA AS DOUBLEDIM ALUNO AS STRINGMEDIA=0

7

ALUNO=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 formulário abaixo:

DIM IDADE AS INTEGERIDADE=TEXTBOX1.TEXTIF (IDADE >=5) AND (IDADE <=7) THEN LABEL3.CAPTION='INFANTIL A'ELSE IF (IDADE >=8) AND (IDADE <=10) THEN LABEL3.CAPTION='INFANTIL B'ELSE IF (IDADE >=11) AND (IDADE <=13) THEN LABEL3.CAPTION='JUVENIL A'ELSE IF (IDADE >=14) AND (IDADE <=17) THEN LABEL3.CAPTION='JUVENIL B'ELSE IF (IDADE>=18) THEN LABEL3.CAPTION='ADULTO';End IF

3-ELABORE UM PROGRAMA PARA TRÊS NUMEROS E INFORMA O MAIOR DELES. Construa o formulário abaixo:

8

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 dólar e converta para Real.

B)Elabore um algoritmo que receba o Nome e Salário de um funcionário e forneça o valor do desconto do Vale Transporte (6%) e Vale refeição (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 acréscimo – forneça a valor das parcelas) -3 vezes (12% acréscimo forneça o valor das parcelas)

Laços de Repetição

“For...Next”, em determinadas situações precisamos fazer a repetição de um ou mais comandos, quase sempre utilizamos o laço de repetiçãoFor, quando já sabemos o números de vezes que o código deve ser executado.

9

Sua sintaxe é a seguinte: For contador = inicio do fim do incremento Comando1 Next

Vamos fazer um exemplo  prático e simples para que você entenda melhor a utilização desta estrutura.

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

Construa o form conforme abaixo:

 Clique no botão Iniciar e digite o código abaixo.

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

10

Programe o botão conforme abaixo.

Para adicionar itens no componente caixa de combinação utilize o comando AddItem e o nome da cidade que você deseja adicionar.

11

Para visualizar a lista que você criou clique no botão criar lista, e depois no componente caixa de combinação.

Outra forma de carregar o combobox é a partir da própria 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.

12

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 estão as informações.

Neste projeto vamos aprender um código para inserir planilhas no Excel via código.

Crie um formulário conforme a tela abaixo:

13

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

Para deletar crie um segundo botão e adicione o comando abaixo.

 “Sheets("nome planilha").Delete”, onde inserirmos sheets que representa planilha, entre parênteses colocamos o nome da planilha e por fim delete.

  

  

14

Revisão de Algoritmo Seqüenciais

Elabore os algoritmos abaixo codificando para a linguagem VBA. Crie um menu e chame cada algoritmo através de um botão.

a)Faça 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 salário mensal e o percentual de reajuste. Calcular e escrever o valor do novo salário.

c)Escreva um algoritmo para ler um valor inteiro e escrever o seu antecessor (anterior a ele)

d)Escreva um algoritmo para ler o número de eleitores de um município, o número de votos brancos, nulos e válidos. Calcular e escrever o percentual que cada um representa em relação ao total de eleitores.

e)Uma revendedora de carros usados paga a seus funcionários vendedores, um salário fixo por mês, mais uma comissão também fixa para cada carro vendido e mais 5 % do valor das vendas por ele efetuadas. Escrever um algoritmo que leia o número de carros por ele vendidos, o valor total de suas vendas, o salário fixo e o valor que recebe por carro vendido. Calcule e escreva o salário 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 número de alunos reprovados na turma C.

b.      O número de alunos reprovados na turma D.

c.      A percentagem de alunos reprovados em relação ao total de alunos das duas turmas.

g) Faça um algoritmo que o usuário digite um número e obtenha como resultado o dobro e o triplo do número digitado

h)Faça um algoritmo em que o usuário irá fornecer a receita e as despesas de uma empresa e obtenha como resultado o lucro e o percentual das despesas em relação a receita

i)Faça 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 salário mínimo. Faça um algoritmo que receba o valor do salário mínimo e a quantidade de quilowatts consumida por uma residência. Calcule e mostre o valor de cada quilowatt, o valor a ser pago por essa residência e o valor a ser pago com desconto de 15%.

15

Revisão de Algoritmo com Desvios (IF)

1. Faça um algoritmo que o usuário 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. Faça um algoritmo que o usuário digitando o nome e a respectiva idade, retorne se esta pessoa é ou não maior de idade

3. Faça um algoritmo que leia o nome, cargo e o salário de um funcionário. Se o cargo for operador, o funcionário deverá receber um aumento de 30%, se o cargo for programador, o funcionário deverá receber um aumento de 18%. Escreva o nome e o novo salário

4. Faça um algoritmo que leia 3 valores. Determine e imprima o maior

5. Para um aluno ser aprovado deve-se: a. se obter um média final maior ou igual a 7 b. se obter uma freqüência superior ou igual a 80% c. faça um algoritmo que leia: nome, 1ª nota, 2ª nota, 3ª nota, quantidade de aulas

dadas e o número de presenças Determine e imprima o nome, se o aluno foi aprovado ou reprovado e a média

6. Uma empresa possui a seguinte política de auxílio: se um funcionário ganha menos de 2 salários mínimos, ele recebe um auxílio de 35%

de auxílio se recebe de 2 salários mínimos até 4 salários, recebe um auxílio de 20% nos demais casos, recebe um auxílio de 10%

Faça um algoritmo em que o operador informando o nome e o salário obtenha como resultado: o nome, o salário e o valor do auxílio

7. Sabendo-se que: a. uma caixa de laranja contém 60 unidades b. uma caixa de limão contém 80 unidades c. uma caixa de morango contém 20 unidades

Faça um algoritmo em que o usuário irá informar o fruto e o respectivo preço unitário e obtenha como resultado a fruta e o preço da caixa

8. Faça um algoritmo que leia nome, sexo, salário. Determine e imprima o nome e o novo salário, sabendo-se que os homens devem receber um aumento de 20% e as mulheres um aumento de 22%

9. Faça 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 preço da mercadoria ao consumidor para obter o lucro desejado.

16

Revisão de Algoritmo com Laços (For - While)

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

Códigos do botão soma – Sair e Limpar. Siga o exemplo do botão somar e faça os botões de Mult – div – sub.

17

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

Código do botão Calcular, limpar e Sair

18

c) Elabore os algoritmos abaixo:

1) Apresentar o total da soma dos cem primeiros números inteiros ( 1 + 2+ 3 ...+ 100).2) Elaborar um programa que apresente no final o somatório dos valores pares

existentes na faixa de 1 até 500.3) Apresentar todos os números divisíveis por 4 que sejam menores que 200. Para

verificar se o número é divisível por 4,

Códigos:

19

4) Neste algoritmo iremos utilizar o laço de repetição Infinito (While). O algoritmo calcula a media de varias notas até o usuário finalizar digitando o nome fim no lugar do nome do aluno.

Códigos:

20

Trabalhando com SubRotinas e Funções

Chamada de um função dentro de outra função:

a)Primeiro crie um modulo e insira um procedimento do tipo função.

Digite o comando:

Depois crie a segunda função : VALOR_LIQUIDO

21

Digite o comando:

22

Exemplo com função e Sub

a)Abra um modulo e programe conforme abaixo:

Digite os códigos no modulo, observe que será criado um procedimento e uma função. O procedimento chama a função sorteio e solicita um calculo para gerar valores aleatórios.

23

Criando um formulário de Pesquisa Personalizado

Nesta Etapa iremos trabalhar mais com a linguagem VBA direcionando para a utilização de formulários.

Para começar você precisa criar um formulário semelhante a esse aqui:

Esse formulário possui os seguintes controles: Um rótulo, uma caixa de texto, uma caixa de seleção, e dois botões de comando. Depois que você ajeitar esses controles no formulário vamos alterar as seguintes propriedades dos controles:

Caixa de Texto(Name): txtLocalizar

Caixa de Seleção(Name): chkCelulaInteiracaption: Coincidir Conteúdo da Célula Inteira

Botão de opção 1(Name): cmdLocalizarCaption: Localizar

Botão de opção 2(Name): cmdCancelCaption: Cancelar

Propriedades do formulário(Name): frmLocalizarCaption: Localizar

24

Programando o botão Localizar

De dois cliques no botão “localizar”, para começar a inserir o código.

A lógica é simples, quando o formulário é carregado o usuário insere um texto na caixa de texto e clica no botão localizar, é necessário que as células nas quais se quer fazer a busca estejam selecionadas, caso contrário não encontrará nada. Quando o botão localizar é clicado, é preciso pegar o valor que estiver escrito na caixa de texto, e procurar na planilha, quando encontrar precisa ativar a célula. Caso não ache, ele precisa mostrar uma mensagem, avisando o usuário de que o valor não foi encontrado na seleção. É preciso também considerar o fato de que o usuário possa não colocar nenhum conteúdo na caixa de texto, e clique no botão localizar, nesse caso, precisamos tratar a situação e exibir uma mensagem de erro.

Primeiro vamos criar uma variável, para armazenar o conteúdo da caixa de texto.

Vamos chamá-la de varLocalizar,e ela vai ser uma variável do tipo variant, para que possa possuir também valores do tipo data.

25

Dim varLocalizar As Variant Vamos tratar o erro caso o texto procurado pelo usuário não seja localizado, vamos fazer assim:

On Error GoTo Erro:Erro: MsgBox "A Expressão não Pôde Ser Localizada"

Pronto, vamos agora supor que o usuário não coloque nada na caixa de texto e clique no botão localizar, vamos exibir uma mensagem de texto avisando que a caixa de texto localizar não pode ficar vazia.

Entre as linhas “On error goto erro: “ e “Erro” escreva o seguinte código:

If txtlocalizar.Text = "" Then Me.Hide MsgBox "Digite a expressão 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 formulário. MsgBox "Digite a expressão a ser Localizada", vbCriticalMsgbox mostra a mensagem que nós colocamos entre aspas na tela, o vbCritical, diz que o formato da mensagem é uma mensagem de erro. Me.Show - Como nós escondemos o formulário, com o me.hide, agora nós voltamos a mostrar o formulário. txtlocalizar.SetFocus - faz com que o foco fique na caixa de texto quando o formulário volta a ser exibido, isso faz com que o formulário voltando a aparecer já permita ao usuário escrever a nova expressão a ser localizada, sem a necessidade de clicar na caixa de texto antes. Exit sub sai da rotina e volta para o formulário, sem executar o restante do código End IF, fecha o IF que nós abrimos para ver se a caixa de texto não estava vazia.

Agora nós já sabemos se a caixa de texto tem alguma coisa para ser procurada ou não, então vamos gravar o conteúdo da caixa de texto, na variável strLocalizar. Para fazer isso basta colocar o seguinte código logo embaixo do end IF.

varLocalizar = txtlocalizar.Text Agora precisamos verificar se o conteúdo da caixa de texto não é uma data caso seja uma data precisamos converter o valor que na caixa de texto é texto para data dentro da variável.

If IsDate(varLocalizar) = True Then varLocalizar = CDate(varLocalizar) End If

26

Pronto, agora podemos começar a procurar o texto na seleção. Mas antes, precisamos ver se a caixa de checagem não está marcada, porque se estiver marcada, então precisamos procurar a expressão exata dentro da célula e não apenas parte dela, além disso precisamos verificar se o conteúdo da caixa de texto não é uma data, porque se for uma data ela precisa ser convertida internamente em nosso programa para uma data, caso contrário o nosso programa não 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 célula inteira estiver marcada If ChkCelulaInteira.Value = True Then 'procura a expressão exata Selection.Find(what:=varLocalizar, after:=ActiveCell, lookat:=xlWhole).Activate Else 'caso contrário, procura por partes da célula Selection.Find(what:=varLocalizar, after:=ActiveCell, lookat:=xlPart).Activate End If 'sai do código Exit Sub

O código acima verifica se o conteúdo da caixa de texto corresponde à uma data, se corresponder à data, então o texto é convertido em data "(cDate( varlocalizar)". Em seguida checa se a caixa de checagem está marcada, se estiver, ele procura a expressão exata se não estiver marcada, então ele procura por qualquer parte nas células. as linhas que fazem isso são muito parecidas, o que muda é apenas uma "palavra".

Procura a expressão exata:

Selection.Find(what:=varLocalizar, after:=ActiveCell, lookat:=xlWhole).Activate Procura a por partes nas células:

Selection.Find(what:=varLocalizar, after:=ActiveCell, lookat:=xlPart).ActivateAgora colocamos um exit sub, para evitar que a mensagem do tratamento de erro não seja exibida desnecessariamente.

O código completo (inclusive com todos os comentários) fica assim:

Private Sub cmdLocalizar_Click() Dim varLocalizar As Variant 'caso não encontre a expressão a ser localizada 'trata o erro On Error GoTo Erro: 'checa se a caixa de texto não está vazia If txtlocalizar.Text = "" Then

27

Me.Hide MsgBox "Digite a expressão 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 varável varLocalizar = txtlocalizar.Text If IsDate(varLocalizar) = True Then varLocalizar = CDate(varLocalizar) End If 'se a caixa de coincidir célula inteira estiver marcada If ChkCelulaInteira.Value = True Then 'procura a expressão exata Selection.Find(what:=varLocalizar, after:=ActiveCell, lookat:=xlWhole).Activate Else 'caso contrário, procura por partes da célula Selection.Find(what:=varLocalizar, after:=ActiveCell, lookat:=xlPart).Activate End If 'sai do código Exit SubErro: 'se não achar, exibe a mensagem MsgBox "A Expressão não Pôde Ser Localizada"End Sub

Pronto, já podemos localizar dados na nossa planilha, lembre-se no entanto que o nosso

código só procura nas células selecionadas.

Programando o botão CANCELAR

Private Sub CmdCancel_Click() 'exconde o formulário Me.Hide 'descarrega o formulário Unload MeEnd Sub

Para colocar esse código no botão, de dois cliques no botão cancelar e escreva o código.

28

Vamos adicionar um módulo ao nosso projeto para chamar o nosso formulário LOCALIZAR no Excel.

Clique em inserir módulo.

Um módulo padrão chamado Módulo1 será adicionado ao projeto.

Dentro desse módulo vamos criar o código que vai chamar o formulário frmlocalizar.

Sub CarregaForm() 'carrega o frmLocalizar frmLocalizar.ShowEnd Sub

Após criar o modulo vamos inserir um botão no Excel para ativar esta macro.

29

Após adicionar o item CarregaForm clique em Modificar e escolha um símbolo conforme abaixo.

Teste seu form localizar.

30

Entendendo EVENTOS no VBA

Eventos são ações que se executam automáticamente em determinadas situações definidas pelo programador.

O Evento OnTime, possui parâmetros que recebem uma data e hora especificada e qual procedimento deve ser executado. é bom lembrar que você não está especificando uma data e hora para executar um procedimento, então essa data e hora não pode ser menor que a data e hora atual.

Utilizando o Evento ON TIME

Abra o excel, e acesse a interface do VBA, insira um Modulo. Declare as seguinte variáveis publicas e os Procedimentos IniciaTimer( ) – Mensagem( ) – ParaTimer()

‘ As variáveis publicas são declaradas para serem usadas em todos os procedimentos, a declaração Publica indica que elas estão disponíveis para todo o projeto.

Public ExecQuando As Double

Public Const cExecIntervaloSegundos = 20 'executa a cada 20 segundos

Public 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

31

‘ A função 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.

32

Criando Menu Personalizados no Excel

Acesse o VBA e crie o Módulo a seguir:

Digite a rotina de criação do menu. A idéia é criar um menu novo e a cada escolha de opção disparar uma mensagem para o usuário.

Salve o modulo e adicione ao Excel a opção para disparar a macro.

33

Clique em......

34