Criar funções personalizadas no Excel

Embora o Excel inclua uma infinidade de funções internas de planilha, é provável que ele não tenha uma função para cada tipo de cálculo que você executar. Os designers do Excel não poderiam prever as necessidades de cálculo de todos os usuários. Em vez disso, o Excel fornece a capacidade de criar funções personalizadas, que são explicadas neste artigo.

Criando uma função personalizada simples

Funções personalizadas, como macros, usam 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 procedimentos Sub . Ou seja, eles 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, eles realizam cálculos em vez de realizar ações. Certos tipos de instruções, como instruções que selecionam e formatam intervalos, são excluídos das funções personalizadas. Neste artigo, você aprenderá a criar e usar funções personalizadas. Para criar funções e macros, trabalhe com o VBE (Editor do Visual Basic), que é aberto em uma nova janela separada do Excel.

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

O exemplo a seguir 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 DESCONTO personalizada nesta pasta de trabalho, siga estas etapas:

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

2. Copie e cole o código a seguir 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
   
   

Usando funções personalizadas

Agora você está pronto para usar a nova função DESCONTO. Feche o Editor do Visual Basic, selecione a célula G7 e digite o seguinte:

=DESCONTO(D7,E7)

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

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

Vamos considerar como o Excel interpreta esse procedimento de função. Quando você pressiona Enter, o Excel procura o nome DESCONTO na pasta de trabalho atual e descobre que ele é uma função personalizada em um módulo VBA. Os nomes dos argumentos entre parênteses, quantidade e preço, são espaços reservados para os valores nos quais o cálculo do desconto se baseia.

A instrução If no seguinte bloco de código examina o argumento da 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 executará 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 Discount. Uma instrução VBA que armazena um valor em uma variável é chamada de 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 de função, o valor armazenado na variável é retornado à fórmula de planilha que chamou a função DESCONTO.

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

Discount = 0

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

Discount = Application.Round(Discount, 2)

O VBA não tem função ROUND, mas o Excel sim. Portanto, para usar ROUND nesta instrução, você diz ao VBA para procurar o método Round (função) no objeto Application (Excel). Você faz isso adicionando a palavra Aplicativo antes da palavra Rodada. Use essa sintaxe sempre que precisar acessar uma função do Excel a partir de um módulo do VBA.

Noções básicas sobre regras de função personalizadas

Uma função personalizada deve 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 geralmente especifica um ou mais argumentos. No entanto, você pode criar uma função sem argumentos. O Excel inclui várias funções internas — ALEATÓRIO e AGORA, por exemplo — que não usam argumentos.

Após a instrução Function, um procedimento de função inclui uma ou mais instruções VBA que tomam decisões e realizam cálculos usando os argumentos passados para a função. Por fim, em algum lugar do 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. Esse valor é retornado à fórmula que chama a função.

Usando palavras-chave VBA em funções personalizadas

O número de palavras-chave do VBA que você pode usar em funções personalizadas é menor do que o número que você pode usar em macros. As funções personalizadas não podem fazer nada além de retornar um valor a uma fórmula em uma planilha ou a uma expressão usada em outra macro ou função VBA. Por exemplo, as funções personalizadas não podem redimensionar janelas, editar uma fórmula em uma célula ou alterar as opções de fonte, cor ou padrão do texto em uma célula. Se você incluir um código de "ação" desse tipo em um procedimento de função, a função retornará o #VALUE! Erro.

A única ação que um procedimento de função pode fazer (além de realizar 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 informações do usuário que executa a função. Você pode usar uma instrução MsgBox como um meio de transmitir informações ao usuário. Você também pode usar caixas de diálogo personalizadas ou UserForms, mas esse é um assunto além do escopo desta introdução.

Documentação de macros e funções personalizadas

Até mesmo macros simples e funções personalizadas podem ser difíceis de ler. Você pode facilitar a compreensão digitando texto explicativo na forma de comentários. Você adiciona comentários precedendo o texto explicativo com um apóstrofo. Por exemplo, o exemplo a seguir mostra a função DESCONTO com comentários. Adicionar comentários como esses torna mais fácil para você ou outras pessoas manter seu código VBA com o passar do tempo. Se você precisar fazer uma alteração no código no futuro, terá mais facilidade para entender o que fez originalmente.

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

Outra maneira de documentar suas macros e funções personalizadas é dar a elas nomes descritivos. Por exemplo, em vez de nomear uma macro como Rótulos, você pode nomeá-la como MonthLabels para descrever mais especificamente a finalidade da macro. Usar nomes descritivos para macros e funções personalizadas é especialmente útil quando você criou muitos procedimentos, especialmente se você criar procedimentos com finalidades semelhantes, mas não idênticas.

A forma como você documenta 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 suas funções personalizadas em qualquer lugar

Para usar uma função personalizada, a pasta de trabalho que contém o módulo no qual você criou a função deve estar aberta. Se essa pasta de trabalho não estiver aberta, você receberá uma #NAME? quando você tenta usar a função. Se você fizer referência à função em uma pasta de trabalho diferente, deverá preceder o nome da função com o nome da pasta de trabalho na qual a função reside. Por exemplo, se você criar uma função chamada DESCONTO em uma pasta de trabalho chamada Personal.xlsb e chamar essa função de outra pasta de trabalho, deverá digitar =personal.xlsb!discount(), não simplesmente =discount().

Você pode evitar alguns pressionamentos de tecla (e possíveis erros de digitação) selecionando suas funções personalizadas na caixa de diálogo Inserir Função. Suas funções personalizadas aparecem na categoria Definido pelo Usuário:

Uma maneira mais fácil de disponibilizar suas funções personalizadas o tempo todo é armazená-las em uma pasta de trabalho separada e salvá-la como um suplemento. Você pode disponibilizar o suplemento sempre que executar o Excel. Veja como fazer isso:

Depois de seguir essas etapas, suas funções personalizadas estarão disponíveis sempre que você executar o Excel. Se você quiser adicionar à sua biblioteca de funções, retorne ao Editor do Visual Basic. Se você olhar no Visual Basic Editor Project Explorer sob um título VBAProject, verá um módulo com o nome do arquivo do suplemento. Seu suplemento terá a extensão .xlam.

Clicar duas vezes nesse módulo no Project Explorer faz com que o Editor do Visual Basic exiba o código da função. Para adicionar uma nova função, posicione o ponto de inserção após a instrução End Function que termina a última função na Janela de Código e comece a digitar. Você pode criar quantas funções precisar dessa maneira, e elas estarão sempre disponíveis na categoria Definido pelo Usuário na caixa de diálogo Inserir Função .

Sobre os autores

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

Precisa de mais ajuda?

Você sempre pode consultar um especialista na Excel Tech Community ou obter suporte nas Comunidades.