Créer des fonctions Excel en VBA disponibles partout

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Créer des fonctions Excel en VBA

Nous avons déjà utilisé des fonctions Visual Basic au travers des procédures de code que nous avons créées dans les précédentes formations. Mais ces fonctions, telle que la fonction RGB() sont des méthodes particulières dans la mesure où elles ne s'appliquent pas à des objets. Ce que nous proposons d'aborder ici est la création de procédures particulières avec le mot clé function qui permettra d'ajouter de nouvelles fonctions de calcul à la bibliothèque Excel. Ces fonctions, créées de toutes pièces en VBA, seront exploitables directement depuis la feuille Excel comme la somme automatique, pour ne citer qu'elle.

Le classeur

• Télécharger le classeur vba-excel-function.xlsm en cliquant sur son lien,
• L'ouvrir dans Excel,

Ce classeur est constitué de deux feuilles que nous allons utiliser pour tester les fonctions que nous allons créer. La feuille Salariés liste les noms de chacun d'eux avec leur date d'entrée dans l'entreprise. Dans la colonne Ancienneté, nous devrons calculer leur ancienneté en fonction de cette date. Nous créerons donc une fonction Age() en VBA Excel. La feuille Budget prévisionnel énumère les charges et leurs sommes hors taxes pour une entreprise. Nous devrons calculer ces montants TTC, en fonction de la TVA, dans la colonne prévue à cet effet. Nous allons donc créer une fonction Ttc() en VBA Excel qui permettra de réaliser ce calcul sans devoir poser l'opération.

La fonction Ttc()

• Basculer dans l'éditeur de code Visual Basic avec le raccourci ALT + F11 par exemple,

Nous souhaitons que cette fonction soit disponible pour toutes les utilisations à venir d'Excel. Nous n'allons donc pas développer le code uniquement pour ce classeur mais pour tous. La macro doit donc être saisie dans le classeur de macros personnelles. Pour ce faire :

• Depuis la fenêtre Projet sur la gauche, sélectionner VBAProject (PERSONAL.XLSB),
• Cliquer ensuite sur le menu Insertion et choisir Module,

Le module apparaît instantanément dans l'explorateur de projet. Pour créer la fonction, nous allons saisir son code dans la fenêtre de code du module, au centre de l'espace de travail. Une fonction est reconnue en tant que telle par Excel lorsqu'elle est déclarée par le mot clé Function. Le nom de la fonction doit suivre (sans espace et sans accent), puis entre les parenthèses, les paramètres dont elle a besoin. Nous nommerons notre fonction : Ttc. Pour réaliser son calcul elle a besoin de deux paramètres : Le montant hors taxe et le taux de TVA.

• Créer la fonction comme suit :

Function Ttc(montant_HT, taux_TVA)

Lorsque vous enfoncez la touche Entrée après avoir fermé la parenthèse, vous remarquez que VBA ferme cette fonction à l'aide des mots clés End Function. Nous devons donc écrire le code qui réalise le calcul, entre ses deux bornes. Notre fonction Ttc() attend deux paramètres reconnus désormais sous les noms montant_HT et taux_TVA. Il s'agit de variables qui prendront la valeur des cellules que nous désignerons depuis la feuille Excel au moment d'exploiter la fonction. Le code est quasiment terminé ! En effet, il nous manque une ligne, celle du calcul dont le résultat doit être enregistré dans une variable. Et cette variable est la fonction elle-même reconnue par son nom, soit Ttc. C'est parce que le résultat est affecté au nom de la fonction que cette dernière saura retourner le résultat du calcul dans la cellule Excel. Le calcul consiste à ajouter au montant hors taxes, la part de pourcentage due à la tva. Décomposé, le calcul est le suivant : Ttc = montant_HT + montant_HT x taux_TVA. Ce qui donne, une fois factorisé : Ttc = montant_HT x (1 + taux_TVA).

• Ajouter la ligne permettant de réaliser le calcul dans les bornes de la fonction,

• Enregistrer le travail (CTRL + S),
• Basculer sur la feuille Budget prévisionnel d'Excel,
• Sélectionner toute la plage de cellules des montants TTC, soit de H9 à H14,
• Taper = suivi des premières lettres de la fonction,

Vous remarquez que contrairement aux autres fonctions Excel, notre fonction n'est pas proposée dans la liste contextuelle. En effet, enregistrée dans le classeur de macros personnelles, elle est bien disponible pour tous les classeurs Excel mais, pour qu'elle soit disponible directement par son nom, nous devrons réaliser une opération spécifique, nous y reviendrons.

• Enfoncer la touche Echap pour annuler le calcul précédent,
• Cliquer sur la flèche du bouton Somme automatique dans le ruban Accueil,
• Dans la liste, choisir Autres fonctions,
• Dans la boîte de dialogue qui suit, choisir la catégorie Personnalisées,
• Sélectionner la fonction Ttc dont le nom est précédé de Personal.xlsb!,
• Puis, cliquer sur Ok,

L'assistant fonction se déclenche indiquant les valeurs (paramètres) à renseigner, tel que nous l'avons codé.

• Sélectionner la première cellule du montant hors taxe, soit F9,
• Cliquer dans la zone Taux_TVA et sélectionner la cellule de la TVA, soit E6,
• Enfoncer la touche F4 du clavier de manière à figer cette dernière,

En effet, le taux de TVA est dans une cellule isolée. Si nous ne la figeons pas, lorsque nous reproduirons le calcul en tirant la poignée, ses références se déplaceront et le calcul sera erroné sur les autres cellules. On parle de références absolues. La formation Excel sur les références absolues permet de vous familiariser avec ces notions.

• Valider par Ok,
• Tirer la poignée de la cellule sur l'ensemble de la colonne TTC,

La fonction marche très bien mais il est frustrant de ne pas pouvoir l'appeler directement par son nom en saisissant la formule dans la cellule. Pour pallier le problème, nous devons créer un complément Excel et y faire référence dans toutes les utilisations à venir.

Complément Excel

• Cliquer sur le ruban Fichier et choisir Enregistrer sous dans la liste,
• Cliquer ensuite sur Parcourir,
• Dans la boîte de dialogue qui suit, choisir Complément Excel (*.xlam) dans la liste Type,
• Puis, cliquer sur Enregistrer,

De retour sur le classeur, son nom n'a pas changé, nous en avons juste créé un complément pour pouvoir exploiter les modules et les fonctions. Pour ce faire :

• Cliquer de nouveau sur Fichier puis choisir Options dans la liste,
• Dans la boîte de dialogue qui suit, sélectionner la catégorie Compléments,
• Puis cliquer sur le bouton Atteindre en bas de la boîte de dialogue,
• Dans la fenêtre suivante, cliquer sur parcourir pour désigner le complément enregistré,
• Cocher la case du complément précédemment enregistré,
• Puis valider par Ok les deux boîtes de dialogues,

• Revenir dans l'éditeur de code,
• Sélectionner toutes les lignes de code de la fonction, et les couper (CTRL + X),
• Dans le volet de gauche, sélectionner le VBAProject correspondant au complément,
• Cliquer sur le menu Insertion puis sur Module,
• Coller le code dans le module du complément et Enregistrer (CTRL + S),
• Revenir sur la feuille Budget prévisionnel,
• Supprimer tous les résultats précédents du calcul du TTC,
• Sélectionner de nouveau toute la plage des TTC,
• Taper = suivi des premières lettres de la fonction Ttc(),

Cette fois la fonction est reconnue par son nom comme en témoigne l'assistant contextuel qui se déclenche.

• Ouvrir la parenthèse,
• Sélectionner le premier montant HT, soit F9,
• Taper un point-virgule (;) pour passer au deuxième argument, le taux de tva,
• Sélectionner la cellule E6 et enfoncer la touche F4 du clavier pour la figer,
• Réaliser la combinaison CTRL + Entrée pour valider le calcul et le répliquer sur l'ensemble des cellules présélectionnées,

Nous obtenons bien le même résultat mais désormais, la fonction Ttc() est plus simple à utiliser et à appeler. Dès lors, toute nouvelle fonction VBA que vous créerez dans ce complément, sera directement accessible par son nom depuis n'importe quel classeur Excel. Nous allons donc en profiter pour créer nos autres fonctions à cet emplacement. Mais avant cela, en tant que puristes il y a quelques petites imperfections que nous ne pouvons pas laisser passer.

• Revenir dans l'éditeur Visual Basic sur le code de la fonction,

Ce qui ne va pas, c'est que nous passons deux paramètres à cette fonction Ttc(). Or ces deux paramètres sont des variables et, toute variable doit être dimensionnée, typée. C'est que qu'apprend le support de formation sur les variables. D'ailleurs la fonction elle-même est une variable puisque le résultat du calcul est stocké dans le nom de la fonction lui-même. C'est ainsi qu'Excel retourne la valeur du calcul dans la cellule. Donc elle doit être typée au même titre que les deux paramètres. Sachez qu'une variable non typée dans VBA prend par défaut le type Variant. Il s'agit du type qui prend le plus de ressources, car non défini et peut, par défaut, accueillir n'importe quelle variable ou objet. Pallions donc le problème. Les deux paramètres sont des nombres réels, car ils peuvent avoir des décimales. Nous allons les déclarer comme des réels simples, le type est Single en VBA. La fonction Ttc() effectue des opérations sur des nombres réels. Dans certains cas, son résultat peut conduire à un réel avec beaucoup de décimales. Le réel qui permet de gérer un grand nombre de décimales est le type Double en VBA. Nous allons donc déclarer la fonction Ttc() en tant que Double.

• Ajouter les déclarations à la fonction.

Function Ttc(montant_HT As Single, taux_TVA As Single) As Double
Ttc = montant_HT * (1 + taux_TVA)
End Function

De retour sur la feuille Excel, si vous utilisez la fonction, rien ne change pour l'utilisateur. Certes, mais c'est beaucoup plus propre ainsi et important en termes de ressources.

La fonction Age()
Cette fonction doit en réalité nous permettre de calculer la différence, en années, entre deux dates, en tenant compte des années bissextiles. Ces deux dates, sont la date du jour et la date à passer en paramètre, qui peut être une date de naissance ou, comme dans notre cas, la date d'entrée du salarié dans l'entreprise. Nous nommerons le paramètre : la_date. Forcément, nous devons le typer comme une date soit Date en VBA. La fonction Age quant à elle doit retourner le résultat en nombre d'années, soit un nombre entier. Nous la déclarerons ainsi en tant qu'Integer.

• A la suite, dans la feuille de code du complément, créer la fonction Age(),

Function Age(la_date As Date) As Integer

End Function

Le calcul quant à lui, à stocker dans la variable Age, est très simple. Il consiste à faire la différence entre la date du jour, fonction Date en VBA Excel, et la date passée en paramètre. Le résultat est la différence entre ces deux dates en nombre de jours. Donc il faut ensuite diviser ce résultat par 365.25 (Nombre de jours dans une année) pour prendre en compte les années bissextiles, une fois sur quatre (0.25), et à prélever la partie entière de ce résultat grâce à la fonction VBA Excel Int(). Soit : Age = Int((Date - la_date) / 365.25)

• Ajouter la ligne de code du calcul à l'intérieur des bornes de la fonction,
• Enregistrer le travail,
• Basculer sur la feuille Salariés,
• Sélectionner la plage de cellules de la colonne Ancienneté, soit F6:F17,
• Taper = suivi du nom de la fonction Age et ouvrir la parenthèse,
• Sélectionner la première cellule de la date d'Entrée, soit E6,
• Fermer la parenthèse et valider par CTRL + Entrée pour répliquer le calcul sur toute la plage.

Vous obtenez tous les résultats d'un clin d'oeil avec une fonction dont le code est on ne peut plus simple. Si d'aventure, vos résultats s'affichent en Euro, il ne s'agit que d'une histoire de formatage que vous pouvez corriger à l'aide de la liste déroulante de la section Nombre du ruban Accueil.

La fonction doublons()
Avant de commencer à créer une telle fonction, nous avons besoin d'un tableau de valeurs sur lequel nous pourrons l'exploiter.

• Créer une nouvelle feuille,
• Saisir des valeurs, avec certaines redondantes, comme le propose la capture ci-dessous :

Nous allons maintenant créer une dernière fonction fort utile, la fonction doublons(). Celle-ci a pour objectif d'indiquer le nombre de fois qu'apparaît une valeur dans une plage de cellules. Elle permettra donc à l'utilisateur de savoir s'il y a des doublons et avec quelle fréquence. De fait, elle a besoin de deux paramètres, le premier est une variable de type Range pour la plage de cellules, le second est la valeur à vérifier, un entier par exemple, donc de type Integer. La fonction elle-même, retourne le nombre d'occurrences trouvé pour cette valeur, elle retourne donc un Integer.

• Créer la fonction doublons de type Integer, attendant les paramètres plage et valeur, respectivement en tant que Range et Integer :

Function doublons(plage As Range, valeur As Integer) As Integer

End Function

Cette fonction doit parcourir l'ensemble des cellules de la plage donnée en argument pour comparer la valeur de chacune avec celle passée en paramètre. Donc nous avons besoin d'une variable cellule à déclarer comme un Range pour parcourir cette plage. De plus, il nous faut comptabiliser chaque occurrence trouvée pour cette valeur. Donc nous avons besoin d'une variable frequence de type Integer pour compter les doublons.

• Déclarer les variables cellule et frequence,

Dim cellule As Range: Dim frequence As Integer

Pour parcourir la plage de cellules, nous devons utiliser uneboucle For Each. Le support de formation sur les boucles For Each en VBA Excel montre différentes façons de parcourir des groupes d'objets pour réaliser des traitements automatisés.

• Ajouter les bornes de la boucle For Each comme suit :

For Each cellule In plage

Next cellule

Littéralement, il faut traduire par : Pour chaque cellule dans la plage de cellules (For Each cellule In plage). Chaque boucle For Each doit se fermer par le mot clé Next suivi du nom de la variable qui parcourt la boucle. A l'intérieur de la boucle, nous devons faire un test pour savoir si la valeur de la cellule en cours est identique à la valeur testée, passée en paramètre. C'est l'instruction If traitée par un support de formation qui va nous permettre de vérifier la condition. Si la valeur de la cellule est égale à la valeur de la variable nommée valeur alors...

• Ajouter les bornes de l'instruction If, comme suit :

If(cellule.Value = valeur) Then

End If

Si le critère est vérifié, à l'intérieur du If, nous devons réaliser une action. Cette action consiste à incrémenter la variable frequence pour comptabiliser les doublons (frequence=frequence+1).

• Ajouter l'incrémentation de la variable à l'intérieur du If :

frequence = frequence + 1

Enfin, après la boucle, c'est-à-dire une fois le traitement de la plage terminé, il ne faut pas oublier d'enregistrer cette valeur obtenue, dans la variable de la fonction. En effet, souvenez-vous, c'est elle qui permet de retourner dans la feuille Excel, le résultat de la formule.

• Après la boucle, ajouter l'affectation suivante :

doublons = frequence

• Enregistrer le travail (CTRL + S),
• Afficher la nouvelle feuille,
• Sélectionner une cellule sous le tableau,
• Taper le nom de la fonction et ouvrir une parenthèse : doublons(,
• Sélectionner toutes les valeurs du tableau à la souris,

Vous définissez ainsi le premier paramètre : plage.

• Taper un point-virgule (;) pour passer au deuxième argument,
• Taper une valeur à tester ou sélectionner une cellule contenant cette valeur,

Vous définissez ainsi le deuxième argument de la fonction : valeur.

• Fermer la parenthèse et valider le calcul par Entrée.

Notre fonction marche très bien. Dans notre cas, elle retourne la valeur 6. Le nombre 36 est en effet répété 6 fois dans notre tableau, ce qui était quasiment impossible à déceler d'un seul coup d'oeil.


Le code complet de cette fonction doublons() est rappelé ci-dessous : Function doublons(plage As Range, valeur As Integer) As Integer
Dim cellule As Range: Dim frequence As Integer

For Each cellule In plage
If (cellule.Value = valeur) Then
frequence = frequence + 1
End If
Next cellule

doublons = frequence
End Function