Editions ENI Livres | Vidéos | e-Formations
Créer ses propres fonctions
Introduction
Excel 2019 propose un panel d’environ 400 fonctions intégrées. Si ce nombre peut paraître impressionnant, il arrive que le besoin de l’utilisateur ne soit pas satisfait malgré tout.
C’est le cas lorsque la "logique métier" est difficile, voire impossible à exprimer à l’aide des fonctions intégrées. Il se peut aussi que les fonctions intégrées ne couvrent pas complètement un domaine ou encore qu’une formule soit tellement longue que sa copie sur un grand nombre de cellules en vienne à augmenter considérablement la taille du classeur Excel.
L’environnement Visual Basic for Applications
Activer l’interface de développement (IDE)
L’accès à l’interface de développement à partir d’Excel n’est pas fourni par défaut. Cet accès n’est disponible que si l’onglet Développeur est visible.
Pour visualiser l’onglet Développeur :
Cliquez sur l’onglet Fichier.
Cliquez sur Options.
Dans la boîte de dialogue Options Excel, cliquez sur l’option Personnaliser le ruban.
Cochez Développeur dans la liste des onglets principaux.
Cliquez sur le bouton OK.
En cliquant sur l’onglet Développeur, puis, dans le groupe Code à gauche du ruban, sur le bouton Visual Basic, vous activez l’interface de développement VBA (IDE) qui va vous permettre de rédiger des fonctions personnalisées.
Outre les fonctions personnalisées, l’IDE permet aussi de rédiger des procédures d’automatisation de traitements Excel. Ces procédures sont habituellement nommées "macros". Le traitement des macros dans Excel 2019 n’est pas abordé ici. Il fait l’objet d’un autre ouvrage des Editions ENI "VBA Excel 2019" dans la collection Ressources informatiques.
Remarquez que VBA est une application distincte d’Excel : on peut facilement le vérifier dans...
La programmation en VBA
Le langage VBA est le langage de programmation Visual Basic 6 (VB6). Toutes les possibilités de ce langage sont donc utilisables. Si vous souhaitez approfondir vos connaissances en matière de programmation, vous pouvez donc vous reporter à l’abondante documentation VB6. De plus, l’IDE de VBA dispose d’une aide en ligne très bien faite. Pour y accéder, cliquez sur le bouton ? de la barre d’outils standard.
La procédure Function
La procédure Function est la seule qui soit étudiée dans le cadre de cet ouvrage. La forme de cette procédure est la suivante :
Function nom_fonction(Arg1 As type1, Arg2 As type2,µ...) As typefonc
-
[instructions]
-
nom_de_la_fonction=...
-
-[instructions]
-
nom_de_la_fonction=...
-
End Function Les mots en gras sont les mots-clés du langage VBA.
Entre parenthèses figurent les arguments de la fonction.
nom_fonction est le nom que vous choisissez pour votre fonction : il doit être explicite, ni trop long, ni trop court. Le nom commence obligatoirement par une lettre. Il ne doit pas être identique à un mot-clé VBA comme Dim ou Function par exemple. Pour éviter les problèmes, utilisez uniquement les lettres, les chiffres et le caractère souligné (_).
type1, type2 et typefonc sont les types des valeurs fournies comme arguments et obtenus comme résultat de la fonction : nombre entier, nombre en double précision, texte...
Comme dans Excel, les fonctions VBA peuvent être sans argument. Les parenthèses de la syntaxe doivent tout de même être présentes.
Entre Function et End Function les instructions de programmation, permettant de décrire la logique de traitement que doit suivre la fonction pour arriver au résultat, sont saisies. Ces instructions comprennent :
-
L’utilisation de variables
-
Les structures de tests
-
Les structures de boucles
Il est, de plus, conseillé d’inclure des commentaires dans le corps de la procédure. Les commentaires ne font pas partie des instructions....
Rédiger des fonctions personnalisées
Pour rédiger une fonction personnalisée, vous devez :
-
Choisir le type de la fonction.
-
Fixer les arguments de la fonction.
-
Programmer la logique "métier" à laquelle doit répondre la fonction.
Choisir le type de la fonction
Une fonction personnalisée renvoie un résultat. Comme pour une variable, le résultat doit être typé : Integer, Long, Boolean, String, etc.
Function Nom_Fonction(............) As typeRésultat Exemples
Function AccordPrêt(............) As Boolean
Function TauxIntérêt(............) As Double Fixer les arguments de la fonction
Les arguments facultatifs
Le plus souvent, la fonction possède un ou des arguments. Certains de ces arguments peuvent être facultatifs. Si c’est le cas, l’argument doit être précédé du qualificatif Optional. Dans le corps de la fonction, on teste la présence de l’argument facultatif par la fonction VBA IsMissing(). L’intérêt est de construire une fonction avec suffisamment d’arguments pour la rendre aussi générale que possible. Une telle option évite la multiplication de fonctions trop particulières.
Si l’un des arguments est qualifié en Optional, tous les arguments qui le suivent doivent eux-mêmes être de type Optional. Un argument Optional...
Exemples de fonctions personnalisées
Calcul du produit vectoriel
La fonction permettant de calculer un produit vectoriel est inexistante dans Excel. Il s’agit donc de combler cette lacune.
est un troisième vecteur tel que 
.La fonction ProduitVectoriel qui réalise ce calcul a pour arguments deux plages de 3 cellules représentant les coordonnées des vecteurs V1 et V2 avec obtention du résultat dans une plage de 3 cellules en colonne.
Function ProduitVectorielV(V1 As Range, V2 As Range) As Variant
' Déclaration d'un tableau à 3 lignes x 1 colonne
Dim t(1 To 3, 1 To 1) As Variant
' Calcul des coordonnées du vecteur résultat de V1^V2
t(1, 1) = V1.Cells(2) * V2.Cells(3) - V2.Cells(2) * V1.Cells(3)
t(2, 1) = V2.Cells(1) * V1.Cells(3) - V1.Cells(1) * V2.Cells(3)
t(3, 1) = V1.Cells(1) * V2.Cells(2) - V2.Cells(1) * V1.Cells(2)
ProduitVectoriel = t
End Function Voici un exemple d’utilisation de la fonction ProduitVectoriel dans Excel. Les formules sont naturellement écrites sous forme matricielle.
Astronomie : calcul du Jour Julien
En astronomie, le Jour Julien est une mesure de datation des événements astronomiques indépendante des calendriers en vigueur à différentes époques : grégorien (le plus utilisé), juif, musulman, chinois, etc. C’est un nombre de jours et éventuellement de fractions de jour. L’année d’origine est -4712.
Un Jour Julien commence à midi à Greenwich c’est-à-dire à 12h en Temps Universel.
Le but de la fonction JJ est de calculer le Jour Julien correspondant à une date et une heure du calendrier grégorien. Cette valeur sera présente dans une cellule sous une forme telle que :
(le format personnalisé utilisé en B43 est jj/mm/aaaa hh:mm:ss)
L’algorithme de calcul comprend les étapes suivantes :
-
Soit A, M et J respectivement l’année, le mois et le jour. Le jour peut être sous forme décimale.
-
Si M>2...
