Criar funções personalizadas no Excel

Apesar de o Excel incluir uma grande diversidade de funções de folha de cálculo incorporadas, é provável que não tenha uma função para cada tipo de cálculo que efetua. Os designers do Excel não conseguiram antecipar as necessidades de cálculo de todos os utilizadores. Em vez disso, o Excel fornece a capacidade de criar funções personalizadas, que são explicadas neste artigo.

Criar uma função personalizada simples

As funções personalizadas, como as macros, utilizam a linguagem de programação Visual Basic for Applications (VBA). Eles diferem das macros de duas maneiras significativas. Primeiro, eles usam procedimentos Function em vez de Sub procedimentos. Ou seja, começam com uma instrução Function em vez de uma instrução Sub e terminam com End Function em vez de End Sub. Em segundo lugar, efetuam cálculos em vez de ações. Determinados tipos de instruções, tais como instruções que selecionam e formatam intervalos, são excluídos das funções personalizadas. Neste artigo, irá aprender a criar e a utilizar funções personalizadas. Para criar funções e macros, trabalha com o Visual Basic Editor (VBE), que é aberto numa nova janela separada do Excel.

Suponha que sua empresa ofereça um desconto de 10% na quantidade na venda de um produto, desde que o pedido seja de mais de 100 unidades. Nos parágrafos seguintes, iremos demonstrar uma função para calcular este desconto.

O exemplo abaixo mostra um formulário de pedido que lista cada item, quantidade, preço, desconto (se houver) e o preço estendido resultante.

Para criar uma função de DESCONTO personalizada neste livro, siga estes passos:

1. Prima Alt+F11 para abrir o Visual Basic Editor (no Mac, prima Fn+ALT+F11) e, em seguida, clique em Inserir>Módulo. Uma nova janela de módulo aparece no lado direito do Visual Basic Editor.

2. Copie e cole o seguinte código no novo módulo.

   Function DISCOUNT(quantity, price)
    If quantity >=100 Then
    DISCOUNT = quantity * price * 0.1
    Else
    DISCOUNT = 0
    End If
   
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   
   

Utilizar funções personalizadas

Agora está pronto para utilizar a nova função DESCONTO. Feche o Visual Basic Editor, selecione a célula G7 e escreva o seguinte:

=DESCONTO(D7;E7)

O Excel calcula o desconto de 10% em 200 unidades em 47,50 $ por unidade e devolve 950,00 $.

Na primeira linha do seu código VBA, Função DESCONTO(quantidade; preço), indicou que a função DESCONTO requer dois argumentos: quantidade e preço. Quando você chama a função em uma célula da planilha, você deve incluir esses dois argumentos. Na fórmula =DESCONTO(D7,E7), D7 é o argumento da quantidade e E7 é o argumento preço . Agora pode copiar a fórmula de DESCONTO para G8:G13 para obter os resultados apresentados abaixo.

Vamos considerar como o Excel interpreta este procedimento funcional. Quando pressiona Enter, o Excel procura o nome DESCONTO no livro atual e descobre que é uma função personalizada num módulo VBA. Os nomes dos argumentos entre parênteses, quantidade e preço, são marcadores de posição para os valores nos quais se baseia o cálculo do desconto.

A instrução If no bloco de código a seguir examina o argumento de quantidade e determina se o número de itens vendidos é maior ou igual a 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Se o número de itens vendidos for maior ou igual a 100, o VBA executa a seguinte instrução, que multiplica o valor da quantidade pelo valor do preço e, em seguida, multiplica o resultado por 0,1:

Discount = quantity * price * 0.1

O resultado é armazenado como a variável Desconto. Uma instrução VBA que armazena um valor numa variável é denominada instrução de atribuição , porque avalia a expressão no lado direito do sinal de igual e atribui o resultado ao nome da variável à esquerda. Como a variável Desconto tem o mesmo nome que o procedimento da função, o valor armazenado na variável é retornado para a fórmula da planilha chamada função DESCONTO.

Se a quantidade for inferior a 100, o VBA executará a seguinte instrução:

Discount = 0

Finalmente, a instrução seguinte arredonda o valor atribuído à variável Desconto para duas casas decimais:

Discount = Application.Round(Discount, 2)

O VBA não tem a função ARRED, mas o Excel tem. Portanto, para usar ROUND nesta instrução, você diz ao VBA para procurar o método Round (função) no objeto Application (Excel). Para tal, adicione a palavra Aplicação antes da palavra Ronda. Utilize esta sintaxe sempre que precisar de aceder a uma função do Excel a partir de um módulo VBA.

Noções sobre regras de função personalizadas

As funções personalizadas têm de começar com uma instrução Function e terminar com uma instrução End Function. Além do nome da função, a instrução Function normalmente especifica um ou mais argumentos. No entanto, pode criar uma função sem argumentos. O Excel inclui várias funções incorporadas (por exemplo, ALEATÓRIO e AGORA) que não utilizam argumentos.

A seguir à instrução Function, um procedimento de função inclui uma ou mais instruções VBA que tomam decisões e efetuam cálculos com os argumentos transmitidos à função. Finalmente, em algum lugar no procedimento da função, você deve incluir uma instrução que atribua um valor a uma variável com o mesmo nome da função. Este valor é devolvido à fórmula que chama a função.

Utilizar palavras-chave VBA em funções personalizadas

O número de palavras-chave VBA que pode utilizar em funções personalizadas é menor do que o número que pode utilizar em macros. As funções personalizadas não podem realizar outras ações para além de devolver um valor a uma fórmula numa folha de cálculo ou a uma expressão utilizada noutra função ou macro do VBA. Por exemplo, as funções personalizadas não podem redimensionar janelas, editar uma fórmula numa célula ou alterar o tipo de letra, a cor ou as opções de padrão do texto numa célula. Se incluir um código de "ação" deste tipo num procedimento de função, a função devolve o #VALUE! .

A única ação que um procedimento de função pode fazer (além de executar cálculos) é exibir uma caixa de diálogo. Você pode usar uma instrução InputBox em uma função personalizada como um meio de obter entrada do usuário que executa a função. Você pode usar uma instrução MsgBox como um meio de transmitir informações para o usuário. Também pode utilizar caixas de diálogo personalizadas ou Formulários de Utilizador, mas este é um assunto que ultrapassa o âmbito desta introdução.

Documentar macros e funções personalizadas

Mesmo macros simples e funções personalizadas podem ser difíceis de ler. Pode facilitar a compreensão dos mesmos escrevendo texto explicativo sob a forma de comentários. Para adicionar comentários, preceda o texto explicativo de um apóstrofo. O exemplo seguinte mostra a função DESCONTO com comentários. Adicionar comentários como estes torna mais fácil para si ou para outras pessoas manter o seu código VBA com o passar do tempo. Se precisar de fazer uma alteração ao código no futuro, será mais fácil compreender o que fez originalmente.

Um apóstrofo indica ao Excel para ignorar tudo à direita na mesma linha, para que possa criar comentários nas linhas isoladamente ou no lado direito das linhas que contêm código VBA. Você pode começar um bloco relativamente longo de código com um comentário que explica sua finalidade geral e, em seguida, usar comentários embutidos para documentar instruções individuais.

Outra forma de documentar as suas macros e funções personalizadas é atribuir-lhes nomes descritivos. Por exemplo, em vez de atribuir o nome Etiquetas a uma macro, pode atribuir-lhe o nome EtiquetasDoMês para descrever mais especificamente o objetivo que a macro serve. A utilização de nomes descritivos para macros e funções personalizadas é especialmente útil quando tiver criado vários procedimentos, especialmente se criar procedimentos com objetivos semelhantes, mas não idênticos.

A forma como documenta as suas macros e funções personalizadas é uma questão de preferência pessoal. O importante é adotar algum método de documentação e usá-lo de forma consistente.

Disponibilizar as suas funções personalizadas em qualquer lugar

Para utilizar uma função personalizada, o livro que contém o módulo no qual criou a função tem de estar aberto. Se esse livro não estiver aberto, obtém uma #NAME? quando tenta utilizar a função. Se referenciar a função num livro diferente, tem de preceder o nome da função com o nome do livro em que a função reside. Por exemplo, se criar uma função chamada DESCONTO num livro denominado Pessoal.xlsb e chamar essa função a partir de outro livro, tem de escrever =personal.xlsb!desconto(), e não simplesmente =desconto().

Pode poupar alguns batimentos de teclas (e possíveis erros de escrita) selecionando as suas funções personalizadas na caixa de diálogo Inserir Função. As funções personalizadas aparecem na categoria Definida pelo Utilizador:

Uma forma mais fácil de disponibilizar sempre as suas funções personalizadas é armazená-las num livro separado e, em seguida, guardar esse livro como um suplemento. Em seguida, pode disponibilizar o suplemento sempre que executar o Excel. Eis como o fazê-lo:

Depois de seguir estes passos, as suas funções personalizadas estarão disponíveis sempre que executar o Excel. Se quiser adicionar funções à sua biblioteca de funções, regresse ao Visual Basic Editor. Se procurar no Explorador de Projeto do Editor do Visual Basic sob um cabeçalho VBAProject, verá um módulo com o nome do seu ficheiro de suplemento. O seu suplemento terá a extensão .xlam.

Clicar duas vezes nesse módulo no Project Explorer faz com que o Visual Basic Editor exiba seu código de função. Para adicionar uma nova função, posicione o ponto de inserção após a instrução End Function que encerra a última função na janela Código e comece a digitar. Pode criar quantas funções precisar desta forma e estas estarão sempre disponíveis na categoria Definida pelo Utilizador na caixa de diálogo Inserir Função .

Sobre os autores

Este conteúdo foi originalmente escrito por Mark Dodge e Craig Stinson como parte de seu livro Microsoft Office Excel 2007 Inside Out. Desde então, foi atualizado para que se aplique também a versões mais recentes do Excel.

Precisa de mais ajuda?

Pode sempre colocar uma pergunta a um especialista da Comunidade Tecnológica do Excel ou obter suporte nas Comunidades.