Vous vous êtes déjà trouvé en manque de formule dans Excel ? Vous avez des calculs spécifiques à votre activité et vous devez les calculées à chaque fois ? Sachez qu’il est possible de créer ses propres fonctions.
Commençons ce tuto excel sur les fonctions personnalisées
Accéder à l’éditeur Visual Basic
Vous devez afficher l’onglet « Développeur » dans le ruban Excel : « Fichier » à « Options » à « Personnalisé le ruban ».
Ensuite, cliquez sur le premier bouton du ruban : « Visual Basic »
Créer un module et une fonction
Dans l’explorateur de projet (CTRL + R pour l’afficher), faites un clic-droit puis sélectionnez « Insertion » à « Module »
Voici les règles avec un exemple concret pour construire une fonction :
Function MontantHT(Montant As Double, Optional TVA As Double = 20) As Double
MontantHT = Montant / (1 + TVA / 100)
End Function
Il faut toujours commencer par « Function » et finir par « End Function »
Ensuite, vous devez mettre un nom (assez explicite) pour la fonction et éventuellement des données d’entrées (ici : « Montant » et « TVA ».
Vous devez également indiquer le type des variables et du résultat de la fonction (ici, on veut des valeurs numérique).
Enfin, vous devez écrire votre calcul et reporter le résultat du calcul dans la fonction.
Vous n’avez plus qu’a écrire la fonction avec ses paramètres dans une cellule Excel :
Dans l’exemple, nous avons indiqué que la valeur TVA est optionnelle et nous avons défini sa valeur par défaut à 20. Donc, il est possible de ne pas renseigner le taux :
Quelques exemples
Voici une fonction qui permet de savoir si une date est dans une année bissextile :
Function Bissextile(Optional LaDate As Date = 99999) As Boolean
Dim div4 As Boolean, div100 As Boolean, div400 As Boolean
Dim Annee As Long
Annee = Year(IIf(LaDate = 99999, Date, LaDate))
div4 = Annee Mod 4 = 0
div100 = Annee Mod 100 = 0
div400 = Annee Mod 400 = 0
If div4 And Not div100 Then
Bissextile = True
Else
Bissextile = IIf(div400, True, False)
End If
End Function
Trouver le trimestre d’une date :
Function Trimestre(Optional LaDate As Date = 99999) As Byte
Trimestre = Int((Month(IIf(Ladate = 99999, Date, LaDate)) – 1) / 3) + 1
End Function
Trouver la couleur d’une cellule :
Function Couleur(Rng As Range) As Long
Application.Volatile True
Couleur = Rng.Interior.Color
End Function
Le Application.Volatile permet de mettre à jour le résultat de la fonction. En effet, l’ensemble des fonctions en VBA sont recalculées dès lors qu’une des données d’entrées est modifiées. Sauf qu’un changement de couleur de cellule n’est pas considéré comme une modification de la donnée d’entrée. Il faut donc « forcer » le recalcul de la fonction.
Compter le nombre de cellule selon une couleur :
Function NbCouleurs(Plage As Range, Cible As Range) As Long
Dim Rng As Range
For Each Rng In Plage
If Rng.Interior.Color = Cible.Interior.Color Then
NbCouleurs = NbCouleurs + 1
End If
Next Rng
End Function
Accessibilité des macros
Afin de profitez de vos macros / fonctions quelques soit le fichier ouvert, vous devez les enregistrées dans le classeur de macro personnelles :
Autre point important, si vous souhaitez diffuser un fichier avec une ou plusieurs fonctions à des collègues, ils ne pourront pas voir le résultat… En effet, ils n’ont pas accès à vos macros. Ils ont cette erreur :
Conclusion
Il est clair que la programmation en Visual Basic ouvre de grandes perspectives quant à l’utilisation d’Excel. Mais faites attention : car vous pouvez vite devenir accro !
Les Experts Excel vous proposent des formations, du développement, de l’expertise et de l’assistance sur Microsoft Excel. N’hésitez plus et contactez-les dès maintenant !