Criar funções personalizadas no Excel

As funções personalizadas, como macros, utilizam a linguagem de programação VBA (Visual Basic for Applications ). Diferem das macros de duas formas significativas. Em primeiro lugar, utilizam procedimentos de Função em vez de Sub procedimentos. Ou seja, começam com uma instrução function em vez de uma instrução Sub e terminam com a Função End em vez de End Sub. Em segundo lugar, executam cálculos em vez de efetuar ações. Determinados tipos de instruções, como instruções que selecionam e formatam intervalos, são excluídos das funções personalizadas. Neste artigo, irá aprender a criar e 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 a sua empresa oferece um desconto de quantidade de 10 por cento na venda de um produto, desde que a encomenda seja para mais de 100 unidades. Nos parágrafos seguintes, vamos demonstrar uma função para calcular este desconto.

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

Para criar uma função DISCOUNT 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. É apresentada uma nova janela do módulo 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
   

Agora, está pronto para utilizar a nova função DISCOUNT. 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 a 47,50 $ por unidade e devolve 950,00 $.

Na primeira linha do código VBA, Função DESCONTO(quantidade, preço), indicou que a função DESCONTO requer dois argumentos, quantidade e preço. Quando chama a função numa célula de folha de cálculo, tem de incluir esses dois argumentos. Na fórmula =DESCONTO(D7;E7), D7 é o argumento quantidade e E7 é o argumento de preço . Agora, pode copiar a fórmula DISCOUNT para G8:G13 para obter os resultados apresentados abaixo.

Vamos considerar como o Excel interpreta este procedimento de função. Quando prime Enter, o Excel procura o nome DISCOUNT 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 seguinte bloco de código examina o argumento 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 , uma vez que avalia a expressão no lado direito do sinal de igual e atribui o resultado ao nome da variável à esquerda. Uma vez que a variável Discount tem o mesmo nome que o procedimento de função, o valor armazenado na variável é devolvido à fórmula da folha de cálculo que chamou a função DISCOUNT.

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

Discount = 0

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

Discount = Application.Round(Discount, 2)

O VBA não tem nenhuma função ROUND, mas o Excel tem. Por conseguinte, para utilizar ROUND nesta instrução, diga ao VBA para procurar o método Round (função) no objeto Aplicação (Excel). Pode fazê-lo ao adicionar a palavra Aplicação antes da palavra Arredondar. Utilize esta sintaxe sempre que precisar de aceder a uma função do Excel a partir de um módulo VBA.

Uma função personalizada tem 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 Função geralmente especifica um ou mais argumentos. No entanto, pode criar uma função sem argumentos. O Excel inclui várias funções incorporadas (RAND e NOW, por exemplo) que não utilizam argumentos.

Seguindo a instrução Função, um procedimento de função inclui uma ou mais instruções VBA que tomam decisões e executam cálculos com os argumentos transmitidos à função. Por fim, algures no procedimento de função, tem de incluir uma instrução que atribua um valor a uma variável com o mesmo nome que a função. Este valor é devolvido à fórmula que chama a função .

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 fazer nada além de devolver um valor a uma fórmula numa folha de cálculo ou a uma expressão utilizada noutra macro ou função VBA. Por exemplo, as funções personalizadas não podem redimensionar janelas, editar uma fórmula numa célula ou alterar as opções de tipo de letra, cor ou padrão do texto numa célula. Se incluir código de "ação" deste tipo num procedimento de função, a função devolve o #VALUE! erro.

A única ação que um procedimento de função pode fazer (para além de efetuar cálculos) é apresentar uma caixa de diálogo. Pode utilizar uma instrução InputBox numa função personalizada como forma de obter entradas do utilizador que executa a função. Pode utilizar uma instrução MsgBox como forma de transmitir informações ao utilizador. Também pode utilizar caixas de diálogo personalizadas ou Formulários de Utilizador, mas este é um assunto para além do âmbito desta introdução.

Até as macros simples e as funções personalizadas podem ser difíceis de ler. Pode torná-los mais fáceis de compreender ao escrever texto explicativo sob a forma de comentários. Pode adicionar comentários antes do texto explicativo com um apóstrofo. Por exemplo, o exemplo seguinte mostra a função DISCOUNT com comentários. Adicionar comentários como estes torna mais fácil para si ou para outras pessoas manter o seu código VBA à medida que o tempo passa. Se precisar de fazer uma alteração ao código no futuro, terá mais facilidade em compreender o que fez originalmente.

Um apóstrofo diz ao Excel para ignorar tudo à direita na mesma linha, para que possa criar comentários em linhas próprias ou no lado direito das linhas que contêm código VBA. Pode começar um bloco de código relativamente longo com um comentário que explica a sua finalidade geral e, em seguida, utilizar comentários inline para documentar declarações individuais.

Outra forma de documentar as macros e as funções personalizadas é dar-lhes nomes descritivos. Por exemplo, em vez de atribuir um nome a uma macro Etiquetas, pode atribuir-lhe o nome MonthLabels 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 cria muitos procedimentos, especialmente se criar procedimentos que tenham finalidades semelhantes, mas não idênticas.

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

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 no qual a função reside. Por exemplo, se criar uma função denominada DESCONTO num livro denominado Pessoal.xlsb e chamar essa função a partir de outro livro, tem de escrever =personal.xlsb!discount(), e não simplesmente =desconto().

Pode guardar algumas combinações de teclas (e possíveis erros de escrita) ao selecionar as suas funções personalizadas na caixa de diálogo Inserir Função. As suas funções personalizadas são apresentadas na categoria Definido 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. 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 ao 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 o Editor do Visual Basic exiba seu código de 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.