« Macros-commandes VBA/Création de Fonction » : différence entre les versions
Contenu supprimé Contenu ajouté
e |
ajout et modification de contenu |
||
Ligne 14 :
Une fonction est un sous-programme qui permet d'exécuter une série d'instructions et retourne une valeur typée à la fin de ce traitement. Cette valeur peut être par la suite exploitée par une autre procédure, fonction ou application. Et la notion de sous-programme quant à elle correspond à une fonction ou procédure dans la plupart des langages de programmation }}
===
* Une procédure Sub est une suite d'instructions qui peut être appelée et exécutée par l'utilisateur ou par une autre procédure et ne renvoie pas de valeur de sortie
* Une procédure Function (ou fonction) est une suite d'instructions qui peut être appelée et exécutée par l'utilisateur ou par une autre procédure et qui renvoie à chaque utilisation une valeur, tout comme les fonctions EXCEL et les fonctions intégrées de VBA, les fonctions peuvent être utilisées dans deux situations :
** Dans une procédure VBA en tant qu'élément d'une expression
** Dans une feuille de travail EXCEL directement avec les formules Standard EXCEL
{{Exemple
| contenu =
'''
<source lang=vb>
Ligne 35 ⟶ 32 :
</source>
'''
<source lang=vb>
Ligne 46 ⟶ 43 :
}}
===
Excel contient plus de 300 fonctions prédéfinies dans une feuille de travail. Mais si cela ne suffit pas, grâce à VBA, on peut créer des fonctions personnalisées.
Ligne 62 ⟶ 59 :
Cependant, si on veut modifier par la suite cette fonction, on sera obligé de modifier toutes les cellules où elle a été introduite et on conserve une certaine complexité de l'écriture.
Alors que sous VBA, il est possible de déclarer la fonction (plus lisible et plus facile à corriger en cas d'erreur) :
<source lang=vb>
Function calculPrime(montant as Double) as Double
If montant>1000 then
Ligne 73 ⟶ 71 :
End If
End Function
</source>
Dans ce cas, il suffit d'introduire dans une case Excel : '''=calculPrime(B5)''' pour obtenir le même résultat qu'avec une fonction SI imbriquée.
}}
Attention, suivant la qualité de la programmation, les fonctions personnalisées peuvent être beaucoup plus lentes que les fonctions intégrées. Tout utilisateur peut appeler la fonction à partir d'une procédure ou de l'interface EXCEL. Une fonction personnalisée permet en principe d'éliminer les redondances de code et de réduire le nombre d'erreurs.
=== L'écriture d'une fonction personnalisée ===
Ligne 94 ⟶ 87 :
* clic droit sur la case Module ==> Insertion ==> Module (on peut aussi créer une fonction qui sera rattachée à une feuille de calcul ou à un classeur plutôt qu'à un module)
<br />
[[File:Création de fonction sous VBA.png|center|500px|center|Instruction de création de fonction sous VBA]]
<br />
* L'éditeur VBA présente alors une page vide dans le Module1 (ou 2 ou 3 ou ...) sur laquelle le code VBA sera saisi
* Écrire la fonction : la déclaration d'une fonction commence par le mot clé "Function" suivi du nom de la fonction, puis d’une liste d’arguments obligatoires ou facultatifs entre parenthèses séparés les uns des autres par une virgule. Le mot clé "End Function" marque la fin de la fonction. Une fonction a la structure suivante :
Ligne 110 ⟶ 104 :
'''Exemple''' :
<source lang=vb>
Function diviser_par_1000(Cellule_a_diviser As Double)
diviser_par_1000 = Cellule_a_diviser / 1000
End Function
</source>
=== Utilisation d'une fonction personnalisée sous VBA ===
Ligne 119 ⟶ 115 :
* Pour créer une formule dans VBA, ouvrons Visual Basic (par exemple ALT+F11), puis ouvrons un module dans un classeur vierge
* Dans le même module on va appeler la fonction Sub AppelFonction()
* Ensuite, il faut lui transmettre les paramètres :
<source lang=vb>
Sub AppelFonction()
Dim variable1 As Byte, variable2 As Byte
variable1 = 3
Ligne 132 ⟶ 125 :
resultat = multiplier(variable1, variable2)
MsgBox resultat
</source>
Ligne 160 ⟶ 153 :
* Vous choisissez la catégorie «Personnalisées» et dans "Sélectionner une fonction:" vous choisissez la fonction crée
<br />
[[File:Insérer fonction.jpg|center|500px|center|Méthode d'Insertion une fonction personnalisée sous Excel]]
<br />
* Cliquez sur le bouton OK
* Vous obtenez une fenêtre intitulée "Arguments de la fonction". Ici, elle vous demande les ventes et l'ancienneté. Vous indiquez la cellule qui contient les ventes (exemple la cellule: B5) et l'ancienneté (exemple la cellule: C5).
<br />
[[File:Argument de la fonction.jpg|center|500px|center|Méthode de sélection d'un argument issu d'une fonction personnalisée sous Excel]]
<br />
== Description de la signature d'une fonction ==
Ligne 181 ⟶ 176 :
En effet, pour créer une procédure fonction, il faut respecter les étapes suivantes :
=== La portée d'une fonction ===
Ligne 202 ⟶ 197 :
Le nom de la fonction jouant le rôle d'une variable locale à la fonction, il est possible de l'utiliser tout au long de la procédure ce qui permet d'économiser la création d'une variable temporaire supplémentaire .
<source lang=vb>
Function MAFONCTION(Param1,... ParamN) As Double
MAFONCTION = Param1 + Param2 /5
Ligne 207 ⟶ 203 :
MAFONCTION = MAFONCTION ^ 2
End Function
</source>
Pour faciliter la création de fonction, le nom doit respecter des conventions standards d'affectation, comme :
*
* nommer les variables en commençant par un caractère alphabétique et ne pas excéder 255 caractères
* nommer les variables sans caractères spéciaux (#@+ ...), seul le caractère underscore _ est accepté
* donner des noms les plus explicites possibles afin de faciliter la relecture de votre programme
* Il est conseillé d'avoir au moins une majuscule dans la variable déclarée. Ensuite lors de la saisie de la variable en minuscule dans la macro, celle-ci reprendra automatiquement la majuscule: cette astuce permet de vérifier les fautes d'orthographe éventuelles.
Ligne 229 ⟶ 220 :
* Un mot clé spécifique à la déclaration d'arguments
* Le nom de l'argument
* Son type
* Éventuellement sa valeur
Ligne 240 ⟶ 228 :
* L'option '''Optional''' : indique que l'argument est facultatif. Tous les arguments facultatifs doivent être situés en fin de liste des arguments, et être de type Variant.
* L'option '''ByVal''' : indique que l'argument est passé par valeur.
* L'option '''ByRef''' : indique que l'argument est passé par référence. C'est l'option par défaut.
* Le mot clé '''ParamArray''' : utilisé uniquement comme dernier argument de la liste pour indiquer que celui-ci est un tableau facultatif d'éléments de type variant. Il ne peut être utilisé avec les mots clés ByVal, ByRef ou Optional.
* '''Type''' : précise le type de données de l'argument passé à la procédure (Byte,Integer, Long,..)
Cependant, il faut retenir que:
* les arguments peuvent être des variables , des constantes, des valeurs littérales ou des expressions
* Certaines fonctions n'ont pas d'argument
* Certaines fonctions
* Certaines fonctions ont une combinaison d'arguments requis et facultatifs
Ligne 267 ⟶ 248 :
La syntaxe est la suivante :
<source lang=vb>
Function nom_fonction(paramètre1, paramètre2,,....) AS type
instruction1
Ligne 272 ⟶ 254 :
nom_fonction=valeur_retour
End Function
</source>
Un nombre quelconque d'assignations de ce type peut apparaître n'importe où dans la procédure. Si aucune valeur n'est attribuée à l'argument nom_fonction, la procédure renvoie une valeur par défaut :
* Une fonction numérique renvoie la valeur 0
* Une fonction de type String renvoie une chaîne de longueur nulle ""
* Une fonction
* Une fonction de type
== Appel d'une fonction ==
Ligne 307 ⟶ 288 :
== Description personnalisée d'une fonction ==
Contrairement aux fonctions natives d'Excel ou VBA, les fonctions personnalisées n'ont aucune description. Pour cela, dans l’éditeur de macros, utiliser le raccourci clavier F2 pour afficher l'explorateur d'objets puis sélectionner "VBAproject" dans le menu déroulant
<br />
[[File:VBAPROJECT.jpg|center|500px|center|Sélection VBA Project]]
<br />
Ainsi, la fonction apparait dans la fenêtre de droite.
<br />
[[File:Fonction.jpg|center|500px|center|Fonction personnalisée]]
<br />
Faire un clic droit, Sélectionner l'option "Propriétés", Saisissez la description
<br />
[[File:Option.jpg|center|500px|center|Option de membre]]
<br />
Vous pouvez visualiser le résultat en affichant la boîte de dialogue "Insérer une fonction":
<br />
[[File:Descritpion fonction.jpg|center|500px|center|Description d'une fonction]]
<br />
== Les fonctions VBA personnalisées ==
Ligne 343 ⟶ 320 :
Grâce aux fonctions personnalisées, il est possible de créer une fonction permettant de calculer plus facilement le montant des intérêts à verser en fonction du montant du Capital, du taux d'intérêt et de la durée de l'emprunt.
<source lang=vb>
CalculerInteret = Format ( Capital * Taux / 100 * Duree), "Currency")
End Function
</source>
Le mot ''ByVal'' permet d'indiquer à la procédure qu'elle doit passer l'argument qui suit en valeur
* ''Long'' signifie que le capital est un nombre entier d'une grande valeur.
* ''Double'' signifie que le taux est un nombre à décimale.
* ''Integer'' indique que la durée est un nombre entier compris entre -32 768 et 32 767.
* ''Currency'' spécifie que le montant de l'intérêt calculé sera un nombre décimal de grande valeur.
Par exemple, pour un emprunt de 10000€ sur une durée de 1 an à un taux d'intérêt de 3% , le montant des intérêts peut se calculer par la formule : = 10000*(3/100)*1.
Ligne 367 ⟶ 339 :
En utilisant la fonction CalculerInteret, l'utilisateur calculera le montant des intérêts à payer directement même si les montants des capitaux et les taux d'intérêts sont différents.
<br />
[[File:CalculerInteret.jpg|400px|center|Capture ecran de la fonction VBA CalculerInteretSimple]]
<br />
=== Calcul d'un montant
<source lang=vb>
Function HT(Montant, TauxTva)
HT= (Montant / (100 + TauxTva) * 100)
End Function
</source>
La Fonction se nomme HT et dépend de deux informations, le montant TTC et le taux de TVA. Pour calculer le montant HT, la fonction va diviser le montant TTC par le taux de TVA auquel on aura ajouté 100, puis va divisé le résultat par 100. Par exemple pour un montant TTC de 12000€ et un taux de TVA à 20%, le calcul de montant Hors taxe se ferait par la formule : = (((12000/100)+20)*100). En utilisant la fonction HT, l'utilisateur pourra calculer le montant HT plus facilement et plus rapidement, surtout si les taux de TVA diffèrent d'un produit à l'autre.
<br />
[[File:HT.jpg|400px|center|Capture ecran de la fonction VBA HT]]
<br />
=== Calcul d'un montant TTC en fonction du Taux de TVA ===
<source lang=vb>
Function TTC(HorsTaxe, TauxTva)
TTC = (HorsTaxe + (HorsTaxe * TauxTva) / 100)
End Function
</source>
La Fonction se nomme TTC et dépend de deux informations, le montant HT et le taux de TVA. Pour calculer le montant TTC, la fonction va multiplié le montant HT par le taux de TVA puis va ajouté 100 au résultat obtenu, et enfin va divisé le résultat par 100. Par exemple pour un montant HT de 1000€ et un taux de TVA à 19,6%, le calcul de montant TTC se ferait par la formule : = ((1000+(1000*19,6))/100). En utilisant la fonction TTC, l'utilisateur pourra calculer le montant TTC plus facilement et plus rapidement.
<br />
[[File:FunctionTTC.jpg|400px|center|Capture de la fonction TTC]]
<br />
=== Calcul de la rentabilité globale ===
Cette fonction permettra de calculer la rentabilité globale d'une opération
<source lang=vb>
Function RentabilitéGlobale(Résultat_net, Valeur_Ajoutée)
RentabilitéGlobale = Résultat_net / Valeur_Ajoutée
End Function
</source>
La fonction a pour nom RentabilitéGlobale. Elle dépend de deux informations, le Résultat Net et la Valeur Ajoutée. La fonction va donc calculer la rentabilité en divisant le montant du Résultat Net par le montant de la Valeur Ajoutée. Par exemple, pour un Résultat net de 20000€ et une Valeur ajoutée de 10000, la fonction RentabilitéGlobale trouve une rentabilité de 2. Cela facilitera donc le travail de tous les comptables et autres gestionnaires.
<br />
[[File:Rentabilitéglobale.jpg|400px|center|Capture ecran de la fonction VBA RentabilitéGlobale]]
<br />
== Annexes ==
Ligne 426 ⟶ 395 :
=== Bibliographie ===
* Ouvrage
=== Liens internes ===
* Testez vos connaissances au travers d'un Quiz : [[Macros-commandes VBA/Quiz/QCM Les fonctions VBA]]
* Appliquez vos connaissances en résolvant l'exercice : [[Macros-commandes VBA/Exercices/Calcul d'une commission]]
* Gérez vos erreurs grâce à l'annexe gestion des erreurs : [[Macros-commandes VBA/
* Quelques fonctions financières existantes : [[Macros-commandes VBA/Annexe/Quelques fonctions Financières sous VBA]]
=== Liens externes ===
* Pour
* Cours complémentaires sur les procédures et les fonctions : [[Macros-commandes VBA/Création de Fonction|http://www.excel-pratique.com/fr/vba/procedures_fonctions.php]]
{{Bas de page
| |||