Bien Excel intègre une multitude de fonctions de feuille de calcul intégrées, il y a des chances qu’elles n’ont pas de fonction pour chaque type de calcul que vous effectuez. Les concepteurs de Excel n’ont pas pu anticiper les besoins de calcul de chaque utilisateur. Au lieu de Excel, vous avez la possibilité de créer des fonctions personnalisées, ce qui est expliqué dans cet article.

Les fonctions personnalisées, telles que les macros, utilisent le langage de programmation Visual Basic pour Applications (VBA). Elles diffèrent des macros de deux manières significatives. Premièrement, elles utilisent des procédures de fonction au lieu de sous-procédures. Autrement dit, elles commencent par une instruction Fonction au lieu d’une instruction Sub et se terminent par la fonction Fin au lieu de End Sub. Deuxièmement, elles effectuent des calculs au lieu d’effectuer des actions. Certains types d’instructions, comme les instructions qui sélectionnent et formatent des plages, sont exclus des fonctions personnalisées. Dans cet article, vous allez découvrir comment créer et utiliser des fonctions personnalisées. Pour créer des fonctions et des macros, vous devez travailler avec l’Éditeur Visual Basic (VBE),qui s’ouvre dans une nouvelle fenêtre séparée de Excel.

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

L’exemple ci-dessous présente un formulaire de commande qui répertorie chaque article, quantité, prix, remise (le cas contraire) et le prix étendu qui en résulte.

Exemple de formulaire de commande sans fonction personnalisée

Pour créer une fonction DISCOUNT personnalisée dans ce manuel, suivez ces étapes :

  1. Appuyez sur Alt+F11 pour ouvrir l’Éditeur de Visual Basic (sur Mac, appuyez sur Fn+Alt+F11),puis cliquez sur Insérer > Module. Une nouvelle fenêtre de module apparaît sur le côté droit de l’éditeur Visual Basic’édition.

  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 les lignes en retrait. Le retrait n’est possible qu’à votre avantage et est facultatif, car le code est exécuté avec ou sans celui-ci. Une fois que vous avez tapé une ligne en retrait, l Visual Basic dans l’Éditeur suppose que la ligne suivante sera également en retrait. Pour sortir d’un caractère de tabulation (c’est-à-dire vers la gauche), appuyez sur Les deux caractères.

Vous êtes maintenant prêt à utiliser la nouvelle fonction DISCOUNT. Fermez l Visual Basic, 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 renvoie 950,00 $.

Dans la première ligne de votre code VBA, fonction DISCOUNT(quantité, prix), vous avez indiqué que la fonction DISCOUNT nécessite deux arguments : quantité et prix. 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 quantité et E7 est l’argument prix. Vous pouvez désormais copier la formule DISCOUNT dans G8:G13 pour obtenir les résultats ci-dessous.

Voyons comment les personnes peuvent Excel cette procédure de fonction. Lorsque vous appuyezsur Entrée, Excel recherche le nom DISCOUNT dans le workbook en cours et trouve qu’il s’agit d’une fonction personnalisée dans un module VBA. Les noms d’arguments entre parenthèses, quantité et prix,sont des espaces pour les valeurs sur lesquelles repose le calcul de la remise.

Exemple de formulaire de commande avec une fonction personnalisée

L’instruction Si du bloc de code suivant examine l’argument quantité 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é en tant que variable Discount. Une instruction VBA qui stocke une valeur dans une variable est appelée instruction d’affectation, car elle évalue l’expression du côté droit du signe égal et affecte le résultat au nom de la variable sur la gauche. Étant donné que la variable Remise a le même nom que la procédure de fonction, la valeur stockée dans la variable est renvoyée à la formule de feuille de calcul appelée fonction DISCOUNT.

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

Discount = 0

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

Discount = Application.Round(Discount, 2)

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

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

À la suite de l’instruction Fonction, une procédure de fonction inclut une ou plusieurs instructions VBA qui prennent des décisions et effectuent des calculs à l’aide des arguments transmis à la fonction. Enfin, dans la procédure de la fonction, vous devez inclure une instruction qui attribue une valeur à une variable ayant le même nom que la fonction. Cette valeur est renvoyé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 renvoyer 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 resizer des fenêtres, modifier une formule dans une cellule, ni modifier les options de police, de couleur ou de motif pour le texte dans une cellule. Si vous incluez un code d'« action » de ce type dans une procédure de fonction, la fonction renvoie la #VALUE! erreur.

L’action qu’une procédure de fonction peut effectuer (en dehors de l’affichage de calculs) est d’afficher une boîte de dialogue. Vous pouvez utiliser une instruction InputBox dans une fonction personnalisée afin d’obtenir l’avis de l’utilisateur exécutant la fonction. Vous pouvez utiliser une instruction MsgBox pour communiquer des informations à l’utilisateur. Vous pouvez également utiliser des boîtes de dialogue personnalisées ou des formesutilisateur, 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 compréhensibles en tapant du texte explicatif sous forme de commentaires. Vous ajoutez des commentaires en faisant précéder le texte explicatif d’une apostrophe. Par exemple, l’exemple suivant montre la fonction DISCOUNT avec des commentaires. L’ajout de commentaires tels que celui-ci facilite la maintenance de votre code VBA au fil du temps. Si vous devez modifier le code ultérieurement, vous comprendrez plus facilement ce que vous avez fait à l’origine.

Exemple d’une fonction VBA avec commentaires

Une apostrophe indique Excel d’ignorer tout ce qui se trouvent à droite sur la même ligne, afin que vous pouvez créer des commentaires sur les lignes seules 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 en ligne 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, particulièrement si vous créez des procédures qui ont des objectifs similaires mais pas identiques.

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

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

Vous pouvez éviter quelques frappes (et erreurs de frappe possibles) 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 facile de rendre vos fonctions personnalisées disponibles à tout moment consiste à les stocker dans un autre workbook, puis à enregistrer ce dernier en tant que add-in. Vous pouvez ensuite rendre le module disponible chaque fois que vous exécutez Excel. Voici comment faire :

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

    Dans Excel 2007, cliquez sur le bouton Microsoft Office enregistrer,puis cliquez sur Enregistrer sous

  2. Dans la boîte de dialogue Enregistrer sous, ouvrez la liste type de la liste, puis sélectionnez Excel-in. 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 classez, cliquez sur Fichier >Excel Options.

    Dans Excel 2007, cliquez sur le bouton Microsoft Office boutondroit, puis cliquez Excel Options.

  4. Dans la Excel Options, cliquez sur la catégorie Modules supplémentaires.

  5. Dans la liste de listes de gestion, sélectionnez Excel-ins. Cliquez ensuite sur le bouton Aller à.

  6. Dans la boîte de dialogue Ajouts, cochez la case en regard du nom que vous avez utilisé pour enregistrer votre travail, comme illustré 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 type de la liste, puis sélectionnez Excel-in. Enregistrez le travail sous un nom reconnaissable, tel que MyFunctions.

  3. Après avoir enregistré le workbook, cliquez sur Outils pour > Excel-ins.

  4. Dans la boîte de dialogue Modules, sélectionnez le bouton Parcourir pour rechercher votre module, cliquez sur Ouvrir,puis cochez la case à côté de votre Add-In dans la zone Modules disponibles.

Après avoir suivi ces étapes, vos fonctions personnalisées seront disponibles chaque fois que vous exécuterez Excel. Si vous voulez ajouter des fonctionnalités à votre bibliothèque de fonctions, revenir à l’éditeur Visual Basic’édition. Si vous regardez dans l’Explorateur Visual Basic éditeur Project un titre VBAProject, vous voyez un module nommé après votre fichier de module. Votre add-in will have the extension .xlam.

Module nommé dans VBE

Le fait de double-cliquer sur ce module dans l’Explorateur Project entraîne l’affichage de votre code Visual Basic’éditeur de fonction. Pour ajouter une nouvelle fonction, positionnez votre point d’insertion après l’instruction Fin de la fonction qui met fin à la dernière fonction dans la fenêtre Code et commencez à taper. Vous pouvez créer autant de fonctions que vous le souhaitez de cette manière, et elles seront toujours disponibles dans la catégorie Définie par l’utilisateur dans la boîte de dialogue Insérer une fonction.

À l’origine, ce contenu a été rédigé par Mark Piloty et Stinson dans le cadre de leur livre Microsoft Office Excel 2007 Inside Out. Il a depuis été mis à jour pour s’appliquer aux versions plus récentes Excel également.

Vous avez besoin d’une aide supplémentaire ?

Vous pouvez toujours consulter un expert de la communauté technique Excel ou obtenir une assistance dans la communauté Answers.

Besoin d’aide ?

Développez vos compétences
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoindre Microsoft Insider

Ces informations vous ont-elles été utiles ?

Dans quelle mesure êtes-vous satisfait(e) de la qualité de la traduction ?
Qu’est-ce qui a affecté votre expérience ?

Nous vous remercions pour vos commentaires.

×