Création d’une fonction personnalisée sous Excel

Vous savez certainement qu’Excel permet de faire des calculs sur des lignes et des colonnes en appliquant des formules intégrées (SOMME, RECHERCHEV, etc.) classées par catégorie (logique, date et heure, etc.). Mais savez-vous que vous pouvez créer vos propres fonctions et les utiliser de la même façon dans vos feuilles de calcul ?

Excel vous permet d’utiliser une multitude de fonctions intégrées : des fonctions statistiques (max, min, écart type, etc.) ou mathématiques (sin, cos, valeur absolue, etc.).

En outre, il est aussi possible de créer vos propres fonctions.

C’est ce que nous allons voir ici sur la base d’un exemple bien concret et pratique : les calculs hors-taxes et TTC.

Même si la création de fonctions personnalisées fait appel au langage VBA, cela reste tout de même à la portée d’un utilisateur déjà familiarisé avec les fonctions.

Par défaut, les fonctions personnalisées sont disponibles pour toutes les feuilles de calcul dans le classeur qui contient la fonction personnalisée.

L’appel à une fonction personnalisée se fait tout comme pour une fonction intégrée.

logo-excel

Accédez à une fonction personnalisée

Les fonctions Excel sont regroupées par catégories, celles que vous envisagez de créer apparaîtront dans la catégorie Personnalisées.

Pour illustrer notre propos, nous allons créer une fonction CONVERT_HTTTC qui permet de calculer un montant HT à partir d’un montant TTC (ou l’inverse).

Une telle fonction nécessite comme paramètre le montant (TTC ou HT), la valeur du taux de TVA, mais aussi le sens de conversion (passage de HT vers TTC ou de TTC vers HT).

Pour ce dernier paramètre, il suffira de préciser VRAI s’il s’agit d’un passage HT vers TTC ou FAUX s’il s’agit de l’inverse.

Une fois la fonction CONVERT_HTTTC créée, il suffit d’écrire dans une cellule (comme pour toute autre fonction), le signe = suivi du nom de la fonction, d’une parenthèse ouvrante puis des trois paramètres séparés par un point virgule : le montant, le taux de TVA et l’information VRAI/FAUX selon qu’il s’agisse d’un passage de HT vers TTC ( préciser VRAI) ou l’inverse (préciser FAUX).

Par exemple :

=CONVERT_HTTTC(384;20;FAUX) calcule le montant HT de 384 pour un taux de TVA de 20 %, soit 320.

ou

=CONVERT_HTTTC(320;20;VRAI) calcule le montant TTC de 320 pour un taux de TVA de 20 %, soit 384.

Créez une fonction de calcul HT/TTC

Une fois Excel ouvert, appuyez au clavier sur les touches ALT + F11 pour ouvrir directement l’éditeur de macros VBA.

À partir de la fenêtre VBA, cliquez sur le menu Insertion puis Module.

Dans la fenêtre qui apparaît, saisissez les instructions de la fonction.

Le code de la fonction (à recopier strictement si vous n’êtes pas habitués au VBA) est le suivant :

[pastacode lang= »javascript » manual= »Function%20CONVERT_HTTTC(Montant%2C%20TauxTVA%2C%20HTversTTC%20As%20Boolean)%0A%0A%20%20%20%20If%20HTversTTC%20Then%0A%20%20%20%20%20%20%20%20CONVERT_HTTTC%20%3D%20Round(Montant%20%2B%20(Montant%20*%20TauxTVA)%20%2F%20100%2C%202)%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20CONVERT_HTTTC%20%3D%20Round(Montant%20%2F%20(100%20%2B%20TauxTVA)%20*%20100%2C%202)%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20End%20If%0A%0AEnd%20Function%0A » message= » » highlight= » » provider= »manual »/]

La fonction CONVERT_HTTTC attend trois informations :

  • Montant
  • TauxTVA
  • HTversTTC

Par exemple, pour passer de TTC à HT, le montant final est égal au montant saisi, divisé par 100 auquel on ajoute le taux de TVA.

Le résultat est ensuite multiplié par 100 puis arrondi (grâce à la fonction Round) à deux chiffres après la virgule.

excel-vba-module

L’appel de la fonction

Une fois la fenêtre de code VBA refermée, depuis le classeur Excel, cliquez sur Enregistrer sous puis sélectionnez le type *.XLSM pour pouvoir enregistrer votre classeur avec sa macro.

Dès lors, si vous ne vous êtes pas trompé dans la saisie du code VBA, elle est opérationnelle.

La seule différence avec une fonction intégrée est que vous n’aurez pas accès à une aide personnalisée sur la fonction.

C’est bien sûr possible, mais cela demande des manipulations qui dépassent le cadre de notre simple exemple…

En VBA, vous devez retrouver dans la fenêtre Projets la référence de votre classeur avec par défaut les trois feuilles de calcul dans l’arborescence Microsoft Excel Objects, mais aussi une arborescence Modules dans lequel l’élément Modulel contient le code de votre macro VBA.

Lors de vos saisies dans l’éditeur VBA, effectuez toutes vos saisies en minuscules, si vous ne vous êtes pas trompé, l’éditeur mettra de lui-même les majuscules ainsi que les couleurs sur les mots-clés et fonctions.

excel-formule-perso

Transformez votre classeur en complément Excel

Certains compléments comme le Solveur et l’Utilitaire d’analyse sont intégrés à Excel.

Vos compléments ne seront pas disponibles par défaut, il faudra les activer.

Pour que votre fonction CONVERT_HTTTC soit disponible pour n’importe quel utilisateur Excel, il faudra lui fournir le fichier XLAM correspondant.

Dans ce cas, vos fonctions personnalisées apparaîtront dans la catégorie Compléments Excel.

Appeler une fonction personnalisée depuis n’importe quel classeur

Pour appeler une fonction personnalisée à partir d’un autre classeur, vous devez créer un classeur de macros complémentaires Excel (extension .XLAM).

Pour ce faire, cliquez sur Fichier/Enregistrer sous, et sélectionnez dans la liste Type, la valeur Macro complémentaires Excel (*.xlam).

Par défaut, le chemin de stockage des macros complémentaires est Users\appData\Roaming, Microsoft\Addlns.

Pour activer votre classeur dans un autre fichier Excel, il faudra passer par Fichier/Options/Compléments, cliquer sur le bouton Atteindre de la commande Gérer les compléments Excel et activer vos macros à partir de la liste.