Dans cet article, nous allons apprendre à créer des fonctions définies par l’utilisateur dans Microsoft Excel en utilisant le VBA.

Fonction définie par l’utilisateur: – Microsoft Excel a déjà beaucoup de fonctions, mais tout le monde a toujours des exigences différentes, une situation, nous pouvons créer notre propre fonction selon l’exigence appelée fonction définie par l’utilisateur. Nous pouvons utiliser la fonction définie par l’utilisateur comme d’autres fonctions dans Excel.

Voici les sujets pour lesquels nous allons créer la fonction définie par l’utilisateur: 1). Comment compter le nombre de mots dans la cellule ou la plage?

2). Comment extraire un mot d’une phrase ou d’une cellule dans Excel?

3). Comment créer la formule ISO?

4). Comment connaître le nom de la feuille de calcul et du classeur à l’aide du VBA?

5). Comment extraire le premier et le dernier mot d’une cellule dans Excel?

Comment créer la fonction définie par l’utilisateur pour compter le nombre de mots dans la cellule ou la plage? Nous avons des données dans la feuille 1 dans laquelle nous avons des adresses, nous voulons donc compter les mots dans une cellule ou une plage en créant la fonction définie par l’utilisateur via VBA dans Excel.

image 1

Pour rendre la fonction définie par l’utilisateur, veuillez suivre les étapes ci-dessous: – * Ouvrez la page VBA et appuyez sur la touche Alt + F11.

  • Insérez un module.

Écrivez le code ci-dessous:

Function WORDSCOUNT(rRange As Range) As Long

Dim rCell As Range

Dim Count As Long

For Each rCellInrRange

lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), " ", "")) + 1

Next rCell

WORDSCOUNT = lCount

End Function

Explications du code: – Pour rendre la fonction définie par l’utilisateur, nous commençons le code au nom de la fonction et définissons les variables. Nous avons utilisé «For Each boucles» dans le code pour compter les mots de la plage.

Comment utiliser cette fonction dans Excel?

Pour utiliser cette fonction, suivez les étapes ci-dessous: – * Accédez à la feuille Excel.

  • Pour compter les mots d’une cellule, entrez la formule dans la cellule D7.

  • = WORDSCOUNT (C7), la cellule C7 est la cellule dans laquelle nous voulons calculer les mots.

  • La fonction renverra 6, ce qui signifie que la cellule C7 contient 6 mots.

  • Pour faire le même calcul pour le reste des cellules, copiez la même formule et collez-la dans la plage.

image 2

  • Pour compter les mots de la plage, utilisez la formule comme = WORDSCOUNT (C7: C16) et appuyez sur Entrée.

  • La fonction retournera le nombre de mots.

image 3

Remarque: – Cette UDF sera utile pour compter les mots dans une plage ou dans une seule cellule.

Nous allons maintenant écrire le code pour compter le mot en utilisant le délimiteur spécifié (,). Suivez les étapes ci-dessous: –

Function SEPARATECOUNTWORDS(rRange As Range, Optional separator As Variant) 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

Pour utiliser cette fonction, suivez les étapes ci-dessous: –

  • Accédez à la feuille Excel.

  • Pour compter les délimiteurs spécifiques dans le mot, nous utiliserons cette fonction définie.

  • = SEPARATECOUNTWORDS (C7) et appuyez sur Entrée.

  • La fonction retournera le nombre de délimiteurs spécifiques.

image 4

Comment extraire un mot d’une phrase ou d’une cellule dans Microsoft Excel à l’aide du VBA? Nous avons des données dans la feuille 1. Dans lequel nous avons des adresses, nous voulons extraire les mots d’une phrase ou d’une cellule ou d’une plage en créant la fonction définie par l’utilisateur via VBA dans Excel.

image 5

Pour rendre la fonction définie par l’utilisateur, veuillez suivre les étapes ci-dessous: –

  • Ouvrez la page VBA et appuyez sur la touche Alt + F11.

  • Insérez un module.

Écrivez le code ci-dessous *: –

Function GETWORD(Text As Variant, N As Integer, Optional Delimiter As Variant) As String

If IsMissing(Delimiter) Then

Delimiter = " "

End If

GETWORD = Split(Text, Delimiter)(N - 1)

End Function

Explication du code: – Dans le code mentionné ci-dessus, nous avons mentionné le nom de la fonction avec les variables. Et puis nous avions défini les critères pour extraire le mot d’une phrase ou d’une cellule.

Nous allons maintenant apprendre à utiliser cette formule. Suivez les étapes ci-dessous: – * Accédez à la feuille Excel.

  • Utilisez cette formule dans la cellule D7.

  • = GETWORD (C7,2) et appuyez sur Entrée.

  • La fonction renverra le deuxième mot de la cellule car dans la formule, nous avions mentionné le mot du 2 ^ nd ^ nombre. Si vous voulez récupérer le mot qui est placé à la position 3 ^ rd ^, vous devez changer le nombre de 2 à 3 dans la formule.

image 6

Comment créer une formule de numéro de semaine ISO dans Microsoft Excel à l’aide du VBA? Nous allons apprendre comment créer une formule de numéro de semaine ISO dans Excel avec cet UDF. Cette fonction nous permettra d’identifier que la date mentionnée appartient à quel numéro de semaine de l’année.

Nous avons une liste de dates dans la feuille, et dans la deuxième colonne, nous voulons récupérer les numéros de semaine.

image 7

Pour créer l’UDF pour cette exigence, suivez les étapes ci-dessous: – * Ouvrez la page VBA et appuyez sur la touche Alt + F11.

  • Insérez un module.

  • Écrivez le code ci-dessous: –

Function 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)

End Function

Explication du code: -: – Dans le code ci-dessus, nous avons mentionné le nom de la fonction avec les variables. Et puis nous avions défini la valeur de la date, puis nous avions défini les critères de la fonction «ISOWEENUMBER».

Comment pouvons-nous utiliser cette fonction dans notre fichier Excel?

  • Accédez à la feuille Excel.

  • Entrez la formule dans la cellule D7.

  • = ISOWEEKNUMBER (C7) et appuyez sur Entrée.

  • La fonction renverra la semaine pour la date saisie dans la cellule.

Maintenant, pour récupérer le numéro de semaine pour chaque date, copiez la même formule dans la plage.

image 8

Nous allons maintenant apprendre comment renvoyer les normes ISO de début d’année dans Excel – Premier lundi de l’année.

Cette fonction vérifie essentiellement que le 1 ^ er ^ lundi de l’année va tomber à quelle date, puis elle commence à calculer le nombre de semaines à partir de cette date. Voyons comment nous pouvons créer l’UDF pour cette exigence.

image 9

Suivez les étapes ci-dessous: – * Ouvrez la page VBA et appuyez sur la touche Alt + F11.

  • Insérez un module.

Écrivez le code ci-dessous *: –

Function ISOSTYR(Year As Integer) As Date

Dim WD As Integer

Dim NY As Date

NY = DateSerial(Year, 1, 1)

WD = (NY - 2) Mod 7

If WD < 4 Then

ISOSTYR = NY - WD

Else

ISOSTYR = NY - WD + 7

End If

End Function

Explication du code: – Dans le code ci-dessus, nous avons mentionné le nom de la fonction avec les variables. Et puis nous avions défini les critères pour les variables, puis nous avions défini l’entrée de formule.

Il vous suffit de fournir l’année 2001 dans ce format et la formule vous donnera le 1 ^ er ^ lundi de l’année.

Nous allons maintenant apprendre à utiliser l’UDF dans un fichier Excel. Suivez les étapes ci-dessous: – * Accédez à la feuille Excel.

  • Entrez la formule dans la cellule D7.

  • = ISOSTYR (C7) et appuyez sur Entrée.

  • La fonction renvoie la date du 1 ^ er ^ lundi de la première semaine du Nouvel An.

  • Pour renvoyer la date du 1 ^ er ^ lundi de la première semaine du Nouvel An, copiez la même formule et collez-la dans la plage.

image 10

Comment connaître le nom de la feuille de calcul et du classeur à l’aide du VBA dans Microsoft Excel? Suivez le code et les étapes ci-dessous: – * Ouvrez la page VBA et appuyez sur la touche Alt + F11.

  • Insérez un module.

Écrivez le code ci-dessous *: –

Function Worksheetname()

Worksheetname = Range("A1").Parent.Name

End Function

Explication du code: – Dans le code ci-dessus, nous avons mentionné le nom de la fonction puis nous avons défini comment connaître le nom de la feuille.

Pour utiliser cette formule, il vous suffit de saisir la formule dans n’importe quelle cellule de cette manière: – = Worksheetname (). La fonction renverra le nom de la feuille.

image 11

Pour créer la fonction pour le nom du classeur, suivez les étapes et le code ci-dessous: – * Ouvrez la page VBA, appuyez sur la touche Alt + F11.

  • Insérez un module.

  • Écrivez le code ci-dessous: –

Function Workbookname()

Workbookname = ThisWorkbook.Name

End Function

Explication du code: -: – Dans le code ci-dessus, nous avons mentionné le nom de la fonction, puis nous avons défini comment connaître le nom du classeur.

Pour utiliser cette formule, il vous suffit de saisir la formule dans n’importe quelle cellule de cette manière: – = Nom du classeur (). La fonction renverra le nom de la feuille.

image 12

Comment extraire le premier et le dernier mot d’une cellule en utilisant VBA dans Microsoft Excel? Nous avons des données dans sheet1 dans lesquelles nous avons des adresses, nous voulons donc extraire le dernier et le premier mot d’une phrase ou d’une cellule ou d’une plage en créant la fonction définie par l’utilisateur via VBA dans Excel.

image 13

Tout d’abord, nous allons écrire la fonction pour extraire le premier mot. Veuillez suivre les étapes ci-dessous: – * Ouvrez la page VBA, appuyez sur la touche Alt + F11.

Insérer un module Écrivez le code ci-dessous *: –

Function 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, "")

End Function

Explication du code: – Dans le code mentionné ci-dessus, nous avons mentionné le nom de la fonction avec les variables. Et puis nous avons défini les critères pour extraire le mot d’une phrase ou d’une cellule.

Nous allons maintenant apprendre à utiliser cette formule. Suivez les étapes ci-dessous: –

  • Accédez à la feuille Excel.

  • Utilisez cette formule dans la cellule D9.

  • = GETFW (C9) et appuyez sur Entrée.

  • La fonction renverra le premier mot des données. Maintenant, pour récupérer le premier mot de toutes les cellules, copiez la même formule dans la plage.

image 14

  • Maintenant, nous allons écrire le code pour extraire le dernier mot de la cellule.

Suivez le code mentionné ci-dessous: – * Ouvrez la page VBA, appuyez sur la touche Alt + F11.

  • Insérez un module.

Écrivez le code ci-dessous *: –

Function 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

Nous allons maintenant apprendre à utiliser cette formule. Suivez les étapes ci-dessous: – * Accédez à la feuille Excel.

  • Utilisez cette formule dans la cellule D9.

  • = GETLW (C9) Appuyez sur Entrée.

  • La fonction renverra le dernier mot des données. Maintenant, pour récupérer le dernier mot de toutes les cellules, copiez la même formule dans la plage.

image 15

Ce sont les fonctions que nous pouvons définir via VBA et que nous pouvons ensuite utiliser comme formule d’Excel. En outre, nous pouvons créer beaucoup plus de fonctions définies par l’utilisateur.

Continuez à apprendre avec nous, nous proposerons des formules plus délicates.

Excel