Criar funções personalizadas no Excel

Nota:  Queremos fornecer-lhe os conteúdos de ajuda mais recentes o mais rapidamente possível e no seu idioma. 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 informar-nos se as informações foram úteis no final desta página? Eis o artigo em inglês para referência.

Apesar do Excel inclui inúmeros das funções de folha de cálculo incorporada, provavelmente não tem uma função para cada tipo de cálculo que executar. Os estruturadores do Excel não foi possivelmente antecipar necessidades de cálculo de cada utilizador. Em vez disso, Excel fornece a capacidade de criar funções personalizadas, que são explicadas neste artigo.

Funções personalizadas, como macros, utilizam o Visual Basic for Applications (VBA) linguagem de programação. Diferem das macros de duas maneiras significativas. Em primeiro lugar, utilizam procedimentos função em vez de procedimentos Sub . Isto é, são iniciados com uma instrução de função em vez de uma instrução Sub e end com a Função de fim em vez de End Sub. Em segundo lugar, eles efectuam cálculos em vez de executa ações. Determinados tipos de demonstrações, tal como declarações que selecionar e formatar intervalos, são excluídos do funções personalizadas. Neste artigo, vai aprender a criar e utilizar funções personalizadas. Para criar funções e as macros, trabalhar com o Visual Basic Editor (VBE), que é aberta numa nova janela separada do Excel.

Imaginemos a que sua empresa oferece um desconto quantidade de 10 por cento sobre o preço de venda de um produto, descrito a ordem destina-se mais de 100 unidades. 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 por extenso resultante.

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

  2. Copie e cole o seguinte código do módulo novo.

    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 destina-se apenas o benefício e é opcional, tal como o código será executado com ou sem-lo. Depois de escrever uma linha de avanço, o Visual Basic Editor assume que a linha seguinte será da mesma forma com avanço. Para mover (ou seja, para a esquerda) um separador de caracteres, prima Shift + Tab.

Agora está pronto para utilizar a nova função desconto. Feche o Visual Basic Editor, selecione a célula G7 e escreva o seguinte:

=DISCOUNT(D7,E7)

O Excel calcula o desconto de 10 por cento 200 unidades na 47.50 $ por unidade e devolve $950.00.

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

Vamos considere como o Excel interpreta este procedimento de função. Quando prime Enter, o Excel procura para o nome de desconto no livro atual e localiza 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 em que se baseia o cálculo do desconto.

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

A se declaração no bloco de seguinte do 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 preço de valor e, em seguida, multiplica 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 numa variável é denominada uma instrução de atribuição , porque-avalia a expressão no lado direito do sinal de igual e atribui o resultado para o nome da variável à esquerda. Uma vez que a variável de desconto tem o mesmo nome que o procedimento function, é devolvido o valor armazenado na variável para a fórmula de folha de cálculo denominada a função de desconto.

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

Discount = 0

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

Discount = Application.Round(Discount, 2)

VBA tem sem função Arred, mas é que o Excel. Por isso, para utilizar ROUND nesta declaração, dizer VBA para procurar o método Arred (função) no objeto Application (Excel). Fazê-lo ao adicionar o aplicação word antes da palavra Arred. Utilize esta sintaxe sempre que precisar 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 de função e termina com uma declaração de função de fim. Para além do nome da função, a declaração de função normalmente Especifica um ou mais argumentos. No entanto, pode criar uma função sem argumentos. O Excel inclui várias funções incorporadas — aleatório e agora, por exemplo — que não utiliza o argumentos.

Após a instrução de função, um procedimento function inclui um ou mais declarações de VBA que tomar decisões e efectuam cálculos utilizando os argumentos transmitidos para a função. Por fim, algures no procedimento function, tem de incluir uma instrução que atribui um valor a uma variável com o mesmo nome como a função. Este valor é devolvido para a fórmula que liga para a função.

O número de palavras-chave do VBA pode utilizar funções personalizadas for menor que o número pode utilizar em macros. Funções personalizadas não são permitidas fazer nada que não seja devolvido um valor a uma fórmula numa folha de cálculo ou a expressão utilizada num outro VBA macro ou função. Por exemplo, funções personalizadas não é possível redimensionar janelas, editar uma fórmula numa célula ou alterar o tipo de letra, cor ou opções de padrão para o texto numa célula. Se incluir código "ação" este tipo de um procedimento function, a função devolve o #VALUE! Ocorreu um erro.

A uma ação que pode efetuar um procedimento function (para além das efetuar cálculos) é apresentada uma caixa de diálogo. Pode utilizar uma declaração CxEntrada numa função personalizada como um meio de introdução de entrada do utilizador executar a função. Pode utilizar uma instrução CxMsg como meio de transmitir informações ao utilizador. Também pode utilizar caixas de diálogo personalizada ou formulários de utilizador, mas que é um assunto para além do âmbito nesta Introdução.

Macros mesmo simples e funções personalizadas podem ser difícil de ler. Pode fazê-los mais fácil de compreender ao escrever o texto explicativo no formulário de comentários. Adicionar comentários ao precede o texto explicativo com um apóstrofo. Por exemplo, o exemplo seguinte mostra a função de desconto com comentários. Adicionar comentários como estes torna mais fácil para o utilizador ou outras pessoas manter o seu código VBA como fases de tempo. Se precisar de efetuar uma alteração ao código no futuro, terá uma hora mais fácil de compreender a ação originalmente.

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

Um apóstrofo indica ao Excel para ignorar tudo para a direita na mesma linha, para que possa criar comentários nas linhas por si ou no lado direito das linhas que contêm VBA código. Pode começar a um bloco comprido de código com um comentário pormenorizado a sua finalidade geral e, em seguida, utilizar comentários inline às demonstrações individuais do documento.

Outra forma das macros e funções personalizadas do documento é para lhes dar nomes descritivos. Por exemplo, em vez de nome de uma macro etiquetas, é-lhe o nome MonthLabels para descrever mais especificamente o objetivo que serve a macro. Utilizar nomes descritivos para as macros e funções personalizadas é especialmente útil quando tiver criado muitos procedimentos, sobretudo, se criar procedimentos que têm efeitos semelhantes, mas não idênticos.

Como documento as macros e funções personalizadas é uma questão de preferência pessoal. O que é importante é adotar qualquer método de documentação e utilizá-la de forma consistente.

Para utilizar uma função personalizada, tem de ser abrir o livro que contém o módulo em que criou a função. Se esse livro não estiver aberto, obtém um #NAME? Erro ao tentar utilizar a função. Se referência a função num livro diferente, tem de preceder o nome da função com o nome do livro no qual a função se encontra. Por exemplo, se criar uma função denominada desconto num livro denominado Personal. xlsb e ligar para essa função a partir de outro livro, tem de escrever =personal.xlsb!discount(), não basta =discount().

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

inserir caixa de diálogo inserir função

Uma forma mais fácil para tornar as suas funções personalizadas disponíveis em todas as horas é armazená-los noutro livro e, em seguida, guardar esse livro como um suplemento. Pode, em seguida, disponibilizar o suplemento sempre que executar o Excel. Eis como fazer isto:

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

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

  2. Na caixa de diálogo Guardar como, abra a lista pendente Guardar com o tipo e, selecione Add-In do Excel. Guarde o livro num nome reconhecível como MyFunctions, na pasta AddIns . A caixa de diálogo Guardar como irá propor nessa pasta, pelo que tudo o que precisa de fazer é aceitar a localização predefinida.

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

    No Excel 2007, clique no Botão do 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 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 ao lado do nome que utilizou para guardar o livro, conforme apresentado abaixo.

    caixa de diálogo de suplementos

  1. Depois de ter criado as funções 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 Add-In do Excel. Guarde o livro num nome reconhecível como MyFunctions.

  3. Depois de ter guardado o livro, clique em Ferramentas > Suplementos.

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

Depois de seguir estes passos, as funções personalizadas irão estar disponíveis sempre que executar o Excel. Se pretende adicionar à sua biblioteca de funções, é devolvido o Visual Basic Editor. Se procurar no Explorador de projeto do Visual Basic Editor sob um cabeçalho VBAProject, irá ver num módulo chamado após o ficheiro de suplemento. O suplemento terão o. xlam extensão.

módulo com nome no VBE

Fazer duplo clique nesse módulo no Project Explorer faz com que o Visual Basic Editor para apresentar o código de função. Para adicionar uma nova função, posicione o ponto de inserção depois da instrução End Function que termina a função última na janela de código e comece a escrever. Pode criar como muitas funções à medida que precisa desta forma e, estes serão sempre estar disponíveis na categoria definida pelo utilizador na caixa de diálogo Inserir função.

Este conteúdo foi originalmente criado por Dodge de marca e Craig Stinson como parte do seu livro do Microsoft Office Excel 2007 Inside Out. Uma vez que foi atualizado para aplicar a versões mais recentes do Excel, assim.

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.

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.

×