VBA- Fonctions NB.SI et NB.SI.ENS

Ce tutoriel vous montre comment utiliser les fonctions NB.SI (« COUNTIF ») et NB.SI.ENS (« COUNTIFS ») d’Excel en VBA

VBA n’a pas d’équivalent des fonctions NB.SI (« COUNTIF ») ou NB.SI.ENS (« COUNTIFS ») que vous pouvez utiliser – un utilisateur doit utiliser les fonctions Excel intégrées dans VBA en utilisant l’objet WorkSheetFunction.

Fonction NB.SI en VBA (COUNTIF) avec WorksheetFunction

L’objet WorksheetFunction peut être utilisé pour appeler la plupart des fonctions Excel qui sont disponibles dans la boîte de dialogue « Insérer une Fonction » dans Excel. La fonction NB.SI (COUNTIF en anglais) est l’une d’entre elles. Remarquez que dans le code VBA le nom anglais des fonctions doit être utilisé même votre Excel est configuré dans une autre langue.

Sub TestNbSi()
   Range("D10") = Application.WorksheetFunction.CountIf(Range("D2:D9"), ">5")
End Sub

La procédure ci-dessus ne comptera les cellules de la plage (D2:D9) que si elles ont une valeur égale ou supérieure à 5. Remarquez que, comme vous utilisez le signe supérieur à, le critère supérieur à 5 doit être entre parenthèses.

Affectation d’un Résultat COUNTIF à une Variable

Il se peut que vous souhaitiez utiliser le résultat de votre formule ailleurs dans le code plutôt que de le réécrire directement dans une plage Excel. Si c’est le cas, vous pouvez affecter le résultat à une variable que vous utiliserez plus tard dans votre code.

Sub AttribuerNbSiVariable()
    Dim résultat As Double
    
    'Assign the variable
    résultat = Application.WorksheetFunction.CountIf(Range("D2:D9"), ">5")
    
    'Show the résultat
    MsgBox "Le nombre de cellule avec une valeur supérieure à 5 est " & résultat
End Sub

Utilisation de NB.SI.ENS (COUNTIFS)

La fonction COUNTIFS (NB.SI.ENS en francais) est similaire à la fonction COUNTIF de la feuille de calcula, mais elle vous permet de vérifier plusieurs critères. Dans l’exemple ci-dessous, la formule comptera le nombre de cellules de D2 à D9 où le prix de vente est supérieur à 6 ET le prix de revient est supérieur à 5.

Sub UtilisationNbSiEns()
   Range("D10") = WorksheetFunction.CountIfs(Range("D2:D9"), ">6", Range("E2:E9"), ">5")
End Sub

Utilisation de COUNTIF avec un Objet Range

Vous pouvez attribuer un groupe de cellules à l’objet Range, puis utiliser cet objet Range avec l’objet WorksheetFunction.

Sub TestNbSiRange()
    Dim rngCompte As Range
     
    'Affectation de la plage de cellules
    Set rngCompte = Range("D2:D9")
    
    'Utilisation de la plage dans la formule
    Range("D10") = WorksheetFunction.CountIf(rngCompte, ">5")
    
    'Libère l'objet Range
    Set rngCompte = Nothing
End Sub

Utilisation de COUNTIFS avec Plusieurs Objets Range

De la même manière, vous pouvez utiliser COUNTIFS sur plusieurs Range Objects.

Sub TestNbSiEnsPlagesMultiples()
    Dim rngCritère1 As Range
    Dim rngCritère2 As Range
    
    'Affectez la plage de cellules
    Set rngCritère1 = Range("D2:D9")
    Set rngCritère2 = Range("E2:E10")
    
    'Utilisation des plages dans la formule
    Range("D10") = Application.WorksheetFunction.CountIfs(rngCritère1, ">6", rngCritère2, ">5")
    
    'Libère les objets Range
    Set rngCritère1 = Nothing
    Set rngCritère2 = Nothing
End Sub

Formule COUNTIF

Lorsque vous utilisez la fonction WorksheetFunction.COUNTIF pour ajouter une somme à une plage de votre feuille de calcul, vous obtenez une valeur statique et non une formule flexible. Cela signifie que lorsque vos chiffres dans Excel changent, la valeur qui a été renvoyée par la WorksheetFunction ne changera pas.

Dans l’exemple ci-dessus, la procédure a compté le nombre de cellules avec des valeurs dans Range(D2:D9) où le prix de vente est supérieur à 6, et le résultat a été placé dans D10. Comme vous pouvez le voir dans la barre de formule, ce résultat est un chiffre et non une formule.

Si l’une des valeurs change dans Range(D2:D9), le résultat en D10 ne changera PAS.

Au lieu d’utiliser la fonction WorksheetFunction.CountIf, vous pouvez utiliser VBA pour appliquer une fonction NB.SI à une cellule à l’aide des boutons Formule ou FormuleR1C1 pour appliquer une fonction NB.Si à une cellule.

Utilisation de la Méthode Formula

La méthode formula des objet Range vous permet de faire référence spécifiquement une plage de cellules, par exemple D2:D9, comme illustré ci-dessous.

Sous TestNbSi()
  Range("D10").Formula = "=COUNTIF(D2:D9, "">5"")"
End Sub

Méthode FormulaR1C1

La méthode FormulaR1C1 est plus souple, car elle ne vous limite pas à une plage de cellules donnée. L’exemple ci-dessous nous donnera la même réponse que celle ci-dessus.

Sub TestNbSi()
   Range("D10").FormulaR1C1 = "=COUNTIF(R[-8]C:R[-1]C, "">5"")"
End Sub

Cependant, pour rendre la formule encore plus flexible, nous pourrions modifier le code pour qu’il ressemble à ceci :

Sub TestNbSi() 
   ActiveCell.FormulaR1C1 = "=COUNTIF(R[-8]C:R[-1]C, "">5"")"
End Sub

Où que vous soyez dans votre feuille de calcul, la formule comptera les cellules qui répondent aux critères directement au-dessus d’elle et placera la réponse dans la cellule active. La plage dans la fonction COUNTIF doit être mentionnée en utilisant la syntaxe Row (R) et Column (C).

Ces deux méthodes vous permettent d’utiliser des formules Excel dynamiques dans VBA.

Il y aura maintenant une formule dans D10 au lieu d’une valeur.