Cours VBA : utilisations des fonctions Excel

Il est possible d'utiliser les fonctions Excel dans le code VBA.
Après avoir entré WorksheetFunction, la liste des fonctions apparaît :
Les fonctions sont toutes en anglais ...
Pour s'y retrouver, une liste des fonctions traduites en anglais est disponible sur ce site, en voici un extrait :

Fonction en français	Fonction en anglais	Description de la fonction
NB	COUNT	détermine les nombres compris dans la liste des arguments.
NB.SI	COUNTIF	compte le nombre de cellules qui répondent à un critère donné dans une plage.
NB.SI.ENS	COUNTIFS	compte le nombre de cellules à l’intérieur d’une plage qui répondent à plusieurs critères.
NB.VIDE	COUNTBLANK	compte le nombre de cellules vides dans une plage.
NBVAL	COUNTA	détermine le nombre de valeurs comprises dans la liste des arguments.

La fonction choisie pour cet exemple est donc NB.VIDE.
Dans l'exemple suivant, le nombre de cellules vides de la plage "A1:D8" est enregistré dans la variable puis affiché dans la boîte de dialogue :

Sub test()
    var_test = WorksheetFunction.CountBlank(Range("A1:D8"))
    MsgBox var_test
End Sub

 

 

Cours VBA : créer une fonction personnalisée

 

 

Pour cet exemple, nous allons créer une fonction SI personnalisée utilisable sur une feuille de calcul comme n'importe quelle autre fonction.
Pour afficher une valeur (B2) si une cellule (C2) contient une valeur ("OUI"), nous pouvons utiliser la fonction SI avec la formule =SI(C2="OUI";B2;0) :

L'objectif ici est de créer une fonction capable de faire cela =SI(C2 a un fond vert;B2;0) que nous écrirons comme ceci : =SI_VERT(C2;B2) :

Développer la fonction personnalisée

Commencez par créer votre fonction :

Function SI_VERT(paye As Range, montant)
   
End Function

Les arguments :

• paye As Range : la cellule à tester
• montant : la valeur à entrer si VRAI

Dans le cas présent, si le test est FAUX, la valeur sera 0 à chaque fois, il est donc inutile d'avoir un argument pour cela.

Pour vérifier si la couleur est correcte, vous pouvez utiliser une cellule contenant la bonne couleur comme point de comparaison :

Function SI_VERT(paye As Range, montant)

    couleur = Sheets("Feuil1").Range("K1").Interior.color

End Function

Mais pour éviter de dépendre d'une cellule, nous allons utiliser ici le n° de la couleur qui nous intéresse :

Function SI_VERT(paye As Range, montant)

    couleur = 5296274 'Vert

End Function

Pour connaître le n° de couleur de fond d'une cellule, sélectionnez la cellule et exécutez cette macro :

Sub test_color()
    MsgBox ActiveCell.Interior.color
End Sub

Il ne reste plus qu'à tester la couleur avec If :

Function SI_VERT(paye As Range, montant)
   
    Application.Volatile
   
    couleur = 5296274 'Vert
 
    If paye.Interior.color = couleur Then 'Si VRAI
       SI_VERT = montant
    Else 'Si FAUX
       SI_VERT = 0
    End If
   
End Function

La fonction est maintenant prête à l'emploi.
Application.Volatile indique que la fonction est volatile (comme c'est le cas pour la fonction SI), ce qui signifie qu'elle doit être recalculée à chaque changement de valeur. Par exemple, si vous modifiez l'un des montants (ou n'importe quelle autre cellule), la fonction est recalculée est affichera le bon montant.
En revanche, la modification de la couleur de fond ne déclenche pas cette mise à jour. Pour recalculer les valeurs sans attendre, vous pouvez par exemple appuyer sur "Delete" en sélectionnant une cellule vide ou ajouter un bouton "Actualiser" pour tout recalculer d'un clic :

Sub actualiser()
    Application.Calculate
End Sub

Compléments

Une autre manière d'écrire cette même fonction :

Function SI_VERT(paye As Range, montant)
    Application.Volatile
    SI_VERT = 0 'Si FAUX
    If paye.Interior.color = 5296274 Then SI_VERT = montant 'Si VRAI
End Function

Vous pouvez télécharger le fichier qui a servi d'exemple en cliquant ici.