Criar funções personalizadas no Excel

Apesar de o Excel incluir uma infinidade de funções de folha de cálculo incorporadas, é provável que esta não tenha uma função para cada tipo de cálculo que realizar. Os projetistas do Excel não podiam prever 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.

Está à procura de informações sobre como criar uma função personalizada de JavaScript que pode executar no Excel para Windows, Excel para Mac ou Excel na Web ? Se estiver, consulte o artigo Descrição geral das funções personalizadas do Excel.

Funções personalizadas, como macros, utilizam a linguagem de programação Visual Basic for Applications (VBA) . Diferem das macros de duas formas significativas. Em primeiro lugar, utilizam procedimentos de função em vez de procedimentos sub . 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 seguida, executam cálculos em vez de realizar ações. Certos tipos de instruções, tais como instruções que selecionam e formatam intervalos, são eliminadas de 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.

Suponhamos que a 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 seguintes parágrafos, iremos demonstrar uma função para calcular este desconto.

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

Exemplo de formulário de encomenda sem uma função personalizada

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. É apresentada uma nova janela de módulo no lado direito do Visual Basic Editor.

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

Nota: Para tornar o seu código mais legível, pode utilizar a tecla de tabulação para avançar linhas. O avanço é apenas para o seu benefício e é opcional, uma vez que o código será executado com ou sem o mesmo. Depois de escrever uma linha com avanço, o Visual Basic Editor assume que a linha seguinte será recuada da mesma forma. Para deslocar-se (ou seja, para o lado esquerdo) um caráter de tabulação, prima Shift +tecla de tabulação.

Agora, está pronto para utilizar a nova função de 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% nas unidades de 200 a $47.50 por unidade e devolve $950.

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

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

Exemplo de formulário de pedido com uma função personalizada

A instrução se 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 de quantidade pelo valor de preço e, em seguida, multiplica o resultado por 0,1:

Discount = quantity * price * 0.1

O resultado é armazenado como descontovariável. Uma instrução VBA que armazena um valor numa variável é denominada declaraçã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. Uma vez que o desconto variável tem o mesmo nome que o procedimento Function, o valor armazenado na variável é devolvido à fórmula da folha de cálculo que chamou a função desconto.

Se quantidade for menor do que 100, o VBA executa a seguinte instrução:

Discount = 0

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

Discount = Application.Round(Discount, 2)

O VBA não tem funções de arredondamento, mas o Excel faz. Por conseguinte, para utilizar o ROUND neste extracto, o VBA pode procurar o método redondo (função) no objeto Application (Excel). Pode fazê-lo adicionando a aplicação Word antes da palavra arredondada. 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 por uma instrução de função e terminar com uma instrução End Function. Para 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 — RAND e agora, por exemplo, que não utilizam argumentos.

Seguindo a instrução de função, um procedimento de função inclui uma ou mais instruções VBA que tomam decisões e efetuam cálculos utilizando os argumentos transmitidos à função. Por fim, em qualquer parte do procedimento Function, tem de 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.

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 efetuar mais nada 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 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 o código "ação" deste tipo num procedimento de função, a função devolve o #VALUE! .

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

Até mesmo macros simples e funções personalizadas podem ser difíceis de ler. Pode facilitar a compreensão ao escrever texto explicativo na forma de comentários. Pode adicionar comentários ao preceder o texto explicativo com um apóstrofo. Por exemplo, o exemplo seguinte mostra a função desconto com comentários. Adicionar comentários como estes permite que o utilizador ou outras pessoas Mantenha o seu código VBA à medida que o tempo passa. Se precisar de efetuar uma alteração no código no futuro, terá um tempo mais fácil para compreender o que fez originalmente.

Exemplo de uma função VBA com comentários

Um apóstrofo diz ao Excel para ignorar tudo o que se encontra à direita na mesma linha, para que possa criar comentários em linhas por si próprio 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 o objetivo geral e, em seguida, utilizar comentários inline 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 um nome a uma etiquetade macro, pode atribuir-lhe um nome MonthLabels para descrever mais especificamente o objetivo da macro. Utilizar nomes descritivos para macros e funções personalizadas é especialmente útil quando criou vários procedimentos, principalmente se criar procedimentos que tenham 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 que é 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 onde criou a função tem de estar aberto. Se esse livro não estiver aberto, obterá uma #NAME? erro ao tentar 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 com o nome desconto num livro denominado pessoal. xlsb e ligar para esta função a partir de outro livro, tem de escrever = pessoal. xlsb! desconto ()e não apenas = desconto ().

Pode poupar-se a alguns toques de teclas (e possíveis erros de digitação) ao selecionar as suas funções personalizadas a partir da caixa de diálogo Inserir função. As suas funções personalizadas são apresentadas na categoria definida pelo utilizador:

inserir caixa de diálogo inserir função

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

  1. Depois de ter criado as funções de que precisa, clique em ficheiro > guardar como.

    No Excel 2007, clique no botão do Microsoft Officee clique em guardar como

  2. Na caixa de diálogo guardar como , abra a lista pendente guardar com o tipo e selecione suplemento do Excel. Guarde o livro com um nome reconhecível, como MyFunctions, na pasta AddIns . A caixa de diálogo guardar como irá propor essa pasta, para que tudo o que precisa de fazer é aceitar a localização predefinida.

  3. Depois de guardar o livro, clique em ficheiro > Opções do Excel.

    No Excel 2007, clique no botão do Microsoft Officee, em seguida, clique em Opções do Excel.

  4. Na caixa de diálogo Opções do Excel , clique na categoria suplementos .

  5. Na lista pendente gerir , selecione suplementos do Excel. Em seguida, clique no botão ir .

  6. Na caixa de diálogo suplementos , selecione a caixa de verificação junto ao nome que utilizou para guardar o seu livro, conforme apresentado abaixo.

    caixa de diálogo de suplementos

  1. Depois de ter criado as funções de que precisa, clique em ficheiro > guardar como.

  2. Na caixa de diálogo guardar como , abra a lista pendente guardar com o tipo e selecione suplemento do Excel. Guarde o livro com um nome reconhecível, como MyFunctions.

  3. Depois de guardar o livro, clique em ferramentas > suplementos do Excel.

  4. Na caixa de diálogo suplementos , selecione o botão procurar para encontrar o seu suplemento, clique em abrire, em seguida, selecione a caixa junto ao seu suplemento na caixa suplementos disponíveis .

Após seguir estes passos, as suas funções personalizadas estarão disponíveis sempre que executar o Excel. Se pretender adicionar à sua biblioteca de funções, volte ao Visual Basic Editor. Se observar o Visual Basic Editor Project Explorer em um cabeçalho VBAProject, verá um módulo com o nome do seu ficheiro de suplemento. O seu suplemento terá a extensão. xlam.

módulo com nome no VBE

Clicar duas vezes nesse módulo no Project Explorer faz com que o Visual Basic Editor apresente o seu código de função. Para adicionar uma nova função, posicione o seu 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 escrever. Pode criar quantas funções forem necessárias desta forma e estarão sempre disponíveis na categoria definida pelo utilizador na caixa de diálogo Inserir função .

Este conteúdo foi criado originalmente por Mark subexposição e Craig Stinson como parte do livro Microsoft Office Excel 2007 Inside Out. Desde que tenha sido atualizado para aplicar também a versões mais recentes do Excel.

Precisa de mais ajuda?

Pode sempre perguntar a um especialista na Comunidade Tecnológica do Excel, obter suporte na Comunidade de Respostas ou sugerir uma nova funcionalidade ou melhoria no UserVoice do Excel.

Nota:  Esta página foi traduzida automaticamente e pode conter erros gramaticais ou imprecisões. O nosso objetivo é que estes conteúdos lhe sejam úteis. Pode indicar-nos se estas informações foram úteis? Eis o artigo em inglês para sua referência.​

Aumente os seus conhecimentos do Office
Explore as formações
Seja o primeiro a obter novas funcionalidades
Adira ao Office Insider

As informações foram úteis?

Obrigado pelos seus comentários!

Obrigado pelo seu feedback! Parece que poderá ser benéfico reencaminhá-lo para um dos nossos agentes de suporte do Office.

×