Excel – Macros e Programação em VBA 13 de setembro de 2007Pág. 1 Excel – Macros e Programação em VBA 13 de setembro de 2007Pág. 1 UTFPR – Damat VBA – Módulos,

UTFPR – Damat

Excel – Macros e Programação em VBA

13 de setembro de 2007 Pág. 1



VBA – Módulos, Procedimentos e Funções

• Módulos são uma coleção de declarações e procedimentos do VBA, que armazenados em conjuntos formam uma unidade;

• Procedimentos são conjuntos de instruções que são executadas e produzem uma ação. São procedimentos:

•Sub

•Function

•Property

Apostila Pág. 82

4

UTFPR – Damat





VBA – Módulos, Procedimentos e Funções

• Function é um conjunto de instruções e pode retornar um valor no final de sua execução. Pode utilizar argumentos, como constante, variáveis ou expressões fornecidas através de uma chamada.

• Sub ou Sub-rotina é um conjunto de instruções que executa um conjunto de funções ou cálculos, ou ainda, uma combinação dos dois. No entanto, não retorna valor. Se esta executar um cálculo, não haverá uma maneira direta de se encontrar o resultado.

Apostila Pág. 82

UTFPR – Damat





Sub-Rotina

• A Sub-rotinas podem ser executadas diretamente ou através da chamada do nome da sub-rotina;

• Quando uma sub-rotina é chamada, a execução desloca-se para dentro da sub-rotina e depois de executados todos os comandos, a execução do código continua com o comando seguinte ao que chamou a sub-rotina;

Apostila Pág. 83

UTFPR – Damat





Sub-rotina

• A sintaxe para a criação de uma Sub-rotina é a seguinte:

• Uma sub-rotina poderá ter valores que são fornecidos em tempo de execução e que são utilizados internamente para a realização de cálculos. Estes valores são chamados de argumentos;

Apostila Pág. 83

UTFPR – Damat





Sub-rotina - Exemplo

Declaramos uma rotina chamada Calcula_imposto a qual espera receber 3 parâmetros: salário, desconto e extras que deverão ser fornecidos nesta ordem;

Apostila Pág. 83

UTFPR – Damat





Sub-rotinas sem ParâmetrosTambém podemos criar sub-rotinas sem parâmetros. Neste caso, posso simplesmente não utilizar os parênteses após o nome da Sub-rotina ou utilizar um par de parênteses, sem nada dentro, conforme mostram as Figuras.

Apostila Pág. 84

UTFPR – Damat





Sub-rotina - ExemploVamos criar uma Sub-rotina que exibe uma Caixa de mensagem com a data e a hora do sistema no formato indicado na Figura a seguir:

Funções

Apostila Pág. 84

UTFPR – Damat





Sub-rotina - Exemplo• Na janela do editor do VBA, dê um clique no

módulo onde você deseja inserir a sub-rotina;

• Após o último procedimento encontrado no módulo, digite:

• Observe que automaticamente aparece o fechamento End Sub para a Sub-rotina;

Apostila Pág. 85

UTFPR – Damat





Sub-rotina - ExemploDigite as linhas de código a seguir:

Apostila Pág. 85

UTFPR – Damat





Sub-rotina - Exemplo

u

Apostila Pág. 85

UTFPR – Damat





Chamada de uma Sub-rotinaTemos duas opções para chamarmos uma sub-rotina de outro código:

Apostila Pág. 84

1.Simplesmente digitando o nome da Sub-rotina;

2.Digitando a palavra Call mais o nome da Sub-rotina;

UTFPR – Damat





Chamada de uma Sub-rotina - Exemplo

Cada vez que for necessário exibir a data e a hora nos formatos do exemplo, basta chamar a Sub-rotina exibe_data_hora, utilizando um dos seguintes comandos:

Apostila Pág. 85

Call exibe_data_horaou

exibe_data_hora

UTFPR – Damat





Chamada de Sub-rotina - Exemplo

Vamos criar uma Sub-rotina que chama a Sub-rotina exibe_data_hora, como mostra a figura.

Funções

Apostila Pág. 84

UTFPR – Damat





Criando e utilizando Funções• Função: grupo de comandos que podem ser

executados simplesmente através da chamada do nome da Função;

• Uma função pode ter um ou mais argumentos;• Quando uma função é chamada, a execução desloca-

se para dentro da Função e executa seu código;• Após o término do cálculo da função, a execução do

código continua com o comando seguinte ao que chamou a função.

• A diferença da Função para a Sub-rotina, é que a função sempre retorna um ou mais valores para o comando que a chamou.

Apostila Pág. 85

UTFPR – Damat





Criando e utilizando Funções

• Sintaxe para declaração de uma Função:

Apostila Pág. 86

UTFPR – Damat





Função sem Parâmetros• Uma função pode ser declarada sem parâmetros.

As Figuras mostram as sintaxes possíveis para este caso.

Apostila Pág. 87

Posteriormente veremos exemplo de função.

UTFPR – Damat





Escopo das variáveis no VBA

Escopo de uma variável: define em que partes do código a variável pode ser utilizada.

• Escopo de Módulo: você pode declarar uma variável na seção de declarações do módulo, isto é, 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;

• Escopo de sub-procedimento/função/macro: se uma variável que foi declarada dentro de um sub-procedimento ou função, somente poderá ser utilizada dentro do sub-procedimento ou função. Uma variável declarada ao nível de procedimento existe enquanto o procedimento estiver sendo executado;

Apostila Pág. 89

UTFPR – Damat





Escopo das variáveis no VBA

Com a janela do VBA aberta, clique logo abaixo da caixa de listagem onde se encontra Geral (Figura);

Declare as variáveis x, y e aux 1 (Figura);

Funções

Apostila Pág. 89

UTFPR – Damat





Exemplo de Escopo de variáveis no VBA

Crie uma sub-rotina como mostrado a seguir:

Sub proc1()Dim a, b x = 10 y = 20 a = 5 b = 7 MsgBox "x= " & x & " y= " & y & " a= " & a & " b= " & bEnd Sub

Apostila Pág. 90

UTFPR – Damat





Exemplo de escopo de variáveis no VBA

Crie uma nova sub-rotina que chamará a sub-rotina proc1 como mostrado a seguir:

Sub Callproc1() Call proc1 aux1 = a + b aux2 = x + y MsgBox "aux1 = " & aux1 MsgBox "aux2 = " & aux2End Sub

Apostila Pág. 90

UTFPR – Damat





Exemplo de escopo de variáveis no VBA

Execute a sub-rotina Callproc1(). Pode-se observar que o resultado para:

aux1= a + b é zero.

E o resultado para:

aux2 = x + y é 30.

As variáveis a e b não tem escopo de módulo. Já as variáveis x e y sim.

Apostila Pág. 90

UTFPR – Damat





Função Personalizada – Exemplo 1

Criar e utilizar uma função personalizada para o cálculo do imposto de renda pessoa física. A função será chamada Calcula_IRPF e receberá como parâmetro o valor da Base de Cálculo para o imposto. A função retornará o valor do imposto a ser pago.

1. Abra o Excel;2. Abra a pasta Módulo 1 – Exercício 05.xls;3. Agora vamos iniciar a criação da função

Calcula_IRPF;

Apostila Pág. 92

UTFPR – Damat





Função Personalizada – Exemplo 14. Pressione Alt + F11 para exibir o Editor do VBA;

6. Para criar uma função que possa ser utilizada em qualquer planilha da sua pasta de trabalho, você deve criar a função em um Módulo de Código separado. Não pode ser em um dos módulos de código de uma das planilhas ou da pasta de trabalho. Clique com o botão direito do mouse em VBAProject (Modulo 1 –Exercício 05.xls) no painel da esquerda. No menu que é exibido, clique em Inserir Módulo. Será criado um módulo chamado Módulo 1, conforme Figura.

5. Agora vamos iniciar a criação da função Calcula_IRPF;

Apostila Pág. 92

UTFPR – Damat






7. Dê um clique duplo em Módulo 1, para selecioná-lo. Agora vamos criar a função Calcula_IRPF dentro do Módulo 1. Para criar uma nova função, selecione no menu Inserir Procedimento. Será exibida a janela Adicionar Procedimento. Digite o nome da função – Calcula_IRPF no campo Nome e marque a opção Função no grupo Tipo. Sua janela deverá estar como na Figura.

8. Clique em OK;

Apostila Pág. 93

UTFPR – Damat






9. Será inserido o que chamamos de “esqueleto da função”, ou seja, a declaração da função e o comando End Sub, conforme mostra a Figura;

10.Agora vamos alterar este código. O primeiro passo é definir os parâmetros que a função receberá e o tipo que será retornado pela função.

Apostila Pág. 93

UTFPR – Damat





Função Personalizada – Exemplo 111.Vamos definir um parâmetro chamado BaseDeCálculo,

do tipo Currency. O tipo de retorno da função também será definido como sendo do tipo Currency. Altere o código para que fique conforme na Figura a seguir:

12.Até aqui fizemos a declaração da função, dos seus argumentos e do tipo de retorno da função. A próxima etapa é o cálculo do valor do imposto, com base no parâmetro BaseDeCálculo.

Public Function Calcula_IRPF(BasedeCálculo As Currency) As Currency End Function

Apostila Pág. 93

UTFPR – Damat





Função Personalizada – Exemplo 1Tabela de cálculo do imposto de renda

12. A seguir, temos o código da função Calcula_IRPF.

Apostila Pág. 94

Base de cálculo anual em R$ Alíquota %Parcela a deduzir do

imposto em R$

Até 15.764,28 -

De 15.764,29 até 31.501,44 15 2.364,64

Acima de 31.501,44 27,5 6.302,32

UTFPR – Damat






If BaseDeCálculo <= 15764.29 ThenCalcula_IRPF = 0

ElseIf BaseDeCálculo > 15764,29 and BaseDeCálculo <= 31501.44 Then

Calcula_IRPF = (BaseDeCálculo * 0.15) – 2364.64Else

Calcula_IRPF = (BaseDeCálculo * 0.275) – 6302.32End If

Dentro da função, digite as linhas de comandos a seguir

Apostila Pág. 94

UTFPR – Damat





Função Personalizada – Exemplo 113. No canto esquerdo da barra de ferramentas na

janela do editor do VBA, pressione o botão para retornar ao Excel;

14. A célula C2, digite a seguinte fórmula:

=Calcula_IRPF(B2)

15. Pressione Enter e observe que o Excel usa a função personalizada Calcula_IRPF para determinar o valor do imposto, conforme mostra Figura.

Apostila Pág. 95

UTFPR – Damat





Função Personalizada – Exemplo 116. O tracinho significa zero. Esta é a formatação

padrão aplicada ao formato Contábil, ou seja, ao invés de 0, exibe o tracinho. Utilize o mouse para estender a fórmula para as demais células da coluna C. O imposto será calculado conforme mostra a Figura ;

Apostila Pág. 95

UTFPR – Damat




13 de setembro de 2007 Pág. 31Lição 22 -


Criar e utilizar uma função personalizada para a validação do CPF. Chame esta função de ValidaCPF.

Importante: O algoritmo de cálculo do DV de CPFs e CNPJs é de domínio público, já tendo sido publicado no diário oficial da união e em diversas revistas de informática, de circulação nacional.

Como calcular o DV do CPFPara entender o algoritmo de cálculo do CPF vamos utilizar um exemplo prático.Considere o seguinte CPF (sem o DV): 333.444.555

UTFPR – Damat






Começamos a multiplicar os dígitos do CPF, a partir da posição 9, ou seja, de trás para frente, por 2, 3, 4, 5 e assim por diante, conforme indicado na tabela a seguir:

UTFPR – Damat






Somo os resultados obtidos na quarta linha da tabela anterior:Soma1 = 30+27+24+28+24+20+20+15+10 Soma1 = 198

Faço a divisão desta soma por 11 e determino o resto da divisão:198/11 Resulta em uma divisão exata, com resto 0Regra: •Quando o resto é zero ou um, o DV é 0•Quando o resto é diferente de zero ou um, o DV éobtido fazendo-se: 11-restoNeste caso como o resto foi zero, o primeiro DV é zero:DV1=0

UTFPR – Damat






O DV1 calculado passa a fazer parte do CPF, conforme indicado pela tabela a seguir:

Agora repetimos o processo anterior, porém já considerando o DV1 como parte integrante do CPF, conforme indicado pela tabela a seguir:

UTFPR – Damat





Função Personalizada – Exemplo 2Somo os resultados obtidos na quarta linha da tabela anterior:Soma2 = 33+30+27+32+28+24+25+20+15+0Soma1 = 234Faço a divisão desta soma por 11 e determino o resto da divisão:234/11 resulta em (21), com resto 3Regra: •Quando o resto é zero ou um, o DV é 0.•Quando o resto é diferente de zero ou um, o DV é obtido fazendo-se: 11-restoNeste caso como o resto foi 3, o segundo DV é :DV2 = 11 – 3 DV2 = 8

UTFPR – Damat






Com isso o CPF, já com os dois DVs fica conforme indicado na tabela a seguir:

Ou seja: 333.444.555-08

Os algoritmos para cálculo dos DVs do CNPJ são praticamente iguais. A única diferença é a quantidade de dígitos do CNPJ é diferente do CPF e no CNPJ após chegar a multiplicação por 10, inicia novamente a multiplicação por 2, depois por 3 e assim por diante.

UTFPR – Damat






Nessa lição vamos criar uma função chamada ValidaCPF. Essa função recebe, como parâmetro, um valor de CPF no formato 111.111.111-11 ou o endereço de uma célula quecontém um CPF nesse formato. A função retorna a palavra:• Válido se o CPF for Válido;• Inválido se o CPF for inválido.

Para criar uma função que possa ser utilizada na planilha, devemos criar a função dentro de um Módulo do VBA, conforme descrito no exemplo de criação da função Calcula_IRPF, mostrado anteriormente.

UTFPR – Damat






Exercício: Criar uma função chamada ValidaCPF, no Módulo 1 da planilha Números deCPF. Em seguida utilizar essa função para verificar se os CPFs da planilha são ou não válidos.

1. Abra o Excel;2. Abra a planilha C:\Programação VBA no Excel\

Números de CPF.xls;3. Selecione o comando Ferramentas -> Macro ->

Editor Visual Basic... ou pressione Alt+F11.

UTFPR – Damat






4. Vamos criar a função ValidaCPF de tal maneira que possa ser utilizada em qualquer local da planilha. Para isso vamos criá-la como uma função Pública, dentro de ummódulo do VBA;5. Para inserir um módulo, selecione o comando Inserir -> Módulo. Será criado o Módulo 1. Agora vamos inserir uma função, dentro desse módulo. Clique em Módulo 1, nopainel da esquerda.6. Selecione o comando Inserir -> Procedimento.7. Será aberta a janela Adicionar procedimento. Preencha os dados conforme indicado na Figura a seguir:

UTFPR – Damat






8. Clique em OK;9. Será inserido o código básico para a criação de uma

função pública (que pode ser utilizada em qualquer parte da planilha), conforme indicado a seguir:

Public Function ValidaCPF()

End Function

UTFPR – Damat





Função Personalizada – Exemplo 210. A função ValidaCPF deverá receber um parâmetro: o

número do CPF no formato 111.111.111-11. A definição dos parâmetros que serão recebidos pela função é feito dentro dos parênteses. Fora dos parênteses, definimos o tipo da função, isto é, que tipo de valor a função irá retornar: inteiro, real, texto, data, etc. No nosso exemplo a função retornará um valor do tipo Texto: Válido ou Inválido. Altere o código para definir o parâmetro a ser recebido e o tipo da função, conforme indicado a seguir:Public Function ValidaCPF(CPF As String) As String

End Function

UTFPR – Damat






Observe que “As String” dentro do parênteses, define que o parâmetro CPF é do tipo texto.Já “As String” fora do parênteses, define o tipo da função, isto é, a função irá retornar um valor do tipo Texto.

11. Agora vamos digitar o código de validação do CPF. Esse código é digitado entre os comandos “Public Function ValidaCPF(CPF As String) As String” e “End Function”.

Nota: Cada comando do VBA deve ser digitado em uma única linha. Se você precisa “quebrar” uma linha, deve ser colocado um caractere de sublinhado no final da linha: _

UTFPR – Damat






12. Digite o código da listagem a seguir. As linhas que iniciam com um apóstrofe – ‘ – são comentários e servem para descrever cada parte da função, ou seja, o que faz cada trecho do código da função. Observe que a função implementa exatamente (como não poderia deixar de ser), os passos do algorítmo de validação, descrito anteriormente.

UTFPR – Damat






Apostila Pág. 99

UTFPR – Damat






Apostila Pág. 100

'Em primeiro lugar vamos testar se o CPF não está'vazio e se tem comprimento maior ou igual à 11'Caso as condições retornem verdadeiras, carregamos os dígitos do CPF na variável d()

UTFPR – Damat






Apostila Pág. 100

If Not (IsNull(CPF)) And Len(CPF) >= 11 Then j = 1 For i = 1 To Len(CPF) If Mid(CPF, i, 1) <> "." And Mid(CPF, i, 1) <> "-" Then d(j) = Mid(CPF, i, 1) j = j + 1 End If NextEnd Ifj = j - 1

UTFPR – Damat






Apostila Pág. 100

'Inserimos agora uma condição para verificar'se o número de dígitos do CPF é 11If j = 11 Then

Comandos para verificação do CPFElse

validaCPF = “Inválido”End if

UTFPR – Damat






Apostila Pág. 100

Soma1 = 0'Cálculo do primeiro DVFor i = 1 to 9

Soma1 = Soma1 + d(i) * (11 - i)Next

Resto1 = Soma1 Mod (11)

UTFPR – Damat






Apostila Pág. 100

If Resto1 = 0 Or Resto1 = 1 Then DV1 = 0Else DV1 = 11 - Resto1End If

UTFPR – Damat






Apostila Pág. 100

For i = 1 to 9Soma2 = Soma2 + d(i) * (12 – i)

NextSoma2 = Soma2 + DV1 * 2

Resto2 = Soma2 Mod 11

UTFPR – Damat






Apostila Pág. 100

If Resto2 = 0 Or Resto2 = 1 Then DV2 = 0Else DV2 = 11 - Resto2End If

UTFPR – Damat






Apostila Pág. 100

UTFPR – Damat






13. Clique no botão ( ) para salvar a função ValidaCPF;

14. Feche a janela do Editor do Visual Basic;

15. Você estará de volta à planilha Números de CPF. a Próxima lição aprenderemos a utilizar a função ValidaCPF para verificar se os CPFs da planilha são válidos ou não;

16. Salve e feche a planilha.

UTFPR – Damat






Apostila Pág. 102

UTFPR – Damat






Apostila Pág. 102

UTFPR – Damat





Função Personalizada – Exemplo 3Vamos criar duas novas funções personalizadas:

• a primeira irá contar o número de valores de uma faixa de células que estão dentro de um intervalo;

• a segunda irá fazer a soma dos valores de uma faixa de células que estão dentro de um intervalo;

Apostila Pág. 103

Verificar se dará tempo

UTFPR – Damat





Função Personalizada – Exemplo 3A sintaxe dessas duas funções vai ficar da seguinte forma;

Apostila Pág. 103

UTFPR – Damat






Apostila Pág. 104

UTFPR – Damat






Apostila Pág. 104

UTFPR – Damat






Apostila Pág. 105

UTFPR – Damat






Apostila Pág. 105

UTFPR – Damat






Apostila Pág. 106

UTFPR – Damat






Apostila Pág. 107

UTFPR – Damat






Apostila Pág. 107

UTFPR – Damat






Apostila Pág. 108

Documents

Excel – Macros e Programação em VBA 13 de setembro de 2007Pág. 1 Excel – Macros e Programação em VBA 13 de setembro de 2007Pág. 1 UTFPR – Damat VBA – Módulos,