Criação de uma função definida pelo usuário (UDF) no Excel VBA (guia definitivo)

Com o VBA, você pode criar uma função personalizada (também chamada de função definida pelo usuário) que pode ser usada nas planilhas como funções regulares.

Eles são úteis quando as funções existentes do Excel não são suficientes. Nesses casos, você pode criar sua própria função definida pelo usuário (UDF) personalizada para atender às suas necessidades específicas.

Neste tutorial, vou cobrir tudo sobre como criar e usar funções personalizadas no VBA.

Se você estiver interessado em aprender VBA da maneira mais fácil, confira meu Treinamento Online Excel VBA.

O que é um procedimento de função no VBA?

Um procedimento Function é um código VBA que realiza cálculos e retorna um valor (ou uma matriz de valores).

Usando um procedimento Function, você pode criar uma função que pode ser usada na planilha (como qualquer função regular do Excel, como SUM ou VLOOKUP).

Depois de criar um procedimento Function usando o VBA, você pode usá-lo de três maneiras:

1. Como uma fórmula na planilha, onde pode tomar argumentos como entradas e retornar um valor ou uma matriz de valores.
2. Como parte de seu código de sub-rotina VBA ou outro código de função.
3. Em formatação condicional.

Embora já existam mais de 450 funções embutidas do Excel disponíveis na planilha, você pode precisar de uma função personalizada se:

• As funções integradas não podem fazer o que você deseja. Nesse caso, você pode criar uma função personalizada com base em seus requisitos.
• As funções integradas podem realizar o trabalho, mas a fórmula é longa e complicada. Nesse caso, você pode criar uma função personalizada que seja fácil de ler e usar.

Observe que as funções personalizadas criadas com o VBA podem ser significativamente mais lentas do que as funções integradas. Portanto, eles são mais adequados para situações em que você não pode obter o resultado usando as funções integradas.

Função vs. Subrotina em VBA

Uma 'sub-rotina' permite que você execute um conjunto de códigos enquanto uma 'Função' retorna um valor (ou uma matriz de valores).

Para dar um exemplo, se você tem uma lista de números (positivos e negativos) e deseja identificar os números negativos, aqui está o que você pode fazer com uma função e uma sub-rotina.

Uma sub-rotina pode percorrer cada célula do intervalo e destacar todas as células que possuem um valor negativo. Nesse caso, a sub-rotina acaba alterando as propriedades do objeto intervalo (alterando a cor das células).

Com uma função personalizada, você pode usá-la em uma coluna separada e pode retornar um TRUE se o valor em uma célula for negativo e FALSE se for positivo. Com uma função, você não pode alterar as propriedades do objeto. Isso significa que você não pode alterar a cor da célula com uma função em si (no entanto, você pode fazer isso usando a formatação condicional com a função personalizada).

Ao criar uma função definida pelo usuário (UDF) usando o VBA, você pode usar essa função na planilha como qualquer outra função. Abordarei mais sobre isso na seção "Diferentes maneiras de usar uma função definida pelo usuário no Excel".

Criação de uma função simples definida pelo usuário no VBA

Deixe-me criar uma função simples definida pelo usuário no VBA e mostrar como ela funciona.

O código a seguir cria uma função que extrairá as partes numéricas de uma string alfanumérica.

Função GetNumeric (CellRef As String) as Long Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Result & Mid (CellRef, i, 1) Next i GetNumeric = Função Final do Resultado

Quando você tiver o código acima no módulo, poderá usar esta função na pasta de trabalho.

Abaixo está como esta função - GetNumeric - pode ser usado no Excel.

Antes de eu contar como essa função é criada no VBA e como ela funciona, há algumas coisas que você deve saber:

• Quando você cria uma função no VBA, ela se torna disponível em toda a pasta de trabalho, como qualquer outra função regular.
• Quando você digita o nome da função seguido pelo sinal de igual, o Excel mostra o nome da função na lista de funções correspondentes. No exemplo acima, quando eu digitei = Get, o Excel me mostrou uma lista que tinha minha função personalizada.

Acredito que este seja um bom exemplo de quando você pode usar o VBA para criar uma função simples de usar no Excel. Você também pode fazer a mesma coisa com uma fórmula (como mostrado neste tutorial), mas isso se torna complicado e difícil de entender. Com esta UDF, você só precisa passar um argumento e obterá o resultado.

Anatomia de uma função definida pelo usuário em VBA

Na seção acima, forneci o código e mostrei como a função UDF funciona em uma planilha.

Agora vamos mergulhar fundo e ver como essa função é criada. Você precisa colocar o código abaixo em um módulo no Editor VB. Abordo este tópico na seção - ‘Onde colocar o código VBA para uma função definida pelo usuário’.

Função GetNumeric (CellRef As String) as Long 'Esta função extrai a parte numérica da string Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Result & Mid (CellRef, i, 1) Next i GetNumeric = Result End Function

A primeira linha do código começa com a palavra - Função.

Esta palavra diz ao VBA que nosso código é uma função (e não uma sub-rotina). A palavra Função é seguida pelo nome da função - GetNumeric. Este é o nome que usaremos na planilha para usar esta função.

• O nome da função não pode conter espaços. Além disso, você não pode nomear uma função se ela conflitar com o nome de uma referência de célula. Por exemplo, você não pode nomear a função ABC123, pois também se refere a uma célula na planilha do Excel.
• Você não deve dar à sua função o mesmo nome de uma função existente. Se você fizer isso, o Excel dará preferência à função embutida.
• Você pode usar um sublinhado se quiser separar palavras. Por exemplo, Get_Numeric é um nome aceitável.

O nome da função é seguido por alguns argumentos entre parênteses. Esses são os argumentos que nossa função precisa do usuário. Esses são exatamente como os argumentos que precisamos fornecer às funções integradas do Excel. Por exemplo, em uma função CONT.SE, existem dois argumentos (intervalo e critérios)

Entre parênteses, você precisa especificar os argumentos.

Em nosso exemplo, há apenas um argumento - CellRef.

Também é uma boa prática especificar que tipo de argumento a função espera. Neste exemplo, uma vez que alimentaremos a função com uma referência de célula, podemos especificar o argumento como um tipo 'Intervalo'. Se você não especificar um tipo de dados, o VBA irá considerá-lo uma variante (o que significa que você pode usar qualquer tipo de dados).

Se você tiver mais de um argumento, poderá especificá-los no mesmo parêntese - separados por vírgula. Veremos posteriormente neste tutorial como usar vários argumentos em uma função definida pelo usuário.

Observe que a função é especificada como o tipo de dados ‘String’. Isso diria ao VBA que o resultado da fórmula seria do tipo de dados String.

Embora eu possa usar um tipo de dados numérico aqui (como Longo ou Duplo), fazer isso limitaria o intervalo de números que ele pode retornar. Se eu tiver uma string longa de 20 números que preciso extrair da string geral, declarar a função como Long ou Double daria um erro (já que o número estaria fora de seu intervalo). Portanto, mantive o tipo de dados de saída da função como String.

A segunda linha do código - aquela em verde que começa com um apóstrofo - é um comentário. Ao ler o código, o VBA ignora esta linha. Você pode usar isso para adicionar uma descrição ou um detalhe sobre o código.

A terceira linha do código declara uma variável ‘StringLength’ como um tipo de dados Integer. Esta é a variável onde armazenamos o valor do comprimento da string que está sendo analisada pela fórmula.

A quarta linha declara a variável Result como um tipo de dados String. Esta é a variável de onde extrairemos os números da string alfanumérica.

A quinta linha atribui o comprimento da string no argumento de entrada à variável ‘StringLength’. Observe que ‘CellRef’ se refere ao argumento que será fornecido pelo usuário ao usar a fórmula na planilha (ou usá-la no VBA - que veremos mais tarde neste tutorial).

A sexta, a sétima e a oitava linhas são a parte do loop For Next. O loop é executado tantas vezes quantos caracteres houver no argumento de entrada. Este número é fornecido pela função LEN e é atribuído à variável ‘StringLength’.

Portanto, o loop vai de '1 a Stringlength'.

Dentro do loop, a instrução IF analisa cada caractere da string e, se for numérico, adiciona esse caractere numérico à variável Result. Ele usa a função MID no VBA para fazer isso.

A penúltima linha do código atribui o valor do resultado à função. É essa linha de código que garante que a função retorne o valor ‘Resultado’ de volta à célula (de onde é chamada).

A última linha do código é End Function. Esta é uma linha de código obrigatória que informa ao VBA que o código da função termina aqui.

O código acima explica as diferentes partes de uma função personalizada típica criada no VBA. Nas seções a seguir, vamos nos aprofundar nesses elementos e também ver as diferentes maneiras de executar a função VBA no Excel.

Argumentos em uma função definida pelo usuário em VBA

Nos exemplos acima, onde criamos uma função definida pelo usuário para obter a parte numérica de uma string alfanumérica (GetNumeric), a função foi projetada para receber um único argumento.

Nesta seção, abordarei como criar funções que não aceitam nenhum argumento do que aquelas que aceitam vários argumentos (exigidos, bem como argumentos opcionais).

Criando uma função no VBA sem nenhum argumento

Na planilha do Excel, temos várias funções que não aceitam argumentos (como RAND, HOJE, AGORA).

Essas funções não dependem de nenhum argumento de entrada. Por exemplo, a função TODAY retornaria a data atual e a função RAND retornaria um número aleatório entre 0 e 1.

Você também pode criar uma função semelhante no VBA.

Abaixo está o código que fornecerá o nome do arquivo. Não leva nenhum argumento, pois o resultado que precisa ser retornado não depende de nenhum argumento.

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

O código acima especifica o resultado da função como um tipo de dados String (como o resultado que queremos é o nome do arquivo - que é uma string).

Esta função atribui o valor de ‘ThisWorkbook.Name’ à função, que é retornado quando a função é usada na planilha.

Se o arquivo foi salvo, ele retorna o nome com a extensão do arquivo, caso contrário, simplesmente fornece o nome.

No entanto, o acima exposto tem um problema.

Se o nome do arquivo mudar, ele não será atualizado automaticamente. Normalmente, uma função é atualizada sempre que há uma mudança nos argumentos de entrada. Mas como não há argumentos nesta função, a função não recalcula (mesmo se você alterar o nome da pasta de trabalho, feche-a e reabra novamente).

Se desejar, você pode forçar um recálculo usando o atalho de teclado - Control + Alt + F9.

Para fazer com que a fórmula seja recalculada sempre que houver uma alteração na planilha, é necessário inserir uma linha de código nela.

O código a seguir faz com que a função recalcule sempre que houver uma alteração na planilha (assim como outras funções de planilha semelhantes, como TODAY ou função RAND).

Function WorkbookName () As String Application.Volatile True WorkbookName = ThisWorkbook.Name End Function

Agora, se você alterar o nome da pasta de trabalho, esta função será atualizada sempre que houver qualquer alteração na planilha ou quando você reabrir esta pasta de trabalho.

Criando uma função em VBA com um argumento

Em uma das seções acima, já vimos como criar uma função que leva apenas um argumento (a função GetNumeric abordada acima).

Vamos criar outra função simples que leva apenas um argumento.

A função criada com o código a seguir converterá o texto referenciado em maiúsculas. Agora já temos uma função para ele no Excel, e essa função serve apenas para mostrar como funciona. Se você tiver que fazer isso, é melhor usar a função UPPER embutida.

Função ConvertToUpperCase (CellRef As Range) ConvertToUpperCase = UCase (CellRef) End Function

Esta função usa a função UCase em VBA para alterar o valor da variável CellRef. Em seguida, ele atribui o valor à função ConvertToUpperCase.

Uma vez que esta função tem um argumento, não precisamos usar a parte Application.Volatile aqui. Assim que o argumento mudar, a função será atualizada automaticamente.

Criando uma função no VBA com vários argumentos

Assim como as funções de planilha, você pode criar funções no VBA que usam vários argumentos.

O código abaixo criaria uma função que extrairá o texto antes do delimitador especificado. Leva dois argumentos - a referência de célula que contém a string de texto e o delimitador.

Função GetDataBeforeDelimiter (CellRef As Range, Delim As String) as String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Result = Left (CellRef, DelimPosition) GetDataBeforeDelimiter = Result Função

Quando você precisa usar mais de um argumento em uma função definida pelo usuário, você pode ter todos os argumentos entre parênteses, separados por uma vírgula.

Observe que, para cada argumento, você pode especificar um tipo de dados. No exemplo acima, ‘CellRef’ foi declarado como um tipo de dados de intervalo e ‘Delim’ foi declarado como um tipo de dados String. Se você não especificar nenhum tipo de dados, o VBA considera que se trata de um tipo de dados variante.

Ao usar a função acima na planilha, você precisa fornecer a referência da célula que tem o texto como o primeiro argumento e o (s) caractere (s) delimitador (es) entre aspas duplas como o segundo argumento.

Em seguida, ele verifica a posição do delimitador usando a função INSTR no VBA. Esta posição é então usada para extrair todos os caracteres antes do delimitador (usando a função ESQUERDA).

Finalmente, ele atribui o resultado à função.

Esta fórmula está longe de ser perfeita. Por exemplo, se você inserir um delimitador que não seja encontrado no texto, ocorrerá um erro. Agora você pode usar a função IFERROR na planilha para se livrar dos erros, ou você pode usar o código abaixo que retorna o texto inteiro quando não consegue encontrar o delimitador.

Função GetDataBeforeDelimiter (CellRef As Range, Delim As String) as String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 If DelimPosition <0 Then DelimPosition = Len (CellRef) Resultado = Left = ( CellRef, DelimPosition) GetDataBeforeDelimiter = Result End Function

Podemos otimizar ainda mais essa função.

Se você inserir o texto (do qual deseja extrair a parte antes do delimitador) diretamente na função, ocorrerá um erro. Vá em frente … experimente!

Isso acontece porque especificamos 'CellRef' como um tipo de dados de intervalo.

Ou, se você quiser que o delimitador esteja em uma célula e use a referência da célula em vez de codificá-lo na fórmula, você não pode fazer isso com o código acima. É porque o Delim foi declarado como um tipo de dados de string.

Se você deseja que a função tenha flexibilidade para aceitar a entrada de texto direta ou referências de células do usuário, é necessário remover a declaração do tipo de dados. Isso acabaria tornando o argumento um tipo de dado variante, que pode pegar qualquer tipo de argumento e processá-lo.

O código abaixo faria isso:

Função GetDataBeforeDelimiter (CellRef, Delim) As String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 If DelimPosition <0 Then DelimPosition = Len (CellRef) Resultado = Left (CellRef, DelimPosition) GetDataBeforeDelimiter = Função Final do Resultado

Criando uma função no VBA com argumentos opcionais

Existem muitas funções no Excel em que alguns dos argumentos são opcionais.

Por exemplo, a lendária função VLOOKUP tem 3 argumentos obrigatórios e um argumento opcional.

Um argumento opcional, como o nome sugere, é opcional para especificar. Se você não especificar um dos argumentos obrigatórios, sua função apresentará um erro, mas se você não especificar o argumento opcional, sua função funcionará.

Mas os argumentos opcionais não são inúteis. Eles permitem que você escolha entre uma variedade de opções.

Por exemplo, na função PROCV, se você não especificar o quarto argumento, PROCV fará uma pesquisa aproximada e se você especificar o último argumento como FALSO (ou 0), ele fará uma correspondência exata.

Lembre-se de que os argumentos opcionais sempre devem vir depois de todos os argumentos obrigatórios. Você não pode ter argumentos opcionais no início.

Agora vamos ver como criar uma função em VBA com argumentos opcionais.

Função com apenas um argumento opcional

Até onde eu sei, não há função embutida que leva apenas argumentos opcionais (posso estar errado aqui, mas não consigo pensar em nenhuma função).

Mas podemos criar um com VBA.

Abaixo está o código da função que fornecerá a data atual no formato dd-mm-aaaa se você não inserir nenhum argumento (ou seja, deixe em branco) e no formato “dd mmmm, aaaa” se você inserir qualquer coisa como o argumento (ou seja, qualquer coisa para que o argumento não fique em branco).

Função CurrDate (opcional fmt como variante) Dim Result If IsMissing (fmt) Then CurrDate = Formato (Data, "dd-mm-aaaa") Else CurrDate = Formato (Data, "dd mmmm, aaaa") End If End Function

Observe que a função acima usa ‘IsMissing’ para verificar se o argumento está ausente ou não. Para usar a função IsMissing, seu argumento opcional deve ser do tipo de dados variante.

A função acima funciona independentemente do que você inserir como argumento. No código, apenas verificamos se o argumento opcional é fornecido ou não.

Você pode tornar isso mais robusto tomando apenas valores específicos como argumentos e mostrando um erro no restante dos casos (conforme mostrado no código a seguir).

Função CurrDate (opcional fmt como variante) Dim Result If IsMissing (fmt) Then CurrDate = Format (Date, "dd-mm-aaaa") ElseIf fmt = 1 Then CurrDate = Format (Date, "dd mmmm, aaaa") Else CurrDate = CVErr (xlErrValue) End If End Function

O código acima cria uma função que mostra a data no formato “dd-mm-aaaa” se nenhum argumento for fornecido, e no formato “dd mmmm, aaaa” quando o argumento é 1. Fornece um erro em todos os outros casos.

Função com argumentos obrigatórios e opcionais

Já vimos um código que extrai a parte numérica de uma string.

Agora, vamos dar uma olhada em um exemplo semelhante que leva tanto os argumentos obrigatórios quanto os opcionais.

O código a seguir cria uma função que extrai a parte do texto de uma string. Se o argumento opcional for TRUE, ele fornecerá o resultado em maiúsculas, e se o argumento opcional for FALSE ou for omitido, ele fornecerá o resultado como está.

Função GetText (CellRef As Range, Optional TextCase = False) As String Dim StringLength As Integer Dim Result As String StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Then Result = Result & Mid (CellRef, i, 1) Next i If TextCase = True Then Result = UCase (Result) GetText = Result End Function

Observe que no código acima, inicializamos o valor de ‘TextCase’ como False (veja entre parênteses na primeira linha).

Fazendo isso, garantimos que o argumento opcional comece com o valor padrão, que é FALSE. Se o usuário especificar o valor como TRUE, a função retornará o texto em maiúsculas e se o usuário especificar o argumento opcional como FALSE ou omiti-lo, o texto retornado será como está.

Criando uma função em VBA com uma matriz como argumento

Até agora, vimos exemplos de criação de uma função com argumentos opcionais / obrigatórios - onde esses argumentos eram um único valor.

Você também pode criar uma função que pode usar uma matriz como argumento. Nas funções de planilha do Excel, há muitas funções que aceitam argumentos de matriz, como SOMA, PROCV, SOMASE, CONT.SE, etc.

Abaixo está o código que cria uma função que fornece a soma de todos os números pares no intervalo especificado das células.

Função AddEven (CellRef as Range) Dim Cell como o intervalo de cada célula em CellRef If IsNumeric (Cell.Value) Then If Cell.Value Mod 2 = 0 Then Result = Result + Cell.Value End If End If Next Cell AddEven = Result End Função

Você pode usar esta função na planilha e fornecer o intervalo de células que têm os números como argumento. A função retornaria um único valor - a soma de todos os números pares (conforme mostrado abaixo).

Na função acima, em vez de um único valor, fornecemos um array (A1: A10). Para que isso funcione, você precisa ter certeza de que o tipo de dados do argumento pode aceitar uma matriz.

No código acima, especifiquei o argumento CellRef como Range (que pode receber uma matriz como entrada). Você também pode usar o tipo de dados variante aqui.

No código, há um loop For Each que passa por cada célula e verifica se é um número de não. Se não estiver, nada acontece e ele passa para a próxima célula. Se for um número, ele verifica se é par ou não (usando a função MOD).

No final, todos os números pares são somados e a soma é atribuída de volta à função.

Criando uma função com número indefinido de argumentos

Ao criar algumas funções no VBA, você pode não saber o número exato de argumentos que um usuário deseja fornecer. Portanto, a necessidade é criar uma função que possa aceitar quantos argumentos forem fornecidos e usá-los para retornar o resultado.

Um exemplo dessa função de planilha é a função SUM. Você pode fornecer vários argumentos para ele (como este):

= SOMA (A1, A2: A4, B1: B20)

A função acima adicionaria os valores em todos esses argumentos. Além disso, observe que podem ser uma única célula ou uma matriz de células.

Você pode criar tal função no VBA tendo o último argumento (ou pode ser o único argumento) como opcional. Além disso, este argumento opcional deve ser precedido pela palavra-chave ‘ParamArray’.

‘ParamArray’ é um modificador que permite aceitar quantos argumentos desejar. Observe que o uso da palavra ParamArray antes de um argumento torna o argumento opcional. No entanto, você não precisa usar a palavra opcional aqui.

Agora vamos criar uma função que pode aceitar um número arbitrário de argumentos e adicionaria todos os números nos argumentos especificados:

Função AddArguments (ParamArray arglist () As Variant) Para Cada arg Em arglist AddArguments = AddArguments + arg Próximo arg End Function

A função acima pode receber qualquer número de argumentos e adicionar esses argumentos para fornecer o resultado.

Observe que você só pode usar um único valor, uma referência de célula, um booleano ou uma expressão como argumento. Você não pode fornecer uma matriz como argumento. Por exemplo, se um de seus argumentos for D8: D10, esta fórmula apresentará um erro.

Se você deseja ser capaz de usar os dois argumentos de várias células, você precisa usar o código a seguir:

Função AddArguments (ParamArray arglist () As Variant) Para Cada arg Em arglist Para Cada Célula Em arg AddArguments = AddArguments + Cell Próxima Célula Próxima arg End Function

Observe que essa fórmula funciona com várias células e referências de matriz; no entanto, ela não pode processar valores ou expressões codificados permanentemente. Você pode criar uma função mais robusta verificando e tratando essas condições, mas essa não é a intenção aqui.

A intenção aqui é mostrar como o ParamArray funciona para que você possa permitir um número indefinido de argumentos na função. Caso você queira uma função melhor do que a criada pelo código acima, use a função SUM da planilha.

Criando uma função que retorna um array

Até agora, vimos funções que retornam um único valor.

Com o VBA, você pode criar uma função que retorna uma variante que pode conter uma matriz inteira de valores.

Fórmulas de matriz também estão disponíveis como funções integradas em planilhas do Excel. Se você está familiarizado com fórmulas de matriz no Excel, sabe que elas são inseridas usando Control + Shift + Enter (em vez de apenas Enter). Você pode ler mais sobre fórmulas de matriz aqui. Se você não sabe sobre fórmulas de matriz, não se preocupe, continue lendo.

Vamos criar uma fórmula que retorna uma matriz de três números (1,2,3).

O código abaixo faria isso.

Função ThreeNumbers () As Variant Dim NumberValue (1 a 3) NumberValue (1) = 1 NumberValue (2) = 2 NumberValue (3) = 3 ThreeNumbers = NumberValue Função final

No código acima, especificamos a função ‘ThreeNumbers’ como uma variante. Isso permite que ele mantenha uma matriz de valores.

A variável ‘NumberValue’ é declarada como uma matriz com 3 elementos. Ele contém os três valores e os atribui à função ‘ThreeNumbers’.

Você pode usar esta função na planilha inserindo a função e pressionando a tecla Control + Shift + Enter (segure as teclas Control e Shift e pressione Enter).

Quando você fizer isso, ele retornará 1 na célula, mas, na realidade, contém todos os três valores. Para verificar isso, use a fórmula abaixo:

= MAX (três números ())

Use a função acima com Control + Shift + Enter. Você notará que o resultado agora é 3, pois é o maior valor no array retornado pela função Max, que obtém os três números como resultado de nossa função definida pelo usuário - ThreeNumbers.

Você pode usar a mesma técnica para criar uma função que retorna uma matriz de nomes de meses, conforme mostrado pelo código a seguir:

Função Meses () As Variant Dim MonthName (1 a 12) MonthName (1) = "Janeiro" MonthName (2) = "Fevereiro" MonthName (3) = "Março" MonthName (4) = "Abril" MonthName (5) = "Maio" MonthName (6) = "Junho" MonthName (7) = "Julho" MonthName (8) = "Agosto" MonthName (9) = "Setembro" MonthName (10) = "Outubro" MonthName (11) = "Novembro "MonthName (12) =" Dezembro "Meses = Função Final de MonthName

Agora, quando você inserir a função = Meses () na planilha do Excel e usar Control + Shift + Enter, ela retornará toda a matriz de nomes de meses. Observe que você vê apenas janeiro na célula, pois é o primeiro valor na matriz. Isso não significa que a matriz está retornando apenas um valor.

Para mostrar que ele está retornando todos os valores, faça isso - selecione a célula com a fórmula, vá para a barra de fórmulas, selecione a fórmula inteira e pressione F9. Isso mostrará todos os valores que a função retorna.

Você pode usar isso usando a fórmula INDEX abaixo para obter uma lista de todos os nomes dos meses de uma vez.

= INDEX (meses (), ROW ())

Agora, se você tem muitos valores, não é uma boa prática atribuir esses valores um por um (como fizemos acima). Em vez disso, você pode usar a função Array no VBA.

Portanto, o mesmo código em que criamos a função ‘Meses’ ficaria mais curto, conforme mostrado abaixo:

Função Meses () Como Variante Meses = Matriz ("Janeiro", "Fevereiro", "Março", "Abril", "Maio", "Junho", _ "Julho", "Agosto", "Setembro", "Outubro" , "Novembro", "dezembro") Função final

A função acima usa a função Array para atribuir os valores diretamente à função.

Observe que todas as funções criadas acima retornam uma matriz horizontal de valores. Isso significa que se você selecionar 12 células horizontais (digamos A1: L1) e inserir a fórmula = Meses () na célula A1, você receberá todos os nomes dos meses.

Mas e se você quiser esses valores em um intervalo vertical de células.

Você pode fazer isso usando a fórmula TRANSPOSE na planilha.

Basta selecionar 12 células verticais (contíguas) e inserir a fórmula abaixo.

Compreendendo o escopo de uma função definida pelo usuário no Excel

Uma função pode ter dois escopos - Público ou Privado.

• UMA Âmbito público significa que a função está disponível para todas as planilhas da pasta de trabalho, bem como todos os procedimentos (Sub e Função) em todos os módulos da pasta de trabalho. Isso é útil quando você deseja chamar uma função de uma sub-rotina (veremos como isso é feito na próxima seção).
• UMA Escopo privado significa que a função está disponível apenas no módulo em que existe. Você não pode usá-lo em outros módulos. Você também não o verá na lista de funções da planilha. Por exemplo, se o nome da função for ‘Meses ()’, e você digitar função no Excel (após o sinal =), ele não mostrará o nome da função. Você pode, no entanto, ainda usá-lo se inserir o nome da fórmula.

Se você não especificar nada, a função é pública por padrão.

Abaixo está uma função que é privada:

Função privada WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Você pode usar esta função nas sub-rotinas e procedimentos nos mesmos módulos, mas não pode usá-la em outros módulos. Esta função também não apareceria na planilha.

O código abaixo tornaria esta função pública. Ele também aparecerá na planilha.

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Diferentes maneiras de usar uma função definida pelo usuário no Excel

Depois de criar uma função definida pelo usuário no VBA, você pode usá-la de muitas maneiras diferentes.

Vamos primeiro cobrir como usar as funções na planilha.

Usando UDFs em planilhas

Já vimos exemplos de como usar uma função criada em VBA na planilha.

Tudo o que você precisa fazer é inserir o nome da função e ele aparecerá no intellisense.

Observe que para que a função apareça na planilha, ela deve ser uma função Pública (conforme explicado na seção acima).

Você também pode usar a caixa de diálogo Inserir Função para inserir a função definida pelo usuário (usando as etapas abaixo). Isso funcionaria apenas para funções que são públicas.

• Vá para a guia Dados.
• Clique na opção ‘Inserir função’.
• Na caixa de diálogo Inserir Função, selecione Definido pelo Usuário como a categoria. Esta opção aparece apenas quando você tem uma função no Editor VB (e a função é Pública).
• Selecione a função na lista de todas as funções definidas pelo usuário público.
• Clique no botão Ok.

As etapas acima inserem a função na planilha. Ele também exibe uma caixa de diálogo Argumentos de função que fornecerá detalhes sobre os argumentos e o resultado.

Você pode usar uma função definida pelo usuário como qualquer outra função do Excel. Isso também significa que você pode usá-lo com outras funções integradas do Excel. Por exemplo. a fórmula abaixo forneceria o nome da pasta de trabalho em maiúsculas:

= SUPERIOR (Nome da pasta de trabalho ())

Usando funções definidas pelo usuário em procedimentos e funções do VBA

Depois de criar uma função, você também pode usá-la em outros subprocedimentos.

Se a função for Pública, ela pode ser usada em qualquer procedimento no mesmo módulo ou em um módulo diferente. Se for privado, só pode ser usado no mesmo módulo.

Abaixo está uma função que retorna o nome da pasta de trabalho.

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

O procedimento a seguir chama a função e exibe o nome em uma caixa de mensagem.

Sub ShowWorkbookName () MsgBox WorkbookName End Sub

Você também pode chamar uma função de outra função.

Nos códigos a seguir, o primeiro código retorna o nome da pasta de trabalho e o segundo retorna o nome em maiúsculas chamando a primeira função.

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Função WorkbookNameinUpper () WorkbookNameinUpper = UCase (WorkbookName) Função final

Chamando uma função definida pelo usuário a partir de outras pastas de trabalho

Se você tiver uma função em uma pasta de trabalho, poderá chamá-la também em outras pastas de trabalho.

Existem várias maneiras de fazer isso:

1. Criação de um suplemento
2. Função de salvamento na pasta de trabalho macro pessoal
3. Referenciando a função de outra pasta de trabalho.

Criação de um suplemento

Ao criar e instalar um suplemento, você terá a função personalizada disponível em todas as pastas de trabalho.

Suponha que você tenha criado uma função personalizada - ‘GetNumeric’ e a queira em todas as pastas de trabalho. Para fazer isso, crie uma nova pasta de trabalho e tenha o código da função em um módulo nesta nova pasta de trabalho.

Agora siga as etapas abaixo para salvá-lo como um suplemento e instale-o no Excel.

• Vá para a guia Arquivo e clique em Salvar como.
• Na caixa de diálogo Salvar como, altere o tipo ‘Salvar como’ para .xlam. O nome que você atribui ao arquivo seria o nome do seu suplemento. Neste exemplo, o arquivo é salvo com o nome GetNumeric.
  • Você notará que o caminho do arquivo onde ele é salvo muda automaticamente. Você pode usar o padrão ou alterá-lo se desejar.
• Abra uma nova pasta de trabalho do Excel e vá para a guia Desenvolvedor.
• Clique na opção Suplementos do Excel.
• Na caixa de diálogo Add-ins, navegue e localize o arquivo que você salvou e clique em OK.

Agora o add-in foi ativado.

Agora você pode usar a função personalizada em todas as pastas de trabalho.

Salvando a função na pasta de trabalho macro pessoal

Uma pasta de trabalho pessoal de macro é uma pasta de trabalho oculta em seu sistema que é aberta sempre que você abre o aplicativo Excel.

É um lugar onde você pode armazenar códigos de macro e, em seguida, acessar essas macros de qualquer pasta de trabalho. É um ótimo lugar para armazenar as macros que você deseja usar com frequência.

Por padrão, não há pasta de trabalho de macro pessoal no Excel. Você precisa criá-lo gravando uma macro e salvando-a na pasta de trabalho Macro pessoal.

Você pode encontrar as etapas detalhadas sobre como criar e salvar macros na pasta de trabalho de macro pessoal aqui.

Referenciando a função de outra pasta de trabalho

Embora os dois primeiros métodos (criar um suplemento e usar a pasta de trabalho macro pessoal) funcionem em todas as situações, se você quiser fazer referência à função de outra pasta de trabalho, essa pasta de trabalho precisa ser aberta.

Suponha que você tenha uma pasta de trabalho com o nome ‘Livro de exercícios com fórmula ', e tem a função com o nome ‘GetNumeric ’.

Para usar esta função em outra pasta de trabalho (enquanto o Pasta de trabalho com fórmula está aberto), você pode usar a fórmula abaixo:

= ’Workbook with Formula’! GetNumeric (A1)

A fórmula acima usará a função definida pelo usuário no Pasta de trabalho com fórmula arquivo e dar-lhe o resultado.

Observe que, como o nome da pasta de trabalho tem espaços, você precisa colocá-lo entre aspas simples.

Usando a declaração de função de saída VBA

Se você deseja sair de uma função enquanto o código está sendo executado, você pode fazer isso usando a instrução ‘Exit Function’.

O código a seguir extrairia os primeiros três caracteres numéricos de uma string de texto alfanumérica. Assim que obtém os três caracteres, a função termina e retorna o resultado.

Função GetNumericFirstThree (CellRef As Range) As Long Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 Then Exit Function If IsNumeric (Mid (CellRef, i, 1)) Then J = J + 1 Result = Result & Mid (CellRef, i, 1) GetNumericFirstThree = Result End If Next i End Function

A função acima verifica o número de caracteres numéricos e, quando obtém 3 caracteres numéricos, sai da função no próximo loop.

Depurar uma função definida pelo usuário

Existem algumas técnicas que você pode usar ao depurar uma função definida pelo usuário no VBA:

Depurando uma função personalizada usando a caixa de mensagem

Use a função MsgBox para mostrar uma caixa de mensagem com um valor específico.

O valor que você exibe pode ser baseado no que você deseja testar. Por exemplo, se você quiser verificar se o código está sendo executado ou não, qualquer mensagem funcionará, e se você quiser verificar se os loops estão funcionando ou não, você pode exibir um valor específico ou o contador de loop.

Depurando uma função personalizada definindo o ponto de interrupção

Defina um ponto de interrupção para poder percorrer cada linha, uma de cada vez. Para definir um ponto de interrupção, selecione a linha onde deseja e pressione F9 ou clique na área vertical cinza que fica para as linhas de código. Qualquer um desses métodos inserirá um ponto de interrupção (você verá um ponto vermelho na área cinza).

Depois de definir o ponto de interrupção e executar a função, ela vai até a linha do ponto de interrupção e então para. Agora você pode percorrer o código usando a tecla F8. Pressionar F8 uma vez move para a próxima linha no código.

Depurando uma função personalizada usando Debug.Print no código

Você pode usar a instrução Debug.Print em seu código para obter os valores das variáveis ​​/ argumentos especificados na janela imediata.

Por exemplo, no código a seguir, usei Debug.Print para obter o valor de duas variáveis ​​- ‘j’ e ‘Result’

Função GetNumericFirstThree (CellRef As Range) As Long Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 Then Exit Function If IsNumeric (Mid (CellRef, i, 1)) Then J = J + 1 Result = Result & Mid (CellRef, i, 1) Debug.Print J, Result GetNumericFirstThree = Result End If Next i End Function

Quando este código é executado, ele mostra o seguinte na janela imediata.

Funções embutidas do Excel vs. Função VBA Definida pelo Usuário

Existem poucos benefícios fortes em usar as funções integradas do Excel sobre as funções personalizadas criadas no VBA.

• As funções integradas são muito mais rápidas do que as funções do VBA.
• Quando você cria um relatório / painel usando funções VBA e o envia a um cliente / colega, eles não precisam se preocupar se as macros estão habilitadas ou não. Em alguns casos, os clientes / clientes ficam assustados ao ver um aviso na barra amarela (que simplesmente pede que habilitem macros).
• Com as funções integradas do Excel, você não precisa se preocupar com as extensões dos arquivos. Se você tiver macros ou funções definidas pelo usuário na pasta de trabalho, será necessário salvá-las em .xlsm.

Embora haja muitos motivos fortes para usar as funções integradas do Excel, em alguns casos, é melhor usar uma função definida pelo usuário.

• É melhor usar uma função definida pelo usuário se sua fórmula embutida for enorme e complicada. Isso se torna ainda mais relevante quando você precisa de outra pessoa para atualizar as fórmulas. Por exemplo, se você tiver uma fórmula enorme composta de muitas funções diferentes, até mesmo alterar uma referência a uma célula pode ser entediante e sujeito a erros. Em vez disso, você pode criar uma função personalizada que aceita apenas um ou dois argumentos e faz todo o trabalho pesado do back-end.
• Quando você precisa fazer algo que não pode ser feito pelas funções embutidas do Excel. Um exemplo disso pode ser quando você deseja extrair todos os caracteres numéricos de uma string. Nesses casos, o benefício de usar uma função definida pelo usuário gar supera seus negativos.

Onde colocar o código VBA para uma função definida pelo usuário

Ao criar uma função personalizada, você precisa colocar o código na janela de código da pasta de trabalho na qual deseja a função.

Abaixo estão as etapas para colocar o código da função ‘GetNumeric’ na pasta de trabalho.

1. Vá para a guia Desenvolvedor.
2. Clique na opção Visual Basic. Isso abrirá o editor VB no backend.
3. No painel Project Explorer no VB Editor, clique com o botão direito em qualquer objeto da pasta de trabalho na qual deseja inserir o código. Se você não vir o Project Explorer, vá para a guia View e clique em Project Explorer.
4. Vá para Inserir e clique em Módulo. Isso irá inserir um objeto de módulo para sua pasta de trabalho.
5. Copie e cole o código na janela do módulo.

Você também pode gostar dos seguintes tutoriais do Excel VBA:

• Trabalhando com células e intervalos no Excel VBA.
• Trabalhando com planilhas no Excel VBA.
• Trabalhando com pastas de trabalho usando VBA.
• Como usar Loops no Excel VBA.
• Eventos do Excel VBA - um guia fácil (e completo)
• Usando instruções IF Then Else em VBA.
• Como gravar uma macro no Excel.
• Como executar uma macro no Excel.
• Como classificar dados no Excel usando VBA (um guia passo a passo).
• Função InStr do Excel VBA - explicada com exemplos.