Créer des fonctions personnalisées dans Excel

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 Rédacteur (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.

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

1. Appuyez sur Alt+F11 pour ouvrir le Rédacteur Visual Basic (sur le Mac, appuyez sur FN+ALT+F11), puis cliquez sur Insérer > Module. Une nouvelle fenêtre de module s’affiche à droite du Rédacteur 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
   

Vous êtes maintenant prêt à utiliser la nouvelle fonction DISCOUNT. Fermez le Rédacteur 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 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é.

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 ! .

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.

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 :

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 :

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 à la Rédacteur Visual Basic. Si vous regardez dans l’Explorer projet Visual Basic Rédacteur 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.

En double-cliquant sur ce module dans le Explorer Project, le Rédacteur Visual Basic 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.