Criar funções personalizadas no Excel

Importante :  Este artigo foi traduzido por um sistema de tradução automática, leia o aviso de isenção de responsabilidade. Para sua referência, veja a versão em inglês deste artigo aqui.

Embora o Excel inclui uma variedade de funções de planilha internas, chances são que ela não tem uma função para cada tipo de cálculo que você executar. Os designers do Excel possivelmente não foi possível prever necessidades de cálculo de cada usuário. Em vez disso, o Excel fornece a capacidade de criar funções personalizadas, que são explicadas neste artigo.

Funções personalizadas, como macros, usam o Visual Basic for Applications (VBA) linguagem de programação. Elas diferem das macros de duas maneiras significativas. Primeiro, eles usarem procedimentos Function em vez de procedimentos Sub . Isto é, eles começam com uma instrução de função em vez de uma instrução Sub e end com End Function em vez de End Sub. Segundo, eles executarem cálculos em vez de tomar ações. Certos tipos de instruções, como declarações que selecione e formate intervalos, são excluídos da funções personalizadas. Neste artigo, você aprenderá a criar e usar funções personalizadas. Para criar funções e macros, você trabalhar com o Editor do Visual Basic (VBE), que abre em uma nova janela separada do Excel.

Suponha que sua empresa oferece um desconto por quantidade de 10% nas vendas de um produto, fornecido a ordem é para mais de 100 unidades. Nos parágrafos a seguir, vamos demonstrar uma função para calcular Este 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.

Formulário de pedido de exemplo sem uma função personalizada

Para criar uma função de 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, em seguida, 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 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

Observação : Para tornar seu código mais legível, você pode usar a tecla Tab para recuar linhas. O recuo é para o benefício apenas e é opcional, conforme o código será executado com ou sem ele. Após digitar uma linha recuada, o Editor do Visual Basic assume que sua próxima linha será recuada da mesma forma. Para mover-out (ou seja, para a esquerda) uma tabulação de caractere, pressione Shift + Tab.

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

=DISCOUNT(D7,E7)

O Excel calcula o desconto de 10 por cento em 200 unidades em $47.50 por unidade e retorna r $950.00.

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

Vamos considerar como o Excel interprete esse procedimento function. Quando você pressiona Enter, o Excel procura o nome do desconto na pasta de trabalho atual e localiza que é uma função personalizada em um módulo do VBA. Os nomes de argumento entre parênteses, quantidade e preço, são espaços reservados para os valores em que o cálculo do desconto é baseado.

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

O se instrução no bloco a seguir de código examina o argumento de quantidade e determina se o número de itens vendidos é maior que 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 é maior que ou igual a 100, o VBA executa a instrução a seguir, que multiplica o valor de quantidade pelo valor preço e, em seguida, multiplicará o resultado por 0,1:

Discount = quantity * price * 0.1

O resultado é armazenado como a variável de desconto. Uma instrução de VBA que armazena um valor em uma variável é chamada de uma instrução de atribuição , porque ele avalia a expressão no lado direito do sinal de igual e atribui o resultado para o nome da variável à esquerda. Porque a variável desconto tem o mesmo nome que o procedimento function, será retornado o valor armazenado na variável para a fórmula de planilha que chamado a função de desconto.

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

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)

VBA não tem nenhuma função ARRED, mas não do Excel. Portanto, para usar ROUND nesta política, você informa ao VBA para procurar o método ARRED (função) no objeto de aplicativo (Excel). Você pode fazer isso adicionando o aplicativo word antes da palavra Arred. Use esta sintaxe sempre que precisar acessar uma função do Excel a partir de um módulo do VBA.

Uma função personalizada deve começar com uma instrução de função e termina com uma instrução End Function. Além do nome da função, a instrução de função 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 usa argumentos.

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

O número de palavras-chave VBA que você pode usar em funções personalizadas é menor que o número que você pode usar em macros. Funções personalizadas não são permitidas para fazer algo diferente de retornar um valor para uma fórmula em uma planilha ou uma expressão usada em outra função ou macro do VBA. Por exemplo, funções personalizadas não é possível redimensionar janelas, editar uma fórmula em uma célula ou alterar a fonte, cor ou opções padrão para o texto em uma célula. Se você incluir o código de "ação" desse tipo em um procedimento function, a função retorna o #VALUE! erro.

A uma ação que um procedimento function pode fazer (com exceção 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 obtenção de entrada do usuário executar a função. Você pode usar uma instrução MsgBox como um meio de transmitir informações para o 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.

Macros mesmo simples e funções personalizadas podem ser difícil de ler. Você pode torná-las mais fácil de compreender digitando um texto explicativo no formulário de comentários. Você pode adicionar 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 para manter seu código VBA passar do tempo. Se você precisar fazer uma alteração no código no futuro, você terá uma hora mais fácil compreender o que você fez por originalmente.

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

Um apóstrofo informa ao Excel para Ignorar tudo para a direita na mesma linha, para que possa criar comentários nas linhas isolada ou à direita de linhas que contêm VBA código. Você pode começar um bloco relativamente longo de código com um comentário que explica a sua finalidade geral e usar comentários embutidos para instruções individuais do documento.

Outra maneira de documentar suas macros e funções personalizadas é dar a elas nomes descritivos. Por exemplo, em vez de nome de uma macro de etiquetas, você pode nomear- MonthLabels descrever mais especificamente a finalidade que serve a macro. Usando nomes descritivos para macros e funções personalizadas é especialmente útil quando você criou muitos procedimentos, especialmente se você criar procedimentos que têm finalidades semelhantes, mas não idênticas.

Como você documentar suas macros e funções personalizadas é uma questão de preferência pessoal. O que é importante é adotar algum método de documentação e usá-lo de forma consistente.

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ê obtém um #NAME? Erro ao tentar usar a função. Se você se referir a função em uma pasta diferente, você deve preceder o nome de função com o nome da pasta de trabalho no qual a função reside. Por exemplo, se você criar uma função chamada desconto em uma pasta de trabalho chamada xlsb e chame essa função de outra pasta de trabalho, você deve digitar =personal.xlsb!discount(), não simplesmente =discount().

Você pode economizar alguns pressionamentos de 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 definido pelo usuário:

caixa de diálogo Inserir Função

Uma maneira fácil de fazer suas funções personalizadas disponíveis em todos os horários é armazená-las em uma pasta de trabalho separada e salve essa pasta de trabalho como um suplemento. Você pode fazer o suplemento disponível sempre que você executar o Excel. Veja aqui como fazer isso:

  1. Após ter criado as funções que você precisa, clique em arquivo > Salvar como.

    Na Excel 2007, clique no Botão Microsoft Office e clique em Salvar como

  2. Na caixa de diálogo Salvar como, abra a lista suspensa Salvar como tipo e selecione Excel Add-In. Salve a pasta de trabalho em um nome possa ser reconhecida, como MyFunctions, na pasta de suplementos . A caixa de diálogo Salvar como irá propor dessa pasta, portanto tudo o que você precisa fazer é aceite o local padrão.

  3. Depois de salvar a pasta de trabalho, clique em arquivo > Opções do Excel.

    No Excel 2007, clique no Botão Microsoft Office e em Opções do Excel.

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

  5. Na lista suspensa Gerenciar, selecione Suplementos do Excel. Clique no botão Ir.

  6. Na caixa de diálogo Suplementos, marque a caixa de seleção ao lado do nome usado para salvar sua pasta de trabalho, conforme mostrado abaixo.

    caixa de diálogo Suplementos

  1. Após ter criado as funções que você precisa, clique em arquivo > Salvar como.

  2. Na caixa de diálogo Salvar como, abra a lista suspensa Salvar como tipo e selecione Excel Add-In. Salve a pasta de trabalho em um nome possa ser reconhecida, como MyFunctions.

  3. Depois de salvar a pasta de trabalho, clique em Ferramentas > Suplementos do Excel.

  4. Na caixa de diálogo Suplementos, selecione o botão Procurar para localizar o add-in, clique em Abrir, marque a caixa ao lado de seu suplemento na caixa Suplementos disponíveis.

Depois de seguir essas etapas, suas funções personalizadas estará disponíveis sempre que você executar o Excel. Se você deseja adicionar à sua biblioteca de função, retorne para o Editor do Visual Basic. Se você olhar no Editor do Visual Basic Project Explorer embaixo de um título de projeto VBA, você verá um módulo chamado após seu arquivo de suplemento. Seu suplemento terá a. xlam extensão.

módulo nomeado no vbe

Clicar duas vezes em módulo no Explorador de projeto faz com que o Editor do Visual Basic para exibir seu código de 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 como muitas funções como precisar dessa maneira, e eles serão sempre estará disponível na categoria definido pelo usuário na caixa de diálogo Inserir função.

Esse conteúdo foi originalmente criado por Mark Dodge e Craig Stinson como parte do seu livro Microsoft Office Excel 2007 Inside Out. Ele foi atualizado para aplicar a versões mais recentes do Excel também.

Precisa de mais ajuda?

Você pode sempre consultar um especialista na Excel Tech Community, obter suporte na Comunidade de respostas ou sugerir um novo recurso ou melhoria no User Voice do Excel.

Observação : Aviso de Isenção de Tradução Automática: Este artigo foi traduzido por computador, sem intervenção humana. A Microsoft oferece essas traduções automáticas para ajudar as pessoas que não falam inglês a aproveitar os textos escritos sobre produtos, serviços e tecnologias da Microsoft. Como este artigo foi traduzido automaticamente, é possível que contenha erros de vocabulário, sintaxe ou gramática.

Expanda suas habilidades
Explore o treinamento
Obtenha novos recursos primeiro
Ingressar no Office Insider

Essas informações foram úteis?

Obrigado por seus comentários!

Agradecemos pelos seus comentários! Parece que pode ser útil conectar você a um de nossos agentes de suporte do Office.

×