Créer des fonctions personnalisées dans Excel

Remarque : Nous faisons de notre mieux pour vous fournir le contenu d’aide le plus récent aussi rapidement que possible dans votre langue. 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 indiquer en bas de page si ces informations vous ont aidé ? Voici l’article en anglais à des fins de référence aisée.

Bien que Excel inclut une multitude de fonctions de feuille de calcul intégré, il est probable qu’est qu'il n’a pas une fonction pour chaque type de calcul que vous effectuez. Les concepteurs d’Excel n’a pas pu éventuellement anticiper les besoins de calcul de tous les utilisateurs. En revanche, Excel vous offre la possibilité de créer des fonctions personnalisées, elles sont décrites dans cet article.

Fonctions personnalisées, comme les macros, utilisent le langage de programmation Visual Basic pour Applications (VBA). Il diffère des macros de deux manières importantes. Tout d’abord, ils utilisent les procédures Function au lieu de procédures Sub . En d’autres termes, avec une instruction de fonction au lieu d’une instruction Sub et de fin à Fin fonction au lieu de End Subau départ. Ensuite, ils effectuent des calculs au lieu de prendre des actions. Certains types d’instructions, tels que des instructions que sélectionner et mettre en forme des plages, sont exclus de 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 utilisez Visual Basic Editor (VBE), qui s’ouvre dans une nouvelle fenêtre distincte à partir d’Excel.

Supposons que votre entreprise propose une remise sur quantité de 10 % sur la vente d’un produit fourni l’ordre est de plus de 100 unités. Dans les paragraphes suivants, nous allons expliquer une fonction pour calculer cette remise.

L’exemple ci-dessous montre un formulaire de commande qui répertorie chaque élément, quantité, prix, la remise (le cas échéant) et le prix total qui en résulte.

Exemple de formulaire ordre sans une fonction personnalisée

Pour créer une fonction personnalisée remise 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 Insertion > 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 augmenter le retrait de lignes. La mise en retrait est à votre intention uniquement et est facultative, comme le code s’exécutera avec ou sans qu’il. Après avoir tapé une ligne en retrait, l’éditeur Visual Basic suppose que votre prochaine ligne sera mis en retrait de la même manière. Pour déplacer (autrement dit, à gauche) caractère un onglet, appuyez sur Maj + Tab.

Vous êtes maintenant prêt à utiliser la nouvelle fonction remise. Fermez Visual Basic Editor, sélectionnez la cellule G7 et tapez les informations suivantes :

=DISCOUNT(D7,E7)

Excel calcule la remise de 10 pour cent sur 200 unités en $47.50 unitaire et retourne $950.00.

Dans la première ligne de votre code VBA, fonction DISCOUNT(quantity, price), vous avez indiqué que la fonction remise requiert deux arguments, la 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 de prix . Désormais, vous pouvez copier la formule de remise à G8:G13 pour obtenir le résultat illustré ci-dessous.

Prenons l’exemple d’interprétation cette procédure function dans Excel. Lorsque vous appuyez sur entrée, Excel recherche du nom de remise dans le classeur actif et il trouve une fonction personnalisée dans un module VBA. Les noms d’arguments entourées parenthèses, quantité et prix, sont des espaces réservés pour les valeurs sur lequel repose le calcul de la remise.

Exemple de formulaire de commande avec une fonction personnalisée

La si instruction dans le bloc de code suivant examine l’argument quantité et détermine si le nombre d’articles vendus est supérieure ou égale à 100 :

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

Si le nombre d’articles vendus est supérieure ou égale à 100, VBA exécute l’instruction suivante, qui multiplie la valeur 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 la variable de remise. Une instruction VBA qui stocke une valeur dans une variable s’appelle une instruction d’affectation , car elle évalue l’expression sur le côté droit du signe égal et assigne le résultat au nom de la variable sur la gauche. Étant donné que la variable de remise a le même nom que la procédure function, la valeur stockée dans la variable est renvoyée à la formule de feuille de calcul qui ont appelé la fonction remise.

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

Discount = 0

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

Discount = Application.Round(Discount, 2)

VBA ne comporte aucune fonction arrondi, mais Excel ne. Par conséquent, pour utiliser la fonction arrondi dans la présente déclaration, indiquer VBA pour rechercher la méthode Round (fonction) dans l’objet Application (Excel). Pour ce faire, ajout du mot Application devant le mot arrondi. Utilisez la syntaxe suivante chaque fois que vous devez accéder à une fonction Excel à partir d’un module VBA.

Une fonction personnalisée doit commencer par une instruction Function et se termine par une instruction End Function. Outre le nom de la fonction, l’instruction Function spécifie généralement un ou plusieurs arguments. Vous pouvez toutefois, créer une fonction avec aucun argument. Excel inclut plusieurs fonctions intégrées — ALEA et maintenant, par exemple, qui n’utilisent des arguments.

Suivre l’instruction Function, une procédure function comprend une ou plusieurs instructions VBA qui prendre des décisions et effectuent des calculs en utilisant les arguments transmis à la fonction. Enfin, n’importe où dans la procédure de la fonction, vous devez inclure une instruction qui attribue une valeur à une variable avec 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 des fonctions personnalisées est plus petit que le nombre que vous pouvez utiliser dans les macros. Fonctions personnalisées n’êtes pas autorisées à faire tout ce que retour une valeur dans une formule dans une feuille de calcul ou à une expression utilisée dans une autre macro VBA ou fonction. Par exemple, fonctions personnalisées ne peuvent pas redimensionner les fenêtres, modifier une formule dans une cellule ou modifier la police, couleur ou des options de modèle pour le texte dans une cellule. Si vous incluez code « action » de ce type dans une procédure function, la fonction renvoie la #VALUE ! erreur.

L’un action une procédure function possibles (à l’exception réalisation de calculs) consiste à afficher une boîte de dialogue. Vous pouvez utiliser une instruction InputBox dans une fonction personnalisée afin d’obtenir la saisie de l’utilisateur qui exécute la fonction. Vous pouvez utiliser une instruction MsgBox comme un moyen de transporter des informations à l’utilisateur. Vous pouvez également utiliser les boîtes de dialogue personnalisées, ou de formulaires utilisateur, mais qui est un sujet dépasse le cadre de cette présentation.

Macros même simples et des fonctions personnalisées peuvent être difficiles à lire. Vous pouvez les rendre plus facile à comprendre en tapant un texte explicatif dans le formulaire de commentaires. Ajouter des commentaires en faisant précéder le texte explicatif une apostrophe. Par exemple, l’exemple suivant montre la fonction remise avec des commentaires. Ajout de commentaires comme ceux-ci simplifie le vous ou d’autres personnes à gérer votre code VBA avec le temps. Si vous devez effectuer une modification dans le code à l’avenir, vous aurez plus facilement à comprendre ce que vous avez fait à l’origine.

Exemple d’une fonction VBA avec des commentaires

Une apostrophe indique à Excel d’ignorer tous les éléments vers la droite sur la même ligne, afin de créer des commentaires sur les lignes en tant que telles ou sur le côté droit de lignes qui contiennent VBA de code. Vous pouvez commencer un bloc de code avec un commentaire qui explique son objectif global relativement long et ensuite utiliser des commentaires incorporés à des instructions individuelles document.

Une autre façon de documenter vos macros et des fonctions personnalisées consiste à leur donner un nom descriptif. Par exemple, plutôt qu’une macro étiquettesnom, vous pouvez le nommer MonthLabels pour décrire plus précisément l’objectif que gère la macro. Utilisation de noms descriptifs pour les macros et des fonctions personnalisées est particulièrement utile lorsque vous avez créé plusieurs procédures, particulièrement si vous créez des procédures qui ont des objectifs similaires, mais pas identiques.

Comment vous documenter vos macros et les fonctions personnalisées est une question de préférences personnelles. Ce qui est important consiste à adopter une méthode de documentation et utilisez de façon cohérente.

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

Vous pouvez enregistrer vous-même certaines combinaisons de touches (et les 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éfini par l’utilisateur :

Boîte de dialogue Insérer une fonction

Méthodes simples pour rendre vos fonctions personnalisées à tout moment consiste à les stocker dans un classeur distinct, puis enregistrez ce classeur comme un complément. Vous pouvez ensuite rendre le complément disponible chaque fois que vous utilisez Excel. Voici comment procéder :

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

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

  2. Dans la boîte de dialogue Enregistrer sous, ouvrez la liste déroulante Type de fichier et sélectionnez Excel Add-In. Enregistrez le classeur sous un nom reconnaissable, tel que MyFunctions, dans le dossier AddIns . La boîte de dialogue Enregistrer sous proposera ce dossier, il vous souhaitez est 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 cliquez 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 OK.

  6. Dans la boîte de dialogue Macros complémentaires, 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. Après avoir créé les fonctions que 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 et sélectionnez Excel Add-In. Enregistrez le classeur sous un nom reconnaissable, tel que 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 cochez la case en regard de votre complément dans la zone Macros complémentaires disponibles.

Après avoir suivi ces étapes, vos fonctions personnalisées sera disponibles chaque fois que vous exécutez Excel. Si vous voulez ajouter à votre bibliothèque de fonctions, revenir à l’éditeur Visual Basic. Si vous regardez dans l’Explorateur de projets Visual Basic Editor sous un titre du projet VBA, vous verrez un module nommé d’après votre fichier complément. Votre complément a l’extension .xlam.

Module nommé dans VBE

En double-cliquant sur ce module dans l’Explorateur de projets entraîne l’éditeur Visual Basic pour afficher votre code de la fonction. Pour ajouter une nouvelle fonction, positionnez le point d’insertion après l’instruction End Function qui met fin à la dernière fonction dans la fenêtre de Code et commencez à taper. Vous pouvez créer en tant que de nombreuses fonctions que vous avez besoin de cette manière, et ils seront toujours être disponibles dans la catégorie défini par l’utilisateur dans 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 appliquer à ainsi que des 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.

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.

×