Criar funções personalizadas no Excel

Embora o Excel inclua uma infinidade 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 executa. Os designers do Excel não poderiam antecipar as necessidades de cálculo de todos os utilizadores. Em vez disso, o Excel fornece-lhe a capacidade de criar funções personalizadas, que são explicadas neste artigo.

Funções personalizadas, como macros, utilizam a linguagem de programação Visual Basic for Applications (VBA). Diferem das macros de duas formas significativas. Primeiro, usam os procedimentos de função em vez de sub-procedimentos. Ou seja, começam com uma declaração de função em vez de uma sub-declaração e terminam com a Função Final em vez de End Sub. Segundo, fazem cálculos em vez de tomarem medidas. Certos tipos de declarações, tais como declarações que selecionam e intervalos de formato, são excluídas das funções personalizadas. Neste artigo, você vai aprender a criar e usar funções personalizadas. Para criar funções e macros, trabalha com o Editor Visual Basic (VBE)que se abre numa nova janela separada do Excel.

Suponha que a sua empresa ofereça um desconto de 10% na venda de um produto, desde que a encomenda seja de mais de 100 unidades. Nos parágrafos seguintes, demonstraremos 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 houver), e o preço alargado resultante.

Formulário de ordem de exemplo 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 Editor Visual Basic (no Mac, prima FN+ALT+F11),e, em seguida, clique em Inserir > Módulo. Uma nova janela do módulo aparece no lado direito do Editor Visual Basic.

  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 'Guia' para linhas de travessão. O entalhe é apenas para seu benefício, e é opcional, pois o código será executado com ou sem ele. Depois de escrever uma linha de recuo, o Editor Visual Basic assume que a sua próxima linha será igualmente recorrida. Para sair (isto é, para a esquerda) um personagem de separador, prima Shift+Tab.

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

Na primeira linha do seu código VBA, Função DISCOUNT (quantidade, preço), indicou que a função DISCOUNT requer dois argumentos, quantidade e preço. Quando ligar para a função numa célula de folha de cálculo, deve incluir esses dois argumentos. Na fórmula =DISCOUNT (D7,E7), D7 é o argumento da quantidade, e E7 é o argumento do 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. Ao premir Enter,o Excel procura o nome DISCOUNT no livro atual e descobre que é uma função personalizada num módulo VBA. Os nomes de argumentos incluídos nos parênteses, quantidade e preço,são espaços reservados para os valores em que se baseia o cálculo do desconto.

Formulário de ordem de exemplo com uma função personalizada

A declaração no bloco de código que se segue examina o argumento da quantidade e determina se o número de artigos vendidos é superior ou igual a 100:

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

Se o número de artigos vendidos for superior ou igual a 100, a VBA executa a seguinte declaração, que multiplica o valor da quantidade pelo valor do preço e multiplica o resultado em 0.1:

Discount = quantity * price * 0.1

O resultado é armazenado como o DescontoVariável . Uma declaração VBA que armazena um valor numa variável é chamada de declaração de atribuição, porque avalia a expressão no lado direito do sinal igual e atribui o resultado ao nome variável à esquerda. Como 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, a VBA executa a seguinte declaração:

Discount = 0

Finalmente, a seguinte declaração contorna o valor atribuído à variável Discount a duas casas decimais:

Discount = Application.Round(Discount, 2)

A VBA não tem função ROUND, mas o Excel tem. Por isso, para utilizar o ROUND nesta declaração, diga à VBA para procurar o método Redondo (função) no objeto Aplicação (Excel). Faça-o adicionando a palavra Aplicação antes da palavra Round. Utilize esta sintaxe sempre que precisar de aceder a uma função Excel a partir de um módulo VBA.

Uma função personalizada deve começar com uma declaração de função e terminar com uma declaração de Função Final. Além do nome da função, a declaração de Função geralmente especifica um ou mais argumentos. Pode, no entanto, criar uma função sem argumentos. O Excel inclui várias funções incorporadas — RAND e NOW, por exemplo — que não usam argumentos.

Na sequência da declaração de Função, um procedimento de função inclui uma ou mais declarações VBA que tomam decisões e realizam cálculos utilizando os argumentos passados para a função. Finalmente, em algum lugar do procedimento de função, você deve incluir uma declaração que atribui 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 são permitidas para fazer outra coisa que não seja devolver um valor a uma fórmula numa folha de cálculo, ou a uma expressão utilizada em outra 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 para o texto numa célula. Se incluir o código de "ação" deste tipo num procedimento de função, a função devolve o #VALUE! #CAMPO!.

A única ação que um procedimento de função pode fazer (para além de realizar cálculos) é exibir uma caixa de diálogo. Pode utilizar uma declaração InputBox numa função personalizada como forma de obter a entrada do utilizador que executa a função. Pode utilizar uma declaração msgBox como forma de transmitir informações ao utilizador. Também pode utilizar caixas de diálogo personalizadas, ou UserForms,mas este é um assunto fora do âmbito desta introdução.

Mesmo macros simples e funções personalizadas podem ser difíceis de ler. Pode torná-los mais fáceis de entender digitando texto explicativo sob a forma de comentários. Adicione comentários antes do texto explicativo com um apóstrofo. Por exemplo, o exemplo a seguir mostra a função DISCOUNT com comentários. Adicionar comentários como estes facilita a manutenção do seu código VBA à medida que o tempo passa. Se precisar de alterar o código no futuro, terá mais facilidade em compreender o que fez originalmente.

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

Um apóstrofo diz ao Excel para ignorar tudo à direita na mesma linha, para que possa criar comentários por si só ou no lado direito das linhas que contenham código VBA. Pode iniciar um bloco de código relativamente longo com um comentário que explique o seu propósito geral e, em seguida, use comentários inline para documentar declarações individuais.

Outra forma de documentar as suas macros e funções personalizadas é dar-lhes nomes descritivos. Por exemplo, em vez de nomear um macro Labels,pode chamar-lhe MonthLabels para descrever mais especificamente o propósito que o macro serve. Usar nomes descritivos para macros e funções personalizadas é especialmente útil quando criamos muitos procedimentos, especialmente se criarmos procedimentos que têm propósitos 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 consistentemente.

Para utilizar uma função personalizada, o livro de trabalho que contém o módulo em que criou a função tem de estar aberto. Se o livro não estiver aberto, tens um #NAME? erro quando tenta utilizar a função. Se referenciar a função num livro diferente, deve preceder o nome da função com o nome do livro em que reside a função. Por exemplo, se criar uma função chamada DISCOUNT num livro chamado Personal.xlsb e chamar essa função de outro livro, deve escrever =personal.xlsb!discount(), não simplesmente =desconto().

Pode guardar algumas teclas (e possíveis erros de dactilografia) selecionando as suas funções personalizadas a partir da caixa de diálogo 'Função inserida'. As suas funções personalizadas aparecem na categoria Definido pelo Utilizador:

inserir caixa de diálogo inserir função

Uma forma mais fácil de disponibilizar as suas funções personalizadas em todos os momentos é armazená-las num livro separado e, em seguida, guardar esse livro como um complemento. Em seguida, pode disponibilizar o add-in sempre que executar o Excel. Eis como fazer isto:

  1. Depois de ter criado as funções necessárias, clique em 'Arquivar > 'Guardar'.

    Em Excel 2007, clique no botão Do Microsoft Officee clique em Guardar

  2. Na caixa de diálogo Save As, abra a lista de drop-down Do Tipo De Guardar e selecione Excel Add-In. Guarde o livro com um nome reconhecível, como MyFunctions,na pasta AddIns. A caixa de diálogo Save As irá propor essa pasta, por isso tudo o que precisa de fazer é aceitar a localização predefinida.

  3. Depois de ter guardado o livro, clique em 'Ficheiro > Opções excel'.

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

  4. Na caixa de diálogo Excel Options, clique na categoria Add-Ins.

  5. Na lista de drop-down manage, selecione Excel Add-Ins. Em seguida, clique no botão Go.

  6. Na caixa de diálogo Add-Ins, selecione a caixa de verificação ao lado do nome utilizado para guardar o seu livro, como mostrado abaixo.

    caixa de diálogo de suplementos

  1. Depois de ter criado as funções necessárias, clique em 'Arquivar > 'Guardar'.

  2. Na caixa de diálogo Save As, abra a lista de drop-down Do Tipo De Guardar e selecione Excel Add-In. Guarde o livro com um nome reconhecível, como MyFunctions.

  3. Depois de ter guardado o livro, clique em Ferramentas > Excel Add-Ins.

  4. Na caixa de diálogo Add-Ins, selecione o botão Procurar para encontrar o seu add-in, clique em Abrire, em seguida, verifique a caixa ao lado do seu Add-In na caixa Disponível de Add-Ins.

Depois de seguir estes passos, as suas funções personalizadas estarão disponíveis sempre que executar o Excel. Se quiser adicionar à sua biblioteca de funções, volte ao Editor Visual Basic. Se olhar no Visual Basic Editor Project Explorer sob uma rubrica VBAProject, verá um módulo com o nome do seu ficheiro adicional. O seu add-in terá a extensão .xlam.

módulo com nome no VBE

Clicar duas vezes nesse módulo no Project Explorer faz com que o Editor Visual Basic apresente o seu código de função. Para adicionar uma nova função, posicione o seu ponto de inserção após a declaração de Função Final que termina a última função na janela Código e comece a digitar. Pode criar todas as funções necessárias desta forma e estarão sempre disponíveis na categoria De Definição de Utilizador na caixa de diálogo 'Função inserida'.

Este conteúdo foi originalmente da autoria de Mark Dodge e Craig Stinson como parte do seu livro Microsoft Office Excel 2007 Inside out. Desde então, foi atualizado para aplicar também às 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 User Voice do Excel.

Precisa de mais ajuda?

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.

×