Criar funções personalizadas no Excel

Funções personalizadas, como macros, usam a linguagem de programação de Visual Basic for Applications (VBA). Elas diferem das macros de duas maneiras significativas. Primeiro, eles usam procedimentos de função 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 a Função Final em vez de End Sub. Em segundo lugar, eles executam cálculos em vez de executar ações. Certos tipos de instruções, como instruções que selecionam e formatizam 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, você trabalha com o Visual Basic Editor (VBE), que é aberto em uma nova janela separada do Excel.

Suponha que sua empresa ofereça um desconto de 10% na venda de um produto, desde que o pedido seja para 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 DISCOUNT personalizada nesta pasta de trabalho, siga estas etapas:

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

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

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

=DISCOUNT(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, desconto de função(quantidade, preço), você indicou que a função DISCOUNT requer dois argumentos, quantidade e preço. Ao chamar a função em uma célula de planilha, você deve incluir esses dois argumentos. Na fórmula =DISCOUNT(D7,E7), D7 é o argumento de quantidade e E7 é o argumento de preço . Agora você pode copiar a fórmula DISCOUNT 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 DISCOUNT na pasta de trabalho atual e descobre que ele é uma função personalizada em um módulo VBA. Os nomes de argumento incluídos em 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 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 executará a seguinte instrução, que multiplica o valor da quantidade pelo valor de preço e multiplica o resultado em 0,1:

Discount = quantity * price * 0.1

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

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 De desconto para duas casas decimais:

Discount = Application.Round(Discount, 2)

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

Uma função personalizada deve começar com uma instrução Function e terminar com uma instrução de Função Final. 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– RAND e NOW, por exemplo, que não usam argumentos.

Seguindo a instrução Function, um procedimento de função inclui uma ou mais instruções VBA que tomam decisões e executam cálculos usando os argumentos passados para a função. Por fim, em algum lugar no procedimento de função, você deve incluir uma instrução que atribui um valor a uma variável com o mesmo nome da função. Esse valor é retornado à fórmula que chama a função.

O número de palavras-chave VBA que você pode usar em funções personalizadas é menor do que o número que você pode usar em macros. 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 para o texto em uma célula. Se você incluir o código de "ação" desse tipo em um procedimento de função, a função retornará o #VALUE! #REF!.

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 a entrada 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.

Até mesmo macros simples e funções personalizadas podem ser difíceis de ler. Você pode torná-los mais fáceis de entender digitando texto explicativo na forma de comentários. Você adiciona comentários antes do texto explicativo com um apóstrofe. Por exemplo, o exemplo a seguir mostra a função DISCOUNT com comentários. Adicionar comentários como esses facilita a manutenção do código VBA à medida que o tempo passa. Se você precisar fazer uma alteração no código no futuro, terá mais facilidade para entender o que fez originalmente.

Um apóstrofe diz ao Excel para ignorar tudo à direita na mesma linha, para que você possa criar comentários em linhas por conta própria ou no lado direito das linhas que contêm código VBA. Você pode começar um bloco de código relativamente longo com um comentário que explica seu propósito geral e, em seguida, usar comentários embutidos para documentar instruções individuais.

Outra maneira de documentar suas macros e funções personalizadas é dar-lhes nomes descritivos. Por exemplo, em vez de nomear um rótulo de macro, você pode nomeá-lo MonthLabels para descrever mais especificamente a finalidade que a macro serve. O uso de nomes descritivos para macros e funções personalizadas é especialmente útil quando você cria muitos procedimentos, especialmente se você cria procedimentos que têm propósitos semelhantes, mas não idênticos.

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 consistentemente.

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? erro ao tentar usar a função. Se você referenciar a 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 DISCOUNT 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 salvar algumas teclas (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 Definida pelo Usuário:

Uma maneira mais fácil de disponibilizar suas funções personalizadas em todos os momentos é armazená-las em uma pasta de trabalho separada e, em seguida, salvar essa pasta de trabalho como um suplemento. Em seguida, você pode disponibilizar o suplemento sempre que executar o Excel. Veja como fazer isso:

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

Clicar duas vezes nesse módulo no Project Explorer faz com que a 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 Função Final que encerra a última função na janela Código e comece a digitar. Você pode criar quantas funções precisar dessa maneira e elas sempre estarão disponíveis na categoria Definido pelo Usuário na caixa de diálogo Inserir Função .

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 aplicar a versões mais recentes do Excel também.