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 !