O que é uma Função

Funções são rotinas que têm como objetivo executar um conjunto de instruções e produzir um valor final.

São definidas por:

• um nome elucidativo da sua função,
• parâmetros - valores enviados entre parêntesis e que constituem o input ( a matéria–prima) da função, i.e., é sobre eles que serão executados os respectivos cálculos. Existem funções que não necessitam de parâmetros – Rand(), Today(), Pi()),
• resultado ou output – valor produzido pela aplicação da função aos parâmetros.

Recorde-se como exemplo a função SUM , esta recebe por parâmetro um conjunto de valores que se pretendem somar, sabe-se que o resultado da aplicação dessa função ao conjunto de valores será o respectivo somatório.

 

Funções do Excel

O Excel dispõe de um conjunto variado de funções (de estatística, de data e hora, de matemática e trigonometria, de finanças, de pesquisa e referência, de bases de dados, de texto, de lógica, e de informação) pré-programadas que se podem utilizar nas células da worksheet para executar os mais diversos cálculos.

Contudo, existem funções que o Excel não contempla e que por vezes os utilizadores gostariam de aplicar para a realização dos seus cálculos usuais. Neste contexto, está previsto o desenvolvimento de funções personalizadas recorrendo a uma codificação das mesmas numa linguagem que o computador possa entender e executar - Visual Basic for Applications (VBA).

 

Como Criar Funções

1. Defina o objectivo da função, os parâmetros e resultado
2. Escreva (em linguagem normal) os passos que essa função deve executar para, com base nos parâmetros produzir o valor final. Nesta etapa está a definir o algoritmo.
3. Traduza o seu algoritmo em VBA. Para escrever uma função em VBA deve obedecer à seguinte sintaxe:

Function <Nome da Função> ( <parametro1>, <parametro2>,…)

…

<Nome da Função> = <Valor ou Expressão>

…

End Function

A definição começa com a palavra-chave Function e termina com as palavras End Function. Para definir o valor produzido pela uma função basta no seu interior, atribuir ao nome da função um determinado valor ou expressão.

 

Aplicações Práticas

Problema: Fazer uma função que calcule o valor com IVA.

• Parâmetros / Input: Valor Ilíquido e Taxa de IVA
• Passos a executar:

a)Multiplicar o Valor Ilíquido pela Taxa de IVA

b)Somar ao Valor Ilíquido o valor calculado no passo anterior. Assim o valor do IVA deve ser atribuído a uma variável, no passo 2.1, para utilização posterior.

• Em VBA:

 

 

 

Problema: Fazer uma função que gere valores aleatórios num intervalo real definido por dois limites

• Parâmetros / Input: Limite Inferior e Limite Superior
• Passos a executar:

 

a)Utilizar a função Rnd para produzir números aleatórios (entre 0 e 1)

b)Somar o Limite Inferior com o resultado da alínea anterior multiplicado pela diferença entre o Limite Superior e o Limite Inferior

• Em VBA:

 

 

 

 

 

 

 

Onde Definir a Função

A função tem de ser definida (escrita) em linguagem VBA num sitio apropriado -Editor de Visual Basic for Applications. Para aceder ao editor escolha a seguinte sequência de opções do menu: Ferramentas / Macro / Editor de Visual Basic

 

 

 

Project Explorer

Se a janela não estiver visível ative-a através do Exibir / Explorer do Objeto ou utilizando o botão ou combinações de letras associadas a esta tarefa.

Nesta janela poderá visualizar a hierarquia dos projetos de Visual Basic for Applications ativos nesse momento.

Entre eles deverá encontrar um cujo nome corresponde ao nome do EstaPasta_de_trabalho do Excel com que está a trabalhar e.g. VBAProject (Pasta1). É dentro deste projeto que deverá trabalhar para que todas as funcionalidades que implemente estejam nele activas sempre que necessário.

Assim se fizer o desdobramento do seu VBAProject, encontrará um folder cuja designação é Microsoft Excel Objects, e um outro designado Modulos (se este não aparecer significa que o seu projecto ainda não possui qualquer função implementada. Para criar este folder deverá fazer: Inserir / Modulo).

Certifique-se, antes de começar a criar a função, que o está a fazer dentro dos modules, caso contrário elas não funcionarão como pretende.

 

 

 

 

Trabalhar no Editor de VBA – Criar uma Função

 

Aplicação Prática

Problema: Criar uma função que calcule o IVA

1. Parâmetros / Input: Valor Ilíquido e Taxa de IVA
2. Passos a executar:

a)Multiplicar o Valor Ilíquido pela Taxa de IVA

b)Para criar a função no Editor de VBA:

 

Uma vez seleccionado o módulo onde pretende construir a função poderá:

• Escrever a função integralmente, ou

• Recorrer ao Inserir / Procedimento para que o VBA lhe crie a estrutura

Nesta caixa de diálogo deverá indicar o tipo de rotina a criar – Function e o nome que pretende dar à função. Automaticamente o Visual Basic for Applications criará a estrutura da rotina, que neste caso ficaria:

Function IVA( )

End Function

 

Depois bastará escrever a expressão que produzirá o valor final, entre estas duas linhas de código, como mostra a figura seguinte

 

 

 

 

Execução de uma Função dentro de uma célula

Uma vez que uma função produz um valor ela poderá ser executada dentro de uma célula numa WorkSheet, à semelhança de qualquer uma outra função do Excel

 

 

Posicione-se na célula onde pretende inserir a função

1. Inserir / Função
2. Selecione a categoria Definida pelo Usuário– repare que aparece listada a função que acabou de criar IVA
3. Clique em OK e de imediato uma janela de ajuda virá auxiliar a utilização da sua função

 

 

1. Introduza os parâmetros e clique em OK

 

 

 

Em suma:

Qualquer função poderá ser utilizada numa WorkSheet do Excel, sendo a sua utilização semelhante a qualquer outra função: numa célula

= <nome da função> (<Param_1> , <Param_2>)

 

 

 

 

 

Utilização de variáveis

As variáveis constituem repositórios temporários de valores, podendo ser utilizadas para diversos fins.Quando se pretende atribuir valores a variáveis dever-se-á indicar o nome da variável, o operador "=" e o valor que se pretende que a variável armazene.

<Nome_Variável> = <Valor>

O nome da variável representa o conteúdo da mesma, i.e., sempre que mencionar o nome da variável é o seu conteúdo que será considerado.

 

Tipos de variáveis

O tipo de variável está associado ao género de informação que esta tem hipótese de armazenar:

Boolean – 2 bytes – Permite armazenar valores Boolenaos – True ou False

Byte – 1 Byte – permite armazenar números sem sinal entre 0 e 255

Currency - 8 bytes – permite armazenar moeda

Date – 8 Bytes – permite armazenar datas

Double – 8 bytes – permite armazenar um real desde –

1.79769313486232E308 até -4.94065645841247E-324 para valores negativos, e desde 1.79769313486232E308 até 4.94065645841247E-324 para valores positivos.

Single – 4 bytes – permite armazenar um real desde -3.402823E38 até

• -1.4011298E-45, para valores negativos e desde 3.402823E38 até 1.4011298E-45, para valores positivos

Integer – 2 bytes - permite armazenar números inteiros entre -32.768 e 32767

Long – 4 bytes – permite armazenar números inteiros entre -2 147 483 648 e

2 147 483 648

Object – 4 bytes – utilizado para fazer referência a um objecto do Excel

String – 1 byte por caractere – permite armazenar conjuntos de caracteres

Variant – 16 bytes - permite armazenar qualquer tipo de dados

User-Defined – permite armazenar valores de tipos diferentes

 

Declaração de Variáveis

As variáveis que utilizar dentro de uma função deverão por norma ser declaradas i.e. definidas, sempre que uma variável for utilizada sem ser definida o VBA considera-a do tipo Variant. A definição é feita da seguinte forma:

Dim Numero As Integer

Onde:

Dim – Palavra chave que indica uma declaração de variáveis (abreviatura de dimensão)

Numero - nome da variável a utilizar

As – palavra chave utilizada para separar o nome da variável do tipo de dados

Integer – tipo de dados atribuído à variável

 

Estruturas de Controlo

O VBA disponibiliza algumas estruturas que pode utilizar para controlar o decurso da execução da rotina. Estas estruturas dão ao programador um poder enorme para construir funções bastante complexas e flexíveis.

 

If-Then-Else

Recorrendo à função IF do Excel, recorde:

=IF( <condição>, <se condição verdadeira>, <se condição falsa> )

A função IF tinha o seguinte comportamento:

1- Avalia a condição, que deverá ser uma expressão booleana colocada como primeiro parâmetro;

2-Se a condição for verdadeira, então (then) realiza as operações colocadas no segundo parâmetro;

3- Caso contrário (else), realiza as operações que formam o terceiro parâmetro

 

A estrutura IF do VBA tem o mesmo tipo de funcionamento, o que difere é a sintaxe.

Assim:

If <Condição> Then

<se condição verdadeira>

[ Else

<se condição falsa> ]

End If

A palavra Else é opcional sendo que no caso de ser omitida, a avaliação negativa da condição implica uma saída automática da Instrução If.

 

Aplicação Prática

Problema: Determinar se um número é par

• Elementos de análise

a)Nome: É_Par

b)Parâmetros: número que vai ser avaliado

 

• Output: True se for par, e False caso contrário

 

• Passos a executar:

a)Dividir o número por dois e apurar o resto da divisão

 

1. Avaliar o resto da divisão:

a)Se for igual a zero, então o número é par e a função assume o valor True

b)Senão, o valor é impar e a função assume o valor False

• Em VBA:

 

 

Interpretação

Dim resto As Double	Declaração da variável resto, onde é guardado o resto da divisão do número por dois
Resto = Numero Mod 2	Mod é uma função que calcula o resto da divisão do Numero por 2 (neste caso). O valor calculado é atribuído à variável resto.
If	Palavra chave que indica o inicio de uma instrução If-Then-Else
Resto = 0	A condição a testar. Serve para determinar a sequência a dar à execução da rotina. Da avaliação desta condição pode-se obter um de dois valores True ou False, sendo que depende deste resultado o caminho a prosseguir. Se for True executará as instruções que seguirem a palavra-chave Then até encontrar a palavra chave Else, não executando mais nada dentro do IF, caso contrário executará o código que se seguir à palavra chave Else até ao End If.
Then	Palavra chave que determina o fim da condição teste. Todas as instruções que têm inicio nesta palavra-chave até à palavra-chave Else serão executadas se a condição for verdadeira.
É_Par = True	Instrução a executar se a condição for verdadeira.
Else	Palavra-chave que determina o terminus de execução das instruções quando o resultado da avaliação for True, e que determina o inicio das instruções a executar se o resultado da condição for False.
É_Par = False	Instrução a executar se a condição for falsa.
End If	Palavra-chave que indica o fim do controlo de If-Then-Else e como tal onde se deve retomar as instruções para prosseguir a execução do procedimento.

 

 

Aplicação Prática

Problema: Crie a função Mod para usar nas células do Excel.

 

• Elementos de análise

a)Nome: Resto_Divisão

b)Parâmetros: Dividendo e Divisor

 

• Output: resto da divisão do Dividendo pelo Divisor

• Passos a executar:

a)Calcular o quociente da divisão (Dividendo / Divisor) na forma inteira

b)Subtrair ao dividendo a multiplicação do quociente pelo Divisor, obtendo assim o resto

• Em VBA:

 

• 

  A instrução adicional ElseIf

  Esta instrução propõe uma condição alternativa se o teste da condição anterior tiver tido um resultado negativo.

  Aplicação Prática

  Problema: Pretende-se criar uma função que classifique etariamente um indivíduo em função da sua idade. A classificação pretendida é a seguinte:

• Elementos de análise

a)Nome: Classe_Etária

b)Parâmetros: Idade

• Output: "Bebé", "Criança", "Adolescente", "Jovem", "Adulto" ou "Idoso"
• Passos a executar:

Avaliar a idade

a)Se tiver menos de três, então é "Bebé"

b)Senão, se tiver menos de treze, então é "Criança"

c)Senão, se tiver menos de vinte, então é "Adolescente"

d)Senão, se tiver menos de vinte e seis, então é "Jovem"

e)Senão, se tiver menos de sessenta e seis, então é "Adulto"

f)Senão é "Idoso"

 

• Em VBA:

 

 

Este tipo de codificação pode tornar-se confuso e difícil de controlar, como tal o VBA proporciona uma estrutura mais simples para este tipo de situações: Select Case.

Select Case

Permite a escolha de um percurso mediante a avaliação de n condições. É de extrema utilidade para evitar os If’s encadeados, dando um maior grau de legibilidade e simplicidade ao código construído.

Select Case <Expressão a ser avaliada>

[Case <Valor da Expressão> [Instruções a realizar]]

...[Case Else [Instruções a realizar na situação residual]]

End Select

 

 

Aplicação Prática

Problema: Alterar a estrutura da função Classe_Etária mediante a aplicação do Select Case.

 

Construção da Estrutura

Select Case	Palavras-Chave que indicam o inicio de um controlo Select Case
Idade	Expressão sujeita a teste, i.e., variável cujo conteúdo está a ser avaliado. Esta variável vai ser comparada sucessivamente pelos valores alternativos apresentados nas instruções Case <Valor>, se encontrar o valor nalguma dessas opções Case executará as linhas de código que aí terão inicio até à opção de Case seguinte. Caso o valor da variável a ser comparada não corresponda a nenhum valor apresentado nas opções Case, existe uma opção Case especial - Case Select - para os restantes valores, neste caso serão executadas todas as instruções que se localizem entre o Case Else e o Case Select.
Case Is<3 ou Case Is<13 ou Case Is<20 ou Case Is<26 ou Case Is<66	Expressões Case. Se o valor da variável for igual a qualquer um dos valores apresentados em cada uma destas expressões, o fluxo de execução terá continuidade na linha abaixo da expressão case que faz o matching, até que uma nova expressão case seja encontrada. Sendo que nessa altura termina o controlo Select Case dando continuidade ao programa nas instruções que se seguirem ao End Select.
Case Else	Será a instrução Case residual, seleccionada somente se nenhuma das outras o tiver sido. Neste caso serão realizadas todas as instruções de código que se lhe seguirem até à expressão End Select. Findo o qual seguirá todas as instruções após o controlo Select case.
End Select	Palavra-Chave que indica o fim do controlo Select Case.

 

 

For – Next

Permite a execução repetida de uma tarefa durante um determinado número de vezes.

For <Inicialização do Contador> To <Valor > [ Step <Valor a Incrementar>]

<Instruções a realizar em cada iteração>

Next

 

Aplicação Prática

Problema: Pretende-se criar uma função que recebendo a base e a potência calcule o valor respectivo.

 

• Elementos de análise

a)Nome: Calc_Potência

b)Parâmetros: Base, Potência

c)Output: Base elevada à potência

• Passos a executar:

Multiplicar sucessivas vezes (número indicado pela potência) a base pelo resultado acumulado das sucessivas operações.

1. Em VBA:

 

 

Função das Variáveis:

Variável	Função
i	Conta o número de vezes que a base já foi multiplicada, é uma variável que será automaticamente incrementada em cada looping do ciclo.
acumulado	Variável que armazena o resultado sucessivo por cada vez que se multiplica. Inicializado a 1 (elemento neutro da multiplicação) antes do ciclo.

 

 

Construção do Ciclo:

For	Palavra-chave que indica o inicio do ciclo For-Next
i = 1 To Potência	Expressão que contém a inicialização do contador, indicando o valor de inicio e de fim. Assim, o número de vezes que o ciclo é executado será: Valor_Fim – Valor_Inicio +1. A palavra To é utilizada para separar o valor do contador no momento inicial do valor no momento final. (Tradução: O contador iniciar-se-á a um e atingirá o valor máximo traduzido pela variável Potência)
Step 1	Palavra chave utilizada para especificar o valor a incrementar ou decrementar ao contador do ciclo por cada vez que o loop é concretizado. Normalmente o valor a incrementar é um, contudo qualquer outro valor pode ser utilizado, desde números decimais, a números negativos (Provocando assim a decrementação). A palavra-chave Step é opcional, sempre que for omitida é assumido 1 como o valor a incrementar por defeito.
acumulado = acumulado * Base	Instrução a realizar de cada vez que o ciclo for executado. Neste caso a instrução é única, contudo poder-se-ão adicionar outras instruções.
Next	Palavra Chave que indica o fim de um ciclo For-Next . Sempre que a execução do ciclo chega à instrução Next incrementa a variável contador e volta ao inicio do ciclo.

 

 

Tradução Integral:

For i = 1 To Potência Step 1

acumulado =acumulado * Base

Next

Para o número de vezes, a iniciar em 1 até que atinja, o valor Potência, pela incrementação de 1 na execução de cada ciclo, deverá multiplicar sucessivamente o resultado acumulado, pela base.

 

Funcionamento do Ciclo:

A primeira execução do ciclo distingue-se das restantes por a ela estar associada a inicialização do contador, sendo o restante procedimento semelhante a qualquer outra execução.

No inicio de cada execução do ciclo, a variável contador é comparada com o valor final de execução. Se o Step for um valor positivo (incrementar) e o valor do contador for superior ao valor final significa que o ciclo já foi realizado o número de vezes pretendido, e então o código acabará a execução da instrução For-Next e seguirá na linha de código que esteja situada imediatamente a seguir, caso contrário executa uma vez mais o ciclo e incrementa a variável contador. Por outro lado, se o Step contiver valor negativo (decrementar) e o valor do contador for inferior ao valor final significa que o ciclo já foi realizado o número de vezes pretendido, e então o código acabará a execução da instrução For-Next e seguirá na linha de código que esteja situada imediatamente a seguir, caso contrário executa uma vez mais o ciclo e decrementa a variável contador.

 

Perigos associados à utilização do ciclo For-Next:

•  
• Não definir o limite de execução (não atribuir valor à variável que o define)
•  
• Definir erroneamente o Step, por forma a que nunca seja obtido o valor que determina o fim da execução

•  
• estas condições implicarão que o ciclo não tenha fim – Ciclos Infinitos

 

 

Aplicação Prática

Problema: Pretende-se criar uma função para calcular um factorial.

 

• Elementos de análise

a)Nome: Factorial

b)Parâmetros: Número

c)Output: Factorial do Número introduzido

• Passos a executar:

a)Verificar se o número é positivo

Se for, multiplicar sucessivas vezes um número pelo resultado acumulado das sucessivas multiplicações dos números inteiros que o antecedem

Senão indicar "ERRO"

• Em VBA:

 

 

While-Wend

A estrutura While-Wend tem um funcionamento similar ao For-Next. Realiza um looping um determinado número de vezes, enquanto que uma determinada condição permaneça verdadeira. A diferença entre este ciclo e o ciclo For-Next é basicamente o facto deste saber apriori o número de vezes que vai ser realizado, enquanto que um ciclo While-Wend poderá ou não sabê-lo pois terminará quando a condição for Falsa.

While <Condição>

<Instruções a realizar em cada iteração>

Wend

 

Aplicações Práticas

Problema: Modificar a estrutura da função Factorial, criada com o ciclo For-Next

(Neste caso o ciclo sabe apriori o número de vezes que irá ser realizado)

 

• Elementos de análise

a)Nome: Factor

b)Parâmetros: Número

c)Output: Factorial do Número introduzido

• Passos a executar:

a)Verificar se o número é positivo

Se for, multiplicar sucessivas vezes um número pelo resultado acumulado das sucessivas multiplicações dos números inteiros que o antecedem

Senão indicar "ERRO"

Em VBA:

 

Problema: Apostar um número entre 1 e 10 e saber ao fim de quantos lançamentos (gerações aleatórias) é que foi seleccionado.

(Neste caso o ciclo não sabe o número de vezes que irá ser realizado)

 

• Elementos de análise

a)Nome: Aposta_1a10

b)Parâmetros: Número

c)Output: Número de lançamentos

• Passos a executar:

a)Verificar se o número está no intervalo definido

Se estiver, gerar continuamente números aleatórios entre 1 e 10 e comparar com o Número da aposta, quando for igual pára e indica o número de lançamentos

Senão estiver no intervalo indicar "ERRO DE INSERÇÃO"

Em VBA:

 

 

A instrução While-Wend tem como função gerar números aleatórios entre 1 e 10 por forma a encontrar o número da aposta, e saber qual o número de lançamentos necessários para que aquele valor fosse obtido.

 

Tradução Integral

While Número <> Lançamento

Lançamento = Int(1 + Rnd() * (10 - 1 + 1))

i = i + 1

Wend

Enquanto o número do lançamento não for igual ao número da aposta, o sorteio continua, o que implica sortear um número e contabilizar o número de sorteios realizados.

 

Se utilizasse a função Gera_Entre para a geração de números aleatórios, a função seria:

 

 

Funcionamento do Ciclo

Existe uma fase de inicialização das variáveis envolvidas na condição – teste para garantir o correcto funcionamento do ciclo.

Avalia a condição teste e se for verdadeira executa todas as instruções até à palavra-chave Wend voltando de novo à avaliação da condição, se for falsa prossegue a execução da rotina nas instruções que se localizam depois da palavra-chave Wend.

 

Perigos associados à utilização do ciclo While-Wend

• Má ou inexistência de inicialização das variáveis envolvidas na condição-teste.

• Garantir que as variáveis envolvidas na condição teste poderão ter valores diferentes por cada vez que o ciclo seja executado.

• Garantir que em algum momento a condição teste é falsa e o ciclo termina a sua execução.

• A não verificação destas condições implicará que o ciclo não tenha fim – Ciclos Infinitos

 

Do – Loop

Esta estrutura é similar à estrutura do While-Wend. Contudo fornece duas possibilidades que estão limitadas àquela estrutura:

• Do Loop permite posicionar a condição teste no inicio ou fim do loop, a condição no fim do Loop evita uma inicialização prévia do valor das variáveis envolvidas na condição teste, dado que essa inicialização pode ser feita no decurso do ciclo com valores reais.

• Do Loop permite ainda especificar se o loop se vai realizar enquanto (while) uma expressão for verdadeira ou até que (until) a condição ser verdadeira (facilidade conseguida através do operador Not)

 

Poderá ser:

Do [{While | Until} <condição>]

<Instruções a realizar por iteração>

Loop

Ou então:

Do

<Instruções a realizar por iteração>

Loop[{While | Until} <condição>]

 

Aplicações Práticas

 

Problema: Apostar um número num intervalo definido e saber ao fim de quantos lançamentos (gerações aleatórias dentro do intervalo considerado) é que foi seleccionado.

 

• Elementos de análise

a)Nome: Aposta

b)Parâmetros: Número, Limite Inferior e Limite Superior

c)Output: Número de lançamentos necessário realizar para que o número apostado fosse seleccionado no intervalo de números definido pelo Limite Inferior e Superior

• Passos a executar:

a)Verificar se o número está no intervalo definido (Limite Inferior a Limite Superior) e se o intervalo está bem definido (Limite Inferior < Limite Superior)

b)Se estiver, gerar continuamente números aleatórios nesse intervalo e comparar com o Número da aposta, quando for igual pára e indica o número de lançamentos

Senão estiver no intervalo indicar "ERRO DE INSERÇÃO"

Em VBA:

 

• Utilizando a condição teste no inicio do Loop e com a palavra While

 

• Utilizando a condição teste no fim do Loop e com a palavra While

 

 

• Utilizando a condição teste no inicio do Loop e com a palavra Until

 

 

• Utilizando a condição teste no fim do Loop e com a palavra Until

 

 

FIQUE TRANQÜILO, ESTE SITE É AUDITADO POR:

EM APENAS 06 ANOS DE PUBLICAÇÃO ESTA HOMEPAGE JÁ ALCANÇOU A MARCA DE MAIS DE 1.500.000 PAGEVIEW!

TUDO SOBRE EXCEL - Com este site seu Excel nunca mais será o mesmo!

Nº PageView Do Portal:

Nº PageView Desta Página:

Usuários Online:

© 2009 tudosobrexcel.com. Todos os direitos reservados.