Macros-commandes VBA/Création de Fonction

**Création de Fonction**
Leçon : Macros-commandes VBA

Chapitre n^o 10
Chap. préc. :	Création de Formulaire
Chap. suiv. :	Création de Graphique
Quiz :	QCM Les fonctions VBA
Exercices :	Calcul d'une commission
Annexe :	La gestion des erreurs
Annexe :	Quelques fonctions Financières sous VBA

En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Création de Fonction
Macros-commandes VBA/Création de Fonction », n'a pu être restituée correctement ci-dessus.

Création d'une fonction

Définition

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

Différence entre les mots clés Sub et Fonction

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

FONCTION :

  Function carre(nombre)
     carre = nombre ^ 2 'La fonction "carre" renvoie la valeur de "carre"
  End Function

PROCEDURE :

   Sub macro_test()
    Dim resultat As Double
    resultat = carre(9.876) 'La variable resultat reçoit la valeur retournée par la fonction
    MsgBox resultat 'Affiche le résultat (ici, le carré de 9.876)
   End Sub

Utilité des fonctions personnalisées

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.

En effet, les fonctions personnalisées sont utiles dans les formules de feuille de travail Excel et les procédures VBA. Les fonctions personnalisées simplifient le travail et permettent à l’utilisateur de gagner du temps.

Par exemple, la création d'une fonction personnalisée capable de raccourcir les formules de manière significative.

Exemple

Supposons le cas d'un calcul de prime tel que le niveau de prime dépende du montant des ventes.

Sous Excel, on utiliserait la fonction : =SI(B5>1000;B5*3/100;SI(B5>800;B5*1/100;B5*0,5/100)).

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) :

  Function calculPrime(montant as Double) as Double
     If montant>1000 then
       calculPrime=montant*3/100
     ElseIf montant>800 then 
       calculPrime=montant*1/100
     Else
        calculPrime=montant*0.5/100
     End If
  End Function

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

Pour créer une fonction Excel sous VBA plusieurs étapes sont nécessaires :

Ajouter le menu "Développeur" au ruban Excel s'il n'apparait pas
Ouvrir l’éditeur VBA en sélectionnant la case "Afficher l'onglet Développeur dans le ruban"
Entrer dans l'éditeur VBA et sélectionner l'icône "Visual Basic".
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)

Instruction de création de fonction sous VBA

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 :

Structure :

Début
 déclarations de variables
 séquences d'actions
 renvoi d'une valeur
Fin

Exemple :

 Function diviser_par_1000(Cellule_a_diviser As Double)
  diviser_par_1000 = Cellule_a_diviser / 1000
 End Function

Conseil

Le nom du module courant peut être retrouvé par ses fonctions ainsi :

 MsgBox Application.VBE.ActiveCodePane.CodeModule.Name

Utilisation d'une fonction personnalisée sous VBA

La création de formules dans Visual Basic est un outil important si l’on souhaite personnaliser son outil ou utiliser dans des procédures des calculs nécessitant une formule non présente dans Excel.

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 :

  Sub AppelFonction()
    Dim variable1 As Byte, variable2 As Byte
      variable1 = 3
      variable2 = 5
      resultat = variable1 * variable2
      MsgBox resultat
  End Sub

il est aussi conseillé de décrire ce que fait la fonction

  ' déclarer les variables
   Dim variable1 As Byte, variable2 As Byte
  ' initialiser les constantes
      variable1 = 3
      variable2 = 5
  ' récupérer le résultat saisi par l'opérateur
      resultat = variable1 * variable2
      MsgBox resultat
    End Sub

Enfin, fermer la fenêtre appuyer sur la touche F5 pour lancer la macro.

Utilisation d'une fonction personnalisée sous Excel

La fonction nouvellement créée est enregistrée dans Excel et peut être utilisée de la même manière que toutes les autres fonctions initiales d'Excel.

Sélectionnez la cellule où vous désirez voir apparaître le résultat de la fonction (exemple la cellule: D5).
Cliquez sur Insérer une fonction du Menu principal
Vous obtenez la fenêtre «Insérer une fonction»
Vous choisissez la catégorie «Personnalisées» et dans "Sélectionner une fonction:" vous choisissez la fonction crée

Méthode d'Insertion une fonction personnalisée sous Excel

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).

Description de la signature d'une fonction

Les signatures des fonctions (1ère ligne correspondant à la déclaration de la fonction) peuvent être beaucoup plus complexes, et leur analyse peut être utile à la compréhension de leur comportement.

Structure :

[Public | Private | Friend] [Static] Function name [(liste d'arguments)] [As type]
 (séquences d'instructions)
End Function

En effet, pour créer une procédure fonction, il faut respecter les étapes suivantes :

déterminer la portée de la fonction
déclarer la procédure en fonction avec le mot clé: Function, suivi du nom de la procédure
définir les arguments en les indiquant entre parenthèse après le nom de la procédure
préciser le type de la valeur retournée après le mot clé As

La portée d'une fonction

La notion de portée, parfois appelée visibilité, défini les limites d’accessibilité d'une variable. Il existe plusieurs instructions de déclarations selon la portée désirée et la déclaration ne se fait pas au même endroit. Et leurs utilisations sont facultatives.

Public : une fonction créée est par défaut de type Public. Il n'est donc pas nécessaire d'écrire le mot Public devant l'instruction Function. La fonction sera alors accessible depuis tous les modules.
Private : pour rendre une fonction utilisable uniquement par les procédures VBA du module dans lequel elle est déclarée, il faut faire précéder le mot Function par le mot Private. Cette fonction pourra alors être utilisée dans la feuille de calcul mais n'apparaitra pas dans la boîte de dialogue "Insérer les fonctions".
Friend : La fonction sera alors accessible depuis tous les modules du projet en cours, le mot clé Friend est utilisé uniquement dans un module de classe.

Le nom de la fonction

Dans VBA, le nom de la fonction sert à la fois à l'identifier et à stocker la valeur de retour. Mais, il peut aussi être considéré comme une variable locale à la fonction, créée automatiquement lors de l'appel, et utilisable à ce titre comme toute autre variable locale.

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 .

 Function MAFONCTION(Param1,... ParamN) As Double
   MAFONCTION = Param1 + Param2 /5
   ' [...]
   MAFONCTION = MAFONCTION ^ 2
 End Function

Pour faciliter la création de fonction, le nom doit respecter des conventions standards d'affectation, comme :

nommer les variables en évitant les mots clés ou instructions réservés par Excel (par exemple Val, Left...)
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.

Les arguments de la fonction

Les arguments se déclarent lors de la définition de la procédure.

Les paramètres suivent les règles de déclarations suivantes :

Un mot clé spécifique à la déclaration d'arguments
Le nom de l'argument
Son type
Éventuellement sa valeur

Déclaration :

 [Optional] [ByVal] [ByRef] [ParamArray] variable As Type

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 ont un nombre fixe d'arguments requis (de 1 à 60)
Certaines fonctions ont une combinaison d'arguments requis et facultatifs

En effet, les fonctions sont constituées d'arguments obligatoires et optionnels. Comme leur nom l'indique, les arguments obligatoires sont nécessaires au bon fonctionnement de la procédure. Quant aux arguments optionnels, lorsqu’ils sont omis, cela n'empêche pas l'exécution de la macro. Les arguments optionnels sont obligatoirement placés en fin de la déclaration.

La valeur de retour de la fonction

Contrairement à une procédure, une fonction retourne un résultat. Ainsi, l'option As type permet de spécifier le type de la valeur retournée.

La syntaxe est la suivante :

  Function nom_fonction(paramètre1, paramètre2,,....) AS type
    instruction1
    instruction2.....
   nom_fonction=valeur_retour
  End Function

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 de type Variant, la valeur Empty
Une fonction de type Object renvoie Nothing

Appel d'une fonction

VBA comporte des fonctions financières et mathématiques. En effet, dans l'explorateur d'objet et à l'intérieur de la librairie VBA, on peut trouver la liste des fonctions.

Néanmoins, pour les fonctions mathématiques, le nombre de fonctions peut être très modeste. Ceci est évidemment la conséquence du fait que l’on a déjà à notre disposition l’ensemble des fonctions dans la feuille de travail Excel.

Pour faire appel à ces fonctions sous VBA, il convient soit de taper Application.WorksheetFunction ou plus simplement WorksheetFunction en appliquant à ceci soit un nombre, soit une variable, soit une plage.

Ainsi par exemple, pour calculer la racine carrée de 25, on pourra si l’on utilise la fonction Racine (Sqrt en anglais) d'Excel, soit taper directement :

WorksheetFunction.Sqrt(25)

soit si 25 est par exemple la valeur de la cellule B4 :

WorksheetFunction.Sqrt(Range(B4))

ou encore :

WorksheetFunction.Sqrt(Cells(4,2))

soit si 25 est la valeur affectée à une variable de type Double appelé x :

WorksheetFunction.Sqrt(x)

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

Ainsi, la fonction apparait dans la fenêtre de droite.

Faire un clic droit, Sélectionner l'option "Propriétés", Saisissez la description

Vous pouvez visualiser le résultat en affichant la boîte de dialogue "Insérer une fonction":

Les fonctions VBA personnalisées

VBA offre la possibilité de créer des fonctions personnalisées, en voici quelques exemples :

Calcul des intérêts

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.

  Function CalculerInteret (ByVal Capital As Long, ByVal Taux As Double, ByVal Duree As Integer) As Currency
    CalculerInteret = Format ( Capital * Taux / 100 * Duree), "Currency")
  End Function

Le mot clé Function indique le début de la fonction qui a pour nom CalculerInteret, le mot clé End Function indique la fin de la fonction 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.

Cette fonction calcule le montant des intérêts en multipliant le capital par la durée et par le taux d'intérêt divisé par 100 (exemple: 5/100 si le taux est de 5%).

Par exemple, pour un emprunt de 10 000 € 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.

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.

Calcul d'un montant HT en fonction du Taux de TVA

Cette fonction est une fonction personnalisée qui permet de calculer le montant Hors Taxe en fonction du montant TTC et du taux de TVA

  Function HT(Montant, TauxTva)
    HT= (Montant / (100 + TauxTva) * 100) 
  End Function

La Fonction se nomme HT et dépend de deux informations, le montant et le taux de TVA. Pour calculer le montant HT, la fonction va diviser le montant par le taux de TVA auquel on aura ajouté 100, puis va diviser le résultat par 100. Par exemple pour un montant de 12 000 € 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.

Calcul d'un montant TTC en fonction du Taux de TVA

Cette fonction permettra de calculer le montant TTC en fonction du montant Hors taxe et du taux de TVA

  Function TTC(HorsTaxe, TauxTva) 
    TTC = (HorsTaxe + (HorsTaxe * TauxTva) / 100)
  End Function

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 multiplier le montant HT par le taux de TVA puis va ajouter 100 au résultat obtenu, et enfin va diviser le résultat par 100. Par exemple pour un montant HT de 1 000 € 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.

Calcul de la rentabilité globale

Cette fonction permettra de calculer la rentabilité globale d'une opération

  Function RentabilitéGlobale(Résultat_net, Valeur_Ajoutée)
    RentabilitéGlobale = Résultat_net / Valeur_Ajoutée
  End Function

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 20 000 € et une Valeur ajoutée de 10 000, la fonction RentabilitéGlobale trouve une rentabilité de 2. Cela facilitera donc le travail de tous les comptables et autres gestionnaires.

Annexes

Bibliographie

Ouvrage complet sur l’utilisation d'EXCEL et VBA dans le domaine financier ==> Chelali HERBADJI, 2012, "La gestion sous EXCEL et VBA : Techniques quantitatives de gestion", Groupe Eyrolles