Comment créer une fonction définie par l’utilisateur en VBA ?

L’un des avantages les plus intéressants de l’apprentissage de VBA est probablement la possibilité de créer vos propres fonctions.

Dans Excel, il existe plus de 450 fonctions, et certaines d’entre elles sont très utiles dans votre travail quotidien. Mais Excel vous donne la possibilité de créer une fonction personnalisée à l’aide de VBA. Oui, vous avez raison. USER‌ DEFINED Function, en abrégé UDF, ou vous pouvez également l’appeler une fonction VBA personnalisée.

Et il y a une chose que je peux dire avec confiance que chaque utilisateur VBA en herbe veut apprendre à créer une fonction définie par l’utilisateur. Pas vous ? Dites « Oui » dans la section des commentaires, si vous faites partie de ces personnes qui souhaitent créer une fonction personnalisée.

Je suis ravi de vous dire qu’il s’agit d’un GUIDE COMPLET pour vous aider à créer votre première fonction personnalisée à l’aide de VBA et à part cela, j’ai partagé quelques exemples de fonctions DÉFINIES PAR L’UTILISATEUR pour vous aider à vous inspirer.

1. Ici, j’utiliserai indifféremment les mots fonction définie par l’utilisateur, fonction personnalisée et UDF. Alors restez avec moi, vous allez être une rock star VBA dans les prochaines minutes.
2. Pour créer un code pour la fonction personnalisée VBA, vous devez l’écrire, vous ne pouvez pas l’enregistrer à l’aide de l’enregistreur de macros .

Pourquoi vous devriez créer une fonction Excel personnalisée

Comme je l’ai dit, il existe de nombreuses fonctions intégrées dans Excel qui peuvent vous aider à résoudre presque tous les problèmes et à effectuer toutes sortes de calculs. Mais, parfois, dans des situations spécifiques, vous devez créer une UDF.

Et, ci-dessous, j’ai énuméré certaines des raisons ou des situations dans lesquelles vous devez utiliser une fonction personnalisée.

1. Lorsqu’il n’y a pas de fonction pour cela

C’est l’une des raisons courantes pour créer une UDF avec VBA, car parfois vous devez calculer quelque chose et il n’y a pas de fonction spécifique pour cela. Je peux vous donner un exemple de comptage de mots à partir d’une cellule et pour cela, j’ai trouvé qu’un UDF peut être une solution parfaite.

2. Remplacer une formule complexe

Si vous travaillez avec des formules, je suis sûr que vous savez que les formules complexes sont difficiles à lire et parfois plus difficiles à comprendre par les autres. Ainsi, une fonction personnalisée peut être une solution à ce problème, car une fois que vous avez créé une UDF, vous n’avez pas besoin d’écrire cette formule complexe encore et encore.

3. Lorsque vous ne souhaitez pas utiliser SUB Routine

Bien que vous puissiez utiliser un code VBA pour effectuer un calcul, les codes VBA ne sont pas dynamiques*. Vous devez exécuter à nouveau ce code si vous souhaitez mettre à jour votre calcul. Mais si vous convertissez ce code en fonction, vous n’avez pas besoin d’exécuter ce code encore et encore car vous pouvez simplement l’insérer en tant que fonction.

Comment créer votre première fonction définie par l’utilisateur dans Excel

Bon alors regarde. J’ai divisé l’ensemble du processus en trois étapes :

1. Déclarer votre procédure en tant que fonction
2. Définir ses arguments et leur type de données
3. Ajouter du code pour calculer la valeur souhaitée

Mais permettez-moi de vous donner:

Vous devez créer une fonction qui peut renvoyer le nom du jour à partir d’une valeur de date. Eh bien, nous avons une fonction qui renvoie le numéro du jour de la semaine mais pas le nom. Vous avez compris ce que je dis? Oui?

Alors, suivons les étapes ci-dessous pour créer votre première fonction définie par l’utilisateur :

1. Tout d’abord, ouvrez votre éditeur Visual Basic en utilisant la touche de raccourci ALT + F11 ou allez dans l’onglet Développeur et cliquez simplement sur le bouton « Visual Basic ».
   
2. La prochaine chose est d’insérer un module, donc faites un clic droit sur la fenêtre du projet VBA, puis allez sur insérer, puis cliquez sur « Module ». (ALERTE : vous devez entrer une FONCTION DÉFINIE PAR L’UTILISATEUR uniquement dans les modules standard. Les modules Sheet et ThisWorkbook sont tous deux un type spécial de module et si vous entrez une UDF dans ces deux modules, Excel ne reconnaît pas que vous créez une UDF).
   
3. La troisième chose est de définir un nom pour la fonction et ici j’utilise « myDayName ». Vous devez donc écrire « Function mydayName ». Pourquoi Fonction avant le Nom ? Lorsque vous créez une fonction VBA, l’utilisation du mot « Fonction » indique à Excel de traiter ce code comme une fonction (assurez-vous de lire la portée d’un UDF avant dans le message).
4. Après cela, vous devez définir des arguments pour votre fonction. Insérez donc des parenthèses de début et écrivez « InputDate As Date ». Ici, InputDate est le nom de l’argument et la date est son type de données. Il est toujours préférable de définir un type de données pour l’argument.
   
5. Maintenant, fermez les parenthèses et écrivez « As String ». Ici, vous définissez le type de données du résultat renvoyé par la fonction et comme vous voulez le nom du jour qui est un texte, son type de données doit donc être « String ». Si vous voulez que le résultat soit autre qu’une chaîne, assurez-vous de définir son type de données en fonction de cela. (Fonction myDayName(InputDate As Date) As String).
   
6. À la fin, appuyez sur ENTER. À ce stade, le nom de votre fonction, son argument, le type de données de l’argument et le type de données de la fonction sont définis et vous avez quelque chose comme ci-dessous dans votre module :
   
7. Maintenant, dans la « Fonction » et la « Fonction de fin », vous devez définir le calcul ou vous pouvez dire le fonctionnement de cette UDF. Dans Excel, il existe une fonction de feuille de calcul appelée « Texte » et nous utilisons la même chose ici. Et pour cela, vous devez écrire le code ci-dessous et avec ce code, vous définissez la valeur qui doit être renvoyée par la fonction. myDayName = WorksheetFunction.Text(InputDate, « dddddd »)
   
8. Maintenant, fermez votre éditeur VB et revenez à la feuille de calcul et dans la cellule B2, entrez « = myDayName (A2) » appuyez sur Entrée et vous aurez le nom du jour.
   

Toutes nos félicitations! Vous venez de créer votre première fonction définie par l’utilisateur. C’est le moment de la vraie Joie. N’est-ce pas? Tapez « Joy » dans la section des commentaires.

Fonctionnement de cette fonction et valeur de retour dans une cellule

Votre première fonction personnalisée est ici, mais le fait est que vous devez comprendre comment cela fonctionne. Si je dis en termes simples, c’est un code VBA mais vous l’avez utilisé comme procédure de fonction. Divisons-le en trois parties :

• Vous l’entrez dans une cellule en tant que fonction et spécifiez la valeur d’entrée.
• Excel exécute le code derrière la fonction et utilise la valeur à laquelle vous avez fait référence.
• Vous avez le résultat dans la cellule.

Mais vous devez comprendre comment cette fonction fonctionne de l’intérieur. J’ai donc divisé l’ensemble du processus en trois parties différentes où vous pouvez voir comment le code que vous avez écrit pour la fonction fonctionne réellement.

Comme vous avez spécifié « InputDate » comme argument de la fonction et lorsque vous entrez la fonction dans la cellule et spécifiez une date, VBA prend cette valeur de date et la fournit à la fonction de texte que vous avez utilisée dans le code.

Et dans l’exemple que j’ai mentionné ci-dessus, la date que vous avez dans la cellule A1 est le 01-Jan-2019.

Après cela, la fonction TEXTE convertit cette date en un jour en utilisant le code de format « dddddd » que vous avez déjà mentionné dans le code de la fonction. Et ce jour qui est retourné par la fonction TEXT est assigné au « myDayName ».

Ainsi, si le résultat de la fonction TEXTE est mardi, cette valeur sera attribuée à « myDayName ».

Et ici le fonctionnement de la fonction touche à sa fin. « myDayName » est le nom de la fonction, donc toute valeur attribuée à « myDayName » sera la valeur du résultat et la fonction que vous avez insérée dans la feuille de calcul la renverra dans la cellule.

Lorsque vous écrivez un code pour une fonction personnalisée, vous devez veiller à ce que la valeur renvoyée par ce code soit affectée au nom de la fonction.

Comment améliorer une UDF pour de bon

Eh bien, vous savez comment créer une fonction VBA personnalisée.

Maintenant…

Il y a une chose dont vous devez vous assurer que le code que vous avez utilisé pour fonctionner doit être assez bon pour gérer toutes les possibilités. Si vous parlez de la fonction que vous venez d’écrire ci-dessus, vous pouvez renvoyer le nom du jour à partir d’une date.

Mais…

Que faire si la valeur que vous avez spécifiée n’est pas une date ? Et si la cellule que vous avez référée est vide ? Il peut y avoir d’autres possibilités, mais je suis sûr que vous avez compris mon point de vue.

Droite? Essayons donc d’améliorer cette fonction personnalisée qui pourrait être en mesure de résoudre les problèmes ci-dessus. Bien. Tout d’abord, vous devez changer le type de données de l’argument et utiliser :

InputDate As Variant

Avec cela, votre fonction personnalisée peut prendre n’importe quel type de données en entrée. Ensuite, nous devons utiliser l’instruction VBA IF pour vérifier InputDate pour certaines conditions. La première condition est de savoir si la cellule est vide ou non. Et pour cela, vous devez utiliser le code ci-dessous :

If InputDate = "" Then
myDayName = ""

Cela rendra la fonction vide si la cellule à laquelle vous avez fait référence est vide.

Un problème est résolu, passons au suivant. Outre une date, il est possible que vous ayez un numéro ou un texte. Donc, pour cela, vous devez également créer une condition qui doit vérifier si la valeur référencée est une date réelle ou non.

Le code serait :

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

Ici, j’utilise un blanc pour les deux conditions afin que si vous avez des données volumineuses, vous puissiez facilement filtrer les valeurs où la valeur d’entrée n’est pas valide. Ainsi, après avoir ajouté les conditions ci-dessus, le code ressemblerait à :

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

Et voici comment cela fonctionne maintenant : je suis sûr que vous pouvez encore apporter des modifications à cette fonction, mais je suis sûr que vous avez clairement compris mon point de vue.

Comment utiliser une fonction VBA personnalisée

À ce stade, vous savez à peu près comment créer une fonction VBA dans Excel. Mais une fois que vous l’avez, vous devez savoir comment vous pouvez l’utiliser. Et dans cette partie du post, je vais partager avec vous comment et où vous pouvez l’utiliser. Alors, sautons dedans.

1. Simplement dans une feuille de calcul

Pourquoi créons-nous une fonction personnalisée ? Simple. Pour l’utiliser dans la feuille de calcul. Vous pouvez simplement entrer une UDF dans une feuille de calcul en utilisant le signe égal et le nom de type de la fonction, puis spécifier ses arguments.

Vous pouvez également entrer une fonction définie par l’utilisateur à partir de la bibliothèque de fonctions. Accédez à l’onglet Formule ➜ Insérer une fonction ➜ Défini par l’utilisateur.

Dans cette liste, vous pouvez choisir la FDU que vous souhaitez insérer.

2. Utilisation d’autres sous-procédures et fonctions

Vous pouvez également utiliser une fonction dans d’autres fonctions ou dans une procédure « Sub ». Vous trouverez ci-dessous un code VBA dans lequel vous avez utilisé la fonction pour obtenir le nom du jour pour la date actuelle.

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

Assurez-vous de lire « Portée d’une UDF » plus loin dans cet article pour en savoir plus sur l’utilisation d’une fonction dans d’autres procédures.

3. Accéder aux fonctions à partir d’un autre classeur

Si vous avez une FDU dans un classeur et que vous souhaitez l’utiliser dans un autre classeur ou dans tous les classeurs, vous le faites en créant un complément pour celle-ci. Suivez ces étapes simples :

• Tout d’abord, vous devez enregistrer le fichier (dans lequel vous avez le code de la fonction personnalisée) en tant que complément.
• Pour cela, allez dans l’onglet Fichier ➜ Enregistrer sous ➜ « Excel Add-Ins (.xalm).

• Après cela, double-cliquez sur le complément et installez-le.

C’est ça. Vous pouvez maintenant utiliser toutes vos fonctions VBA dans n’importe quel classeur.

Différentes façons de créer une fonction VBA personnalisée [niveau avancé]

À ce stade, vous savez comment créer une fonction personnalisée dans VBA. Mais le fait est que lorsque nous utilisons des fonctions In-Built, elles viennent avec différents types d’arguments.

Donc, dans cette section de ce guide, vous allez apprendre à créer une UDF avec les différents types d’arguments.

• Sans aucun argument
• Avec un seul argument
• Avec plusieurs arguments
• Utilisation de tableau comme argument

… allons de l’avant.

1. Sans aucun argument

Vous souvenez-vous des fonctions comme MAINTENANT et AUJOURD’HUI où vous n’avez pas besoin d’entrer d’argument ?

Oui. Vous pouvez créer une fonction définie par l’utilisateur dans laquelle vous n’avez pas besoin d’entrer d’argument. Faisons-le avec un exemple :

Créons une fonction personnalisée qui peut renvoyer l’emplacement du fichier actuel. Et voici le code :

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

Cette fonction renvoie le chemin de l’emplacement où le fichier actuel est stocké et si le classeur n’est stocké nulle part, il affichera un message indiquant « Le fichier n’est pas encore enregistré ».

Maintenant, si vous portez une attention particulière au code de cette fonction, vous n’avez pas besoin de définir d’argument (entre parenthèses). Vous venez de définir le type de données pour le résultat de la fonction.

La règle de base de la création d’une fonction sans argument est un code dans lequel vous n’avez rien à saisir.

En termes simples, la valeur que vous souhaitez obtenir en retour de la fonction doit être calculée automatiquement.

Et dans cette fonction, vous avez la même chose.

Ce code ActiveWorkbook.FullName renvoie l’emplacement du fichier et celui-ci ActiveWorkbook.Name renvoie le nom. Vous n’avez rien à saisir.

2. Avec un seul argument

Nous avons déjà couvert cette chose en apprenant à créer une fonction définie par l’utilisateur. Mais creusons un peu plus et créons une fonction différente. C’est la fonction que j’ai créée il y a quelques mois pour extraire l’URL d’un lien hypertexte .

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

Maintenant, dans cette fonction, vous n’avez qu’un seul argument.

Lorsque vous entrez ceci dans une cellule, puis spécifiez la cellule où vous avez un lien hypertexte, il renverra l’URL du lien hypertexte. Maintenant, dans cette fonction, le travail principal est effectué par :

rng.Hyperlinks(1).Address

Mais le rng est ce que vous devez spécifier. Dites « Facile » dans la section des commentaires si vous trouvez que la création d’un UDF est facile.

3. Avec plusieurs arguments

Normalement, la plupart des fonctions intégrées d’Excel ont plusieurs arguments. Il est donc indispensable que vous appreniez à créer une fonction personnalisée avec plusieurs arguments.

Prenons un exemple : vous souhaitez supprimer des lettres particulières d’une chaîne de texte et souhaitez conserver le reste de la partie.

Eh bien, vous avez des fonctions comme RIGHT et LEN que vous allez utiliser dans cette fonction personnalisée. Mais ici, nous n’avons pas besoin de cela. Tout ce dont nous avons besoin est une fonction personnalisée utilisant VBA.

Alors, voici la fonction :

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

Bon alors regarde :

Dans cette fonction, vous avez deux arguments :

• rng : dans cet argument, vous devez spécifier la cellule à partir de laquelle vous souhaitez supprimer le premier caractère d’un texte.
• cnt : Et dans l’argument, vous devez spécifier le nombre de caractères à supprimer (si vous souhaitez supprimer plusieurs caractères du texte).

Lorsque vous l’entrez dans une cellule, cela fonctionne comme ci-dessous :

3.1 Création d’une fonction définie par l’utilisateur avec des arguments facultatifs et obligatoires

Si vous pensez à la fonction que nous venons de créer dans l’exemple ci-dessus où vous avez deux arguments différents, eh bien, les deux sont nécessaires. Et, si vous manquez l’un de ces éléments, vous obtiendrez une erreur comme celle-ci.

Maintenant, si vous pensez logiquement, la fonction que nous avons créée consiste à supprimer le premier caractère. Mais ici, vous devez spécifier le nombre de caractères à supprimer. Donc, mon point est que cet argument devrait être facultatif et doit en prendre un comme valeur par défaut.

Qu’en penses-tu?

Dites « Oui » dans la section des commentaires si vous êtes d’accord avec moi sur ce point.

Bon alors regarde. Pour rendre un argument facultatif, il vous suffit d’ajouter « Facultatif » avant celui-ci. Juste comme ça:

Mais l’important est de faire fonctionner votre code avec ou sans la valeur de cet argument. Ainsi, notre nouveau code pour la même fonction ressemblerait à ceci : Maintenant, dans le code, si vous ignorez la spécification du deuxième argument.

4. Utiliser Array comme argument

Il existe quelques fonctions intégrées qui peuvent prendre des arguments sous forme de tableau et vous pouvez également créer votre fonction VBA personnalisée pour ce faire.

Faisons-le avec un exemple simple où vous devez créer une fonction où vous additionnez les valeurs d’une plage où vous avez des nombres et du texte. Nous y voilà.

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

Dans le code ci-dessus de la fonction, nous avons utilisé une plage entière A1:A10 au lieu d’une valeur unique ou d’une référence de cellule.

En utilisant la boucle FOR EACH, il vérifiera chaque cellule de la plage et additionnera la valeur si la cellule contient un nombre.

La portée d’une fonction définie par l’utilisateur

En termes simples, la portée d’une fonction signifie si elle peut être appelée à partir d’autres procédures ou non. Une FDU peut avoir deux types de portées différents.

1. Publique

Vous pouvez rendre votre fonction personnalisée publique afin de pouvoir l’appeler dans toutes les feuilles de calcul du classeur. Pour rendre une fonction publique, il vous suffit d’utiliser le mot « Public », comme ci-dessous.

Mais une fonction est une fonction publique par défaut si vous ne la rendez pas privée. Dans tous les exemples que nous avons couverts, tous sont publics.

2. Privé

Lorsque vous rendez une fonction privée, vous pouvez l’utiliser dans les procédures du même module.

Disons que si vous avez votre UDF dans « Module1 », vous ne pouvez l’utiliser que dans les procédures que vous avez dans « Module1 ». Et il n’apparaîtra pas dans la liste des fonctions de la feuille de calcul (lorsque vous utilisez le signe = et essayez de taper le nom) mais vous pouvez toujours l’utiliser en tapant son nom et en spécifiant des arguments.

Limitations de la fonction définie par l’utilisateur [UDF]

Les UDF sont super utiles. Mais ils sont limités dans certaines situations. Voici quelques éléments que je souhaite que vous notiez et que vous vous rappeliez lors de la création d’une fonction personnalisée dans VBA.

• Vous ne pouvez pas modifier, supprimer ou mettre en forme des cellules et une plage à l’aide d’une fonction personnalisée.
• Impossible également de déplacer, renommer, supprimer ou ajouter des feuilles de calcul à un classeur.
• Modifiez la valeur d’une autre cellule.
• Il ne peut pas non plus modifier les options d’environnement.

Existe-t-il une différence entre une fonction intégrée et une fonction définie par l’utilisateur ?

Je suis content que vous ayez demandé. Eh bien, pour répondre à cette question, je veux partager certains des points qui, à mon avis, sont importants pour vous.

1. Plus lent que l’intégré : si vous comparez la vitesse des fonctions intégrées et des fonctions VBA, vous constaterez que le précédent est rapide. La raison derrière cela est que les fonctions intégrées sont écrites en C++ ou FORTRAN.
2. Difficile de partager des fichiers : nous partageons souvent des fichiers par e-mail et sur le cloud. Si vous utilisez l’une des fonctions personnalisées, vous devez partager ce fichier au format « xlam » afin qu’une autre personne puisse également utiliser votre fonction personnalisée.

Mais comme je l’ai dit plus haut dans  » Pourquoi vous devriez créer une fonction Excel personnalisée « , il existe des situations spécifiques dans lesquelles vous pouvez opter pour une fonction VBAcustom.

Conclusion

La création d’une fonction définie par l’utilisateur est simple. Tout ce que vous avez à faire est d’utiliser « Function » avant le nom pour le définir en tant que fonction, ajouter des arguments, définir le type de données des arguments, puis définir le type de données pour la valeur de retour.

À la fin, ajoutez du code pour calculer la valeur que vous souhaitez obtenir en retour de la fonction. Ce guide que j’ai partagé avec vous aujourd’hui est le plus simple pour apprendre à créer une fonction personnalisée dans VBA et je suis sûr que vous l’avez trouvé utile.

Mais maintenant, dis-moi une chose.

Les UDF sont utiles, qu’en pensez-vous ?

S’il vous plaît partagez vos points de vue avec moi dans la section des commentaires. J’aimerais avoir de vos nouvelles, et s’il vous plaît, n’oubliez pas de partager ce message avec vos amis, je suis sûr qu’ils l’apprécieront.

Tutoriels associés

• Liste des fonctions VBA – expliquée avec des exemples
• Comment exécuter une macro dans Excel
• Comment utiliser l’instruction IF dans VBA
• Comment travailler avec des plages et des cellules dans VBA
• Ajouter une nouvelle feuille à l’aide d’un code VBA