Como criar uma função definida pelo usuário em vba?

Provavelmente um dos benefícios mais legais de aprender VBA é a capacidade de criar suas próprias funções.

No Excel existem mais de 450 funções, e algumas delas são muito úteis no seu trabalho diário. Mas o Excel oferece a opção de criar uma função personalizada usando VBA. Sim você está certo. Função USER‌ DEFINED, abreviada como UDF, ou você também pode chamá-la de função VBA personalizada.

E há uma coisa que posso dizer com segurança: todo usuário iniciante de VBA deseja aprender como criar uma função definida pelo usuário. Você não? Diga “Sim” na seção de comentários, se você for uma daquelas pessoas que deseja criar um recurso personalizado.

Tenho o prazer de informar que este é um GUIA COMPLETO para ajudá-lo a criar sua primeira função personalizada usando VBA e, além disso, compartilhei alguns exemplos de FUNÇÕES DEFINIDAS PELO USUÁRIO para ajudar a inspirar você.

1. Aqui usarei as palavras função definida pelo usuário, função personalizada e UDF de forma intercambiável. Então fique comigo, você será uma estrela do rock do VBA nos próximos minutos.
2. Para criar código para uma função personalizada VBA, você precisa escrevê-lo, não pode gravá-lo usando o gravador de macro .

Por que você deve criar uma função personalizada do Excel

Como eu disse, existem muitas funções integradas no Excel que podem ajudá-lo a resolver quase todos os problemas e realizar todos os tipos de cálculos. Mas, às vezes, em situações específicas, você precisa criar uma UDF.

E a seguir listei alguns dos motivos ou situações em que você deve usar uma função personalizada.

1. Quando não há função para isso

Esse é um dos motivos mais comuns para criar uma UDF com VBA, pois às vezes é preciso calcular algo e não existe uma função específica para isso. Posso dar um exemplo de contagem de palavras de uma célula e para isso descobri que uma UDF pode ser uma solução perfeita.

2. Substitua uma fórmula complexa

Se você trabalha com fórmulas, tenho certeza de que sabe que fórmulas complexas são difíceis de ler e, às vezes, mais difíceis de serem entendidas por outras pessoas. Portanto, uma função personalizada pode ser uma solução para esse problema porque, depois de criar uma UDF, você não precisa escrever essa fórmula complexa repetidas vezes.

3. Quando você não quiser usar a rotina SUB

Embora você possa usar código VBA para realizar um cálculo, os códigos VBA não são dinâmicos*. Você deve executar este código novamente se quiser atualizar seu cálculo. Mas se você converter esse código em uma função, não precisará executá-lo repetidamente, pois poderá simplesmente inseri-lo como uma função.

Como criar sua primeira função definida pelo usuário no Excel

Ok, então olhe. Dividi todo o processo em três etapas:

1. Declare seu procedimento como uma função
2. Defina seus argumentos e seu tipo de dados
3. Adicione código para calcular o valor desejado

Mas deixe-me dar a você:

Você precisa criar uma função que possa retornar o nome do dia a partir de um valor de data. Bem, temos uma função que retorna o número do dia da semana, mas não o nome. Você entende o que eu estou dizendo? Sim?

Então, vamos seguir as etapas abaixo para criar sua primeira função definida pelo usuário:

1. Em primeiro lugar, abra seu editor Visual Basic usando a tecla de atalho ALT + F11 ou vá atéa aba Desenvolvedor e simplesmente clique no botão “Visual Basic”.
   
2. O próximo passo é inserir um módulo, então clique com o botão direito na janela do projeto VBA, vá em inserir e clique em “Módulo”. (ALERTA: Você deve inserir uma FUNÇÃO DEFINIDA PELO USUÁRIO apenas em módulos padrão. Ambos os módulos Sheet e ThisWorkbook são um tipo especial de módulo e se você inserir uma UDF nesses dois módulos, o Excel não reconhece que você está criando uma UDF) .
   
3. A terceira coisa é definir um nome para a função e aqui estou usando “myDayName”. Então você precisa escrever “Function mydayName”. Por que função antes do nome? Ao criar uma função VBA, usar a palavra “Função” informa ao Excel para tratar esse código como uma função (certifique-se de ler o escopo de uma UDF antes na mensagem).
4. Depois disso você precisa definir argumentos para sua função. Portanto, insira parênteses iniciais e escreva “InputDate As Date”. Aqui, InputDate é o nome do argumento e data é o seu tipo de dados. É sempre melhor definir um tipo de dados para o argumento.
   
5. Agora feche os parênteses e escreva “As String”. Aqui você define o tipo de dado do resultado retornado pela função e como deseja o nome do dia que é um texto, então seu tipo de dado deve ser “String”. Se você quiser que o resultado seja algo diferente de uma string, certifique-se de definir o tipo de dados de acordo. (myDayName (InputDate As Date) como função String).
   
6. No final, pressione ENTER. Neste ponto, o nome da sua função, seu argumento, tipo de dados do argumento e tipo de dados da função estão definidos e você tem algo como abaixo em seu módulo:
   
7. Agora em “Função” e “Função Final” você precisa definir o cálculo ou pode dizer o funcionamento desta UDF. No Excel existe uma função de planilha chamada “Texto” e estamos usando a mesma aqui. E para isso você tem que escrever o código abaixo e com este código você define o valor que deve ser retornado pela função. meuNomeDia = WorksheetFunction.Text(InputDate, “dddddd”)
   
8. Agora feche seu editor VB e volte para a planilha e na célula B2 digite “=myDayName(A2)” pressione Enter e você terá o nome do dia.
   

Parabéns! Você acabou de criar sua primeira função definida pelo usuário. Este é o momento da verdadeira alegria. Não é? Digite “Alegria” na seção de comentários.

Como esta função funciona e retorna o valor em uma célula

Sua primeira função personalizada está aqui, mas a questão é que você precisa entender como isso funciona. Se eu disser em termos simples, é um código VBA, mas você o usou como procedimento de função. Vamos dividir em três partes:

• Você o insere em uma célula como uma função e especifica o valor de entrada.
• O Excel executa o código por trás da função e usa o valor que você referenciou.
• Você tem o resultado na célula.

Mas você precisa entender como essa função funciona por dentro. Então dividi todo o processo em três partes diferentes onde você pode ver como o código que você escreveu para a função realmente funciona.

Como você especificou “InputDate” como argumento da função e quando você insere a função na célula e especifica uma data, o VBA pega esse valor de data e o fornece para a função de texto que você usou no código.

E no exemplo que citei acima, a data que você tem na célula A1 é 01/01/2019.

Depois disso, a função TEXT converte esta data em um dia usando o código de formato “dddddd” que você já mencionou no código da função. E este dia que é retornado pela função TEXT é atribuído a “myDayName”.

Assim, se o resultado da função TEXT for terça-feira, este valor será atribuído a “myDayName”.

E aqui termina a operação da função. “myDayName” é o nome da função, portanto, qualquer valor atribuído a “myDayName” será o valor do resultado e a função que você inseriu na planilha o retornará na célula.

Ao escrever código para uma função personalizada, você deve garantir que o valor retornado por esse código seja atribuído ao nome da função.

Como melhorar uma UDF para sempre

Bem, você sabe como criar uma função VBA personalizada.

AGORA…

Há uma coisa que você precisa para garantir que o código que você usou para trabalhar seja bom o suficiente para lidar com todas as possibilidades. Se você está falando sobre a função que acabou de escrever acima, você pode retornar o nome do dia a partir de uma data.

Mirar…

E se o valor que você especificou não for uma data? E se a célula a que você se referiu estiver vazia? Pode haver outras possibilidades, mas tenho certeza de que você entendeu.

CERTO? Então, vamos tentar melhorar esta função personalizada que pode corrigir os problemas acima. BOM. Primeiro, você precisa alterar o tipo de dados do argumento e usar:

 InputDate As Variant

Com isso, sua função customizada pode receber qualquer tipo de dado como entrada. Em seguida, precisamos usar a instrução VBA IF para verificar InputDate para determinadas condições. A primeira condição é se a célula está vazia ou não. E para isso você deve utilizar o código abaixo:

 If InputDate = "" Then myDayName = ""

Isso tornará a função vazia se a célula que você referenciou estiver vazia.

Um problema está resolvido, vamos passar para o próximo. Além de uma data, você pode ter um número ou texto. Então para isso você também precisa criar uma condição que deverá verificar se o valor referenciado é uma data real ou não.

O código seria:

 If IsDate(InputDate) = False Then myDateName = ""

Aqui estou usando um espaço em branco para ambas as condições para que, se você tiver dados grandes, possa filtrar facilmente valores onde o valor de entrada não é válido. Então, depois de adicionar as condições acima, o código ficaria assim:

 Function myDayName(InputDate As Variant) As String If InputDate = "" Then myDayName = "" Else If IsDate(InputDate) = False Then myDateName = "" Else myDayName = WorksheetFunction.Text(InputDate, "dddddd") End If End If End Function

E é assim que funciona agora: tenho certeza de que você ainda pode fazer alterações nesse recurso, mas tenho certeza de que entendeu claramente meu ponto.

Como usar uma função VBA personalizada

Neste ponto, você sabe aproximadamente como criar uma função VBA no Excel. Mas depois de obtê-lo, você precisa saber como usá-lo. E nesta parte do post vou compartilhar com vocês como e onde você pode utilizá-lo. Então, vamos começar.

1. Simplesmente em uma planilha

Por que estamos criando uma função personalizada? Simples. Para usá-lo na planilha. Você pode simplesmente inserir uma UDF em uma planilha usando o sinal de igual e o nome do tipo da função e, em seguida, especificar seus argumentos.

Você também pode inserir uma função definida pelo usuário na biblioteca de funções. Vá para a guia Fórmula ➜ Inserir Função ➜ Definido pelo Usuário.

Nesta lista você pode escolher a UDF que deseja inserir.

2. Usando outros subprocedimentos e funções

Você também pode usar uma função dentro de outras funções ou em um procedimento “Sub”. Abaixo está um código VBA no qual você usou a função para obter o nome do dia para a data atual.

 Sub todayDay() MsgBox "Today is " & myDayName(Date) End Sub

Certifique-se de ler “Escopo de uma UDF” posteriormente neste artigo para saber mais sobre como usar uma função em outros procedimentos.

3. Acesse funções de outra pasta de trabalho

Se você tiver uma UDF em uma pasta de trabalho e quiser usá-la em outra pasta de trabalho ou em todas as pastas de trabalho, faça isso criando um suplemento para ela. Siga estas etapas simples:

• Primeiro, você precisa salvar o arquivo (no qual possui o código da função personalizada) como um suplemento.
• Para fazer isso, vá até a aba Arquivo ➜ Salvar como ➜ “Suplementos do Excel (.xalm).

• Depois disso, clique duas vezes no add-in e instale-o.

É isso. Agora você pode usar todas as suas funções VBA em qualquer pasta de trabalho.

Diferentes maneiras de criar uma função VBA personalizada [nível avançado]

Neste ponto, você sabe como criar uma função personalizada no VBA. Mas o fato é que quando usamos funções In-Built, elas vêm com diferentes tipos de argumentos.

Portanto, nesta seção deste guia, você aprenderá como criar uma UDF com os diferentes tipos de argumentos.

• Sem qualquer argumento
• Com um único argumento
• Com vários argumentos
• Usando array como argumento

… seguindo em frente.

1. Sem argumentos

Lembra de funções como AGORA e HOJE, nas quais você não precisa inserir um argumento?

Sim. Você pode criar uma função definida pelo usuário onde não precisa inserir nenhum argumento. Vamos fazer isso com um exemplo:

Vamos criar uma função personalizada que possa retornar a localização do arquivo atual. E aqui está o código:

 Function myPath() As String Dim myLocation As String Dim myName As String myLocation = ActiveWorkbook.FullName myName = ActiveWorkbook.Name If myLocation = myName Then myPath = "File is not saved yet." Else myPath = myLocation End If End Function

Esta função retorna o caminho do local onde o arquivo atual está armazenado e se a pasta de trabalho não estiver armazenada em nenhum lugar, mostrará uma mensagem dizendo “O arquivo ainda não foi salvo”.

Agora, se você prestar muita atenção ao código desta função, não precisará definir um argumento (entre parênteses). Você acabou de definir o tipo de dados para o resultado da função.

A regra básica para criar uma função sem argumentos é um código no qual você não precisa digitar nada.

Simplificando, o valor que você deseja obter da função deve ser calculado automaticamente.

E nesta função você tem a mesma coisa.

Este ActiveWorkbook.FullName retorna a localização do arquivo e este ActiveWorkbook.Name retorna o nome. Você não precisa inserir nada.

2. Com um único argumento

Já abordamos isso aprendendo como criar uma função definida pelo usuário. Mas vamos nos aprofundar um pouco mais e criar uma função diferente. Esta é a função que criei há alguns meses para extrair a URL de um hiperlink .

 Function giveMeURL(rng As Range) As String On Error Resume Next giveMeURL = rng.Hyperlinks(1).Address End Function

Agora nesta função você só tem um argumento.

Quando você insere isso em uma célula e especifica a célula onde há um hiperlink, ele retornará a URL do hiperlink. Agora nesta função o trabalho principal é feito por:

 rng.Hyperlinks(1).Address

Mas o rng é o que você precisa especificar. Diga “Fácil” na seção de comentários se achar fácil criar uma UDF.

3. Com vários argumentos

Normalmente, a maioria das funções integradas do Excel possuem vários argumentos. Portanto é essencial que você aprenda como criar uma função customizada com múltiplos argumentos.

Vejamos um exemplo: você deseja remover letras específicas de uma sequência de texto e deseja manter o resto da parte.

Bem, você tem funções como RIGHT e LEN que usará nesta função personalizada. Mas aqui não precisamos disso. Tudo o que precisamos é de uma função personalizada usando VBA.

Então, aqui está a função:

 Function removeFirstC(rng As String, cnt As Long) As String removeFirstC = Right(rng, Len(rng) - cnt) End Functio

Ok, então olhe:

Nesta função você tem dois argumentos:

• rng: Neste argumento, você deve especificar a célula da qual deseja remover o primeiro caractere de um texto.
• cnt: E no argumento você precisa especificar o número de caracteres a serem removidos (se quiser remover vários caracteres do texto).

Quando você insere em uma célula, funciona como abaixo:

3.1 Criando uma função definida pelo usuário com argumentos opcionais e obrigatórios

Se você pensar na função que acabamos de criar no exemplo acima, onde você tem dois argumentos diferentes, bem, ambos são necessários. E, se você perder alguma dessas opções, receberá um erro como este.

Agora se você pensar logicamente, a função que criamos é remover o primeiro caractere. Mas aqui você precisa especificar o número de caracteres a serem removidos. Portanto, meu argumento é que esse argumento deve ser opcional e deve considerar um como padrão.

O que você acha?

Diga “Sim” na seção de comentários se você concorda comigo nisso.

Ok, então olhe. Para tornar um argumento opcional, basta adicionar “Opcional” antes dele. Bem desse jeito:

Mas o importante é fazer o seu código funcionar com ou sem o valor deste argumento. Portanto, nosso novo código para a mesma função ficaria assim: Agora no código, se você ignorar a especificação do segundo argumento.

4. Use Array como argumento

Existem algumas funções integradas que podem receber argumentos de array e você também pode criar sua função VBA personalizada para fazer isso.

Vamos fazer isso com um exemplo simples onde você precisa criar uma função onde você soma os valores de um intervalo onde você tem números e texto. Aqui estamos.

 Function addNumbers(CellRef As Range) Dim Cell As Range For Each Cell In CellRef If IsNumeric(Cell.Value) = True Then Result = Result + Cell.Value End If Next Cell addNumbers = Result End Function

No código da função acima, usamos um intervalo de números inteiros A1:A10 em vez de um único valor ou referência de célula.

Usando o loop FOR EACH, ele verificará cada célula do intervalo e somará o valor se a célula contiver um número.

O escopo de uma função definida pelo usuário

Em termos simples, o escopo de uma função significa se ela pode ser chamada a partir de outros procedimentos ou não. Uma FDU pode ter dois tipos diferentes de escopos.

1. Público

Você pode tornar pública sua função personalizada para poder chamá-la em todas as planilhas da pasta de trabalho. Para tornar uma função pública, basta utilizar a palavra “Público”, conforme abaixo.

Mas uma função é pública por padrão se você não a tornar privada. Em todos os exemplos que cobrimos, todos eles são públicos.

2. Privado

Ao tornar uma função privada, você pode usá-la em procedimentos no mesmo módulo.

Digamos que se você tiver sua UDF no “Módulo1”, você só poderá utilizá-la nos procedimentos que tiver no “Módulo1”. E não aparecerá na lista de funções da planilha (quando você usar o sinal = e tentar digitar o nome), mas ainda poderá usá-lo digitando seu nome e especificando argumentos.

Limitações da função definida pelo usuário [UDF]

UDFs são muito úteis. Mas eles são limitados em certas situações. Aqui estão algumas coisas que quero que você observe e lembre ao criar uma função personalizada no VBA.

• Você não pode editar, excluir ou formatar células e intervalos usando uma função personalizada.
• Também não é possível mover, renomear, excluir ou adicionar planilhas a uma pasta de trabalho.
• Altere o valor de outra célula.
• Também não pode alterar as opções de ambiente.

Existe uma diferença entre uma função interna e uma função definida pelo usuário?

Estou feliz que você perguntou. Bem, para responder a essa pergunta, quero compartilhar alguns pontos que considero importantes para você.

1. Mais lento que o integrado: se você comparar a velocidade das funções integradas e das funções VBA, descobrirá que a primeira é rápida. A razão por trás disso é que as funções integradas são escritas em C++ ou FORTRAN.
2. Dificuldade para compartilhar arquivos: Frequentemente compartilhamos arquivos por e-mail e pela nuvem. Se você estiver usando alguma das funções personalizadas, precisará compartilhar esse arquivo no formato “xlam” para que outra pessoa também possa usar sua função personalizada.

Mas como eu disse acima em “Por que você deve criar uma função personalizada do Excel”, existem situações específicas em que você pode optar por uma função VBAcustom.

Conclusão

Criar uma função definida pelo usuário é simples. Tudo o que você precisa fazer é usar “Função” antes do nome para defini-lo como uma função, adicionar argumentos, definir o tipo de dados dos argumentos e, em seguida, definir o tipo de dados para o valor de retorno.

No final, adicione código para calcular o valor que deseja obter da função. Este guia que compartilhei com você hoje é o mais fácil para aprender como criar uma função personalizada no VBA e tenho certeza que você o achou útil.

Mas agora me diga uma coisa.

UDFs são úteis, o que você acha?

Por favor, compartilhe suas opiniões comigo na seção de comentários. Eu adoraria ouvir de você e não se esqueça de compartilhar esta postagem com seus amigos, tenho certeza que eles irão gostar.

Tutoriais relacionados

• Lista de funções VBA – explicadas com exemplos
• Como executar uma macro no Excel
• Como usar a instrução IF em VBA
• Como trabalhar com intervalos e células em VBA
• Adicione uma nova planilha usando código VBA