Créer des fonctions personnalisées, Excel VBA

Créer des fonctions personnalisées, Excel VBA

Créer des fonctions personnalisées en VBA pour Excel (aussi appelées « User Defined Functions » ou UDF) permet d’ajouter des fonctionnalités spécifiques qui ne sont pas disponibles dans les fonctions intégrées d’Excel. Voici un exemple détaillé pour vous aider à comprendre comment créer des fonctions personnalisées en VBA, avec des explications étape par étape. 

Étape 1 : Accéder à l’éditeur VBA 

1. Ouvrez Excel. 
2. Appuyez sur Alt + F11 pour ouvrir l’éditeur VBA. 
3. Dans l’éditeur, cliquez sur Insertion dans le menu, puis sélectionnez Module pour insérer un nouveau module de code. 

Étape 2 : Écrire une fonction personnalisée simple 

Voici un exemple d’une fonction personnalisée qui calcule la somme de deux nombres. 

Code VBA pour la fonction : 

Function Additionner(a As Double, b As Double) As Double 
    Additionner = a + b 
End Function

Explication du code : 

1. Function Additionner(a As Double, b As Double) As Double : 

• Function déclare que vous allez créer une fonction. 
• Additionner est le nom de la fonction. 
• (a As Double, b As Double) sont les arguments de la fonction, ce qui signifie que vous allez entrer deux nombres (a et b) dans cette fonction. As Double indique que ces arguments sont des nombres à virgule flottante (c’est-à-dire des nombres décimaux). 
• As Double à la fin de la ligne indique que le type de retour de la fonction est également un nombre décimal (Double). 

2. Additionner = a + b : Cette ligne effectue l’addition de a et b et retourne le résultat dans la fonction. 

3. End Function : Cela marque la fin de la fonction. 

Étape 3 : Utiliser la fonction dans Excel 

1. Après avoir écrit votre code dans le module VBA, vous pouvez revenir à votre feuille Excel. 
2. Dans une cellule, tapez la fonction personnalisée comme une fonction Excel normale : 

=Additionner(5, 3) 

Cela affichera le résultat 8 dans la cellule. 

Étape 4 : Créer une fonction personnalisée avec des fonctionnalités supplémentaires 

Imaginons maintenant une fonction personnalisée qui calcule la moyenne de plusieurs nombres et qui permet d’ignorer les valeurs négatives. 

Code VBA pour la fonction avec condition : 

Function MoyennePositive(ParamArray valeurs() As Variant) As Double 
    Dim somme As Double 
    Dim compteur As Integer 
    Dim i As Integer 
    somme = 0 
    compteur = 0 
    ' Parcours de toutes les valeurs dans le tableau 
    For i = LBound(valeurs) To UBound(valeurs) 
        If valeurs(i) > 0 Then 
            somme = somme + valeurs(i) 
            compteur = compteur + 1 
        End If 
    Next i  
    ' Si aucun nombre positif, on renvoie 0 
    If compteur > 0 Then 
        MoyennePositive = somme / compteur 
    Else 
        MoyennePositive = 0 
    End If 
End Function

Explication du code : 

1. Function MoyennePositive(ParamArray valeurs() As Variant) As Double : 

• ParamArray permet de passer un nombre variable d’arguments à la fonction, ce qui est utile pour accepter plusieurs valeurs. 
• valeurs() est un tableau de type Variant, ce qui signifie qu’il peut contenir différents types de données (par exemple, des entiers, des décimaux, etc.). 

2. somme et compteur : Ces variables sont utilisées pour stocker la somme des valeurs positives et le nombre de valeurs positives respectivement. 

3. For i = LBound(valeurs) To UBound(valeurs) : Cette boucle parcourt tous les éléments du tableau valeurs. LBound et UBound donnent respectivement les indices du premier et du dernier élément du tableau. 

4. If valeurs(i) > 0 Then : Si la valeur est positive, elle est ajoutée à la somme, et le compteur est incrémenté. 

5. MoyennePositive = somme / compteur : Si des nombres positifs ont été trouvés, la fonction retourne la moyenne des valeurs positives. Sinon, elle retourne 0. 

Étape 5 : Utilisation de la fonction dans Excel 

1. Revenir à la feuille Excel et saisir la fonction dans une cellule avec plusieurs valeurs, par exemple : 

=MoyennePositive(5, -3, 2, 8, -1) 

Cela renverra la moyenne des valeurs positives, c’est-à-dire (5 + 2 + 8) / 3 = 5. 

Étape 6 : Autres exemples de fonctions personnalisées 

Voici quelques autres exemples pour montrer la flexibilité de VBA : 

1. Fonction qui calcule le carré d’un nombre :                                                                                         

   Function Carre(x As Double) As Double 
       Carre = x * x 
   End Function

2. Fonction qui vérifie si un nombre est pair ou impair :

Function EstPair(x As Double) As String 
    If x Mod 2 = 0 Then 
        EstPair = "Pair" 
    Else 
        EstPair = "Impair" 
    End If 
End Function

Étape 7 : Gérer les erreurs dans une fonction personnalisée 

Il est souvent utile de gérer les erreurs dans les fonctions VBA. Voici un exemple où la fonction vérifie si l’utilisateur entre des valeurs valides (non nulles et numériques). 

Function Diviser(a As Double, b As Double) As Double 
    On Error GoTo Erreur 
    If b = 0 Then 
        Diviser = "Erreur: Division par zéro" 
        Exit Function 
    End If 
    Diviser = a / b 
    Exit Function 
Erreur: 
    Diviser = "Erreur: Entrée invalide" 
End Function

Dans cet exemple, si l’utilisateur tente de diviser par zéro ou entre une valeur invalide, un message d’erreur s’affiche. 

Conclusion 

Créer des fonctions personnalisées en VBA pour Excel permet d’étendre les capacités d’Excel et d’automatiser des calculs spécifiques à vos besoins. Vous pouvez utiliser des arguments multiples, des conditions, des boucles et même gérer les erreurs dans vos fonctions pour les rendre plus robustes. Ces fonctions peuvent être utilisées dans les cellules Excel comme les fonctions intégrées.