Como criar uma função definida pelo usuário no Microsoft Excel

Anonim

Neste artigo, vamos aprender como criar funções definidas pelo usuário no Microsoft Excel usando o VBA.

Função definida pelo usuário: - O Microsoft Excel já está tendo muitas funções, mas ainda assim todo mundo tem requisitos diferentes, situação, podemos criar nossa própria função de acordo com o requisito que é chamado de função definida pelo usuário. Podemos usar a função definida pelo usuário como outras funções no Excel.

Abaixo estão os tópicos para os quais criaremos a função definida pelo usuário:

1). Como contar o número de palavras na célula ou intervalo?

2). Como extrair uma palavra de uma frase ou célula no Excel?

3). Como criar a fórmula para ISO?

4). Como saber o nome da planilha e da pasta de trabalho usando o VBA?

5). Como extrair a primeira e a última palavra de uma célula no Excel?

Como criar a função definida pelo usuário para contar o número de palavras na célula ou intervalo?

Temos dados na planilha 1 na qual temos alguns endereços, então queremos contar as palavras em uma célula ou um intervalo, criando a função definida pelo usuário por meio do VBA no Excel.

Para fazer a função definida pelo usuário, siga as etapas abaixo: -

  • Abra a página do VBA e pressione a tecla Alt + F11.
  • Insira um módulo.

Escreva o código abaixo mencionado:

 Função PALAVRASCOUNT (rRange As Range) As Long Dim rCell As Range Contagem Dim As Long For Cada rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Replace (Trim (rCell), "", "")) + 1 Próximo rCell PALAVRASCOUNT = Função Final lCount

Explicações de código: - Para fazer a função definida pelo usuário iniciamos o código para o nome da função e definimos as variáveis. Usamos “For Each loops” no código para contar as palavras no intervalo.
Como usar esta função no Excel?

Para usar esta função, siga as etapas abaixo: -

  • Vá para a planilha do Excel.
  • Para contar as palavras de uma célula, insira a fórmula na célula D7.
  • = PALAVRAS CONTA (C7), a célula C7 é a célula na qual queremos calcular as palavras.
  • A função retornará 6, o que significa que a célula C7 contém 6 palavras.
  • Para fazer o mesmo cálculo para o restante das células, copie a mesma fórmula e cole no intervalo.

  • Para contar as palavras no intervalo, use a fórmula como = PALAVRAS CONTA (C7: C16) e pressione Enter.
  • A função retornará a contagem das palavras.


Nota: - Este UDF será útil para contar as palavras em um intervalo ou em uma única célula.

Agora vamos escrever o código para contar a palavra usando o delimitador especificado (,). Siga as etapas abaixo: -

 Função SEPARATECOUNTWORDS (rRange como intervalo, separador opcional como variante) As Long Dim rCell As Range Dim Count As Long If IsMissing (separator) Then separator = "," End If For Each rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Replace (Trim (rCell), separator, "")) Next rCell SEPARATECOUNTWORDS = lCount End Function

Para usar esta função, siga as etapas abaixo: -

  • Vá para a planilha do Excel.
  • Para contar os delimitadores específicos na palavra, usaremos esta função definida.
  • = SEPARATECOUNTWORDS (C7) e pressione Enter.
  • A função retornará a contagem dos delimitadores específicos.


Como extrair uma palavra de uma frase ou célula no Microsoft Excel usando o VBA?

Temos dados na planilha1. No qual temos alguns endereços, queremos extrair as palavras de uma frase ou célula ou um intervalo, criando a função definida pelo usuário através do VBA no Excel.


Para fazer a função definida pelo usuário, siga as etapas abaixo: -

  • Abra a página do VBA e pressione a tecla Alt + F11.
  • Insira um módulo.
  • Escreva o código abaixo mencionado:-
 Função GETWORD (Texto como Variante, N como Inteiro, Delimitador Opcional como Variante) As String If IsMissing (Delimiter) Then Delimiter = "" End If GETWORD = Split (Texto, Delimitador) (N - 1) End Function

Explicação do código: - No código mencionado acima, mencionamos o nome da função com as variáveis. E então definimos os critérios para extrair a palavra da frase ou célula.

Agora aprenderemos como usar essa fórmula. Siga as etapas abaixo: -

  • Vá para a planilha do Excel.
  • Use esta fórmula na célula D7.
  • = GETWORD (C7,2) e pressione Enter.
  • A função retornará a segunda palavra da célula porque na fórmula mencionamos para 2WL palavra do número. Se você deseja recuperar a palavra que está colocada em 3rd posição, você precisa alterar o número de 2 para 3 na fórmula.


Como criar a fórmula do número da semana ISO no Microsoft Excel usando o VBA?

Aprenderemos como podemos criar a fórmula do número da semana ISO no Excel com este UDF. Esta função usaremos para identificar que a data mencionada pertence a qual número da semana do ano.

Temos uma lista de datas na planilha e, na segunda coluna, queremos recuperar os números da semana.

Para criar a UDF para este requisito, siga as etapas fornecidas abaixo:-

  • Abra a página do VBA e pressione a tecla Alt + F11.
  • Insira um módulo.
  • Escreva o código abaixo mencionado: -
 Função ISOWEEKNUMBER (Indate As Date) As Long Dim Dt As Date Dt = DateSerial (Year (Indate - Weekday (Indate - 1) + 4), 1, 3) ISOWEEKNUMBER = Int ((Indate - Dt + Weekday (Dt) + 5 ) / 7) Função final

Explicação do código: -: - No código acima, mencionamos o nome da função com as variáveis. E então definimos o valor da data e definimos os critérios da função “ISOWEENUMBER”.
Como podemos usar esta função em nosso arquivo Excel?

  • Vá para a planilha do Excel.
  • Insira a fórmula na célula D7.
  • = ISOWEEKNUMBER (C7) e pressione Enter.
  • A função retornará a semana para a data inserida na célula. Agora, para recuperar o número da semana para cada data, copie a mesma fórmula no intervalo.


Agora aprenderemos como retornar os padrões ISO do início do ano no Excel - Primeira segunda-feira do ano.

Esta função irá basicamente verificar se 1st A segunda-feira do ano cairá nessa data e então começará a calcular o número de semanas a partir dessa data. Vamos ver como podemos criar a UDF para este requisito.


Siga as etapas abaixo:-

  • Abra a página do VBA e pressione a tecla Alt + F11.
  • Insira um módulo.
  • Escreva o código abaixo mencionado:-
 Função ISOSTYR (ano como inteiro) como data Dim WD como inteiro Dim NY como data NY = DateSerial (ano, 1, 1) WD = (NY - 2) Mod 7 If WD <4 Then ISOSTYR = NY - WD Else ISOSTYR = NY - WD + 7 End If End Function

Explicação do código: - No código acima, mencionamos o nome da função com as variáveis. E, então, definimos os critérios para as variáveis ​​e definimos a entrada da fórmula.

Você só precisa fornecer o ano de 2001 neste formato e a fórmula lhe dará 1st Segunda-feira do ano.

Agora aprenderemos como usar o UDF no arquivo Excel. Siga as etapas abaixo: -

  • Vá para a planilha do Excel.
  • Insira a fórmula na célula D7.
  • = ISOSTYR (C7) e pressione Enter.
  • A função retornará a data do 1st Segunda-feira da primeira semana do Ano Novo.
  • Para retornar a data do 1st Segunda-feira da primeira semana do Ano Novo, copie a mesma fórmula e cole no intervalo.


Como saber o nome da planilha e da pasta de trabalho usando o VBA no Microsoft Excel?

Siga o código e as etapas fornecidas abaixo:-

  • Abra a página do VBA e pressione a tecla Alt + F11.
  • Insira um módulo.
  • Escreva o código abaixo mencionado:-
 Função Worksheetname () Worksheetname = Range ("A1"). Parent.Name End Function

Explicação do código: - No código acima mencionamos o nome da função e depois definimos como saber o nome da folha.

Para usar esta fórmula, você só precisa inserir a fórmula em qualquer célula desta forma: - = Nome da planilha (). A função retornará o nome da planilha.


Para criar a função para o nome da pasta de trabalho, siga as etapas e códigos fornecidos abaixo: -

  • Abra a página do VBA e pressione a tecla Alt + F11.
  • Insira um módulo.
  • Escreva o código abaixo mencionado: -
 Function Workbookname () Workbookname = ThisWorkbook.Name End Function

Explicação do código: -: - No código acima, mencionamos o nome da função e, em seguida, definimos como saber o nome da pasta de trabalho.

Para usar esta fórmula, você só precisa inserir a fórmula em qualquer célula desta forma: - = Workbookname (). A função retornará o nome da planilha.


Como extrair a primeira e a última palavra de uma célula usando o VBA no Microsoft Excel?

Temos dados na planilha1 na qual temos alguns endereços, portanto, queremos extrair a última e a primeira palavra de uma frase ou célula ou de um intervalo, criando a função definida pelo usuário por meio do VBA no Excel.

Primeiro, vamos escrever a função para extrair a primeira palavra. Siga as etapas abaixo: -

  • Abra a página do VBA e pressione a tecla Alt + F11.
  • Insira um módulo

Escreva o código abaixo mencionado:-

 Função GETFW (Text As String, Optional Separator As Variant) Dim FW As String If IsMissing (Separator) Then Separator = "" End If FW = Left (Text, InStr (1, Text, Separator, vbTextCompare)) GETFW = Replace (FW , Separator, "") Função Final

Explicação do código: - No código mencionado acima, mencionamos o nome da função com as variáveis. E então definimos os critérios para extrair a palavra da frase ou célula.

Agora aprenderemos como usar essa fórmula. Siga as etapas abaixo: -

  • Vá para a planilha do Excel.
  • Use esta fórmula na célula D9.
  • = GETFW (C9) e pressione Enter.
  • A função retornará a primeira palavra dos dados. Agora, para recuperar a primeira palavra de todas as células, copie a mesma fórmula no intervalo.

Agora vamos escrever o código para extrair a última palavra da célula. Siga o código mencionado abaixo: -

  • Abra a página do VBA e pressione a tecla Alt + F11.
  • Insira um módulo.
  • Escreva o código abaixo mencionado:-
 Função GETLW (Text As String, Optional Separator As Variant) Dim LW As String If IsMissing (Separator) Then Separator = "" End If LW = StrReverse (Text) LW = Left (lastword, InStr (1, LW, Separator, vbTextCompare) ) GETLW = StrReverse (Replace (LW, Separator, "")) End Function

Agora aprenderemos como usar essa fórmula. Siga as etapas abaixo: -

  • Vá para a planilha do Excel.
  • Use esta fórmula na célula D9.
  • = GETLW (C9) Pressione Enter.
  • A função retornará a última palavra dos dados. Agora, para recuperar a última palavra de todas as células, copie a mesma fórmula no intervalo.

Estas são as funções que podemos definir através do VBA e então podemos usá-las como fórmula do Excel. Além disso, podemos criar muito mais funções definidas pelo usuário. Continue aprendendo conosco, nós encontraremos fórmulas mais complicadas.