VBA – Fonction NB en VBA (Count)

Ce tutoriel montre comment utiliser la fonction NB d’Excel en VBA

La fonction NB d’Excel est accessible dans VBA en utilisant worksheetfunction. La fonction NB est utilisée pour compter le nombre de cellules de votre feuille de calcul qui contiennent des valeurs numériques. On y accède à l’aide de la méthode WorksheetFunction en VBA en utilisant son nom anglais soit COUNT. Les noms des fonctions accessible par l’objet WorksheetFunction doivent être utilisé en anglais dans le code VBA.

Fonction COUNT de l’Objet WorksheetFunction

L’objet WorksheetFunction peut être utilisé pour appeler la plupart des fonctions Excel disponibles dans la boîte de dialogue Insérer une Fonction d’Excel. La fonction COUNT est l’une d’entre elles.

Sub TestFonctionCount
  Range("D33") = Application.WorksheetFunction.Count(Range("D1:D32"))
End Sub

Vous pouvez avoir jusqu’à 30 arguments dans la fonction COUNT. Chacun de ces arguments doit faire référence à une plage de cellules. L’exemple ci-dessous compte combien de cellules contiennent des valeurs numériques situées dans la plage de cellules D1 à D9

Sub TestCount()   
   Range("D10") = Application.WorksheetFunction.Count(Range("D1:D9"))
End Sub

L’exemple ci-dessous compte combien de valeurs numériques se trouvent dans une plage de la colonne D et dans une plage de la colonne F. Si vous ne tapez pas l’objet Application, il sera assumé par défaut.

Sub TestCountMultiple()
   Range("G8") = WorksheetFunction.Count(Range("G2:G7"), Range("H2:H7"))
End Sub

Affectation d’un Résultat de la Fonction Count à 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 AssignerCount()
    Dim résultat As Integer
    ' Affectation de la variable
    résultat = WorksheetFunction.Count(Range("H2:H11"))
    ' Afficher le résultat
    MsgBox "Le nombre de cellules qui contiennent des valeurs est " & résultat
End Sub

Fonction COUNT Avec un Objet Range

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

Sub TestCountRange()
    Dim rng As Range
    ' Affectation de la plage de cellules
    Set rng = Range("G2:G7")
    ' Utilisation de la plage dans la formule
    Range("G8") = WorksheetFunction.Count(rng)
    ' Libère l 'objet plage
    Set rng = Nothing
End Sub

Compter des Objets de Plage Multiples

De la même manière, vous pouvez compter les cellules contenant des valeurs numériques dans plusieurs objets Range.

Sub TestCountRangeMultiples()
    Dim rngA As Range
    Dim rngB As Range
    ' Affectez la plage de cellules
    Set rngA = Range("D2:D10")
    Set rngB = Range("E2:E10")
    ' Utilisez la plage dans la formule
    Range("E11") = WorksheetFunction.Count(rngA, rngB)
    ' Libère l 'objet plage
    Set rngA = Nothing
    Set rngB = Nothing
End Sub

Utilisation de NBVAL en VBA (COUNTA)

La fonction NB d’Excel (COUNT en anglais) compte les cellules qui contiennent des valeurs numériques seulement sans compter celles qui contiennent du texte. Pour compter les cellules qui contiennent des valeurs incluant des valeurs de type texte dans VBA, nous devons utiliser la fonction COUNTA qui est l’équivalent de la fonction NBVAL en anglais.

Sub TestCountA()
   Range("B8) = Application.WorksheetFunction.CountA(Range("B1:B6")) 
End Sub

Dans l’exemple ci-dessous, la fonction COUNT renverrait un zéro car il n’y a aucune valeur numérique dans la colonne B, tandis qu’elle renverrait un 4 pour la colonne C. La fonction COUNTA, en revanche, compterait les cellules contenant du texte et renverrait une valeur de 5 dans la colonne B tout en renvoyant une valeur de 4 dans la colonne C.

Utilisation de la Fonction NB.VIDE en VBA (COUNTBLANKS)

La fonction NB.VIDE (COUNTBLANKS en anglais) ne compte que les cellules vides de la plage de cellules, c’est-à-dire les cellules qui ne contiennent aucune donnée.

Sub TestCountBlank()   
   Range("B8) = Application.WorksheetFunction.CountBlanks(Range("B1:B6")) 
End Sub

Dans l’exemple ci-dessous, la colonne B ne contient aucune cellule vierge, tandis que la colonne C en contient une.

Utilisation de la Fonction NB.SI (COUNTIF)

Une autre fonction de feuille de calcul qui peut être utilisée est la fonction NB.SI (COUNTIF en anglais).

Sub TestCountIf()
   Range("H14") = WorksheetFunction.CountIf(Range("H2:H10"), ">0")
   Range("H15") = WorksheetFunction.CountIf(Range("H2:H10"), ">100")
   Range("H16") = WorksheetFunction.CountIf(Range("H2:H10"), ">1000")
   Range("H17") = WorksheetFunction.CountIf(Range("H2:H10"), ">10000")
End Sub

La procédure ci-dessus ne comptera les cellules contenant des valeurs que si les critères sont remplis – supérieur à 0, supérieur à 100, supérieur à 1000 et supérieur à 10000. Vous devez mettre les critères entre guillemets pour que la formule fonctionne correctement.

Inconvénients de WorksheetFunction

Lorsque vous utilisez la WorksheetFunction pour compter les valeurs d’une plage dans votre feuille de calcul, une valeur statique est renvoyée, et non une formule dynamique. Cela signifie que lorsque vos chiffres dans Excel changent, la valeur renvoyée par la WorksheetFunction ne change pas.

Dans l’exemple ci-dessus, la procédure TestCount a compté les cellules de la colonne H où une valeur numérique est présente. 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 donc dans la plage (H2:H12), les résultats en H14 NE changeront PAS.

Au lieu d’utiliser la fonction WorksheetFunction.Count, vous pouvez utiliser VBA pour appliquer une formule NB à une cellule à l’aide des méthodes Formula ou FormulaR1C1.

Utilisation de la Méthode Formula

La méthode de la Formula vous permet de spécifier une plage de cellules, par exemple H2:H12, comme illustré ci-dessous.

Sub TestCountFormule()
  Range("H14").Formula = "=COUNT(H2:H12)"
End Sub

Utilisation de la Méthode FormulaR1C1

La méthode FromulaR1C1 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 TestCountFormula()
   Range("H14").Formula = "=COUNT(R[-9]C:R[-1]C)"
End Sub

Cependant, pour rendre la formule plus flexible, nous pourrions modifier le code de la manière suivante :

Sub TestCountFormula() 
   ActiveCell.FormulaR1C1 = " =COUNT(R[-11]C:R[-1]C) " 
End Sub

Où que vous vous trouviez dans votre feuille de calcul, la formule comptera les valeurs dans les 12 cellules situées directement au-dessus de la cellule active et placera la réponse dans celle-ci. La plage dans la fonction COUNT 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 H14 au lieu d’une valeur.