Création d'une fonction définie par l'utilisateur (UDF) dans Excel VBA (Guide ultime)

Avec VBA, vous pouvez créer une fonction personnalisée (également appelée fonction définie par l'utilisateur) qui peut être utilisée dans les feuilles de calcul tout comme les fonctions normales.

Ceux-ci sont utiles lorsque les fonctions Excel existantes ne suffisent pas. Dans de tels cas, vous pouvez créer votre propre fonction définie par l'utilisateur (UDF) personnalisée pour répondre à vos besoins spécifiques.

Dans ce didacticiel, je couvrirai tout sur la création et l'utilisation de fonctions personnalisées dans VBA.

Si vous souhaitez apprendre VBA en toute simplicité, consultez mon Formation en ligne Excel VBA.

Qu'est-ce qu'une procédure de fonction en VBA?

Une procédure Function est un code VBA qui effectue des calculs et renvoie une valeur (ou un tableau de valeurs).

À l'aide d'une procédure Function, vous pouvez créer une fonction que vous pouvez utiliser dans la feuille de calcul (comme toute fonction Excel classique telle que SUM ou VLOOKUP).

Lorsque vous avez créé une procédure Function à l'aide de VBA, vous pouvez l'utiliser de trois manières :

1. Sous forme de formule dans la feuille de calcul, où il peut prendre des arguments comme entrées et renvoie une valeur ou un tableau de valeurs.
2. Dans le cadre de votre code de sous-programme VBA ou d'un autre code de fonction.
3. Dans la mise en forme conditionnelle.

Bien qu'il existe déjà plus de 450 fonctions Excel intégrées disponibles dans la feuille de calcul, vous aurez peut-être besoin d'une fonction personnalisée si :

• Les fonctions intégrées ne peuvent pas faire ce que vous voulez faire. Dans ce cas, vous pouvez créer une fonction personnalisée en fonction de vos besoins.
• Les fonctions intégrées peuvent faire le travail, mais la formule est longue et compliquée. Dans ce cas, vous pouvez créer une fonction personnalisée facile à lire et à utiliser.

Notez que les fonctions personnalisées créées à l'aide de VBA peuvent être considérablement plus lentes que les fonctions intégrées. Par conséquent, ceux-ci sont mieux adaptés aux situations où vous ne pouvez pas obtenir le résultat en utilisant les fonctions intégrées.

Fonction vs. Sous-programme en VBA

Un « Sous-programme » vous permet d'exécuter un ensemble de code tandis qu'une « Fonction » renvoie une valeur (ou un tableau de valeurs).

Pour vous donner un exemple, si vous avez une liste de nombres (à la fois positifs et négatifs), et que vous souhaitez identifier les nombres négatifs, voici ce que vous pouvez faire avec une fonction et un sous-programme.

Un sous-programme peut parcourir chaque cellule de la plage et peut mettre en évidence toutes les cellules qui ont une valeur négative. Dans ce cas, la sous-routine finit par changer les propriétés de l'objet plage (en changeant la couleur des cellules).

Avec une fonction personnalisée, vous pouvez l'utiliser dans une colonne séparée et elle peut renvoyer un VRAI si la valeur d'une cellule est négative et FAUX si elle est positive. Avec une fonction, vous ne pouvez pas modifier les propriétés de l'objet. Cela signifie que vous ne pouvez pas changer la couleur de la cellule avec une fonction elle-même (cependant, vous pouvez le faire en utilisant la mise en forme conditionnelle avec la fonction personnalisée).

Lorsque vous créez une fonction définie par l'utilisateur (UDF) à l'aide de VBA, vous pouvez utiliser cette fonction dans la feuille de calcul comme n'importe quelle autre fonction. J'en parlerai plus à ce sujet dans la section « Différentes manières d'utiliser une fonction définie par l'utilisateur dans Excel ».

Création d'une fonction simple définie par l'utilisateur dans VBA

Permettez-moi de créer une fonction simple définie par l'utilisateur dans VBA et de vous montrer comment cela fonctionne.

Le code ci-dessous crée une fonction qui extraira les parties numériques d'une chaîne alphanumérique.

Fonction 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) Suivant i GetNumeric = Fonction de fin de résultat

Lorsque vous avez le code ci-dessus dans le module, vous pouvez utiliser cette fonction dans le classeur.

Voici comment cette fonction - ObtenirNumérique - peut être utilisé dans Excel.

Maintenant, avant de vous expliquer comment cette fonction est créée dans VBA et comment elle fonctionne, vous devez savoir certaines choses :

• Lorsque vous créez une fonction dans VBA, elle devient disponible dans l'ensemble du classeur comme n'importe quelle autre fonction régulière.
• Lorsque vous tapez le nom de la fonction suivi du signe égal à, Excel affiche le nom de la fonction dans la liste des fonctions correspondantes. Dans l'exemple ci-dessus, lorsque j'ai entré = Obtenir, Excel m'a montré une liste contenant ma fonction personnalisée.

Je pense que c'est un bon exemple lorsque vous pouvez utiliser VBA pour créer une fonction simple à utiliser dans Excel. Vous pouvez également faire la même chose avec une formule (comme indiqué dans ce tutoriel), mais cela devient compliqué et difficile à comprendre. Avec cette UDF, vous n'avez qu'à passer un argument et vous obtenez le résultat.

Anatomie d'une fonction définie par l'utilisateur dans VBA

Dans la section ci-dessus, je vous ai donné le code et vous ai montré comment la fonction UDF fonctionne dans une feuille de calcul.

Approfondissons maintenant et voyons comment cette fonction est créée. Vous devez placer le code ci-dessous dans un module de l'éditeur VB. Je couvre ce sujet dans la section - « Où mettre le code VBA pour une fonction définie par l'utilisateur ».

Function GetNumeric(CellRef As String) as Long ' Cette fonction extrait la partie numérique de la chaîne 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

La première ligne du code commence par le mot - Function.

Ce mot indique à VBA que notre code est une fonction (et non un sous-programme). Le mot Function est suivi du nom de la fonction - GetNumeric. C'est le nom que nous utiliserons dans la feuille de calcul pour utiliser cette fonction.

• Le nom de la fonction ne peut pas contenir d'espaces. De plus, vous ne pouvez pas nommer une fonction si elle entre en conflit avec le nom d'une référence de cellule. Par exemple, vous ne pouvez pas nommer la fonction ABC123 car elle fait également référence à une cellule dans une feuille de calcul Excel.
• Vous ne devez pas donner à votre fonction le même nom qu'une fonction existante. Si vous faites cela, Excel privilégiera la fonction intégrée.
• Vous pouvez utiliser un trait de soulignement si vous souhaitez séparer les mots. Par exemple, Get_Numeric est un nom acceptable.

Le nom de la fonction est suivi de quelques arguments entre parenthèses. Ce sont les arguments dont notre fonction aurait besoin de la part de l'utilisateur. Ce sont exactement comme les arguments que nous devons fournir aux fonctions intégrées d'Excel. Par exemple dans une fonction COUNTIF, il y a deux arguments (plage et critères)

Entre parenthèses, vous devez spécifier les arguments.

Dans notre exemple, il n'y a qu'un seul argument - CellRef.

Il est également recommandé de spécifier le type d'argument attendu par la fonction. Dans cet exemple, puisque nous allons alimenter la fonction avec une référence de cellule, nous pouvons spécifier l'argument en tant que type « Range ». Si vous ne spécifiez pas de type de données, VBA le considérera comme une variante (ce qui signifie que vous pouvez utiliser n'importe quel type de données).

Si vous avez plusieurs arguments, vous pouvez les spécifier dans la même parenthèse - séparés par une virgule. Nous verrons plus loin dans ce tutoriel comment utiliser plusieurs arguments dans une fonction définie par l'utilisateur.

Notez que la fonction est spécifiée en tant que type de données « String ». Cela indiquerait à VBA que le résultat de la formule serait du type de données String.

Bien que je puisse utiliser ici un type de données numérique (tel que Long ou Double), cela limiterait la plage de nombres qu'il peut renvoyer. Si j'ai une longue chaîne de 20 nombres que je dois extraire de la chaîne globale, déclarer la fonction comme Long ou Double donnerait une erreur (car le nombre serait hors de sa plage). Par conséquent, j'ai conservé le type de données de sortie de la fonction en tant que chaîne.

La deuxième ligne du code - celle en vert qui commence par une apostrophe - est un commentaire. Lors de la lecture du code, VBA ignore cette ligne. Vous pouvez l'utiliser pour ajouter une description ou un détail sur le code.

La troisième ligne du code déclare une variable 'StringLength' en tant que type de données Integer. C'est la variable où nous stockons la valeur de la longueur de la chaîne qui est analysée par la formule.

La quatrième ligne déclare la variable Result en tant que type de données String. C'est la variable où nous allons extraire les nombres de la chaîne alphanumérique.

La cinquième ligne affecte la longueur de la chaîne dans l'argument d'entrée à la variable 'StringLength'. Notez que 'CellRef' fait référence à l'argument qui sera donné par l'utilisateur lors de l'utilisation de la formule dans la feuille de calcul (ou de son utilisation dans VBA - que nous verrons plus loin dans ce didacticiel).

Les sixième, septième et huitième lignes font partie de la boucle For Next. La boucle s'exécute autant de fois qu'il y a de caractères dans l'argument d'entrée. Ce nombre est donné par la fonction LEN et est affecté à la variable 'StringLength'.

La boucle va donc de « 1 à Stringlength ».

Dans la boucle, l'instruction IF analyse chaque caractère de la chaîne et si elle est numérique, elle ajoute ce caractère numérique à la variable Result. Pour ce faire, il utilise la fonction MID de VBA.

L'avant-dernière ligne du code attribue la valeur du résultat à la fonction. C'est cette ligne de code qui garantit que la fonction renvoie la valeur « Résultat » dans la cellule (d'où elle est appelée).

La dernière ligne du code est End Function. Il s'agit d'une ligne de code obligatoire qui indique à VBA que le code de fonction se termine ici.

Le code ci-dessus explique les différentes parties d'une fonction personnalisée typique créée dans VBA. Dans les sections suivantes, nous approfondirons ces éléments et verrons également les différentes manières d'exécuter la fonction VBA dans Excel.

Arguments dans une fonction définie par l'utilisateur dans VBA

Dans les exemples ci-dessus, où nous avons créé une fonction définie par l'utilisateur pour obtenir la partie numérique d'une chaîne alphanumérique (GetNumeric), la fonction a été conçue pour prendre un seul argument.

Dans cette section, je vais expliquer comment créer des fonctions qui ne prennent aucun argument à celles qui prennent plusieurs arguments (requis ainsi que des arguments facultatifs).

Créer une fonction en VBA sans aucun argument

Dans la feuille de calcul Excel, nous avons plusieurs fonctions qui ne prennent aucun argument (telles que RAND, AUJOURD'HUI, MAINTENANT).

Ces fonctions ne dépendent d'aucun argument d'entrée. Par exemple, la fonction AUJOURD'HUI renverrait la date actuelle et la fonction RAND renverrait un nombre aléatoire entre 0 et 1.

Vous pouvez également créer une fonction similaire dans VBA.

Ci-dessous le code qui vous donnera le nom du fichier. Il ne prend aucun argument car le résultat qu'il doit renvoyer ne dépend d'aucun argument.

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

Le code ci-dessus spécifie le résultat de la fonction en tant que type de données String (car le résultat que nous voulons est le nom de fichier - qui est une chaîne).

Cette fonction attribue la valeur de « ThisWorkbook.Name » à la fonction, qui est renvoyée lorsque la fonction est utilisée dans la feuille de calcul.

Si le fichier a été enregistré, il renvoie le nom avec l'extension de fichier, sinon il donne simplement le nom.

Ce qui précède a cependant un problème.

Si le nom du fichier change, il ne sera pas automatiquement mis à jour. Normalement, une fonction s'actualise chaque fois qu'il y a un changement dans les arguments d'entrée. Mais comme il n'y a pas d'arguments dans cette fonction, la fonction ne recalcule pas (même si vous modifiez le nom du classeur, fermez-le puis rouvrez-le).

Si vous le souhaitez, vous pouvez forcer un recalcul en utilisant le raccourci clavier - Ctrl + Alt + F9.

Pour que la formule recalcule chaque fois qu'il y a une modification dans la feuille de calcul, vous devez y ajouter une ligne de code.

Le code ci-dessous permet à la fonction de recalculer chaque fois qu'il y a un changement dans la feuille de calcul (tout comme d'autres fonctions de feuille de calcul similaires telles que la fonction AUJOURD'HUI ou RAND).

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

Maintenant, si vous modifiez le nom du classeur, cette fonction sera mise à jour chaque fois qu'il y a un changement dans la feuille de calcul ou lorsque vous rouvrez ce classeur.

Créer une fonction en VBA avec un seul argument

Dans l'une des sections ci-dessus, nous avons déjà vu comment créer une fonction qui ne prend qu'un seul argument (la fonction GetNumeric décrite ci-dessus).

Créons une autre fonction simple qui ne prend qu'un seul argument.

La fonction créée avec le code ci-dessous convertirait le texte référencé en majuscules. Maintenant, nous avons déjà une fonction pour cela dans Excel, et cette fonction est juste pour vous montrer comment cela fonctionne. Si vous devez le faire, il est préférable d'utiliser la fonction UPPER intégrée.

Fonction ConvertToUpperCase(CellRef As Range) ConvertToUpperCase = UCase(CellRef) End Function

Cette fonction utilise la fonction UCase dans VBA pour modifier la valeur de la variable CellRef. Il affecte ensuite la valeur à la fonction ConvertToUpperCase.

Puisque cette fonction prend un argument, nous n'avons pas besoin d'utiliser la partie Application.Volatile ici. Dès que l'argument change, la fonction est automatiquement mise à jour.

Créer une fonction en VBA avec plusieurs arguments

Tout comme les fonctions de feuille de calcul, vous pouvez créer des fonctions dans VBA qui prennent plusieurs arguments.

Le code ci-dessous créerait une fonction qui extraira le texte avant le délimiteur spécifié. Il prend deux arguments - la référence de cellule qui contient la chaîne de texte et le délimiteur.

Fonction 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 = Résultat End Function

Lorsque vous devez utiliser plusieurs arguments dans une fonction définie par l'utilisateur, vous pouvez avoir tous les arguments entre parenthèses, séparés par une virgule.

Notez que pour chaque argument, vous pouvez spécifier un type de données. Dans l'exemple ci-dessus, 'CellRef' a été déclaré comme type de données de plage et 'Delim' a été déclaré comme type de données String. Si vous ne spécifiez aucun type de données, VBA considère qu'il s'agit d'un type de données variant.

Lorsque vous utilisez la fonction ci-dessus dans la feuille de calcul, vous devez donner la référence de cellule qui a le texte comme premier argument et le ou les caractères délimiteurs entre guillemets doubles comme deuxième argument.

Il vérifie ensuite la position du délimiteur à l'aide de la fonction INSTR en VBA. Cette position est ensuite utilisée pour extraire tous les caractères avant le délimiteur (à l'aide de la fonction GAUCHE).

Enfin, il affecte le résultat à la fonction.

Cette formule est loin d'être parfaite. Par exemple, si vous entrez un délimiteur qui ne se trouve pas dans le texte, cela générera une erreur. Vous pouvez maintenant utiliser la fonction SIERREUR dans la feuille de calcul pour vous débarrasser des erreurs, ou vous pouvez utiliser le code ci-dessous qui renvoie l'intégralité du texte lorsqu'il ne trouve pas le délimiteur.

Function 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) Result = Left( CellRef, DelimPosition) GetDataBeforeDelimiter = Result End Function

Nous pouvons encore optimiser cette fonction.

Si vous entrez le texte (dont vous voulez extraire la partie avant le délimiteur) directement dans la fonction, cela vous donnera une erreur. Allez-y… essayez-le !

Cela se produit car nous avons spécifié le "CellRef" comme type de données de plage.

Ou, si vous voulez que le délimiteur soit dans une cellule et utilisez la référence de cellule au lieu de le coder en dur dans la formule, vous ne pouvez pas le faire avec le code ci-dessus. C'est parce que le Delim a été déclaré comme un type de données de chaîne.

Si vous souhaitez que la fonction ait la possibilité d'accepter la saisie de texte directe ou les références de cellule de l'utilisateur, vous devez supprimer la déclaration de type de données. Cela finirait par faire de l'argument un type de données variant, qui peut prendre n'importe quel type d'argument et le traiter.

Le code ci-dessous ferait ceci:

Fonction GetDataBeforeDelimiter(CellRef, Delim) As String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 Si DelimPosition < 0 Then DelimPosition = Len(CellRef) Résultat = Delim(CellRef, GetDataBeforeDelimiter = Fonction de fin de résultat

Création d'une fonction dans VBA avec des arguments facultatifs

Il existe de nombreuses fonctions dans Excel où certains des arguments sont facultatifs.

Par exemple, la fonction légendaire RECHERCHEV a 3 arguments obligatoires et un argument facultatif.

Un argument facultatif, comme son nom l'indique, est facultatif à spécifier. Si vous ne spécifiez pas l'un des arguments obligatoires, votre fonction va vous donner une erreur, mais si vous ne spécifiez pas l'argument optionnel, votre fonction fonctionnera.

Mais les arguments optionnels ne sont pas inutiles. Ils vous permettent de choisir parmi une gamme d'options.

Par exemple, dans la fonction RECHERCHEV, si vous ne spécifiez pas le quatrième argument, RECHERCHEV effectue une recherche approximative et si vous spécifiez le dernier argument comme FAUX (ou 0), alors il fait une correspondance exacte.

N'oubliez pas que les arguments facultatifs doivent toujours venir après tous les arguments requis. Vous ne pouvez pas avoir d'arguments facultatifs au début.

Voyons maintenant comment créer une fonction en VBA avec des arguments facultatifs.

Fonction avec seulement un argument facultatif

Pour autant que je sache, il n'y a pas de fonction intégrée qui ne prend que des arguments facultatifs (je peux me tromper ici, mais je ne peux pas penser à une telle fonction).

Mais nous pouvons en créer un avec VBA.

Vous trouverez ci-dessous le code de la fonction qui vous donnera la date du jour au format jj-mm-aaaa si vous ne saisissez aucun argument (c'est-à-dire laissez-le vide), et au format "jj mmmm, aaaa" si vous saisissez quelque chose comme argument (c'est-à-dire n'importe quoi pour que l'argument ne soit pas vide).

Function CurrDate(Facultatif fmt As Variant) Dim Result If IsMissing(fmt) Then CurrDate = Format(Date, "dd-mm-yyyy") Else CurrDate = Format(Date, "dd mmmm, yyyy") End If End Function

Notez que la fonction ci-dessus utilise 'IsMissing' pour vérifier si l'argument est manquant ou non. Pour utiliser la fonction IsMissing, votre argument facultatif doit être du type de données variant.

La fonction ci-dessus fonctionne peu importe ce que vous entrez comme argument. Dans le code, nous vérifions uniquement si l'argument optionnel est fourni ou non.

Vous pouvez rendre cela plus robuste en ne prenant que des valeurs spécifiques comme arguments et en affichant une erreur dans le reste des cas (comme indiqué dans le code ci-dessous).

Function CurrDate(Facultatif fmt As Variant) Dim Result If IsMissing(fmt) Then CurrDate = Format(Date, "dd-mm-yyyy") ElseIf fmt = 1 Then CurrDate = Format(Date, "dd mmmm, yyyy") Else CurrDate = CVErr(xlErrValue) Fin si fonction de fin

Le code ci-dessus crée une fonction qui affiche la date au format "jj-mm-aaaa" si aucun argument n'est fourni, et au format "jj mmmm,aaaa" lorsque l'argument est 1. Il génère une erreur dans tous les autres cas.

Fonction avec des arguments obligatoires et facultatifs

Nous avons déjà vu un code qui extrait la partie numérique d'une chaîne.

Voyons maintenant un exemple similaire qui prend à la fois des arguments obligatoires et facultatifs.

Le code ci-dessous crée une fonction qui extrait la partie texte d'une chaîne. Si l'argument optionnel est VRAI, il donne le résultat en majuscules, et si l'argument optionnel est FAUX ou est omis, il donne le résultat tel quel.

Fonction GetText(CellRef As Range, Facultatif 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

Notez que dans le code ci-dessus, nous avons initialisé la valeur de 'TextCase' comme False (regardez entre les parenthèses dans la première ligne).

En faisant cela, nous nous sommes assurés que l'argument facultatif commence par la valeur par défaut, qui est FALSE. Si l'utilisateur spécifie la valeur comme TRUE, la fonction renvoie le texte en majuscules, et si l'utilisateur spécifie l'argument facultatif comme FALSE ou l'omet, alors le texte renvoyé est tel quel.

Créer une fonction en VBA avec un tableau comme argument

Jusqu'à présent, nous avons vu des exemples de création d'une fonction avec des arguments facultatifs/requis - où ces arguments étaient une valeur unique.

Vous pouvez également créer une fonction qui peut prendre un tableau comme argument. Dans les fonctions de feuille de calcul Excel, de nombreuses fonctions prennent des arguments de tableau, tels que SUM, VLOOKUP, SUMIF, COUNTIF, etc.

Vous trouverez ci-dessous le code qui crée une fonction qui donne la somme de tous les nombres pairs dans la plage spécifiée des cellules.

Function AddEven(CellRef as Range) Dim Cell As Range pour chaque cellule dans CellRef If IsNumeric(Cell.Value) Then If Cell.Value Mod 2 = 0 Then Result = Result + Cell.Value End If End If Next Cell AddEven = Résultat End Une fonction

Vous pouvez utiliser cette fonction dans la feuille de calcul et fournir la plage de cellules qui ont les nombres comme argument. La fonction renverrait une seule valeur - la somme de tous les nombres pairs (comme indiqué ci-dessous).

Dans la fonction ci-dessus, au lieu d'une valeur unique, nous avons fourni un tableau (A1:A10). Pour que cela fonctionne, vous devez vous assurer que le type de données de l'argument peut accepter un tableau.

Dans le code ci-dessus, j'ai spécifié l'argument CellRef comme Range (qui peut prendre un tableau en entrée). Vous pouvez également utiliser le type de données variant ici.

Dans le code, il y a une boucle For Each qui parcourt chaque cellule et vérifie s'il s'agit d'un certain nombre de non. Si ce n'est pas le cas, rien ne se passe et il passe à la cellule suivante. S'il s'agit d'un nombre, il vérifie s'il est pair ou non (en utilisant la fonction MOD).

À la fin, tous les nombres pairs sont additionnés et la somme est réaffectée à la fonction.

Création d'une fonction avec un nombre indéfini d'arguments

Lors de la création de certaines fonctions dans VBA, vous ne connaissez peut-être pas le nombre exact d'arguments qu'un utilisateur souhaite fournir. Il est donc nécessaire de créer une fonction pouvant accepter autant d'arguments fournis et de les utiliser pour renvoyer le résultat.

Un exemple d'une telle fonction de feuille de calcul est la fonction SOMME. Vous pouvez lui fournir plusieurs arguments (comme celui-ci) :

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

La fonction ci-dessus ajouterait les valeurs dans tous ces arguments. Notez également qu'il peut s'agir d'une seule cellule ou d'un tableau de cellules.

Vous pouvez créer une telle fonction dans VBA en ayant le dernier argument (ou ce pourrait être le seul argument) comme facultatif. De plus, cet argument facultatif doit être précédé du mot-clé 'ParamArray'.

'ParamArray' est un modificateur qui vous permet d'accepter autant d'arguments que vous le souhaitez. Notez que l'utilisation du mot ParamArray avant un argument rend l'argument facultatif. Cependant, vous n'avez pas besoin d'utiliser le mot Facultatif ici.

Créons maintenant une fonction qui peut accepter un nombre arbitraire d'arguments et ajouterait tous les nombres dans les arguments spécifiés :

Fonction AddArguments(ParamArray arglist() As Variant) Pour chaque argument dans arglist AddArguments = AddArguments + arg Next arg End Function

La fonction ci-dessus peut prendre n'importe quel nombre d'arguments et ajouter ces arguments pour donner le résultat.

Notez que vous ne pouvez utiliser qu'une seule valeur, une référence de cellule, un booléen ou une expression comme argument. Vous ne pouvez pas fournir un tableau comme argument. Par exemple, si l'un de vos arguments est D8:D10, cette formule vous donnera une erreur.

Si vous souhaitez pouvoir utiliser les deux arguments multi-cellules, vous devez utiliser le code ci-dessous :

Fonction AddArguments(ParamArray arglist() As Variant) Pour chaque argument dans arglist Pour chaque cellule dans arg AddArguments = AddArguments + Cell Next Cell Next arg End Function

Notez que cette formule fonctionne avec plusieurs cellules et références de tableau, cependant, elle ne peut pas traiter les valeurs ou les expressions codées en dur. Vous pouvez créer une fonction plus robuste en vérifiant et en traitant ces conditions, mais ce n'est pas l'intention ici.

Le but ici est de vous montrer comment fonctionne ParamArray afin que vous puissiez autoriser un nombre indéfini d'arguments dans la fonction. Si vous souhaitez une meilleure fonction que celle créée par le code ci-dessus, utilisez la fonction SOMME dans la feuille de calcul.

Création d'une fonction qui renvoie un tableau

Jusqu'à présent, nous avons vu des fonctions qui renvoient une seule valeur.

Avec VBA, vous pouvez créer une fonction qui renvoie une variante pouvant contenir tout un tableau de valeurs.

Les formules matricielles sont également disponibles en tant que fonctions intégrées dans les feuilles de calcul Excel. Si vous connaissez les formules matricielles dans Excel, vous savez qu'elles sont saisies à l'aide de Ctrl + Maj + Entrée (au lieu de simplement Entrée). Vous pouvez en savoir plus sur les formules matricielles ici. Si vous ne connaissez pas les formules matricielles, ne vous inquiétez pas, continuez à lire.

Créons une formule qui renvoie un tableau de trois nombres (1,2,3).

Le code ci-dessous ferait cela.

Fonction ThreeNumbers() As Variant Dim NumberValue (1 à 3) NumberValue(1) = 1 NumberValue(2) = 2 NumberValue(3) = 3 ThreeNumbers = NumberValue End Function

Dans le code ci-dessus, nous avons spécifié la fonction « TroisNombres » comme variante. Cela lui permet de contenir un tableau de valeurs.

La variable 'NumberValue' est déclarée comme un tableau à 3 éléments. Il contient les trois valeurs et l'affecte à la fonction « TroisNombres ».

Vous pouvez utiliser cette fonction dans la feuille de calcul en entrant la fonction et en appuyant sur la touche Ctrl + Maj + Entrée (maintenez les touches Ctrl et Maj enfoncées, puis appuyez sur Entrée).

Lorsque vous faites cela, il renverra 1 dans la cellule, mais en réalité, il contient les trois valeurs. Pour vérifier cela, utilisez la formule ci-dessous :

=MAX(TroisNombres())

Utilisez la fonction ci-dessus avec Control + Shift + Enter. Vous remarquerez que le résultat est maintenant 3, car ce sont les plus grandes valeurs du tableau renvoyé par la fonction Max, qui obtient les trois nombres comme résultat de notre fonction définie par l'utilisateur - ThreeNumbers.

Vous pouvez utiliser la même technique pour créer une fonction qui renvoie un tableau de noms de mois, comme indiqué par le code ci-dessous :

Fonction Months() As Variant Dim MonthName(1 à 12) MonthName(1) = "Janvier" MonthName(2) = "Février" MonthName(3) = "Mars" MonthName(4) = "Avril" MonthName(5) = "Mai" MonthName(6) = "Juin" MonthName(7) = "Juillet" MonthName(8) = "Août" MonthName(9) = "Septembre" MonthName(10) = "Octobre" MonthName(11) = "Novembre" " MonthName(12) = "Décembre" Mois = MonthName End Function

Maintenant, lorsque vous entrez la fonction =Mois () dans la feuille de calcul Excel et utilisez Ctrl + Maj + Entrée, il renverra l'intégralité du tableau des noms de mois. Notez que vous ne voyez que janvier dans la cellule car il s'agit de la première valeur du tableau. Cela ne signifie pas que le tableau ne renvoie qu'une seule valeur.

Pour vous montrer qu'il renvoie toutes les valeurs, procédez comme suit : sélectionnez la cellule avec la formule, accédez à la barre de formule, sélectionnez la formule entière et appuyez sur F9. Cela vous montrera toutes les valeurs que la fonction renvoie.

Vous pouvez l'utiliser en utilisant la formule INDEX ci-dessous pour obtenir une liste de tous les noms de mois en une seule fois.

=INDEX(Mois(),LIGNE())

Maintenant, si vous avez beaucoup de valeurs, ce n'est pas une bonne pratique d'attribuer ces valeurs une par une (comme nous l'avons fait ci-dessus). Au lieu de cela, vous pouvez utiliser la fonction Array dans VBA.

Ainsi, le même code dans lequel nous créons la fonction « Mois » deviendrait plus court comme indiqué ci-dessous :

Function Months() As Variant Months = Array("Janvier", "Février", "Mars", "Avril", "Mai", "Juin", _ "Juillet", "Août", "Septembre", "Octobre" , "Novembre", "Décembre") Fonction de fin

La fonction ci-dessus utilise la fonction Array pour affecter les valeurs directement à la fonction.

Notez que toutes les fonctions créées ci-dessus renvoient un tableau horizontal de valeurs. Cela signifie que si vous sélectionnez 12 cellules horizontales (disons A1: L1) et entrez la formule =Mois () dans la cellule A1, cela vous donnera tous les noms de mois.

Mais que se passe-t-il si vous voulez ces valeurs dans une plage verticale de cellules.

Vous pouvez le faire en utilisant la formule TRANSPOSE dans la feuille de calcul.

Sélectionnez simplement 12 cellules verticales (contiguës) et entrez la formule ci-dessous.

Comprendre la portée d'une fonction définie par l'utilisateur dans Excel

Une fonction peut avoir deux portées - Publique ou alors Privé.

• UNE Portée publique signifie que la fonction est disponible pour toutes les feuilles du classeur ainsi que toutes les procédures (Sous et Fonction) dans tous les modules du classeur. Ceci est utile lorsque vous souhaitez appeler une fonction à partir d'un sous-programme (nous verrons comment cela se fait dans la section suivante).
• UNE Portée privée signifie que la fonction n'est disponible que dans le module dans lequel elle existe. Vous ne pouvez pas l'utiliser dans d'autres modules. Vous ne le verrez pas non plus dans la liste des fonctions de la feuille de calcul. Par exemple, si votre nom de fonction est 'Mois ()' et que vous tapez fonction dans Excel (après le signe =), il ne vous montrera pas le nom de la fonction. Vous pouvez cependant toujours l'utiliser si vous entrez le nom de la formule.

Si vous ne spécifiez rien, la fonction est une fonction publique par défaut.

Vous trouverez ci-dessous une fonction qui est une fonction privée :

Fonction privée WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function

Vous pouvez utiliser cette fonction dans les sous-programmes et les procédures dans les mêmes modules, mais ne pouvez pas l'utiliser dans d'autres modules. Cette fonction n'apparaîtrait pas non plus dans la feuille de calcul.

Le code ci-dessous rendrait cette fonction publique. Il apparaîtra également dans la feuille de calcul.

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

Différentes manières d'utiliser une fonction définie par l'utilisateur dans Excel

Une fois que vous avez créé une fonction définie par l'utilisateur dans VBA, vous pouvez l'utiliser de différentes manières.

Voyons d'abord comment utiliser les fonctions de la feuille de calcul.

Utilisation des FDU dans les feuilles de calcul

Nous avons déjà vu des exemples d'utilisation d'une fonction créée en VBA dans la feuille de calcul.

Tout ce que vous avez à faire est d'entrer le nom de la fonction, et il apparaît dans l'intellisense.

Notez que pour que la fonction apparaisse dans la feuille de calcul, il doit s'agir d'une fonction publique (comme expliqué dans la section ci-dessus).

Vous pouvez également utiliser la boîte de dialogue Insérer une fonction pour insérer la fonction définie par l'utilisateur (en suivant les étapes ci-dessous). Cela ne fonctionnerait que pour les fonctions publiques.

• Allez dans l'onglet Données.
• Cliquez sur l'option « Insérer une fonction ».
• Dans la boîte de dialogue Insérer une fonction, sélectionnez Défini par l'utilisateur comme catégorie. Cette option n'apparaît que lorsque vous avez une fonction dans l'éditeur VB (et que la fonction est publique).
• Sélectionnez la fonction dans la liste de toutes les fonctions définies par l'utilisateur public.
• Cliquez sur le bouton Ok.

Les étapes ci-dessus inséreraient la fonction dans la feuille de calcul. Il affiche également une boîte de dialogue Arguments de fonction qui vous donnera des détails sur les arguments et le résultat.

Vous pouvez utiliser une fonction définie par l'utilisateur comme n'importe quelle autre fonction dans Excel. Cela signifie également que vous pouvez l'utiliser avec d'autres fonctions Excel intégrées. Par exemple. la formule ci-dessous donnerait le nom du classeur en majuscules :

=UPPER(Nom du classeur())

Utilisation de fonctions définies par l'utilisateur dans les procédures et fonctions VBA

Lorsque vous avez créé une fonction, vous pouvez également l'utiliser dans d'autres sous-procédures.

Si la fonction est publique, elle peut être utilisée dans n'importe quelle procédure du même module ou d'un module différent. S'il est privé, il ne peut être utilisé que dans le même module.

Vous trouverez ci-dessous une fonction qui renvoie le nom du classeur.

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

La procédure ci-dessous appelle la fonction, puis affiche le nom dans une boîte de message.

Sub ShowWorkbookName() MsgBox WorkbookName End Sub

Vous pouvez également appeler une fonction à partir d'une autre fonction.

Dans les codes ci-dessous, le premier code renvoie le nom du classeur et le second renvoie le nom en majuscules en appelant la première fonction.

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

Fonction WorkbookNameinUpper() WorkbookNameinUpper = UCase(WorkbookName) End Function

Appel d'une fonction définie par l'utilisateur à partir d'autres classeurs

Si vous avez une fonction dans un classeur, vous pouvez également appeler cette fonction dans d'autres classeurs.

Il y a plusieurs façons de le faire :

1. Création d'un complément
2. Fonction d'enregistrement dans le classeur de macros personnelles
3. Référencement de la fonction à partir d'un autre classeur.

Création d'un complément

En créant et en installant un complément, vous aurez la fonction personnalisée disponible dans tous les classeurs.

Supposons que vous ayez créé une fonction personnalisée - « GetNumeric » et que vous la vouliez dans tous les classeurs. Pour ce faire, créez un nouveau classeur et disposez le code de la fonction dans un module de ce nouveau classeur.

Suivez maintenant les étapes ci-dessous pour l'enregistrer en tant que complément, puis installez-le dans Excel.

• Allez dans l'onglet Fichier et cliquez sur Enregistrer sous.
• Dans la boîte de dialogue Enregistrer sous, remplacez le type "Enregistrer sous" par .xlam. Le nom que vous attribuez au fichier serait le nom de votre complément. Dans cet exemple, le fichier est enregistré sous le nom GetNumeric.
  • Vous remarquerez que le chemin du fichier où il est enregistré change automatiquement. Vous pouvez utiliser celui par défaut ou le modifier si vous le souhaitez.
• Ouvrez un nouveau classeur Excel et accédez à l'onglet Développeur.
• Cliquez sur l'option Compléments Excel.
• Dans la boîte de dialogue Compléments, parcourez et localisez le fichier que vous avez enregistré, puis cliquez sur OK.

Maintenant, le complément a été activé.

Vous pouvez maintenant utiliser la fonction personnalisée dans tous les classeurs.

Enregistrement de la fonction dans le classeur de macros personnelles

Un classeur de macros personnelles est un classeur masqué dans votre système qui s'ouvre chaque fois que vous ouvrez l'application Excel.

C'est un endroit où vous pouvez stocker des codes de macro, puis accéder à ces macros à partir de n'importe quel classeur. C'est un endroit idéal pour stocker les macros que vous souhaitez utiliser souvent.

Par défaut, il n'y a pas de classeur de macros personnelles dans votre Excel. Vous devez le créer en enregistrant une macro et en l'enregistrant dans le classeur de macros personnelles.

Vous pouvez trouver les étapes détaillées sur la façon de créer et d'enregistrer des macros dans le classeur de macros personnelles ici.

Référencer la fonction à partir d'un autre classeur

Alors que les deux premières méthodes (création d'un complément et utilisation d'un classeur de macros personnelles) fonctionneraient dans toutes les situations, si vous souhaitez référencer la fonction à partir d'un autre classeur, ce classeur doit être ouvert.

Supposons que vous ayez un classeur avec le nom 'Cahier d'exercices avec formule', et il a la fonction avec le nom 'GetNumeric'.

Pour utiliser cette fonction dans un autre classeur (pendant que le Cahier d'exercices avec formule est ouvert), vous pouvez utiliser la formule ci-dessous :

='Classeur avec formule'!GetNumeric(A1)

La formule ci-dessus utilisera la fonction définie par l'utilisateur dans le Cahier d'exercices avec formule fichier et vous donne le résultat.

Notez que puisque le nom du classeur contient des espaces, vous devez le mettre entre guillemets simples.

Utilisation de l'instruction de fonction de sortie VBA

Si vous souhaitez quitter une fonction pendant l'exécution du code, vous pouvez le faire en utilisant l'instruction « Exit Function ».

Le code ci-dessous extraira les trois premiers caractères numériques d'une chaîne de texte alphanumérique. Dès qu'elle obtient les trois caractères, la fonction se termine et renvoie le résultat.

Fonction 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

La fonction ci-dessus vérifie le nombre de caractères numériques et lorsqu'elle obtient 3 caractères numériques, elle quitte la fonction dans la boucle suivante.

Débogage d'une fonction définie par l'utilisateur

Il existe quelques techniques que vous pouvez utiliser lors du débogage d'une fonction définie par l'utilisateur dans VBA :

Débogage d'une fonction personnalisée à l'aide de la boîte de message

Utilisez la fonction MsgBox pour afficher une boîte de message avec une valeur spécifique.

La valeur que vous affichez peut être basée sur ce que vous voulez tester. Par exemple, si vous souhaitez vérifier si le code est exécuté ou non, n'importe quel message fonctionnera, et si vous souhaitez vérifier si les boucles fonctionnent ou non, vous pouvez afficher une valeur spécifique ou le compteur de boucles.

Déboguer une fonction personnalisée en définissant le point d'arrêt

Définissez un point d'arrêt pour pouvoir parcourir chaque ligne une par une. Pour définir un point d'arrêt, sélectionnez la ligne où vous le souhaitez et appuyez sur F9, ou cliquez sur la zone verticale grise qui est laissée aux lignes de code. N'importe laquelle de ces méthodes insère un point d'arrêt (vous verrez un point rouge dans la zone grise).

Une fois que vous avez défini le point d'arrêt et que vous exécutez la fonction, elle va jusqu'à la ligne du point d'arrêt, puis s'arrête. Vous pouvez maintenant parcourir le code à l'aide de la touche F8. Appuyez une fois sur F8 pour passer à la ligne suivante du code.

Débogage d'une fonction personnalisée à l'aide de Debug.Print dans le code

Vous pouvez utiliser l'instruction Debug.Print dans votre code pour obtenir les valeurs des variables/arguments spécifiés dans la fenêtre immédiate.

Par exemple, dans le code ci-dessous, j'ai utilisé Debug.Print pour obtenir la valeur de deux variables - "j" et "Résultat"

Function 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

Lorsque ce code est exécuté, il affiche ce qui suit dans la fenêtre immédiate.

Fonctions intégrées Excel vs. Fonction définie par l'utilisateur VBA

Il y a peu d'avantages importants à utiliser les fonctions intégrées d'Excel par rapport aux fonctions personnalisées créées dans VBA.

• Les fonctions intégrées sont beaucoup plus rapides que les fonctions VBA.
• Lorsque vous créez un rapport/un tableau de bord à l'aide des fonctions VBA et que vous l'envoyez à un client/collègue, ils n'auront pas à se soucier de savoir si les macros sont activées ou non. Dans certains cas, les clients/clients ont peur en voyant un avertissement dans la barre jaune (qui leur demande simplement d'activer les macros).
• Avec les fonctions Excel intégrées, vous n'avez pas à vous soucier des extensions de fichiers. Si vous avez des macros ou des fonctions définies par l'utilisateur dans le classeur, vous devez l'enregistrer au format .xlsm.

Bien qu'il existe de nombreuses bonnes raisons d'utiliser les fonctions intégrées d'Excel, dans certains cas, il est préférable d'utiliser une fonction définie par l'utilisateur.

• Il est préférable d'utiliser une fonction définie par l'utilisateur si votre formule intégrée est énorme et compliquée. Cela devient encore plus pertinent lorsque vous avez besoin de quelqu'un d'autre pour mettre à jour les formules. Par exemple, si vous avez une formule énorme composée de nombreuses fonctions différentes, même la modification d'une référence à une cellule peut être fastidieuse et sujette aux erreurs. Au lieu de cela, vous pouvez créer une fonction personnalisée qui ne prend qu'un ou deux arguments et fait tout le gros du travail du backend.
• Lorsque vous devez faire quelque chose qui ne peut pas être fait par les fonctions intégrées d'Excel. Un exemple de ceci peut être lorsque vous souhaitez extraire tous les caractères numériques d'une chaîne. Dans de tels cas, l'avantage d'utiliser une fonction définie par l'utilisateur gar l'emporte sur ses inconvénients.

Où mettre le code VBA pour une fonction définie par l'utilisateur

Lors de la création d'une fonction personnalisée, vous devez placer le code dans la fenêtre de code du classeur dans lequel vous souhaitez la fonction.

Vous trouverez ci-dessous les étapes pour mettre le code de la fonction « GetNumeric » dans le classeur.

1. Allez dans l'onglet Développeur.
2. Cliquez sur l'option Visual Basic. Cela ouvrira l'éditeur VB dans le backend.
3. Dans le volet Explorateur de projets de l'éditeur VB, cliquez avec le bouton droit sur n'importe quel objet du classeur dans lequel vous souhaitez insérer le code. Si vous ne voyez pas l'Explorateur de projets, accédez à l'onglet Affichage et cliquez sur Explorateur de projets.
4. Allez dans Insérer et cliquez sur Module. Cela insérera un objet module pour votre classeur.
5. Copiez et collez le code dans la fenêtre du module.

Vous aimerez peut-être également les didacticiels Excel VBA suivants :

• Travailler avec des cellules et des plages dans Excel VBA.
• Travailler avec des feuilles de calcul dans Excel VBA.
• Travailler avec des classeurs en utilisant VBA.
• Comment utiliser les boucles dans Excel VBA.
• Événements Excel VBA - Un guide simple (et complet)
• Utilisation des instructions IF Then Else dans VBA.
• Comment enregistrer une macro dans Excel.
• Comment exécuter une macro dans Excel.
• Comment trier les données dans Excel à l'aide de VBA (un guide pas à pas).
• Fonction Excel VBA InStr - Expliquée avec des exemples.