MS OFFICE - EXCEL 2013 Exemplos e Exercícios

(1)

MS OFFICE - EXCEL 2013

Exemplos e Exercícios

Bruno Almeida de Jesus

(2)

PLANEJAMENTO

DIAS CONTEÚDO

04/06 Apresentações, MS Excel (versões), Revisão (Fórmulas e Formatação Condicional) 06/06 Microsoft Office Excel – Revisão (Funções de data, estatísticas, lógicas)

11/06 Microsoft Office Excel – Revisão (Funções de procura e referência.) 13/06 Microsoft Office Excel – Validação de células. Colar especial. Filtros.

18/06 Microsoft Office Excel – Proteção de células, planilhas e arquivos. Vínculos e Suplementos.

20/06 Microsoft Office Excel – Consolidação e Auditoria em Fórmulas

25/06 Microsoft Office Excel – Tabelas dinâmicas 27/06 Microsoft Office Excel – Exercício

04/07 Microsoft Office Excel – Macros e Formulários com VBA

(3)

MICROSOFT OFFICE EXCEL

• Recursos que exercitaremos esta semana:

(4)

Programação de computadores - VBA

Nesta aula, abordaremos :

• Conceitos básicos da linguagem VBA

• Comandos de entrada, saída, estrutura de seleção e repetição na linguagem VBA

(5)

VBA – Visual Basic for Application

A linguagem de programação utilizada pelo Microsoft Excel é o

VBA - Visual Basic for Applications.

Conforme veremos a partir de agora esta é uma linguagem, ao

mesmo tempo, extremamente simples e poderosa.

Com o VBA temos acesso completo a todos os elementos de

todos os objetos de uma planilha do Excel.

Eduardo

Arruda

(6)

Por que utilizar?

O VBA é uma linguagem de programação interna do

Microsoft Excel (na prática é a linguagem de programação

para todos os aplicativos do Office: Access, Word, Excel e

Power Point). Usamos o VBA pelo mesmo motivo que

utilizamos macros - para automatizar tarefas e rotinas

repetitivas, envolvendo os diversos elementos do banco

de dados (tabelas, consultas, formulários, relatórios, folhas

de dados, macros e módulos). No entanto, o VBA oferece

maior poder e controle mais detalhado do que as ações de

macro.

Eduardo

Arruda

(7)

Vantagens em utilizarmos o VBA:

• Manipulação de objetos

• Criação de funções definidas pelo usuário

• Definição de condições e controle de fluxo

• Realização de cálculos complexos e solução

de problemas que envolvem uma lógica

complexa

Eduardo

Arruda

(8)

O Ambiente de Programação - o Editor VBA

Eduardo

Arruda

(9)

Declaração de Variáveis

• Uma variável é um espaço na memória do computador, reservado para armazenar um ou mais valores. Fazemos referência a este espaço utilizando nomes. Como o valor armazenado pode variar, à medida que o código VBA é executado, estas estruturas são chamadas de variáveis. • No VBA, não é obrigatório a declaração de variáveis. Porém é recomendável que declaremos

todas as variáveis, de tal forma que o código fique mais claro e de fácil compreensão. Para declararmos uma variável, utilizamos o comando Dim, conforme exemplificado abaixo:

Dim x

Dim nome

Dim teste

Eduardo

Arruda

(10)

Declaração de Variáveis

• Neste caso estamos apenas declarando o nome da variável, sem declarar de que tipo (texto,inteiro, data, etc) é a variável. Uma variável declarada sem tipo é considerada do tipo Variant, o que na prática significa que a variável pode conter qualquer tipo de valor. Pode parecer uma prática interessante a não declaração do tipo da variável, porém isso é altamente desaconselhável. Se não declararmos o tipo, conforme descrito anteriormente, a variável poderá conter qualquer valor. Neste caso o que impede de um campo numérico conter valores de texto ou vice-versa??

• A sintaxe para o comando Dim é a seguinte:

Dim nome_da_variável As tipo_da_variável

Também podemos declarar mais do que uma variável, com um único comando Dim. Para isto, basta separar as variáveis, com vírgula, conforme exemplificado abaixo:

Dim x,y,z As String Dim nome as Double

(11)

Declaração de Variáveis

IMPORTANTE: Observe que definimos o "tipo" de cada variável. O

Tipo define quais dados podem ser armazenados em uma

variável. Por exemplo, variáveis que armazenam valores

numéricos, não devem aceitar caracteres de texto. Variáveis

que armazenam datas, não devem aceitar datas inválidas, como

por exemplo 30/02/2001.

• Toda variável no VBA, é do tipo Variant, isto significa que a

variável pode ser de qualquer tipo. O que define o tipo da

variável é o valor que está armazenado no momento. Existem

funções que conversão de tipo, conforme veremos mais

adiante.

Eduardo

Arruda

(12)

Declaração de Variáveis

• Também podemos utilizar variáveis que não

foram, explicitamente, declaradas com o

comando Dim. Com isso, a variável é criada na

memória, no momento da sua utilização.

• Para fazer com que toda variável tenha que ser,

explicitamente, declarada, antes de ser

utilizada, devemos utilizar o seguinte comando

na seção de declaração do módulo:

• Option Explicit

(13)

Declaração de Variáveis

Exemplo:

Dim a As Integer Dim b As Integer a=5

b=2 c=a+b

Msgbox "A variável C vale: " & c

(14)

Cálculos, Operadores Aritméticos e Exemplos

Dim a As Integer Dim b As Integer

Dim som, subtr, divis, mult, intdivs, expo, modul As Double a = 25

b = 3

' Uso os operadores aritméticos para efetuar cálculos som=a+b subtr=a-b divis=a/b mult=a*b intdivs=a\b expo=a^b

modul= a mod b

'Uso de MsgBox para exibir os resultados.

MsgBox "Os números são: " & a & " e " & b & Chr(13)

MsgBox "Soma: " & som & Chr(13)& "Subtração: " & subtr & Chr(13) MsgBox "Divisão: " & divis & Chr(13)& "Multiplicação: " & mult & Chr(13) MsgBox "Divisão inteira: " & intdivs & Chr(13)& "Exponenciação: " & expo & Chr(13)

MsgBox "Resto da divisão: " & modul

Eduardo

Arruda

(15)

Estrutura If...Then e os Operadores de Comparação

If x>y Then

' Comandos a serem executados quando x for maior do que y

Comando 1 Comando 2 ...

Comando n

Else

' Comandos a serem executados quando x for menor ou igual a y

Comando 1 Comando 2 ...

Comando n

End If

Eduardo

Arruda

(16)

Estrutura If...Then Exemplo:

Dim x,y,z,k As Integer x=12 y=15 z=20 k=15 If x<y Then

MsgBox "x é menor do que y." Else

MsgBox "x é maior do que y." End If

Eduardo

Arruda

16

If y<x Then

MsgBox "Y é menor do que x" Else

MsgBox "Y é maior do que x" End If

If y<=k Then

MsgBox "y é menor ou igual à k" Else

MsgBox "Y é maior do que k" End If

If x<>z Then

(17)

Estrutura If...Then

Comparando valores de tipos diferentes.

Comparação Resultado

Entre duas variáveis com valores

numéricos. Comparação normal. Entre duas variáveis com valores

do tipo texto. É efetuada uma comparação entre valores de texto. Por exemplo

Xuxa é maior do que Abel (X vem depois do A).

Uma das variáveis possui um valor numérico e outra possui um valor de texto

O valor numérico será sempre considerado menor do que o valor de texto.

Uma das variáveis possui um valor numérico e a outra está vazia.

Efetua uma comparação numérica, atribuindo 0 (zero) para a variável vazia.

Uma variável possui um valor de texto e a outra está vazia, sem valor.

Efetua uma comparação de texto, atribuindo texto de comprimento zero (" ")para a variável vazia. As duas variáveis possuem um

valor vazio. São consideradas iguais.

(18)

O Escopo das variáveis, no VBA.

• O escopo de uma variável, define em que partes do código a variável pode ser utilizada. Em VBA, podemos ter os seguintes escopos para as variáveis:

• Escopo de Módulo: Uma variável declarada dentro do Módulo (um módulo pode conter um ou mais procedimentos ou funções, cada procedimento função começa com um Sub ou Function e termina com um End Sub ou End Function, respectivamente.

• Veremos mais sobre funções e procedimentos, nas próximas lições), mas fora de qualquer Procedimento. Com isso a variável pode ser utilizada dentro de todo o bloco de código do Módulo, inclusive dentro dos Procedimentos, caso exista algum.

(19)

• Um módulo de uma planilha pode conter uma ou mais macros,

sendo que cada macro é um procedimento, isto é, inicia com um

Sub e termina com um End Sub. Tudo o que estiver entre Sub e End

Sub, faz parte da respectiva macro. Uma variável declarada ao nível

de Módulo, existe enquanto o Módulo estiver sendo executado.

• São as variáveis declaradas na seção de Declarações do Módulo.

Uma variável declarada ao nível de Módulo, poderá ser utilizada em

todos os procedimentos/funções do módulo, isto é, em todas as

macros que fazem parte do respectivo módulo.

(20)

A estrutura If ... Then...ElseIf...Else

20

If condição Then

‘Comandos a serem executados, caso a condição seja verdadeira.

Comando1 Comando2 ...

ElseIf condição-2

‘Comandos a serem executados, caso a condição2 seja verdadeira.

ElseIf condição-3

‘Comandos a serem executados, caso a condição3 seja verdadeira.

ElseIf condição-n

‘Comandos a serem executados, caso a condição n seja verdadeira.

Else

‘Comandos a serem executados, caso nenhuma das condições anteriores seja verdadeira.

(21)

A estrutura Select...Case

Quando precisamos realizar uma série de testes, é mais eficiente utilizarmos uma única estrutura Select...Case , do que utilizarmos uma série de testes utilizando a estrutura If...Then...ElseIf

Dim x x=10

Select Case x Case 2

MsgBox "X vale 2 !" Case 4

MsgBox "X vale 10 !" Case Else

MsgBox "X não é um número par, menor do que 12 " End Select

(22)

Estruturas de repetição.

• Em determinadas situações, precisamos repetir um ou mais comandos, um número específico de vezes, ou até que uma determinada condição torne-se verdadeira ou falsa.

• Por exemplo, pode ser que haja a necessidade de percorrer todos os registros de uma determinada tabela, até que o último registro seja alcançado. Para isso, utilizamos as chamadas estruturas de repetição, ou Laços.

• A partir de agora, aprenderemos as estruturas de repetição disponíveis.

Eduardo

Arruda

(23)

A estrutura For...Next.

Eduardo

Arruda

23

• Utilizamos o laço For...Next, para repetir um segmento de código, um número determinado de vezes.

• Utilizamos esta estrutura, quando já sabemos o número de vezes que uma determinada seção de código deve ser repetida.

• Neste tipo de estrutura, normalmente, utilizamos uma variável como contador.

• Este contador varia de um valor inicial até um valor final. O Formato geral desta estrutura é o seguinte:

For contador = inicio to fim incremento

Comando1 Comando2 .

. .

Comando N

(24)

A estrutura For...Next.

Eduardo

Arruda

24

Dim x

x=10

Soma=0

' Faz a soma dos 10 primeiros números maiores do que zero

For i=1 to x

Soma = Soma +i

A estrutura Do...Loop.

Eduardo

Arruda

25

Esta estrutura pode ser utilizada para repetir um trecho de código,

enquanto uma determinada condição for verdadeira, ou até que uma determinada condição torne-se verdadeira .

Podemos utilizar dois operadores condicionais diferentes:

While ou Until . Os operadores While ou Until, podem ser utilizados de duas maneiras diferentes:

No início do laço, ou no final do laço. Com isso temos quatro situações distintas, vamos analisar cada uma delas, a partir de agora.

A estrutura Do While Condição...Loop.

Neste caso, estamos utilizando o operador condicional While, no início do laço. O formato geral, neste caso é o seguinte

Do While condição/teste

(26)

A estrutura Do...Loop.

Eduardo

Arruda

26

Dim x

x=10

Contador=1

Soma=0

' Faz a soma dos 10 primeiros números maiores do que zero

Do While Contador <= x

Soma = Soma + Contador Contador = Contador + 1

Loop

(27)

A estrutura Do... Loop While Condição.

27

Neste caso, deslocamos o teste de condição para o final do laço.

Com o teste no final do laço, o código dentro do laço, será executado, pelo menos

uma vez,

pois o teste somente é feito no final, e continuará sendo executado, enquanto a

condição for verdadeira.

O formato geral, neste caso é o seguinte:

Do

Comando1 Comando2 .

. .

Comandon

(28)

A estrutura Do... Loop While Condição.

Eduardo

Arruda

28

Dim x

x=10

Contador=1

Soma=0

' Faz a soma dos 10 primeiros números maiores do que zero

Do

Loop While Contador <= x

(29)

A estrutura Do Until Condição...Loop.

Neste caso, estamos utilizando o operador condicional Until,

no início do laço. O formato geral, neste caso é o

seguinte:

Eduardo

Arruda

29

Do Until condição

Comando1 Comando2 .

. .

Comandon

(30)

A estrutura Do Until Condição...Loop.

30

Dim x

x=10

Contador=1

Soma=0

' Faz a soma dos 10 primeiros números maiores do que zero

Do Until Contador > x

Loop

(31)

Funções do VBA

Funções de Tipo

Eduardo

Arruda

31 A Função IsDate.

A função IsDate recebe uma variável ou expressão como argumento, e determina se a variável ou

expressão é uma data válida, ou pode ser convertida para uma data válida. Caso o argumento passado seja uma data válida, a função retorna Verdadeiro, caso contrário, retorna Falso. Podemos utilizar esta função, por exemplo, para verificar se o usuário digitou uma data válida, em um campo de um formulário. A sintaxe da função IsDate é a seguinte:

IsDate(NomeDaVariável) ou

IsDate(expressão)

A seguir temos um exemplo de utilização da função IsDate:

If IsDate(x) Then

MsgBox "Você digitou uma data válida !"

Else

MsgBox "Data inválida, digite novamente !“

(32)

Funções do VBA

Funções de Tipo

Eduardo

Arruda

32

A função IsNumeric.

A função IsNumeric recebe uma variável ou expressão como argumento, e determina se o valor atribuído à variável ou expressão é numérico, ou pode ser convertido para numérico. Caso o valor seja numérico, ou possa ser convertido, a função retorna Verdadeiro, caso contrário, retorna Falso.

A sintaxe da função IsNumeric é a seguinte: IsNumeric(NomeDaVariável)

ou

IsNumeric(expressão)

A seguir temos um exemplo de utilização da função IsNumeric: Dim x,y,z x=123

'Atribuo um valor que não pode ser convertido 'para numérico y = "Riachuelo - 80"

z = Date()

mensagem = "Valor de x: " & x &" É numérico ? " & IsNumeric(x)& Chr(13)

(33)

Funções do VBA

Funções de Tipo

Função CStr

A função CStr converte uma variável ou resultado de um

expressão, para o subtipo String.

Observe este linha de código:

MsgBox CStr("12345" & " hectares de terra")

Eduardo

Arruda

(34)

VBA - Funções para tratamento de Texto

Função Chr

A função Chr(número), recebe um número como parâmetro, e retorna o caractere ASCII, associado ao número passado como parâmetro.

Considere o exemplo:

Chr(65)

Este exemplo de uso da função retorna o caractere "A" maiúsculo.

Eduardo

Arruda

(35)

VBA - Funções para tratamento de Texto

Função Len

Esta função determina o tamanho da String que foi passada como parâmetro para a função. Considere o exemplo:

MsgBox Len("Este é um exemplo de uso da função Len !!")

Este exemplo de uso da função, retorna 41,

Eduardo

Arruda

(36)

VBA - Funções para tratamento de Texto

Função LCase

Esta função converte para minúsculas, a String que foi passada como parâmetro para a função. Considere o exemplo:

MsgBox Lcase("ESTE É UM EXEMPLO DE USO DA FUNÇÃO LCASE!!")

Este exemplo de uso da função, converte o parâmetro passado, para letras minúsculas.

Eduardo

Arruda

(37)

VBA - Funções para tratamento de Texto

Função String

Esta função retorna um determinado caractere, um número especificado de vezes. O formato geral da função é o seguinte:

String(n, Caracter)

onde n é o número de vezes que Caractere deve ser repitido. Considere o exemplo:

MsgBox String(35,"*")

Eduardo

Arruda

(38)

Funções para tratamento de Data e Hora

Função Date

Retorna a data corrente do sistema. Não precisamos passar parâmetros para esta função. Considere o exemplo abaixo:

MsgBox "Data do Sistema: " & Date()

Eduardo

Arruda

(39)

Funções para tratamento de Data e Hora

Função Day

Recebe como parâmetro uma data, e retorna um número entre 1 e 31, indicando o dia do mês. O formato geral é o seguinte:

Day(data)

Considere o exemplo abaixo:

MsgBox "Dia do mês: " & Day(Date( ))

Eduardo

Arruda

(40)

Funções para tratamento de Data e Hora

Função Month

Recebe como parâmetro uma data, e retorna um número entre 1 e 12, indicando o mês do ano. O formato geral é o seguinte:

Month(data)

MsgBox "Mês do ano: " & Month(Date( ))

Eduardo

Arruda

(41)

Funções para tratamento de Data e Hora

Função MonthName

Recebe como parâmetro um número, indicativo do mês do ano (1 - Janeiro, 2 - Fevereiro, e assim por diante), e um segundo parâmetro que pode ser Verdadeiro ou Falso. Se o segundo parâmetro for verdadeiro, o nome do mês será exibido abreviadamente. O formato geral é o seguinte:

MonthName(número_do_mês, abreviar)

MsgBox "Mês do ano: " & MonthName(Month(Date))

Eduardo

Arruda

(42)

Funções para tratamento de Data e Hora

Função Hour

Recebe como parâmetro uma hora, e retorna um número entre 0 e 23, indicando o hora do dia. O formato geral é o seguinte:

Hour(horário)

MsgBox "Hora do dia: " & Hour(Time( ))

(43)

Funções para tratamento de Data e Hora

Eduardo

Arruda

43

A função DateDiff

Esta função pode ser utilizada para determinar o número de intervalos (em dias, trimestres, semestres, anos, etc), entre duas datas. A sintaxe desta função é o seguinte:

DateDiff(intervalo, data1, data2)

O parâmetro intervalo é uma String que diz que tipo de intervalo vamos calcular. Por exemplo, é este parâmetro que define se queremos calcular o número de dias, ou o número de meses entre duas datas. Na Tabela a seguir, temos os valores possíveis para o parâmetro intervalo.

Valores para o parâmetro intervalo

Valor Descrição yyyy Anos q Trimestres m Meses

y Dias do ano (o mesmo que dias) d Dias

w Semanas

ww Semanas do ano (o mesmo que semanas) h Horas

n Minutos s Segundos

A título de exemplo, vamos calcular o número de meses, desde o descobrimento do Brasil, até 31 de Dezembro de 1999. Para isso, utilizaríamos o seguinte comando

(44)

Eduardo

Arruda

44

Funções para tratamento de Data e Hora

A função DateAdd

Esta função pode ser utilizada para determinar uma data futura, com base em uma data fornecida, o tipo de período a ser acrescentado (dias, meses, anos, etc), e o número de períodos a serem acrescentados. A sintaxe desta função é o seguinte:

DateAdd(intervalo, número_de_intervalos, data)

O parâmetro intervalo é uma String que diz que tipo de intervalo vamos acrescentar. Por exemplo, é este parâmetro que define se queremos acrescentar um número especificado de dias, meses, anos, etc. Na Tabela a seguir, temos os valores possíveis para o parâmetro intervalo.

Valores para o parâmetro intervalo.

Valor Descrição yyyy Anos q Trimestres m Meses

y Dias do ano (o mesmo que dias) d Dias

w Semanas

ww Semanas do ano h Horas

n Minutos s Segundos

A título de exemplo, vamos calcular a data em que tivemos um período de 1000 meses, após o descobrimento do Brasil. Para isso, utilizaríamos o seguinte comando:

(45)

Função Year

Recebe como parâmetro uma data, e retorna um número

indicativo do ano. O formato geral é o seguinte:

Year(data)

Considere o exemplo abaixo:

MsgBox "Ano atual: " & Year(Date( ))

45

(46)

Função WeekDay

Recebe como parâmetros uma data, e um parâmetro opcional, que indica qual o primeiro dia da semana. Se este parâmetro for omitido, o primeiro dia da semana será considerado Domingo. O valor para o primeiro dia da semana é numérico: 1 - Domingo, 2 - Segunda-feira, e assim por diante.

WeekDay(data, prim_dia_semana.)

MsgBox "Dia da semana: " & WeekDay("31/12/1999")

Eduardo

Arruda

46

(47)

Funções para tratamento de Data e Hora

Função WeekDayName

Recebe como parâmetro um número, indicativo do dia da semana, e um segundo parâmetro que pode ser Verdadeiro ou Falso. Se o segundo parâmetro for verdadeiro, o nome do dia da semana será exibido abreviadamente. O formato geral é o seguinte:

WeekDayName(número_do_dia, abreviar)

MsgBox "Dia da semana: " & WeekDayName(6,False)

Eduardo

Arruda

(48)

Funções para Cálculos

Função Descrição

Abs(n) Retorna o valor absoluto (sem sinal), do número n.

Atn(n) Retorna o valor do arco, cuja tangente é o número n. O número n deve ser fornecido em radianos.

Cos(n) Retorna o coseno do número n. O número n deve ser fornecido em radianos.

Exp(n) Retorna o número e (logaritmo neperiano e=2,7183), elevado no número n.

Log(n) Retorna o logaritmo natural de um número n.

Rnd(n) Retorna um número aleatório entre 0 e 1.

(49)

Funções para Cálculos

Função Descrição

Sgn(n) Retorna um número inteiro, indicando o sinal do número n. Retorna -1 para números negativos e 1 para números positivos.

Sin(n) Retorna o seno do número n. O número n deve ser fornecido em radianos

Sqr(n) Retorna a Raiz quadrada do número n.

Tan(n) Retorna a tangente do número n. O número n deve ser fornecido em radianos.

NOTA: Para converter graus para radianos, multiplique o valor em graus por pi (3.14), e divida o resultado por 180.