Criando funções personalizadas no Excel

Saiba como obter mais de sua planilha eletrônica com recursos de programação.

Por Ladislau Freitas em 27/06/2012


 

O Microsoft Excel, assim como diversos outros programas de planilha eletrônica, está repleto de funções prontas para uso. Tais funções podem ser acessadas facilmente pressionando-se [SHIFT]+F3 em qualquer célula da planilha. São centenas, divididas em várias categorias: científicas, contábeis, matemáticas, engenharia, etc..

A cada nova versão do programa, mais e mais recursos são adicionados visando automatizar tarefas e facilitar a vida do usuário. No entanto, pode ser que você, usuário do Excel, se depare com situações em que o programa talvez não tenha a solução que você necessita pronta para uso ou que talvez você imagine uma solução diferente ou mais prática para o problema.

Nessas horas, o melhor a fazer é arregaçar as mangas e resolver tudo no velho e bom estilo "Do it yourself!" , ou "faça você mesmo!"

Desde a versão 5, o Excel disponibiliza um recurso de programação interessante: o VBA (Visual Basic for Applications). O VBA é uma versão da linguagem de programação Visual Basic específica para os aplicativos da família Office. Com o VBA, é possível automatizar tarefas e definir funções personalizadas conhecidas como UDF (user defined functions). Funções personalizadas podem ser usadas como qualquer outra função do Excel. Após a criação, aparecem listadas no assistente de funções do Excel juntamente com todas as outras, porém numa categoria específica denominada "Definida pelo usuário".

Mãos à massa!

Para trabalhar com o VBA, o Excel dispõe de um IDE (Integrated Developement Enviroment, ou Ambiente de Desenvolvimento Integrado) semelhante ao encontrado no ambiente de desenvolvimento do Microsoft Visual Basic. Quem já está acostumado com este tipo de ambiente de programação e, principalmente, com a linguagem, se sentirá em casa.

O Visual Basic é relativamente fácil. Sua estrutura e modo de escrita é muito semelhante ao Inglês básico, não requerendo identificadores como ";" (ponto e vírgula) ou "{ }" (chaves) para delimitar ou finalizar instruções, como ocorre em linguagens como "C" e "Java".

Também não faz distinção de comandos escritos com letra minúscula ou maiúscula como ocorre em outras linguagens "mais burocráticas" ou de baixo nível.

O Visual Basic é uma linguagem de alto nível, orientada a eventos e de fácil compreensão. Um pouco de noção de lógica de programação e entendimento sobre escopo de variáveis e funções são os requisitos mínimos necessários para poder trabalhar com ela. Requisitos estes que com certeza não faltam a um operador experiente de Excel.

Para acessar o IDE do VBA, em versões do Office anteriores a 2007, basta ir ao menu "Ferramentas" e, em seguida, "Macro / Editor do Visual Basic".

Acesso ao Editor do Visual Basic no Excel 2003

 

Para quem migrou o pacote Office para a Versão 2007, por padrão o acesso ao IDE vem desativado. Para ativá-lo, abra o Excel e clique no menu do Office (botão redondo no canto superior esquerdo).

 

Ativando acesso ao Editor do Visual Basic no Excel 2007 (1/2)

Em seguida, clique no botão "Opções do Excel". Na caixa de diálogo que se abrirá, ative a opção descrita como "Mostrar guia Desenvolvedor na Faixa de Opções".

Ativando acesso ao Editor do Visual Basic no Excel 2007 (2/2)

Feito isso, você notará um novo item de menu ao lado de "Exibição" no seu Excel 2007. Clicando nele você será levado as opções de desenvolvimento e uma delas é o botão "Visual Basic".

Agora, se você estiver utilizando o Excel 2010, clique na aba "Arquivo" e, em seguida, "Opções". Na caixa de diálogo que irá abrir, marque o item "Desenvolvedor" na listagem à direita. Desta forma, uma nova aba será adicionada ao Excel, na qual é possível acessar o ambiente de desenvolvimento do Visual Basic.

No Excel 2010, o acesso ao ambiente de desenvolvimento é ligeiramente diferente da versão 2007.

 

VBA no Excel: Noções Básicas

Para programar em VBA no Excel, a primeira coisa a se fazer é criar um módulo. Este módulo é o local onde seu código fonte ficará armazenado. Para criar um módulo clique no menu "Inserir" do IDE do VBA e, em seguida, "Módulo".

Após fazer isso, você verá que, no painel esquerdo do IDE, um novo item denominado "Modulo1" foi criado. Você poderá renomeá-lo se desejar. Clique duas vezes sobre ele para que, no painel direito, o módulo seja aberto para edição.

A criação de funções dentro de módulos é uma maneira de organizar seu código fonte, principalmente se você criar muitas funções. Por exemplo, você pode criar um módulo só para funções financeiras, outro para funções estatísticas, etc. Cada módulo pode armazenar várias funções.

Ambiente de desenvolvimento do Visual Basic for Applications (VBA)

Há também, os Módulos de Classe. Tais módulos são usados para implementação de técnicas básicas de programação Orientada a Objetos. Mas isso é um assunto para outra matéria. Vamos nos ater a programação padrão.

No Excel, é possível escrever macros na forma de procedimentos (Sub) ou funções (Function).

A diferença básica entre um é outro é que os procedimentos não retornam valores e são mais indicados para realizar tarefas automáticas. Já as funções podem retornar um valor e são mais indicados para realizar tarefas onde seja necessário obter um dado após um processamento de dados, ou seja, para a criação de fórmulas personalizadas.

Além disso, macros escritas na forma de procedimento não podem ser executadas automaticamente dentro das células como uma fórmula normal. Elas devem ser executadas a partir de uma ação do usuário ou através do menu "Ferramentas / Macro / Macros...".

No entanto, um procedimento pode ser chamado de dentro de uma função e vice-versa, como acontece em qualquer outro ambiente de programação.

 

Procedimento VS. Função
No exemplo à seguir, criaremos um procedimento que escreve a frase "Isso é um teste" na célula "A1" da planilha ativa. Em seguida, você poderá comparar o código fonte com o de uma função que faz exatamente a mesma coisa.

Dentro do módulo, escreva:

Sub Teste1()

ActiveSheet.Range(A1).Value = "Isso é um teste"

End Sub

No exemplo acima, criamos um procedimento denominado "Teste1". A palavra chave "ActiveSheet" refere-se a um objeto do Excel, cuja propriedade Range (que identifica uma faixa ou intervalo de células) tem seu valor (Value) alterado para "Isso é um teste".

Para executar este procedimento, em versões do Excel anteriores a 2007, volte à sua planilha do Excel (não é necessário salvar o procedimento) e vá ao menu "Ferramentas /Macro / Macros...". Para quem tem o Excel 2007, basta ir ao menu "Desenvolvedor' e clicar no botão "Macros".

Ao executar o procedimento, a célula "A1" da planilha ativa receberá o valor definido na macro.

Compare agora, com a versão "Função" da macro. No módulo, escreva:

Function Teste2()

Teste2 = "Isso é um teste"

End Function

Agora, volte a sua planilha do Excel. Pressione [SHIFT]+F3 e, na caixa de rolagem de categoria de funções, escolha "Definidas pelo usuário". Você verá o nome de sua função lá. 

Pressionando [SHIFT]+F3

Alternativamente, você pode simplesmente digitar em qualquer célula:

=teste2()

 

No nosso exemplo de função, escrevemos que o nome dela, "Teste2", é igual à "Isso é um teste". Igualar o nome da função à alguma coisa é a forma como o Visual Basic tem de fazer com que a função retorne algo. Se você fizer isso com uma Sub, um erro em tempo de execução será gerado, porque procedimentos não podem retornar valor.

A princípio, o procedimento parece ser algo burocrático e que a função é mais prática. Mas isso não é verdade. Cada modo é melhor para determinado tipo de programação, e isso você perceberá com o tempo. O problema do nosso exemplo é que quisemos demonstrar a diferença básica entre uma Sub e uma Function de uma forma muito simplória.

Como procedimentos não retornam valor, para que o resultado desejado pudesse ser escrito, tivemos que recorrer a um determinado objeto do Excel e alterar seu valor. Já a função faz isso "por definição".

Para ter uma lista precisa de todos os objetos e suas respectivas propriedades, basta abrir o IDE do VBA e ir para a ajuda, ou simplesmente pressionar F1 sobre uma palavra chave qualquer dentro do módulo.

A não ser que você queira criar um "verdadeiro aplicativo" dentro do Excel, acessar objetos não vai ser algo muito corriqueiro e a palavra chave "Function" será a que você mais irá usar.

 

Aquecendo os motores

Agora que você já sabe a diferença básica entre um procedimento e uma função, vamos entrar em exemplos "mais funcionais".

Vamos criar uma função que, dado um valor de entrada definido pelo usuário, calcule o fatorial de um número inteiro qualquer.

Neste exemplo, além de demonstrar como capturar um dado fornecido pelo usuário, demonstrará a criação de variáveis. Alternativamente, criaremos uma segunda versão da função para demonstrar que funções VBA podem ser escritas de maneira recursiva.

No módulo digite:

' Obs: Linha de comentário sempre inicia com uma aspa.
' Procure sempre documentar seu código fonte
' Função Fatorial, versão 1

Function Fatorial(numero As Integer)

Dim Resultado as Long

If numero <= 1 Then

Fatorial = 1

Else

Resultado = numero

Do

Resultado = Resultado * numero - 1

numero = numero - 1

Loop While numero >= 1

Fatorial = Resultado

End If

End Function

Entendendo o algoritmo do exemplo
Na matemática, o fatorial de um número n (n pertence ao conjunto dos números naturais) é sempre o produto de todos os seus antecessores, incluindo si próprio e excluindo o zero. O Fatorial é muito usado como base de algoritmos de encriptação e análise combinatória.

No exemplo anterior, a função Fatorial recebe através da variável numero (sem acento, pois o VBA não aceita nomes de variáveis com acento) um número inteiro (integer em VB) dado pelo usuário.

Em seguida, criamos uma variável denominada Resultado, que tem como função acumular o resultado das sucessivas multiplicações necessárias para se obter o resultado final, uma espécie de acumulador. Para fazer isso, usamos a palavra chave Dim (instrução que cria a variável), seguida do nome da variável (Resultado) e das palavras chave As (que em inglês significa como) e Long (que indica um tipo de dado inteiro longo).

Quando o número dado pelo usuário for menor ou igual à 1, nossa função retornará o resultado 1. Caso contrário, entrará no loop delimitado pelas palavras chave Do... Loop While (ou seja, Faça...Loop Enquanto).

O resultado que foi acumulado na variável resultado é atribuído ao nome da função, que retornará a resposta ao usuário da planilha eletrônica.

Recursividade
O algoritmo que escrevemos anteriormente é capaz de retornar com correção o Fatorial de um número inteiro. Porém, há como melhorá-lo para deixá-lo menor e mais conciso. Para isso, apelaremos para a recursividade. Veja:

Function Fatorial (numero as Integer)

If numero <= 1 Then

Fatorial = 1

Else

Fatorial = numero * Fatorial(numero - 1)

End If

End Function

Nesta nova versão da nossa função Fatorial, não foi preciso criar uma variável acumuladora, nem mesmo criar um loop para efetuar as sucessivas multiplicações.

Mas então, como o computador "sabe" o resultado?

Bem... Nem sempre um algoritmo pode ser escrito dessa forma. Um objeto é dito recursivo se ele consistir parcialmente ou for definido em termos de si próprio. É como dizer que a solução do problema está dentro do próprio problema.

No entanto, um algoritmo só poderá ser escrito desta forma se houver uma condição de parada, senão corre-se o risco de ele rodar indefinidamente. É como colocar um espelho frente ao outro. Eles se refletirão indefinidamente até o infinito.

No nosso exemplo, a condição de parada é o número de entrada ser menor ou igual à 1. Nesta condição, a função não chamará a si mesma para calcular o fatorial.

Algoritmos recursivos são possíveis porque o computador armazena todas as chamadas à função em uma pilha na memória. Se não houver uma condição de parada, haverá um erro por estouro de pilha.

Fim de papo

Com os conhecimentos adquiridos aqui, você será capaz de criar uma série de outras funções matemáticas simples para personalizar e otimizar sua planilha.

Se você não tem conhecimento de Visual Basic, consulte a ajuda do VBA e procure principalmente pelos termos:

"Tipos de dados"
"Palavras-chave"

Até a próxima!

| URL curta

Comentários ()


© Copyright 1998-2014 Grupo NZN | A empresa Privacidade Termos de uso Trabalhe conosco RSS Contato