Applies ToExcel pour Microsoft 365 Excel pour Microsoft 365 pour Mac Excel pour le web Excel 2024 Excel 2024 pour Mac Excel 2021 Excel 2021 pour Mac Excel 2019 Excel 2019 pour Mac Excel 2016

Bien qu’Excel inclue une multitude de fonctions de feuille de calcul intégrées, il est probable qu’il n’ait pas de fonction pour chaque type de calcul que vous effectuez. Les concepteurs d’Excel ne pouvaient pas anticiper les besoins de calcul de chaque utilisateur. Au lieu de cela, Excel vous permet de créer des fonctions personnalisées, ce qui est expliqué dans cet article.

Les fonctions personnalisées, comme les macros, utilisent le langage de programmation Visual Basic pour Applications (VBA). Elles diffèrent des macros de deux manières significatives. Tout d’abord, ils utilisent des procédures Function au lieu de procédures Sub . Autrement dit, ils commencent par une instruction Function au lieu d’une instruction Sub et se terminent par End Function au lieu de End Sub. Deuxièmement, ils effectuent des calculs au lieu d’effectuer des actions. Certains types d’instructions, tels que les instructions qui sélectionnent et les plages de mise en forme, sont exclus des fonctions personnalisées. Dans cet article, vous allez apprendre à créer et à utiliser des fonctions personnalisées. Pour créer des fonctions et des macros, vous utilisez Visual Basic Editor (VBE), qui s’ouvre dans une nouvelle fenêtre distincte d’Excel.

Supposons que votre entreprise offre une remise de quantité de 10 % sur la vente d’un produit, à condition que la commande porte sur plus de 100 unités. Dans les paragraphes suivants, nous allons illustrer une fonction pour calculer cette remise.

L’exemple ci-dessous montre un formulaire de commande qui répertorie chaque article, quantité, prix, remise (le cas échéant) et le prix étendu résultant.

Exemple de formulaire de commande sans fonction personnalisée

Pour créer une fonction DISCOUNT personnalisée dans ce classeur, procédez comme suit :

  1. Appuyez sur Alt+F11 pour ouvrir Visual Basic Editor (sur le Mac, appuyez sur FN+ALT+F11), puis cliquez sur Insérer > Module. Une nouvelle fenêtre de module s’affiche sur le côté droit de Visual Basic Editor.

  2. Copiez et collez le code suivant dans le nouveau module.

    Function DISCOUNT(quantity, price)
       If quantity >=100 Then
         DISCOUNT = quantity * price * 0.1
       Else
         DISCOUNT = 0
       End If
     
     DISCOUNT = Application.Round(Discount, 2)
    End Function
    

Remarque : Pour rendre votre code plus lisible, vous pouvez utiliser la touche Tab pour mettre en retrait les lignes. La mise en retrait est à votre avantage uniquement et est facultative, car le code s’exécute avec ou sans elle. Une fois que vous avez tapé une ligne mise en retrait, Visual Basic Editor suppose que votre ligne suivante sera mise en retrait de la même façon. Pour déplacer (c’est-à-dire vers la gauche) un caractère de tabulation, appuyez sur Maj+Tab.

Vous êtes maintenant prêt à utiliser la nouvelle fonction DISCOUNT. Fermez Visual Basic Editor, sélectionnez la cellule G7, puis tapez ce qui suit :

=DISCOUNT(D7,E7)

Excel calcule la remise de 10 % sur 200 unités à 47,50 $ par unité et retourne 950 ,00 $.

Dans la première ligne de votre code VBA, Function DISCOUNT(quantity, price), vous avez indiqué que la fonction DISCOUNT nécessite deux arguments, quantity et price. Lorsque vous appelez la fonction dans une cellule de feuille de calcul, vous devez inclure ces deux arguments. Dans la formule =DISCOUNT(D7,E7), D7 est l’argument quantity et E7 est l’argument price . Vous pouvez maintenant copier la formule DISCOUNT dans G8 :G13 pour obtenir les résultats présentés ci-dessous.

Examinons comment Excel interprète cette procédure de fonction. Lorsque vous appuyez sur Entrée, Excel recherche le nom DISCOUNT dans le classeur actif et détecte qu’il s’agit d’une fonction personnalisée dans un module VBA. Les noms d’arguments placés entre parenthèses, quantité et prix sont des espaces réservés pour les valeurs sur lesquelles le calcul de la remise est basé.

Exemple de formulaire de commande avec une fonction personnalisée

L’instruction If du bloc de code suivant examine l’argument quantity et détermine si le nombre d’articles vendus est supérieur ou égal à 100 :

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Si le nombre d’articles vendus est supérieur ou égal à 100, VBA exécute l’instruction suivante, qui multiplie la valeur de quantité par la valeur de prix , puis multiplie le résultat par 0,1 :

Discount = quantity * price * 0.1

Le résultat est stocké sous la forme de la variable Discount. Une instruction VBA qui stocke une valeur dans une variable est appelée instruction d’affectation , car elle évalue l’expression à droite du signe égal et affecte le résultat au nom de la variable à gauche. Étant donné que la variable Discount porte le même nom que la procédure de fonction, la valeur stockée dans la variable est retournée à la formule de feuille de calcul qui a appelé la fonction DISCOUNT.

Si la quantité est inférieure à 100, VBA exécute l’instruction suivante :

Discount = 0

Enfin, l’instruction suivante arrondit la valeur affectée à la variable Discount à deux décimales :

Discount = Application.Round(Discount, 2)

VBA n’a pas de fonction ROUND, contrairement à Excel. Par conséquent, pour utiliser ROUND dans cette instruction, vous indiquez à VBA de rechercher la méthode Round (fonction) dans l’objet Application (Excel). Pour ce faire, ajoutez le mot Application avant le mot Round. Utilisez cette syntaxe chaque fois que vous avez besoin d’accéder à une fonction Excel à partir d’un module VBA.

Une fonction personnalisée doit commencer par une instruction Function et se terminer par une instruction End Function. En plus du nom de la fonction, l’instruction Function spécifie généralement un ou plusieurs arguments. Toutefois, vous pouvez créer une fonction sans argument. Excel inclut plusieurs fonctions intégrées (RAND et NOW, par exemple) qui n’utilisent pas d’arguments.

Après l’instruction Function, une procédure de fonction comprend une ou plusieurs instructions VBA qui prennent des décisions et effectuent des calculs à l’aide des arguments passés à la fonction. Enfin, quelque part dans la procédure de fonction, vous devez inclure une instruction qui affecte une valeur à une variable portant le même nom que la fonction. Cette valeur est retournée à la formule qui appelle la fonction .

Le nombre de mots clés VBA que vous pouvez utiliser dans les fonctions personnalisées est inférieur au nombre que vous pouvez utiliser dans les macros. Les fonctions personnalisées ne sont pas autorisées à faire autre chose que de retourner une valeur à une formule dans une feuille de calcul ou à une expression utilisée dans une autre macro ou fonction VBA. Par exemple, les fonctions personnalisées ne peuvent pas redimensionner des fenêtres, modifier une formule dans une cellule ou modifier les options de police, de couleur ou de motif pour le texte d’une cellule. Si vous incluez du code « action » de ce type dans une procédure de fonction, la fonction retourne le #VALUE ! peut avoir les autres causes suivantes.

La seule action qu’une procédure de fonction peut effectuer (en dehors de l’exécution de calculs) est l’affichage d’une boîte de dialogue. Vous pouvez utiliser une instruction InputBox dans une fonction personnalisée comme moyen d’obtenir une entrée de l’utilisateur qui exécute la fonction. Vous pouvez utiliser une instruction MsgBox comme moyen de transmettre des informations à l’utilisateur. Vous pouvez également utiliser des boîtes de dialogue personnalisées, ou UserForms, mais il s’agit d’un sujet qui dépasse le cadre de cette introduction.

Même les macros simples et les fonctions personnalisées peuvent être difficiles à lire. Vous pouvez les rendre plus faciles à comprendre en tapant du texte explicatif sous la forme de commentaires. Vous ajoutez des commentaires en précédant le texte explicatif d’une apostrophe. Par exemple, l’exemple suivant montre la fonction DISCOUNT avec des commentaires. L’ajout de commentaires de ce type permet à vous ou à d’autres personnes de gérer plus facilement votre code VBA au fil du temps. Si vous devez apporter une modification au code à l’avenir, vous aurez plus de facilité à comprendre ce que vous avez fait à l’origine.

Exemple de fonction VBA avec commentaires

Une apostrophe indique à Excel d’ignorer tout ce qui se trouve à droite sur la même ligne, de sorte que vous pouvez créer des commentaires sur les lignes par eux-mêmes ou sur le côté droit des lignes contenant du code VBA. Vous pouvez commencer un bloc de code relativement long avec un commentaire qui explique son objectif global, puis utiliser des commentaires inline pour documenter des instructions individuelles.

Une autre façon de documenter vos macros et fonctions personnalisées consiste à leur donner des noms descriptifs. Par exemple, au lieu de nommer une macro Étiquettes, vous pouvez la nommer MonthLabels pour décrire plus précisément l’objectif de la macro. L’utilisation de noms descriptifs pour les macros et les fonctions personnalisées est particulièrement utile lorsque vous avez créé de nombreuses procédures, en particulier si vous créez des procédures qui ont des objectifs similaires mais pas identiques.

La façon dont vous documentez vos macros et fonctions personnalisées est une question de préférence personnelle. L’important est d’adopter une méthode de documentation et de l’utiliser de manière cohérente.

Pour utiliser une fonction personnalisée, le classeur contenant le module dans lequel vous avez créé la fonction doit être ouvert. Si ce classeur n’est pas ouvert, vous obtenez une #NAME ? lorsque vous essayez d’utiliser la fonction . Si vous référencez la fonction dans un autre classeur, vous devez faire précéder le nom de la fonction par le nom du classeur dans lequel réside la fonction. Par exemple, si vous créez une fonction appelée DISCOUNT dans un classeur appelé Personal.xlsb et que vous appelez cette fonction à partir d’un autre classeur, vous devez taper =personal.xlsb !discount(), pas simplement =discount().

Vous pouvez vous enregistrer des séquences de touches (et d’éventuelles erreurs de saisie) en sélectionnant vos fonctions personnalisées dans la boîte de dialogue Insérer une fonction. Vos fonctions personnalisées apparaissent dans la catégorie Définie par l’utilisateur :

Boîte de dialogue Insérer une fonction

Un moyen plus simple de rendre vos fonctions personnalisées disponibles à tout moment consiste à les stocker dans un classeur distinct, puis à enregistrer ce classeur en tant que complément. Vous pouvez ensuite rendre le complément disponible chaque fois que vous exécutez Excel. Voici comment procéder :

  1. Après avoir créé les fonctions dont vous avez besoin, cliquez sur Fichier > Enregistrer sous.

  2. Dans la boîte de dialogue Enregistrer sous , ouvrez la liste déroulante Type d’enregistrement , puis sélectionnez Complément Excel. Enregistrez le classeur sous un nom reconnaissable, tel que MyFunctions, dans le dossier AddIns . La boîte de dialogue Enregistrer sous propose ce dossier. Il vous suffit donc d’accepter l’emplacement par défaut.

  3. Après avoir enregistré le classeur, cliquez sur Fichier > Options Excel.

  4. Dans la boîte de dialogue Options Excel , cliquez sur la catégorie Compléments .

  5. Dans la liste déroulante Gérer , sélectionnez Compléments Excel. Cliquez ensuite sur le bouton Go .

  6. Dans la boîte de dialogue Compléments , activez la case à cocher en regard du nom que vous avez utilisé pour enregistrer votre classeur, comme indiqué ci-dessous.

    Boîte de dialogue Compléments

  1. Après avoir créé les fonctions dont vous avez besoin, cliquez sur Fichier > Enregistrer sous.

  2. Dans la boîte de dialogue Enregistrer sous , ouvrez la liste déroulante Type d’enregistrement , puis sélectionnez Complément Excel. Enregistrez le classeur sous un nom reconnaissable, tel que MyFunctions.

  3. Après avoir enregistré le classeur, cliquez sur Outils > compléments Excel.

  4. Dans la boîte de dialogue Compléments , sélectionnez le bouton Parcourir pour rechercher votre complément, cliquez sur Ouvrir, puis cochez la case en regard de votre Add-In dans la zone Compléments disponibles .

Après avoir suivi ces étapes, vos fonctions personnalisées sont disponibles chaque fois que vous exécutez Excel. Si vous souhaitez ajouter à votre bibliothèque de fonctions, revenez à Visual Basic Editor. Si vous regardez dans l’Explorateur de projets Visual Basic Editor sous un en-tête VBAProject, vous verrez un module nommé d’après votre fichier de complément. Votre complément aura l’extension .xlam.

Module nommé dans VBE

Si vous double-cliquez sur ce module dans l’Explorateur de projets, Visual Basic Editor affiche le code de votre fonction. Pour ajouter une nouvelle fonction, positionnez votre point d’insertion après l’instruction End Function qui met fin à la dernière fonction dans la fenêtre Code, puis commencez à taper. Vous pouvez créer autant de fonctions que nécessaire de cette façon, et elles seront toujours disponibles dans la catégorie Définie par l’utilisateur de la boîte de dialogue Insérer une fonction .

Ce contenu a été créé à l’origine par Mark Dodge et Craig Stinson dans le cadre de leur livre Microsoft Office Excel 2007 Inside Out. Il a depuis été mis à jour pour s’appliquer également aux versions plus récentes d’Excel.

Vous avez besoin d’une aide supplémentaire ?

Vous pouvez toujours poser des questions à un expert de la Communauté technique Excel ou obtenir une assistance dans la Communauté de support.

Besoin d’aide ?

Vous voulez plus d’options ?

Explorez les avantages de l’abonnement, parcourez les cours de formation, découvrez comment sécuriser votre appareil, etc.

Les communautés vous permettent de poser des questions et d'y répondre, de donner vos commentaires et de bénéficier de l'avis d'experts aux connaissances approfondies.