Créer des fonctions personnalisées dans Excel

Bien qu’Excel inclut 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. Excel vous permet de créer des fonctions personnalisées, qui sont expliquées dans cet article.

Création d’une fonction personnalisée simple

Les fonctions personnalisées, telles que les macros, utilisent le langage de programmation Visual Basic pour Applications (VBA). Ils diffèrent des macros de deux manières significatives. Tout d’abord, ils utilisent des procédures de fonction au lieu de sous-procédures . C’est-à-dire qu’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’agir. Certains types d’instructions, telles que les instructions qui sélectionnent et mettent en forme des plages, 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 devez utiliser le Visual Basic Editor (VBE), qui s’ouvre dans une nouvelle fenêtre distincte d’Excel.

Supposons que votre entreprise offre une remise sur quantité de 10 % sur la vente d’un produit, à condition que la commande soit supérieure à 100 unités. Dans les paragraphes suivants, nous allons présenter une fonction permettant de calculer cette remise.

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

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>un 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
   
   

Utilisation de fonctions personnalisées

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 USD par unité et renvoie 950,00 USD.

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 d’une feuille de calcul, vous devez inclure ces deux arguments. Dans la formule =DISCOUNT(D7,E7), D7 est l’argument de quantité et E7 est l’argument de prix . Vous pouvez maintenant copier la formule DISCOUNT dans G8 :G13 pour obtenir les résultats 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 des arguments entre parenthèses, Quantité et Prix, sont des espaces réservés aux valeurs sur lesquelles repose le calcul de la remise.

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 la quantité par la valeur du 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 Remise. 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 situé à 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 renvoyée à la formule de la feuille de calcul qui appelait 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 ARRONDI, 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, vous 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.

Présentation des règles de fonction personnalisée

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

Après l’instruction Function, 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, quelque part dans la procédure de fonction, vous devez inclure une instruction qui attribue une valeur à une variable portant le même nom que la fonction. Cette valeur est renvoyée à la formule qui appelle la fonction.

Utilisation de mots clés VBA dans des fonctions personnalisées

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 redimensionner les fenêtres, modifier une formule dans une cellule ou modifier les options de police, de couleur ou de motif du texte dans une cellule. Si vous incluez un code « action » de ce type dans une procédure de fonction, la fonction renvoie le #VALUE ! erreur.

La seule action qu’une procédure de fonction peut effectuer (en dehors de l’exécution de calculs) est d’afficher une boîte de dialogue. Vous pouvez utiliser une instruction InputBox dans une fonction personnalisée afin 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 des formulaires utilisateur, mais c’est un sujet qui dépasse le cadre de cette introduction.

Documentation des macros et des fonctions personnalisées

Même les macros simples et les fonctions personnalisées 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 de ce type facilite la gestion de 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.

Une apostrophe indique à Excel d’ignorer tout ce qui se trouve à droite sur la même ligne. Vous pouvez donc 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 par un commentaire qui explique son objectif général, puis utiliser des commentaires incorporés pour documenter des instructions individuelles.

Une autre façon de documenter vos macros et fonctions personnalisées est de leur donner des noms descriptifs. Par exemple, plutôt que de nommer une macro Labels, vous pouvez la nommer MonthLabels pour décrire plus précisément l’objectif qu’elle sert. 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 ayant 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. Ce qui est important, c’est d’adopter une méthode de documentation et de l’utiliser de manière cohérente.

Rendre vos fonctions personnalisées disponibles partout

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 un #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 du nom du classeur dans lequel elle réside. Par exemple, si vous créez une fonction appelée DISCOUNT dans un classeur intitulé 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 épargner quelques frappes (et d’éventuelles erreurs de frappe) 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 :

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 :

Une fois ces étapes effectuées, vos fonctions personnalisées seront disponibles chaque fois que vous exécuterez Excel. Si vous souhaitez compléter votre bibliothèque de fonctions, revenez à Visual Basic Editor. Si vous regardez dans l’Explorer de projets de 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.

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

À propos des auteurs

Ce contenu a été initialement rédigé 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 nouvelles versions 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 de l’aide dans les Communautés.