{"id":9571,"date":"2021-04-04T13:20:17","date_gmt":"2021-04-04T11:20:17","guid":{"rendered":"https:\/\/www.auditsi.eu\/?p=9571"},"modified":"2021-04-04T13:54:24","modified_gmt":"2021-04-04T11:54:24","slug":"ajouter-des-fonctions-a-excel-est-particulierement-simple-il-suffit-dacceder-a-lediteur-de-programme-vba-edi-puis-de-faire-preceder-le-programme-de-linstruction-function","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=9571","title":{"rendered":"Ajouter de nouvelles fonctions \u00e0 Excel"},"content":{"rendered":"

Le tableur<\/a> Excel est un outil de calcul extr\u00eamement polyvalent. Son \u00e9diteur de formule de calcul offre un choix tr\u00e8s riche de fonctions de calcul. L’inconv\u00e9nient des formules de calcul est que leur duplication favorise les erreurs (copier\/coller<\/a>…) et leur mise \u00e0 jour peut se r\u00e9v\u00e9ler ardue.<\/p>\n

Plut\u00f4t que d’utiliser des formules de calcul, il est possible d’ajouter de nouvelles fonctions \u00e0 Excel en les cr\u00e9ant de toute pi\u00e8ce. L’avantage \u00e9tant que la formule de calcul n’est r\u00e9dig\u00e9e qu’une seule fois, facilitant \u00e0 la fois sa mise \u00e0 jour et son contr\u00f4le. La d\u00e9marche de cr\u00e9ation de nouvelles fonctions est assez simple. Une fois cr\u00e9\u00e9es, ces fonctions sont utilisables dans les formules de calcul Excel et peuvent \u00eatre associ\u00e9es \u00e0 d’autres fonctions, natives ou non, pour donner lieu \u00e0 des calculs tr\u00e8s \u00e9labor\u00e9s.<\/p>\n

\"Calcul<\/p>\n

Par exemple, le calcul de primes ou de remises de fin d’ann\u00e9e (RFA) peut \u00eatre r\u00e9alis\u00e9 \u00e0 l’aide de formules de calcul :<\/p>\n

=C7*0,025<\/p>\n

Dans ce cas, la prime est calcul\u00e9e sur la base du chiffre d’affaires (stock\u00e9 en cellule C7) auquel est appliqu\u00e9 un taux de 2,5 %.<\/p>\n

Pour \u00e9viter d’avoir \u00e0 dupliquer la formule de calcul de la prime sur toute une feuille de calcul, il est envisageable de cr\u00e9er une fonction Excel qui assure la m\u00eame t\u00e2che.<\/p>\n

Ajouter des fonctions \u00e0 Excel est particuli\u00e8rement simple. Il suffit d’acc\u00e9der \u00e0 l’\u00e9diteur de programme VBA (EDI)<\/a> puis de faire pr\u00e9c\u00e9der le programme de l’instruction Function<\/em> suivi d’un nom (au gr\u00e9 du cr\u00e9ateur de la fonction) puis de ponctuer le tout avec End Function<\/em>.<\/p>\n

Function Nom_fonction(param\u00e8tre1, param\u00e8tre2...)\r\n\r\n\r\nEnd Function\r\n<\/pre>\n
Ainsi, pour le calcul des primes li\u00e9es au chiffre d’affaires (CA), la fonction \u00e9quivalente \u00e0 la formule de calcul expos\u00e9e ci-avant se pr\u00e9sente ainsi :<\/p>\n
Function Prime_CA(CA As Double)\r\n'Calcul d'une prime en fonction du CA r\u00e9alis\u00e9\r\n    Prime_CA = CA * 0.025\r\nEnd Function\r\n<\/pre>\n
Le CA est transmis \u00e0 la fonction par l’interm\u00e9diaire du param\u00e8tre qui suit imm\u00e9diatement le nom de la fonction. Le code VBA stocke ce param\u00e8tre dans la variable CA (de type Double<\/em><\/a>, c’est-\u00e0-dire un nombre d\u00e9cimal). Le r\u00e9sultat du calcul est retourn\u00e9 \u00e0 Excel \u00e0 l’aide de la commande “Prime_CA = …<\/em>“.<\/p>\n
Les fonctions VBA sont automatiquement int\u00e9gr\u00e9es \u00e0 Excel. Ainsi, lorsque l’utilisateur du classeur Excel commence \u00e0 saisir une formule de calcul, Excel lui propose les commandes qui correspondent \u00e0 l’entr\u00e9e en cours (saisie pr\u00e9dictive) y compris les fonctions cr\u00e9\u00e9es par l’utilisateur.<\/p>\n
\"Excel<\/p>\n
Le tableau de calcul int\u00e9grant la nouvelle fonction Prime_CA<\/em> se pr\u00e9sente ainsi :<\/p>\n
\"Calcul<\/p>\n
A noter : un classeur Excel qui comprend des macros VBA doit \u00eatre enregistr\u00e9 avec une extension *.XLSM<\/a> au lieu de *.XLSX, faute de quoi les macros seront supprim\u00e9es lors de l’enregistrement du classeur.<\/p>\n
Le classeur contenant les exemples est propos\u00e9 en libre t\u00e9l\u00e9chargement en bas de cette page.<\/p>\n
Bien entendu, les fonctions cr\u00e9\u00e9es par l’utilisateur peuvent \u00eatre int\u00e9gr\u00e9es dans des formules de calcul plus ou moins complexes. Par exemple :<\/p>\n
=SI(SOMME(C7:D7)>200000;Prime_CA(SOMME(C7:D7)*2);Prime_CA(SOMME(C7:D7)))<\/p>\n
La fonction Prime_CA<\/em> est assez simple, elle se contente d’appliquer un taux sur une base. Dans la r\u00e9alit\u00e9, le taux de RFA varie g\u00e9n\u00e9ralement en fonction du CA r\u00e9alis\u00e9<\/strong> ; une grille de calcul comprenant diff\u00e9rentes tranches de CA et taux \u00e0 appliquer est param\u00e9tr\u00e9e. Dans ce cas, la fonction pourra prendre cette forme :<\/p>\n
Function Prime_CA_Grille(CA As Double)\r\n'Calcul d'une prime en fonction du CA r\u00e9alis\u00e9\r\n    'Grilles CA et pourcentage (Pct) ; exemple CA >= 100 000 \u20ac et < 200 000 \u20ac, 1.5 % de prime\r\n    Const GrilleCA = \"0;100000;200000;250000;300000\"\r\n    Const GrillePct = \"0;1.5;2;2.5;2.75\"\r\n    \r\n    Dim Table_GrilleCA As Variant\r\n    Dim Table_GrillePct As Variant\r\n    Dim i As Integer\r\n    \r\n    'Conversion des grilles en tableaux de donn\u00e9es\r\n    Table_GrilleCA = Split(GrilleCA, \";\")\r\n    Table_GrillePct = Split(GrillePct, \";\")\r\n    \r\n    'Parcours de la table de CA et calcul de la prime\r\n    For i = UBound(Table_GrilleCA) To LBound(Table_GrilleCA) Step -1\r\n        If CA >= Table_GrilleCA(i) Then\r\n            Prime_CA_Grille = CA * Val(Table_GrillePct(i)) \/ 100\r\n            Exit For\r\n        End If\r\n    Next i\r\nEnd Function\r\n<\/pre>\n
Les grilles de CA et taux sont d\u00e9finies respectivement \u00e0 l’aide des constantes GrilleCA<\/em> et GrillePct<\/em> avant d’\u00eatre converties en variables tableaux<\/a> (Table_GrilleCA<\/em> et Table_GrillePct<\/em>) \u00e0 l’aide de la fonction VBA Split<\/em><\/a>.<\/p>\n
Les deux lignes de code :<\/p>\n
Const GrilleCA = \"0;100000;200000;250000;300000\"\r\nConst GrillePct = \"0;1.5;2;2.5;2.75\"\r\n<\/pre>\n
D\u00e9finissent la grille de remises suivante :<\/p>\n
\"Excel<\/p>\n
Ainsi, un CA compris entre 100 et 200 K\u20ac donnera une RFA de 1,5 %. La boucle compteur For… To… Step…<\/em><\/a> permet de parcourir les tranches de CA de la grille de RFA (des tranches sup\u00e9rieures (Ubound<\/em>) vers les tranches inf\u00e9rieures (Lbound<\/em>) pour d\u00e9terminer le taux de remise (Table_GrillePct(i)<\/em>) et calculer la remise (Prime_CA_Grille = CA * Val(Table_GrillePct(i)) \/ 100<\/em>).)<\/p>\n
\"Calcul<\/p>\n
Une autre m\u00e9thode de calcul moins \u00e9l\u00e9gante mais tout aussi fonctionnelle aurait pu \u00eatre utilis\u00e9e en imbriquant des tests conditionnels (If… Then… Else…<\/em>)<\/a> \u00e0 la mani\u00e8re des SI(…)<\/em> des formules de calcul Excel. La fonction r\u00e9dig\u00e9e de la sorte donnerait le code source suivant :<\/p>\n
Function Prime_CA_Grille_IF(CA As Double)\r\n'Calcul d'une prime en fonction du CA r\u00e9alis\u00e9 avec des tests conditionnels If... Then... Else...\r\n    Dim Taux_Remise As Double\r\n    \r\n    If CA >= 300000 Then\r\n        Taux_Remise = 2.75\r\n    ElseIf CA >= 250000 Then\r\n        Taux_Remise = 2.5\r\n    ElseIf CA >= 200000 Then\r\n        Taux_Remise = 2\r\n    ElseIf CA >= 100000 Then\r\n        Taux_Remise = 1.5\r\n    Else:\r\n        Taux_Remise = 0\r\n    End If\r\n        \r\n    Prime_CA_Grille_IF = CA * Taux_Remise \/ 100\r\nEnd Function\r\n<\/pre>\n
Si cette forme de r\u00e9daction semble plus lisible, elle est plus difficile \u00e0 mettre \u00e0 jour en cas de modification de la grille de remise (notamment ajout de tranches).<\/p>\n
Son \u00e9quivalent en formule de calcul Excel serait :<\/p>\n
=C7*SI(C7>= 300000;2.75; SI(C7>= 250000;2.5; SI(C7>= 200000;2; SI(C7>= 100000;1.5;0)\/100<\/p>\n
Plus complexes encore sont les remises calcul\u00e9es en fonction de paliers<\/strong>. Dans le cadre d’un calcul par palier, le taux de remise appliqu\u00e9 varie en fonction de la d\u00e9composition du CA r\u00e9alis\u00e9 par tranche (palier). Par exemple, l’application de la grille propos\u00e9e plus haut \u00e0 un CA de 220 K\u20ac donne une remise de 0 % de jusqu’\u00e0 99 999 \u20ac de CA + 1,5 % de 100 000 \u20ac \u00e0 199 999 \u20ac + 2 % de 200 000 \u20ac \u00e0 220 000 \u20ac ‘soit dans cet exemple une remise de 1 900 \u20ac.<\/p>\n
Function Prime_CA_Palier(CA As Double)\r\n'Calcul d'une prime en fonction du CA r\u00e9alis\u00e9\r\n    'Grilles CA et pourcentage (Pct) ; exemple CA = 220 000 \u20ac, 0 % de prime jusque 99 999 \u20ac + 1,5 % de prime de 100 000 \u20ac \u00e0 199 999 \u20ac + 2 % de prime de 200 000 \u20ac \u00e0 220 000 \u20ac\r\n    'soit dans cet exemple une prime de 1 900 \u20ac\r\n    Const GrilleCA = \"0;100000;200000;250000;300000\"\r\n    Const GrillePct = \"0;1.5;2;2.5;2.75\"\r\n    \r\n    Dim Table_GrilleCA As Variant\r\n    Dim Table_GrillePct As Variant\r\n    Dim i As Integer\r\n    Dim CA_Plafond As Double\r\n    \r\n    'Conversion des grilles en tableaux de donn\u00e9es\r\n    Table_GrilleCA = Split(GrilleCA, \";\")\r\n    Table_GrillePct = Split(GrillePct, \";\")\r\n    \r\n    'Parcours de la table de CA et calcul de la prime\r\n    For i = UBound(Table_GrilleCA) To LBound(Table_GrilleCA) Step -1\r\n        Select Case i < UBound(Table_GrilleCA)\r\n            'Si i = UBound(Table_GrilleCA) -> derni\u00e8re tranche de la grille de CA\r\n            Case True:\r\n                Select Case Table_GrilleCA(i + 1) < CA\r\n                    Case True:\r\n                        CA_Plafond = Table_GrilleCA(i + 1)\r\n                    Case False:\r\n                        If CA > Table_GrilleCA(i) Then CA_Plafond = CA Else CA_Plafond = Table_GrilleCA(i)\r\n                End Select\r\n            Case False:\r\n                If CA > Table_GrilleCA(i) Then CA_Plafond = CA Else CA_Plafond = Table_GrilleCA(i)\r\n        End Select\r\n        Prime_CA_Palier = Prime_CA_Palier + (CA_Plafond - Table_GrilleCA(i)) * Val(Table_GrillePct(i)) \/ 100\r\n    Next i\r\nEnd Function\r\n<\/pre>\n
Exemple de tableau de calcul de remises par palier :<\/p>\n
\"Calcul<\/p>\n
Enfin, le calcul des primes ou remises peut \u00eatre d\u00e9termin\u00e9 en fonction d’objectifs fix\u00e9s individuellement<\/strong>.<\/p>\n
Par exemple, la grille de RFA ci-apr\u00e8s fixe le pourcentage de remise en fonction de l’atteinte de l’objectif de CA (soit CA r\u00e9alis\u00e9 \/ objectif de CA) :<\/p>\n
\"Calcul<\/p>\n
Cette grille est param\u00e9tr\u00e9e ainsi dans le code VBA :<\/p>\n
Const GrillePctObjectif = \"0;95;100;105;110\"\r\nConst GrillePctPrime = \"0;1.5;2;2.5;2.75\"<\/pre>\n
Le calcul d’une prime fix\u00e9e en fonction d’objectifs individuels n\u00e9cessite deux param\u00e8tres : le CA r\u00e9alis\u00e9 (ici : CA<\/em>) et l’objectif de CA (ici : CA_Objectif<\/em>). Les param\u00e8tres transmis par une fonction sont s\u00e9par\u00e9s les uns des autres par des virgules.<\/p>\n
Function Prime_Objectif(CA As Double, CA_Objectif As Double)\r\n'Calcul d'une prime en fonction de la r\u00e9alisation d'un objectif\r\n    'Grilles CA et pourcentage (Pct) ; exemple % r\u00e9alisation > 110 % , 2.75 % de prime\r\n    Const GrillePctObjectif = \"0;95;100;105;110\"\r\n    Const GrillePctPrime = \"0;1.5;2;2.5;2.75\"\r\n    \r\n    Dim Table_GrillePctObjectif As Variant\r\n    Dim Table_GrillePctPrime As Variant\r\n    Dim i As Integer\r\n    Dim PctR\u00e9alis\u00e9 As Double\r\n    \r\n    'Conversion des grilles en tableaux de donn\u00e9es\r\n    Table_GrillePctObjectif = Split(GrillePctObjectif, \";\")\r\n    Table_GrillePctPrime = Split(GrillePctPrime, \";\")\r\n    \r\n    PctR\u00e9alis\u00e9 = CA \/ CA_Objectif\r\n    \r\n    'Parcours de la table des pourcentages d'objectif et calcul de la prime\r\n    For i = UBound(Table_GrillePctObjectif) To LBound(Table_GrillePctObjectif) Step -1\r\n        If PctR\u00e9alis\u00e9 >= Table_GrillePctObjectif(i) \/ 100 Then\r\n            Prime_Objectif = CA * Val(Table_GrillePctPrime(i)) \/ 100\r\n            Exit For\r\n        End If\r\n    Next i\r\nEnd Function<\/pre>\n
Le calcul de remises en fonction d’objectifs individuels se r\u00e9sume ainsi :<\/p>\n
\"Calcul<\/p>\n
Code source de l’ensemble des fonctions du classeur :<\/p>\n
'Cr\u00e9er de nouvelles fonctions Excel en VBA\r\n'Beno\u00eet RIVIERE, 04\/2021\r\n'www.auditsi.eu\r\n\r\n\r\nOption Explicit\r\n\r\n\r\nFunction Prime_CA(CA As Double)\r\n'Calcul d'une prime en fonction du CA r\u00e9alis\u00e9\r\n    Prime_CA = CA * 0.025\r\nEnd Function\r\n\r\n\r\nFunction Prime_CA_Grille(CA As Double)\r\n'Calcul d'une prime en fonction du CA r\u00e9alis\u00e9\r\n    'Grilles CA et pourcentage (Pct) ; exemple CA >= 100 000 \u20ac et < 200 000 \u20ac, 1.5 % de prime\r\n    Const GrilleCA = \"0;100000;200000;250000;300000\"\r\n    Const GrillePct = \"0;1.5;2;2.5;2.75\"\r\n    \r\n    Dim Table_GrilleCA As Variant\r\n    Dim Table_GrillePct As Variant\r\n    Dim i As Integer\r\n    \r\n    'Conversion des grilles en tableaux de donn\u00e9es\r\n    Table_GrilleCA = Split(GrilleCA, \";\")\r\n    Table_GrillePct = Split(GrillePct, \";\")\r\n    \r\n    'Parcours de la table de CA et calcul de la prime\r\n    For i = UBound(Table_GrilleCA) To LBound(Table_GrilleCA) Step -1\r\n        If CA >= Table_GrilleCA(i) Then\r\n            Prime_CA_Grille = CA * Val(Table_GrillePct(i)) \/ 100\r\n            Exit For\r\n        End If\r\n    Next i\r\nEnd Function\r\n\r\n\r\nFunction Prime_CA_Palier(CA As Double)\r\n'Calcul d'une prime en fonction du CA r\u00e9alis\u00e9\r\n    'Grilles CA et pourcentage (Pct) ; exemple CA = 220 000 \u20ac, 0 % de prime jusque 99 999 \u20ac + 1,5 % de prime de 100 000 \u20ac \u00e0 199 999 \u20ac + 2 % de prime de 200 000 \u20ac \u00e0 220 000 \u20ac\r\n    'soit dans cet exemple une prime de 1 900 \u20ac\r\n    Const GrilleCA = \"0;100000;200000;250000;300000\"\r\n    Const GrillePct = \"0;1.5;2;2.5;2.75\"\r\n    \r\n    Dim Table_GrilleCA As Variant\r\n    Dim Table_GrillePct As Variant\r\n    Dim i As Integer\r\n    Dim CA_Plafond As Double\r\n    \r\n    'Conversion des grilles en tableaux de donn\u00e9es\r\n    Table_GrilleCA = Split(GrilleCA, \";\")\r\n    Table_GrillePct = Split(GrillePct, \";\")\r\n    \r\n    'Parcours de la table de CA et calcul de la prime\r\n    For i = UBound(Table_GrilleCA) To LBound(Table_GrilleCA) Step -1\r\n        Select Case i < UBound(Table_GrilleCA)\r\n            'Si i = UBound(Table_GrilleCA) -> derni\u00e8re tranche de la grille de CA\r\n            Case True:\r\n                Select Case Table_GrilleCA(i + 1) < CA\r\n                    Case True:\r\n                        CA_Plafond = Table_GrilleCA(i + 1)\r\n                    Case False:\r\n                        If CA > Table_GrilleCA(i) Then CA_Plafond = CA Else CA_Plafond = Table_GrilleCA(i)\r\n                End Select\r\n            Case False:\r\n                If CA > Table_GrilleCA(i) Then CA_Plafond = CA Else CA_Plafond = Table_GrilleCA(i)\r\n        End Select\r\n        Prime_CA_Palier = Prime_CA_Palier + (CA_Plafond - Table_GrilleCA(i)) * Val(Table_GrillePct(i)) \/ 100\r\n    Next i\r\nEnd Function\r\n\r\n\r\nFunction Prime_Objectif(CA As Double, CA_Objectif As Double)\r\n'Calcul d'une prime en fonction de la r\u00e9alisation d'un objectif\r\n    'Grilles CA et pourcentage (Pct) ; exemple % r\u00e9alisation > 110 % , 2.75 % de prime\r\n    Const GrillePctObjectif = \"0;95;100;105;110\"\r\n    Const GrillePctPrime = \"0;1.5;2;2.5;2.75\"\r\n    \r\n    Dim Table_GrillePctObjectif As Variant\r\n    Dim Table_GrillePctPrime As Variant\r\n    Dim i As Integer\r\n    Dim PctR\u00e9alis\u00e9 As Double\r\n    \r\n    'Conversion des grilles en tableaux de donn\u00e9es\r\n    Table_GrillePctObjectif = Split(GrillePctObjectif, \";\")\r\n    Table_GrillePctPrime = Split(GrillePctPrime, \";\")\r\n    \r\n    PctR\u00e9alis\u00e9 = CA \/ CA_Objectif\r\n    \r\n    'Parcours de la table des pourcentages d'objectif et calcul de la prime\r\n    For i = UBound(Table_GrillePctObjectif) To LBound(Table_GrillePctObjectif) Step -1\r\n        If PctR\u00e9alis\u00e9 >= Table_GrillePctObjectif(i) \/ 100 Then\r\n            Prime_Objectif = CA * Val(Table_GrillePctPrime(i)) \/ 100\r\n            Exit For\r\n        End If\r\n    Next i\r\nEnd Function\r\n<\/pre>\n
Autre exemple de cr\u00e9ation de fonction<\/a> appliqu\u00e9 au bar\u00e8me d’heures des commissaires aux comptes<\/a>.<\/p>\n
___<\/p>\n
Pour aller plus loin : programmer en VBA<\/a><\/p>\n
\"Share<\/a>