Le tableur Excel est un outil de calcul extrêmement polyvalent. Son éditeur de formule de calcul offre un choix très riche de fonctions de calcul. L’inconvénient des formules de calcul est que leur duplication favorise les erreurs (copier/coller…) et leur mise à jour peut se révéler ardue.
Plutôt que d’utiliser des formules de calcul, il est possible d’ajouter de nouvelles fonctions à Excel en les créant de toute pièce. L’avantage étant que la formule de calcul n’est rédigée qu’une seule fois, facilitant à la fois sa mise à jour et son contrôle. La démarche de création de nouvelles fonctions est assez simple. Une fois créées, ces fonctions sont utilisables dans les formules de calcul Excel et peuvent être associées à d’autres fonctions, natives ou non, pour donner lieu à des calculs très élaborés.
Par exemple, le calcul de primes ou de remises de fin d’année (RFA) peut être réalisé à l’aide de formules de calcul :
=C7*0,025
Dans ce cas, la prime est calculée sur la base du chiffre d’affaires (stocké en cellule C7) auquel est appliqué un taux de 2,5 %.
Pour éviter d’avoir à dupliquer la formule de calcul de la prime sur toute une feuille de calcul, il est envisageable de créer une fonction Excel qui assure la même tâche.
Ajouter des fonctions à Excel est particulièrement simple. Il suffit d’accéder à l’éditeur de programme VBA (EDI) puis de faire précéder le programme de l’instruction Function suivi d’un nom (au gré du créateur de la fonction) puis de ponctuer le tout avec End Function.
Function Nom_fonction(paramètre1, paramètre2...) End Function
Ainsi, pour le calcul des primes liées au chiffre d’affaires (CA), la fonction équivalente à la formule de calcul exposée ci-avant se présente ainsi :
Function Prime_CA(CA As Double)
'Calcul d'une prime en fonction du CA réalisé
Prime_CA = CA * 0.025
End Function
Le CA est transmis à la fonction par l’intermédiaire du paramètre qui suit immédiatement le nom de la fonction. Le code VBA stocke ce paramètre dans la variable CA (de type Double, c’est-à-dire un nombre décimal). Le résultat du calcul est retourné à Excel à l’aide de la commande “Prime_CA = …“.
Les fonctions VBA sont automatiquement intégrées à Excel. Ainsi, lorsque l’utilisateur du classeur Excel commence à saisir une formule de calcul, Excel lui propose les commandes qui correspondent à l’entrée en cours (saisie prédictive) y compris les fonctions créées par l’utilisateur.
Le tableau de calcul intégrant la nouvelle fonction Prime_CA se présente ainsi :
A noter : un classeur Excel qui comprend des macros VBA doit être enregistré avec une extension *.XLSM au lieu de *.XLSX, faute de quoi les macros seront supprimées lors de l’enregistrement du classeur.
Le classeur contenant les exemples est proposé en libre téléchargement en bas de cette page.
Bien entendu, les fonctions créées par l’utilisateur peuvent être intégrées dans des formules de calcul plus ou moins complexes. Par exemple :
=SI(SOMME(C7:D7)>200000;Prime_CA(SOMME(C7:D7)*2);Prime_CA(SOMME(C7:D7)))
La fonction Prime_CA est assez simple, elle se contente d’appliquer un taux sur une base. Dans la réalité, le taux de RFA varie généralement en fonction du CA réalisé ; une grille de calcul comprenant différentes tranches de CA et taux à appliquer est paramétrée. Dans ce cas, la fonction pourra prendre cette forme :
Function Prime_CA_Grille(CA As Double)
'Calcul d'une prime en fonction du CA réalisé
'Grilles CA et pourcentage (Pct) ; exemple CA >= 100 000 € et < 200 000 €, 1.5 % de prime
Const GrilleCA = "0;100000;200000;250000;300000"
Const GrillePct = "0;1.5;2;2.5;2.75"
Dim Table_GrilleCA As Variant
Dim Table_GrillePct As Variant
Dim i As Integer
'Conversion des grilles en tableaux de données
Table_GrilleCA = Split(GrilleCA, ";")
Table_GrillePct = Split(GrillePct, ";")
'Parcours de la table de CA et calcul de la prime
For i = UBound(Table_GrilleCA) To LBound(Table_GrilleCA) Step -1
If CA >= Table_GrilleCA(i) Then
Prime_CA_Grille = CA * Val(Table_GrillePct(i)) / 100
Exit For
End If
Next i
End Function
Les grilles de CA et taux sont définies respectivement à l’aide des constantes GrilleCA et GrillePct avant d’être converties en variables tableaux (Table_GrilleCA et Table_GrillePct) à l’aide de la fonction VBA Split.
Les deux lignes de code :
Const GrilleCA = "0;100000;200000;250000;300000" Const GrillePct = "0;1.5;2;2.5;2.75"
Définissent la grille de remises suivante :
Ainsi, un CA compris entre 100 et 200 K€ donnera une RFA de 1,5 %. La boucle compteur For… To… Step… permet de parcourir les tranches de CA de la grille de RFA (des tranches supérieures (Ubound) vers les tranches inférieures (Lbound) pour déterminer le taux de remise (Table_GrillePct(i)) et calculer la remise (Prime_CA_Grille = CA * Val(Table_GrillePct(i)) / 100).)
Une autre méthode de calcul moins élégante mais tout aussi fonctionnelle aurait pu être utilisée en imbriquant des tests conditionnels (If… Then… Else…) à la manière des SI(…) des formules de calcul Excel. La fonction rédigée de la sorte donnerait le code source suivant :
Function Prime_CA_Grille_IF(CA As Double)
'Calcul d'une prime en fonction du CA réalisé avec des tests conditionnels If... Then... Else...
Dim Taux_Remise As Double
If CA >= 300000 Then
Taux_Remise = 2.75
ElseIf CA >= 250000 Then
Taux_Remise = 2.5
ElseIf CA >= 200000 Then
Taux_Remise = 2
ElseIf CA >= 100000 Then
Taux_Remise = 1.5
Else:
Taux_Remise = 0
End If
Prime_CA_Grille_IF = CA * Taux_Remise / 100
End Function
Si cette forme de rédaction semble plus lisible, elle est plus difficile à mettre à jour en cas de modification de la grille de remise (notamment ajout de tranches).
Son équivalent en formule de calcul Excel serait :
=C7*SI(C7>= 300000;2.75; SI(C7>= 250000;2.5; SI(C7>= 200000;2; SI(C7>= 100000;1.5;0)/100
Plus complexes encore sont les remises calculées en fonction de paliers. Dans le cadre d’un calcul par palier, le taux de remise appliqué varie en fonction de la décomposition du CA réalisé par tranche (palier). Par exemple, l’application de la grille proposée plus haut à un CA de 220 K€ donne une remise de 0 % de jusqu’à 99 999 € de CA + 1,5 % de 100 000 € à 199 999 € + 2 % de 200 000 € à 220 000 € ‘soit dans cet exemple une remise de 1 900 €.
Function Prime_CA_Palier(CA As Double)
'Calcul d'une prime en fonction du CA réalisé
'Grilles CA et pourcentage (Pct) ; exemple CA = 220 000 €, 0 % de prime jusque 99 999 € + 1,5 % de prime de 100 000 € à 199 999 € + 2 % de prime de 200 000 € à 220 000 €
'soit dans cet exemple une prime de 1 900 €
Const GrilleCA = "0;100000;200000;250000;300000"
Const GrillePct = "0;1.5;2;2.5;2.75"
Dim Table_GrilleCA As Variant
Dim Table_GrillePct As Variant
Dim i As Integer
Dim CA_Plafond As Double
'Conversion des grilles en tableaux de données
Table_GrilleCA = Split(GrilleCA, ";")
Table_GrillePct = Split(GrillePct, ";")
'Parcours de la table de CA et calcul de la prime
For i = UBound(Table_GrilleCA) To LBound(Table_GrilleCA) Step -1
Select Case i < UBound(Table_GrilleCA)
'Si i = UBound(Table_GrilleCA) -> dernière tranche de la grille de CA
Case True:
Select Case Table_GrilleCA(i + 1) < CA
Case True:
CA_Plafond = Table_GrilleCA(i + 1)
Case False:
If CA > Table_GrilleCA(i) Then CA_Plafond = CA Else CA_Plafond = Table_GrilleCA(i)
End Select
Case False:
If CA > Table_GrilleCA(i) Then CA_Plafond = CA Else CA_Plafond = Table_GrilleCA(i)
End Select
Prime_CA_Palier = Prime_CA_Palier + (CA_Plafond - Table_GrilleCA(i)) * Val(Table_GrillePct(i)) / 100
Next i
End Function
Exemple de tableau de calcul de remises par palier :
Enfin, le calcul des primes ou remises peut être déterminé en fonction d’objectifs fixés individuellement.
Par exemple, la grille de RFA ci-après fixe le pourcentage de remise en fonction de l’atteinte de l’objectif de CA (soit CA réalisé / objectif de CA) :
Cette grille est paramétrée ainsi dans le code VBA :
Const GrillePctObjectif = "0;95;100;105;110" Const GrillePctPrime = "0;1.5;2;2.5;2.75"
Le calcul d’une prime fixée en fonction d’objectifs individuels nécessite deux paramètres : le CA réalisé (ici : CA) et l’objectif de CA (ici : CA_Objectif). Les paramètres transmis par une fonction sont séparés les uns des autres par des virgules.
Function Prime_Objectif(CA As Double, CA_Objectif As Double)
'Calcul d'une prime en fonction de la réalisation d'un objectif
'Grilles CA et pourcentage (Pct) ; exemple % réalisation > 110 % , 2.75 % de prime
Const GrillePctObjectif = "0;95;100;105;110"
Const GrillePctPrime = "0;1.5;2;2.5;2.75"
Dim Table_GrillePctObjectif As Variant
Dim Table_GrillePctPrime As Variant
Dim i As Integer
Dim PctRéalisé As Double
'Conversion des grilles en tableaux de données
Table_GrillePctObjectif = Split(GrillePctObjectif, ";")
Table_GrillePctPrime = Split(GrillePctPrime, ";")
PctRéalisé = CA / CA_Objectif
'Parcours de la table des pourcentages d'objectif et calcul de la prime
For i = UBound(Table_GrillePctObjectif) To LBound(Table_GrillePctObjectif) Step -1
If PctRéalisé >= Table_GrillePctObjectif(i) / 100 Then
Prime_Objectif = CA * Val(Table_GrillePctPrime(i)) / 100
Exit For
End If
Next i
End Function
Le calcul de remises en fonction d’objectifs individuels se résume ainsi :
Code source de l’ensemble des fonctions du classeur :
'Créer de nouvelles fonctions Excel en VBA
'Benoît RIVIERE, 04/2021
'www.auditsi.eu
Option Explicit
Function Prime_CA(CA As Double)
'Calcul d'une prime en fonction du CA réalisé
Prime_CA = CA * 0.025
End Function
Function Prime_CA_Grille(CA As Double)
'Calcul d'une prime en fonction du CA réalisé
'Grilles CA et pourcentage (Pct) ; exemple CA >= 100 000 € et < 200 000 €, 1.5 % de prime
Const GrilleCA = "0;100000;200000;250000;300000"
Const GrillePct = "0;1.5;2;2.5;2.75"
Dim Table_GrilleCA As Variant
Dim Table_GrillePct As Variant
Dim i As Integer
'Conversion des grilles en tableaux de données
Table_GrilleCA = Split(GrilleCA, ";")
Table_GrillePct = Split(GrillePct, ";")
'Parcours de la table de CA et calcul de la prime
For i = UBound(Table_GrilleCA) To LBound(Table_GrilleCA) Step -1
If CA >= Table_GrilleCA(i) Then
Prime_CA_Grille = CA * Val(Table_GrillePct(i)) / 100
Exit For
End If
Next i
End Function
Function Prime_CA_Palier(CA As Double)
'Calcul d'une prime en fonction du CA réalisé
'Grilles CA et pourcentage (Pct) ; exemple CA = 220 000 €, 0 % de prime jusque 99 999 € + 1,5 % de prime de 100 000 € à 199 999 € + 2 % de prime de 200 000 € à 220 000 €
'soit dans cet exemple une prime de 1 900 €
Const GrilleCA = "0;100000;200000;250000;300000"
Const GrillePct = "0;1.5;2;2.5;2.75"
Dim Table_GrilleCA As Variant
Dim Table_GrillePct As Variant
Dim i As Integer
Dim CA_Plafond As Double
'Conversion des grilles en tableaux de données
Table_GrilleCA = Split(GrilleCA, ";")
Table_GrillePct = Split(GrillePct, ";")
'Parcours de la table de CA et calcul de la prime
For i = UBound(Table_GrilleCA) To LBound(Table_GrilleCA) Step -1
Select Case i < UBound(Table_GrilleCA)
'Si i = UBound(Table_GrilleCA) -> dernière tranche de la grille de CA
Case True:
Select Case Table_GrilleCA(i + 1) < CA
Case True:
CA_Plafond = Table_GrilleCA(i + 1)
Case False:
If CA > Table_GrilleCA(i) Then CA_Plafond = CA Else CA_Plafond = Table_GrilleCA(i)
End Select
Case False:
If CA > Table_GrilleCA(i) Then CA_Plafond = CA Else CA_Plafond = Table_GrilleCA(i)
End Select
Prime_CA_Palier = Prime_CA_Palier + (CA_Plafond - Table_GrilleCA(i)) * Val(Table_GrillePct(i)) / 100
Next i
End Function
Function Prime_Objectif(CA As Double, CA_Objectif As Double)
'Calcul d'une prime en fonction de la réalisation d'un objectif
'Grilles CA et pourcentage (Pct) ; exemple % réalisation > 110 % , 2.75 % de prime
Const GrillePctObjectif = "0;95;100;105;110"
Const GrillePctPrime = "0;1.5;2;2.5;2.75"
Dim Table_GrillePctObjectif As Variant
Dim Table_GrillePctPrime As Variant
Dim i As Integer
Dim PctRéalisé As Double
'Conversion des grilles en tableaux de données
Table_GrillePctObjectif = Split(GrillePctObjectif, ";")
Table_GrillePctPrime = Split(GrillePctPrime, ";")
PctRéalisé = CA / CA_Objectif
'Parcours de la table des pourcentages d'objectif et calcul de la prime
For i = UBound(Table_GrillePctObjectif) To LBound(Table_GrillePctObjectif) Step -1
If PctRéalisé >= Table_GrillePctObjectif(i) / 100 Then
Prime_Objectif = CA * Val(Table_GrillePctPrime(i)) / 100
Exit For
End If
Next i
End Function
Autre exemple de création de fonction appliqué au barème d’heures des commissaires aux comptes.
___
Pour aller plus loin : programmer en VBA
Derniers articles parBenoît RIVIERE (voir tous)
- Nouveautés de l’interpréteur de formules de calcul (v1.1) - dimanche 3 novembre 2024
- Dématérialisation de la facturation : nouvelles mentions obligatoires - lundi 28 octobre 2024
- Interpréteur de formules de calcul en Python - dimanche 13 octobre 2024
- Les données de la facturation électronique - mercredi 9 octobre 2024
- VBA/SQL vs Power Query : deux solutions complémentaires - mercredi 2 octobre 2024
Benoît,
Merci pour ton article sur la création de fonction qui est très inspirant.
En analysant de plus près le code VBA, il est plus facile de comprendre pourquoi tu as opté par la déclaration de constantes puis de la conversion de la grille en tableau (avec split). Cela paraît plus compliquer de définir comme variables (variant) les données contenues dans les cellules d’une feuille (Range(“A1:A5”).value). Dans ton exemple sur la grille de RFA, permettre à l’utilisateur décisionnaire de modifier la grille aurait permis une souplesse d’utilisation à condition que le contenu des cellules soit considéré comme du texte (String) si j’ai bien compris.
Bonjour Claude,
Merci pour ton message.
J’ai pris le parti de coder la grille de remise en dur dans le code pour des raisons pratiques. Mais il est tout à fait possible de la stocker dans une feuille Excel.
Si le sujet des échanges (lecture / écriture) de données Excel à partir d’un programme VBA, je l’ai traité notamment ici : https://www.auditsi.eu/?p=5678.
Bonne soirée,
Benoît