Créer des fonctions personnalisées dans Excel

Même si Excel inclut une multitude de fonctions de feuille de calcul intégrées, il est probable qu’il n’existe pas de fonction pour chaque type de calcul que vous effectuez. Les concepteurs d’Excel ne pouvaient pas anticiper les besoins en calculs de chaque utilisateur. À la place, Excel vous permet de créer des fonctions personnalisées, qui sont décrites dans cet article.

Vous recherchez des informations sur la création d’une fonction JavaScript personnalisée que vous pouvez exécuter sur Excel pour Windows, Excel pour Mac ou Excel Online ? Vous pouvez voir l’article vue d’ensemble des fonctions personnalisées d’Excel.

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. Tout d’abord, ils utilisent des procédures de fonction au lieu de procédures Sub . Il s’agit d’une instruction fonction au lieu d’une instruction Sub et se terminent par la fonction end au lieu de End Sub. Deuxièmement, il effectue des calculs au lieu d’entreprendre des actions. Certains types d’instructions, tels que les déclarations qui sélectionnent et présentent des plages de mise en forme, 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 pouvez utiliser Visual Basic Editor (VBE)qui s’ouvre dans une nouvelle fenêtre séparée d’Excel.

Supposons que votre société dispose d’une remise de 10% sur la vente d’un produit, à condition que la commande contenait plus de 100 unités. Les paragraphes suivants montrent une fonction de calcul de cette remise.

L’exemple ci-dessous montre un bon de commande qui recense chaque article, quantité, prix, remise (le cas échéant) et le prix global obtenu.

Exemple de bon de commande sans fonction personnalisée

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

  1. Appuyez sur ALT + F11 pour ouvrir Visual Basic Editor (sur 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 l’éditeur Visual Basic.

  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. Le retrait est uniquement destiné à votre avantage, et est facultatif, car le code sera exécuté avec ou sans celui-ci. Après avoir tapé une ligne mise en retrait, l’éditeur Visual Basic suppose que votre ligne suivante sera mise en retrait de façon similaire. Pour vous déplacer entre les taquets de tabulation (à gauche), appuyez sur MAJ + TAB.

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

= REMISE (D7; E7)

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

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

Examinons la façon dont Excel interprète cette procédure fonction. Lorsque vous appuyez sur entrée, Excel recherche la remise sur le nom dans le classeur actif et détermine qu’il s’agit d’une fonction personnalisée dans un module VBA. Les noms d’arguments entre parenthèses, Quantity et Pricesont des espaces réservés pour les valeurs sur lesquelles le calcul de la remise est basé.

Exemple de bon de commande avec fonction personnalisée

L’instruction if dans le bloc de code suivant examine l’argument Quantity et détermine si le nombre d’éléments 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’éléments vendus est supérieur ou égal à 100, VBA exécute l’instruction suivante, qui multiplie la valeur de quantité par la valeur Price , puis multiplie le résultat par 0,1:

Discount = quantity * price * 0.1

Le résultat est stocké en tant que remisevariable. Une instruction VBA qui stocke une valeur dans une variable est appelée instruction d' assignation , car elle évalue l’expression sur le côté droit du signe égal et attribue le résultat au nom de la variable sur la gauche. Dans la mesure où la remise variable porte le même nom que la procédure fonction, la valeur stockée dans la variable est renvoyée à la formule de feuille de calcul qui a appelé la fonction de remise.

Si le nombre est inférieur à 100, VBA exécute l’instruction suivante:

Discount = 0

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

Discount = Application.Round(Discount, 2)

VBA ne possède pas de fonction arrondi, mais Excel le fait. 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 l' application Word avant le mot arrondi. Utilisez cette syntaxe chaque fois que vous devez accéder à une fonction Excel à partir d’un module VBA.

Une fonction personnalisée doit commencer par une instruction fonction et se terminer par une instruction end fonction. Outre le nom de la fonction, l’instruction fonction spécifie généralement un ou plusieurs arguments. Toutefois, vous pouvez créer une fonction sans arguments. Excel inclut plusieurs fonctions intégrées, à savoir Alea et maintenant, par exemple, n’utilisez pas d’arguments.

Suite à l’instruction fonction, une procédure fonction inclut une ou plusieurs instructions VBA qui permettent de prendre des décisions et d’effectuer des calculs à l’aide des arguments transmis à la fonction. Enfin, dans le cadre de la procédure Function, vous devez inclure une instruction qui assigne 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 à effectuer une action autre 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 redimensionner les fenêtres, modifier une formule dans une cellule ou changer les options de police, de couleur ou de motif pour le texte dans une cellule. Si vous incluez le code «action» de ce genre dans une procédure fonction, la fonction renvoie la #VALUE! .

Il est possible d’afficher une boîte de dialogue dans la procédure d’une fonction (à l’exception des calculs). Vous pouvez utiliser une instruction InputBox dans une fonction personnalisée comme moyen d’obtenir une entrée de l’utilisateur exécutant la fonction. Vous pouvez utiliser une instruction BoîteMsg comme moyen de transmettre des informations à l’utilisateur. Vous pouvez également utiliser des boîtes de dialogue personnalisées, ou des formulairesutilisateur, mais ce n’est pas le cadre de cette présentation.

Même les macros et fonctions personnalisées simples peuvent être difficiles à lire. Vous pouvez les rendre plus faciles à comprendre en tapant un 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 ceux-ci permettent à vous-même ou à d’autres personnes de conserver votre code VBA en temps passé. Si vous avez besoin d’apporter des modifications au code à l’avenir, vous pourrez plus facilement 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 sur la même ligne, de sorte que vous pouvez créer des commentaires sur des lignes en tant que telles ou sur le côté droit de lignes contenant du code VBA. Vous pouvez commencer un bloc de code relativement long avec un commentaire expliquant son objectif global et utiliser des commentaires incorporés pour documenter des instructions individuelles.

Une autre méthode pour documenter vos macros et fonctions personnalisées consiste à leur donner des noms descriptifs. Par exemple, plutôt que de nommer une étiquettede macro, vous pouvez la nommer MonthLabels pour décrire plus en particulier la finalité 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 similaires, mais pas identiques.

La façon dont vous documentez vos macros et fonctions personnalisées est une question de préférences personnelles. 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 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? erreur 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 du nom du classeur dans lequel réside la fonction. Par exemple, si vous créez une fonction nommé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 (), et pas simplement = discount ().

Vous pouvez vous faire économiser des séquences de touches (et éventuellement des erreurs de frappe) en sélectionnant les fonctions personnalisées dans la boîte de dialogue Insérer une fonction. Vos fonctions personnalisées s’affichent dans la catégorie définie par l’utilisateur:

Boîte de dialogue Insérer une fonction

Il est désormais plus facile de rendre les fonctions personnalisées disponibles à tout moment en les stockant dans un classeur distinct, puis en enregistrant ce classeur sous la forme d’un complément. Vous pouvez ensuite rendre le complément disponible chaque fois que vous exécutez Excel. Pour cela, procédez comme suit:

  1. Une fois que vous avez créé les fonctions dont vous avez besoin, cliquez sur fichier > Enregistrer sous.

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

  2. Dans la boîte de dialogue Enregistrer sous , ouvrez la liste déroulante type de fichier , puis sélectionnez complément Excel. Enregistrez le classeur sous un nom identifiable (par exemple, MyFunctions) dans le dossier AddIns . La boîte de dialogue Enregistrer sous propose alors ce dossier; il vous suffit donc d’accepter l’emplacement par défaut.

  3. Une fois que vous avez enregistré le classeur, cliquez sur fichier > Options Excel.

    Dans Excel 2007, cliquez sur le bouton Microsoft Office, puis sur 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 atteindre .

  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 illustré ci-dessous.

    Boîte de dialogue Compléments

  1. Une fois que vous avez 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 de fichier , puis sélectionnez complément Excel. Enregistrez le classeur sous un nom identifiable (par exemple, MyFunctions.

  3. Une fois que vous avez 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 activez la case à cocher en regard de votre complément dans la zone macros complémentaires disponibles .

Après avoir suivi ces étapes, vos fonctions personnalisées seront disponibles chaque fois que vous exécuterez Excel. Si vous voulez ajouter à votre bibliothèque de fonctions, retournez dans Visual Basic Editor. Dans l’Explorateur de projets de Visual Basic Editor sous un titre VBAProject, vous verrez un module intitulé après votre fichier de complément. Votre complément aura pour extension. xlam.

Module nommé dans VBE

Le double-clic sur ce module dans l’Explorateur de projets entraîne l’affichage de votre code de fonction par Visual Basic Editor. Pour ajouter une nouvelle fonction, positionnez le point d’insertion à la fin de l’instruction de la fonction end qui termine la dernière fonction dans la fenêtre de code et commencez à taper. Vous pouvez créer autant de fonctions que nécessaire de cette manière, et celles-ci sont toujours disponibles dans la catégorie défini par l’utilisateur de la boîte de dialogue Insérer une fonction .

Ce contenu a été créé par Mark dense et Craig Stinson dans le cadre de son livre Microsoft Office Excel 2007. Ce dernier a été mis à jour de manière à s’appliquer également aux versions plus récentes d’Excel.

Vous avez besoin d’une aide supplémentaire ?

Vous pouvez toujours consulter un expert de la communauté technique Excel, obtenir une assistance dans la communauté Answers ou suggérer une nouvelle fonctionnalité ou une amélioration sur le forum Excel User Voice.

Remarque :  Cette page a été traduite automatiquement et peut donc contenir des erreurs grammaticales ou des imprécisions. Notre objectif est de faire en sorte que ce contenu vous soit utile. Pouvez vous nous dire si les informations vous ont été utiles ? Voici l’article en anglais pour référence.

Développez vos compétences dans Office
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×